Solved

Output report with coldfusion

Posted on 2012-12-20
5
161 Views
Last Modified: 2013-01-24
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
Comment
Question by:earwig75
  • 3
  • 2
5 Comments
 
LVL 25

Expert Comment

by:dgrafx
ID: 38745266
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
 

Author Comment

by:earwig75
ID: 38745310
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
 
LVL 25

Accepted Solution

by:
dgrafx earned 500 total points
ID: 38745440
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
 

Author Comment

by:earwig75
ID: 38745498
So I can use declare in a cfquery?
0
 
LVL 25

Expert Comment

by:dgrafx
ID: 38745515
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

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

PROBLEM: How to add your own buttons to the bottom toolbar with paging info ( result count ). While creating a cfgrid, I ran into an issue where I wanted to embed my own custom buttons where the default ones ( insert / delete / etc… ) are for aes…
CFGRID Custom Functionality Series -  Part 1 Hi Guys, I was once asked how it is possible to to add a hyperlink in the cfgrid and open the window to show the data. Now this is quite simple, I have to use the EXT JS library for this and I achiev…
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.
Learn how to create flexible layouts using relative units in CSS.  New relative units added in CSS3 include vw(viewports width), vh(viewports height), vmin(minimum of viewports height and width), and vmax (maximum of viewports height and width).

867 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now