Data Manipulation Techniques

[MeltdownMX]

Hi, I am working on an Application that pulls data from a variety of tables.
I would like to run monthly reports and all of the queries will change from
month to month based on whatever Month the user selects. Things are involved
like Calendar running year information and fiscal year information so every
month will be different for example: an FY report for December 2004 (which is
avail Jan 1 2005) will include a date range from Oct 1 - Dec 31 2004 and a
calendar year report for August 2004 (which is avail Sep 1 2004) will include
a date range from Jan 1 - Aug 31 2004. What I havent been able to find are
techniques or examples used to help me construct these queries so that they are
dynamic and wont have to be changed every month by hand. They will simply
changed based on whatever the user selects from lets say a drop-down menu or
inputting a month/year to run the report they want. I have queries that work,
but we have to change them every month for the different dates by hand. Does
anyone have any information to get me started on the right track?? If not any
other websites that explain how to do this would be greatly appreciated. I
always love a good tutorial. Thanks!!

[mike.short]

You can include ColdFusion code in your SQL between the <cfquery> tags. For
example, if you wanted to select by the value of a variable Request.ID, the
code might look like this: <cfquery name='qTest' datasource='XXX'> SELECT *
FROM PERSON WHERE ID = '#Request.ID#' </cfquery> The WHERE clause can
even be built up from a series of <CFIF> tags, so that the select can be highly
variable based on user-inputted values.

[MeltdownMX]

Hi Mike, thanks. I know how to do all that already. Maybe I am making it more
complicated than it is. There are several date functions in SQL and in CF. I
didnt know if anyone knows some good techniques in using them to generate
reports or websites / tutorials that focuses on that kind of stuff. I would
like to use the power of SQL to do all the fetching for me instead of
incorporating tons of if statements and things like that.

[dempster]

ColdFusion does have a full range of date functions that you can use in this
situation. You need to develop an algorithm that takes the user's choices and
translates that into an appropriate SQL statement. Based on your example, it
looks like a FY report runs from Oct 1 through the end of the month selected,
and it will be available the following month. So your form that lets the user
make selections can be programmed to get the current date and then display the
previous month back to the earliest report as options. The program that
processes this can take that month and year and create a date object that is
the last day of the month at 11:59 pm. You then use this information in a SQL
statement to select dates BETWEEN your start date (Oct 1) and this end date.
You'd use the same technique for an annual report. If this is what you are
trying to do and you need more explanation, please let me know. Paul Dempsey
Dickinson College