TCP/IP is a set of network protocols which is best known for connecting the machines that make up the Internet. The truth is that TCP/IP is one of the oldest network protocols and its survival is mainly based on its simplicity and universality.
COURSE of the MONTH
13 days, 23 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
Output report with coldfusion
Posted on 2012-12-20
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
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
3-
2
5 Comments
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.
What is an example value for your compdate and moddate columns?
Can you post a couple example rows with values for each column?
Thanks.
Active 3 days ago
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.
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
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
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 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!
Featured Post
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
This is an updated version of a post made on my blog over 3 years ago. It is unfortunately, still very relevant as we continue to see both SQLi (SQL injection) and XSS (cross site scripting) attacks hitting some of the most recognizable website and …
Hi.
There are several upload tutorials using jquery and coldfusion. I found a very interesting one here Upload Your Files using Jquery & ColdFusion and Preview them (http://www.randhawaworld.com/) .
I did keep the main js functions but made sever…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash"
After Shake : http://www.videocopilot.net/presets/after_shake/
All lightning effects with instructions : http://www.mediaf…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen.
Visualize your data! ... really see it
To use the code to create a calendar from a q…
Suggested Courses
Course of the Month13 days, 23 hours left to enroll
801 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.