Solved

Output report with coldfusion

Posted on 2012-12-20
5
162 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

This article  is about submitting  form through  ColdFusion.Ajax.submitForm to the action page and send a response back in JSON format which later can be decoded using ColdFusion.JSON.decode. By this way you can avoid the usual page refresh for subm…
Today, I was working on some optimization and spam-stopping techniques when I encountered Ben Nadel's post to reduce spam feature using Math (http://www.bennadel.com/blog/197-How-I-Stop-Spammers-On-My-ColdFusion-Blog.htm). While this method is not o…
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

810 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