Hello everybody, I really need someone to help me here. I'm developing a graphical interface to report the no of jobs logged in a period. The x axis displays the period (days) and the y-axis displays the number of jobs logged in a given day.
The data is pulled from the jobs table. The job logged date value is stored in the logdateTime column. I've written a stored procedure to get the count of jobs logged in a given day. Here is the code. <cfquery name="qJobsLogged" datasource="dbname"> EXEC countJobsLogged @startdatedate = '2005/02/01', @stopdate = '2005/02/28' </cfquery> <!-- sp code starts here --> CREATE proc countJobsLogged @startdate datetime, @stopdate datetime as set nocount on declare @x datetime, @count int create table #temp_report( dtlogged varchar(15), jobsCount int ) select @x = @startdate while @x <= @stopdate begin select @count = isnull(count(1),0) from jobs where logdatetime >= @x + '00:00:00' and logdatetime <= @x + '23:59:59' insert into #temp_report values(convert(varchar(15),@x,106), @count) select @x = dateadd(d, 1, @x) end select * from #temp_report drop table #temp_report set nocount off GO <!-- sp code ends here --> The above sp loops from the user selected start date to the user selected end date and returns a recordset showing the no of jobs logged. Ex JOBSCOUNT DTLOGGED 2 01 Feb 2005 3 02 Feb 2005 4 03 Feb 2005 So far so good. I want to modify the select stmt in that I want it to also return the jobPriority and the jobNumber (these are columns in the job table) for jobs that it found logged on a particular day. So for example if say for ex, there are 2 jobs logged on the 01 Feb 2005, I want the final recordset to look something like this JOBSCOUNT DTLOGGED jobPriority jobNumber 2 01 Feb 2005 3 01JS1003 2 01 Feb 2005 2 01JS1004 3 02 Feb 2005 4 01JS1005 3 02 Feb 2005 4 01JS1006 3 02 Feb 2005 5 01JS1016 4 03 Feb 2005 1 01JS1018 4 03 Feb 2005 2 01JS1020 4 03 Feb 2005 3 01JS1021 4 03 Feb 2005 4 01JS1022 I was hoping someone could show me the sql to achieve this result Best regards cfcoder ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Find out how to get a fax number that sends and receives faxes using your current email address http://www.houseoffusion.com/banners/view.cfm?bannerid=64 Message: http://www.houseoffusion.com/lists.cfm/link=i:6:2208 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/6 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:6 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.6 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
