earwig75
asked on
Output report with coldfusion
I have a table with some columns: "UnQID" "UserID" "CompDate" "ModDate"
I would like to output a query that will show a list of UserIDs and a count of how many CompDates or Moddates they appear in for a given timespan. Could someone assist?
I would provide the date range into a query in a cfc with arguments. I'd like it to display a YTD or a given range. Any ideas?
It would look something like this:
Month: Dec
-----------
Ralph 25
Jonathan 75
Roberto 58
I would like to output a query that will show a list of UserIDs and a count of how many CompDates or Moddates they appear in for a given timespan. Could someone assist?
I would provide the date range into a query in a cfc with arguments. I'd like it to display a YTD or a given range. Any ideas?
It would look something like this:
Month: Dec
-----------
Ralph 25
Jonathan 75
Roberto 58
ASKER
Basically you can think of them as orders. How many orders does each person complete a day. ModDate is the date an order is created. CompDate is a date the order is completed.
I'd like to a table to show how many were created by each person based on those dates.
I'd like to a table to show how many were created by each person based on those dates.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
So I can use declare in a cfquery?
yes
I'm in the habit of doing it this way so that I can create a stored procedure and send in var values to it that originally may be CF vars.
If you are doing a CF Query right on the same page as your code you could just simplify and do something like :
BETWEEN #CreateODBCDateTime(Sdate) # and #CreateODBCDateTime(Edate) #
and in this case Sdate would be a CF variable you've sent in i.e. Apr 1, 2012
I just prefer my method even if the query is on the same page - seems cleaner - but it is your choice - you are the developer!
I'm in the habit of doing it this way so that I can create a stored procedure and send in var values to it that originally may be CF vars.
If you are doing a CF Query right on the same page as your code you could just simplify and do something like :
BETWEEN #CreateODBCDateTime(Sdate)
and in this case Sdate would be a CF variable you've sent in i.e. Apr 1, 2012
I just prefer my method even if the query is on the same page - seems cleaner - but it is your choice - you are the developer!
What is an example value for your compdate and moddate columns?
Can you post a couple example rows with values for each column?
Thanks.