Group by days of week

[Shal2]

I have a stored procedure.
It takes two parameters @StartDate and @EndDate. When any date range is entered, @startdate always selects the closest monday and @enddate always selects the closest sunday(some one helped me out). It works fine as I did this for weekly results. Now they want to enter any date range (like the whole month, or whole year) and to want to group the result by days of the week. The report result is like below:

Date Count
------------------------------
Monday 06/07/2004 250
Tuesday 06/08/2004 300
Wednesday 06/09/2004 350
Thursday 06/10/2004 256
Friday 06/11/2004 234
Saturday 06/12/2004 450
Sunday 06/13/2004 234

This is if they enter a small range like startdate '06/06/2004' and enddate '06/12/2004'. If they enter a wide range like '06/06/2004' and '06/30/2004' then the report displays the remaining dates as well, but I want to group it by days, like no date is needed, just;

Day Cummlative Count
-----------------------------
Monday 1,234
Tuesday 2,345
Wednesday 3,345
Thursday 2,345
Friday 5,768
Saturday 6,098
sunday 4,654
-----------------------------------

How do I do this?
Thanks,
Shal

 

[philhege]

Post the T-SQL, so we can get going.

Phil Hegedusich
Senior Web Developer
IIMAK

http://www.iimak.com

-----------
Boy howdy, my Liberal Studies degree really prepared me for this....
-----------
A skeleton walks into a bar, and says "I'll have a beer and a mop.

 

[Shal2]

Here is the stored procedure:



CREATE PROCEDURE FootTraffic_test

@Store VARCHAR(35),
@StartDate Datetime,
@EndDate Datetime

AS

SET @StartDate =
CASE
WHEN datepart(dw, convert(datetime, @StartDate)) = 2
THEN @StartDate
WHEN datepart(dw, convert(datetime, @StartDate)) between 3 and 5
THEN DateAdd(dd, -1*(datepart(dw, convert(datetime, @StartDate))-2), @StartDate)
WHEN datepart(dw, convert(datetime, @StartDate)) between 6 and 7
THEN DateAdd(dd, 9 - datepart(dw, convert(datetime, @StartDate)), @StartDate)
WHEN datepart(dw, convert(datetime, @StartDate)) = 1
THEN DateAdd(dd, 1, @StartDate)
END

--SET @EndDate = DateAdd(wk, 1, @StartDate)

SET @EndDate =
CASE
WHEN datepart(dw, convert(datetime, @EndDate)) = 1
THEN @EndDate
WHEN datepart(dw, convert(datetime, @EndDate)) between 3 and 4
THEN DateAdd(dd, -1*(datepart(dw, convert(datetime, @EndDate))-1), @EndDate)
WHEN datepart(dw, convert(datetime, @EndDate)) between 5 and 7
THEN DateAdd(dd, 8 - datepart(dw, convert(datetime, @EndDate)), @EndDate)
WHEN datepart(dw, convert(datetime, @EndDate)) = 2
THEN DateAdd(dd, -1, @EndDate)
END



Select
store,
SUM(count) as COUNT,
convert(datetime,convert(varchar(12),actualtime,101)) as actualtime,
Hours

from
(
Select
store,
count,
actualtime,
case
--when time < '19000101 08:00:00.000' then '[A-Before8]'
when time between '19000101 08:00:00.001' and '19000101 9:00:00.000' then 'A 8-9am'
when time between '19000101 9:00:00.001' and '19000101 10:00:00.000' then 'B 9-10am'
when time between '19000101 10:00:00.001' and '19000101 11:00:00.000' then 'C 10-11am'
when time between '19000101 11:00:00.001' and '19000101 12:00:00.000' then 'D 11-12pm'
when time between '19000101 12:00:00.001' and '19000101 13:00:00.000' then 'E 12-1pm'
when time between '19000101 13:00:00.001' and '19000101 14:00:00.000' then 'F 1-2pm'
when time between '19000101 14:00:00.001' and '19000101 15:00:00.000' then 'G 2-3pm'
when time between '19000101 15:00:00.001' and '19000101 16:00:00.000' then 'H 3-4pm'
when time between '19000101 16:00:00.001' and '19000101 17:00:00.000' then 'I 4-5pm'
when time between '19000101 17:00:00.001' and '19000101 18:00:00.000' then 'J 5-6pm'
when time between '19000101 18:00:00.001' and '19000101 19:00:00.000' then 'K 6-7pm'
when time between '19000101 19:00:00.001' and '19000101 20:00:00.000' then 'L 7-8pm'
--when time > '19000101 20:00:00.001' then '[N-After8]'
else 'OUTSIDE HOURS' end as Hours

from
(select store,count,time as actualtime, dateadd(dd,datediff(d,time,'19000101'),time) as time

from
people_counter
where
store = @Store and
time between @Startdate and @EndDate)dt
)dt1

group by store,convert(datetime,convert(varchar(12),actualtime,101)),Hours


Thank you,
Shal

 

[Shal2]

I was able to achieve this from the report by using:
grouping similar to:

=WeekDayName(weekday('mydate'))

This was in reporting services and this can be done in crystal reports also I guess.

Thanks,
Shal.

 

[philhege]

Right, although you can do the same thing with datepart() in the SP. I try to minimize the data-crunching in the report; the cleaner the initial data set is, the less work it has to do.

Good luck,

Phil Hegedusich
Senior Web Developer
IIMAK

http://www.iimak.com

-----------
Boy howdy, my Liberal Studies degree really prepared me for this....
-----------
A skeleton walks into a bar, and says "I'll have a beer and a mop.

 

[Shal2]

Hi Phil,

Will try the datepart in the stored procedure.

Thank you for the help,
Shal