• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 175
  • Last Modified:

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
0
earwig75
Asked:
earwig75
  • 3
  • 2
1 Solution
 
dgrafxCommented:
How do you mean - "how many comp dates or mod dates they appear in"?
What is an example value for your compdate and moddate columns?
Can you post a couple example rows with values for each column?
Thanks.
0
 
earwig75Author Commented:
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.
0
 
dgrafxCommented:
First off we can create a couple date vars so you can send in dates from your front end language. You could just replace the date string in single quotes 'Apr 1, 2012' with a CF variable.
Or whatever you want to do ...

Try something like this:

Declare @Sdate DateTime, @Edate DateTime
SET @Sdate = Convert(Date,'Apr 1, 2012')
SET @Edate = Convert(Date,'Apr 30, 2012')

SELECT UserID, (Select count(a.UserID) FROM YourTable a WITH (NOLOCK) Where a.UserID = YourTable.UserID) as count
FROM YourTable WITH (NOLOCK)
WHERE
CompDate BETWEEN @Sdate and @Edate
OR
ModDate BETWEEN @Sdate and @Edate
Group by UserID
Order by UserID
0
 
earwig75Author Commented:
So I can use declare in a cfquery?
0
 
dgrafxCommented:
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!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now