Hi All,
This is related to one of my previous posts but different issue.
This stored procedure has two select statement which returns two different sets of data, (one may return 200 rows, the other may return 400), but I want to have both on a single report for comparision. I have tried union and join but doesn't seem to work. I have also tried a summary temp table but because each returns different number of rows doesn't seem to be useful.Can anyone suggest few ideas.
I want the result like below in the report:
Store Day Count(this year) Count(last year)
-----------------------------------------------
First Mon 100 80
Tue 250 200
wed 220 200
thur 300 240
fri 350 340
sat 490 357
sun 345 245
----------------------------------------------
I can do the grouping in the report and also not trying to compare the same dates in both years, just approximate same week last year will do, what is important is the same day(mon, tue...) and not the same week or same dates.
Just to get any idea how many customers visited stores this year particular week and how many visited last year same week(approxiamte).
Thanks,
Shal
---------------------------------------------------
CREATE PROCEDURE FootTraffic_Cumulative_test
@Store VARCHAR(35),
@StartDate Datetime,
@EndDate Datetime
AS
SET DATEFIRST 7
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
Declare @StartDateLast Datetime
Declare @EndDateLast Datetime
Set @StartDateLast = DateAdd(yy, -1, @StartDate)
SET @StartDateLast =
CASE
WHEN datepart(dw, convert(datetime, @StartDateLast)) = 2
THEN @StartDateLast
WHEN datepart(dw, convert(datetime, @StartDateLast)) between 3 and 5
THEN DateAdd(dd, -1*(datepart(dw, convert(datetime, @StartDateLast))-2), @StartDateLast)
WHEN datepart(dw, convert(datetime, @StartDateLast)) between 6 and 7
THEN DateAdd(dd, 9 - datepart(dw, convert(datetime, @StartDateLast)), @StartDateLast)
WHEN datepart(dw, convert(datetime, @StartDateLast)) = 1
THEN DateAdd(dd, 1, @StartDateLast)
END
Set @EndDateLast = DateAdd(yy, -1, @EndDate)
SET @EndDateLast =
CASE
WHEN datepart(dw, convert(datetime, @EndDateLast)) = 1
THEN @EndDateLast
WHEN datepart(dw, convert(datetime, @EndDateLast)) between 3 and 4
THEN DateAdd(dd, -1*(datepart(dw, convert(datetime, @EndDateLast))-1), @EndDateLast)
WHEN datepart(dw, convert(datetime, @EndDateLast)) between 5 and 7
THEN DateAdd(dd, 8 - datepart(dw, convert(datetime, @EndDateLast)), @EndDateLast)
WHEN datepart(dw, convert(datetime, @EndDateLast)) = 2
THEN DateAdd(dd, -1, @EndDateLast)
END
Select
store,
SUM(count) as COUNT,
--actualtime
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 09:00:00.000' then 'A 8-9am'
when time between '19000101 09: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
Select
store,
SUM(count) as COUNT,
--actualtime
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 09:00:00.000' then 'A 8-9am'
when time between '19000101 09: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 @StartdateLast and @EndDateLast
)dt
)dt1
group by store,convert(datetime,convert(varchar(12),actualtime,101)),Hours
GO
----------------------------------------------------------
This is related to one of my previous posts but different issue.
This stored procedure has two select statement which returns two different sets of data, (one may return 200 rows, the other may return 400), but I want to have both on a single report for comparision. I have tried union and join but doesn't seem to work. I have also tried a summary temp table but because each returns different number of rows doesn't seem to be useful.Can anyone suggest few ideas.
I want the result like below in the report:
Store Day Count(this year) Count(last year)
-----------------------------------------------
First Mon 100 80
Tue 250 200
wed 220 200
thur 300 240
fri 350 340
sat 490 357
sun 345 245
----------------------------------------------
I can do the grouping in the report and also not trying to compare the same dates in both years, just approximate same week last year will do, what is important is the same day(mon, tue...) and not the same week or same dates.
Just to get any idea how many customers visited stores this year particular week and how many visited last year same week(approxiamte).
Thanks,
Shal
---------------------------------------------------
CREATE PROCEDURE FootTraffic_Cumulative_test
@Store VARCHAR(35),
@StartDate Datetime,
@EndDate Datetime
AS
SET DATEFIRST 7
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
Declare @StartDateLast Datetime
Declare @EndDateLast Datetime
Set @StartDateLast = DateAdd(yy, -1, @StartDate)
SET @StartDateLast =
CASE
WHEN datepart(dw, convert(datetime, @StartDateLast)) = 2
THEN @StartDateLast
WHEN datepart(dw, convert(datetime, @StartDateLast)) between 3 and 5
THEN DateAdd(dd, -1*(datepart(dw, convert(datetime, @StartDateLast))-2), @StartDateLast)
WHEN datepart(dw, convert(datetime, @StartDateLast)) between 6 and 7
THEN DateAdd(dd, 9 - datepart(dw, convert(datetime, @StartDateLast)), @StartDateLast)
WHEN datepart(dw, convert(datetime, @StartDateLast)) = 1
THEN DateAdd(dd, 1, @StartDateLast)
END
Set @EndDateLast = DateAdd(yy, -1, @EndDate)
SET @EndDateLast =
CASE
WHEN datepart(dw, convert(datetime, @EndDateLast)) = 1
THEN @EndDateLast
WHEN datepart(dw, convert(datetime, @EndDateLast)) between 3 and 4
THEN DateAdd(dd, -1*(datepart(dw, convert(datetime, @EndDateLast))-1), @EndDateLast)
WHEN datepart(dw, convert(datetime, @EndDateLast)) between 5 and 7
THEN DateAdd(dd, 8 - datepart(dw, convert(datetime, @EndDateLast)), @EndDateLast)
WHEN datepart(dw, convert(datetime, @EndDateLast)) = 2
THEN DateAdd(dd, -1, @EndDateLast)
END
Select
store,
SUM(count) as COUNT,
--actualtime
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 09:00:00.000' then 'A 8-9am'
when time between '19000101 09: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
Select
store,
SUM(count) as COUNT,
--actualtime
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 09:00:00.000' then 'A 8-9am'
when time between '19000101 09: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 @StartdateLast and @EndDateLast
)dt
)dt1
group by store,convert(datetime,convert(varchar(12),actualtime,101)),Hours
GO
----------------------------------------------------------