Crosstab Query with Date Fields

[mostlySimple]

Pulling my hair out!
Ok here's what I got:
I need to output 2 queries with 2 different datefields with different Years (2
max) in tabular format
EXAMPLE:
2004 2005
Jan Feb March April ...etc Jan Feb March April ...etc
2 3 0 5 0 1 3 8


Total count by month and year will be placed under appropriate colum.

I can't get it to work. Anyone have any insight to this one?

[jorgepino]

can you post some code...?

[dempster]

It's hard to tell without some code exactly what you are trying to do. You can
output queries to put data or totals into arrays, then use those arrays to
display the output. You can also have your output append text to a variable and
then display that variable to the web page. You can even output the same query
more than once to format different parts of your page.

Give us some more details about what you are doing.

-Paul

[mostlySimple]

Sorry I rushed that initial statement. So here's a more detailed spec.

-User inputs 1 date
-The report will show Individuals on the left side and months across the top
(maximum of 24 months or 2 year window)
-The data under each month will be the count of Individuals with ?A Date?
equal to the calendar month
-But individual dates to left (dates less than the 1 date input) of the 1 date
input will represent a different date field from database than the date results
to the right (dates greater than the 1 date input).

I initially tried to take the results of a query pick it apart using arrays
came close just couldn't spilt which date sets to show from left or right of
date input. I don't know how to write a query to produce results in a tabular
format.

Hope that was a little clearer than the first time around. Thanks in advance
for any help you may provide.
\
webs

[mostlySimple]

User inputs 1 date
-The report will show Individuals on the left side and months across the top
(maximum of 24 months or 2 year window)
-The data under each month will be the count of Individuals with ?A Date?
equal to the calendar month
-But individual dates to left (dates less than the 1 date input) of the 1 date
input will represent a different date field from database than the date results
to the right (dates greater than the 1 date input).

I initially tried to take the results of a query pick it apart using arrays
came close just couldn't spilt which date sets to show from left or right of
date input. I don't know how to write a query to produce results in a tabular
format.

Heres a query used to pull the data:

<cfquery name="t">
Select t1.date1,t1.date1,t2.fname,t2.lname
FROM table1 t1, table2 t2_referral ar
WHERE t1.userID = t2.userID
</cfquery>

Yes 1 input date. date1 less than input date and date2 greater than input date.

The data under each month will be the count of Individuals with ?A date1 or
date2? equal to the calendar month

Range of 2 years or 24 months.

[MikerRoo]

You can use a brute-force method and just loop through each table cell running
a query -- or query of queries.

In order to get all the data at once, you will want to use SQL techiniques
like grouping tables or user defined functions.

[url]http://www.sswug.org/see/21626[/url] is an excellent article (paid subscription
required).

[mostlySimple]

ha, ha...thats a good idea.