I think that your whole stored procedure is much more complicated than
you need it to be. Try this and let me know:
---cfCode to call procedure----
<cfstoredProc Datesource="YourDatabase" Procedure="countJobsLogged">
<cfprocparam dbvarname="startdate" cfsqltype="CF_SQL_datetime"
value="YourStartDate" type="In">
<cfprocparam dbvarname="stopdate" cfsqltype="CF_SQL_datetime"
value="YourStartDate" type="In">
<cfprocResult Name="qJobsLogged">
</cfstoredProc>
----Procedure------
CREATE proc countJobsLogged @startdate datetime, @stopdate datetime
as
set nocount on
Select
JOBSCOUNT,DTLOGGED,jobPriority,jobNumber,convert(varchar(10),logdate,101
) logDate
>From Jobs
Where logdatetime between @startdate and @stopdate
Order by convert(varchar(10),logdate,101)
---End Procedure----
---Now you can use query of query to get your count this way.---
<cfquery datasource="query" name="GetTotals">
Select Count(*)
From qJobsLogged
Group By logDate
</cfquery>
And you have all of the information you want nice and easy with out that
while loop and creating a tmp table which does not need to be done at
all. I reformatted the logdate using convert just so that the group by
in the query of query does not have to worry about about the times
during the day, just the day itself, which is what you were trying to do
with the while loop form what I could tell. Let me know if this works
for you.
christian
-----Original Message-----
From: cf coder [mailto:[EMAIL PROTECTED]
Sent: Monday, March 14, 2005 8:29 AM
To: SQL
Subject: sql - select query with count() and one or more columns
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
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Discover CFTicket - The leading ColdFusion Help Desk and Trouble
Ticket application
http://www.houseoffusion.com/banners/view.cfm?bannerid=48
Message: http://www.houseoffusion.com/lists.cfm/link=i:6:2209
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
