Query woes, trying to count transactions for multiple days

[mrccopeland]

Hello All,
I'm having a bit of a difficult time here. I have a query that I can run for a single day and it produces the correct output for the situation. The query goes as follows:

SELECT DATEPART(mm, entrydate) AS tranmonth,
DATEPART(dd, entrydate) AS tranday,
DATEPART(yyyy, entrydate) AS tranyear,
(SELECT COUNT(*)
FROM TRANSERVICE
WHERE (ENTRYDATE between '07-16-2003 10:00:00' AND '07-16-2003 11:59:00') OR
(ENTRYDATE between '07-16-2003 14:00:00' AND '07-16-2003 15:59:00')) AS Peak,
(SELECT COUNT(*)
FROM TRANSERVICE
WHERE ENTRYDATE >= '07-16-2003 07:00:00' AND
ENTRYDATE <= '07-16-2003 18:59:59') AS BusinessHours,
(SELECT COUNT(*)
FROM TRANSERVICE
WHERE ENTRYDATE >= '07-16-2003 19:00:00' AND
ENTRYDATE <= '07-17-2003 06:59:59') AS OffHours
FROM TRANSERVICE
WHERE ENTRYDATE >= '07-16-2003 00:00:00' AND
ENTRYDATE <= '07-17-2003 06:59:59'
GROUP BY DATEPART(mm, entrydate), DATEPART(dd, entrydate), DATEPART(yyyy, entrydate)
ORDER BY DATEPART(mm, entrydate), DATEPART(dd, entrydate), DATEPART(yyyy, entrydate)


It's used to count the number of transactions for a given day during some specific hours. The output follows as such

7 16 2003 6820 13913 0


Where my problem comes in is being able to run this for multiple days at a given time. Say I want to run this for the month of July. I don't want just one record for july with a total count, what I'm looking for is a record for each day in July with the counts for that day. Something like:

7 1 2003 6788 10999 12
7 2 2003 3248 12000 0
7 3 2003 0 0 0
7 4 2003 10 19 0
.....
7 31 2003 8723 9823 14


Any suggestions?

 

[JayKusch]

May want to looh at putting your code in a loop of some sort, then use a DateADD(day,1,'YourMovingDate) and increment the days to process that way.

Thanks

J. Kusch

 

[mrccopeland]

Tried this:

declare @iCurInt int
declare @iCurDate datetime

select @iCurInt = 1
set @iCurDate = '07-16-2003 00:00:00'

while @iCurInt < 3
begin
SELECT DATEPART(mm, entrydate) AS tranmonth,
DATEPART(dd, entrydate) AS tranday,
DATEPART(yyyy, entrydate) AS tranyear,
(SELECT COUNT(*)
FROM TRANSERVICE
WHERE (ENTRYDATE between DATEADD(hour, 10, @iCurDate ) AND DATEADD(hour, 12, @iCurDate)) OR
(ENTRYDATE between DATEADD(hour, 14, @iCurDate ) AND DATEADD(hour, 16, @iCurDate ))) AS Peak,
(SELECT COUNT(*)
FROM TRANSERVICE
WHERE ENTRYDATE between DATEADD(hour, 7, @iCurDate ) AND DATEADD(hour, 19, @iCurDate )) AS BusinessHours,
(SELECT COUNT(*)
FROM TRANSERVICE
WHERE (ENTRYDATE between @iCurDate AND DATEADD(hour, 6, @iCurDate )) or
(ENTRYDATE between DATEADD(hour, 19, @iCurDate ) AND DATEADD(hour, 24, @iCurDate ))) AS OffHours
FROM TRANSERVICE
WHERE ENTRYDATE between @iCurDate AND DATEADD(hour, 24, @iCurDate )
GROUP BY DATEPART(mm, entrydate), DATEPART(dd, entrydate), DATEPART(yyyy, entrydate)
ORDER BY DATEPART(mm, entrydate), DATEPART(dd, entrydate), DATEPART(yyyy, entrydate)


select @iCurInt = @iCurInt + 1
select @iCurDate = DATEADD(day, 1, @iCurDate)
end


But it didn't return what I expected:
7 17 2003 9200 32093 670
7 18 2003 9200 32093 670

Am I missing something here?!?

 

[SQLBill]

Try this change at the end of the script:

Change this:
select @iCurInt = @iCurInt + 1
select @iCurDate = DATEADD(day, 1, @iCurDate)
end

To this:

set @iCurInt = @iCurInt + 1
set @iCurDate = DATEADD(day, 1, @iCurDate)

select @iCurDate
end

The final select will display what @iCurDate value is set to. That way you can make sure it's being incremented properly.

-SQLBill

 

[mrccopeland]

Thanks SQLBill and JayKusch for your help with this. I've found out what I need to do and my problem while I was doing it. One more piece of advice I could use. I'm doing this through QueryAnalyzer and the output is less than desirable. Is there a better way of accomplishing clean output through QA? Currently, I know it's kind of crude but I'm new to this arena, I just cut and paste into excel as these are just some adhoc stuff that we are doing. Can the output be redirected to an excel file or other file of some sort through the Query Analyzer?

Thanks again

C. Copeland