Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Combine two select in stored procedure for report

Status
Not open for further replies.

Shal2

Programmer
Dec 3, 2001
52
NZ
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
----------------------------------------------------------
 
Why not just use both sets separately in the report by making a subreport?

Questions about posting. See faq183-874
 
Hi SQLSister,

Thank you for the idea, I had never created subreports before, but now for this one I did and it works.

Thank you,
Shal
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top