October 14, 2010 at 8:23 pm
So I am trying to create a join to create a relatively wide report but I can't seem to get it to work.
delcare @startdate smalldatetime, @enddate smalldatetime
selecgt @startdate '20100901', @enddate '20100930'
select datepart(ww, created) as Week,
s.commonname as Store,
count(*) as sales
from
bystore b,
stores s,
rca r
where
b.store = s.storeid and
b.sr = r.sr and
created >= @startdate and
created <= @enddate + '23:59:59'
group by datepart(ww, created), s.commonname, s.storeid
order by s.storeid
Naturally this results in output similar to the following -
WeekStore Sales
37NewYork2
38NewYork6
39NewYork10
40NewYork7
36LosAngeles8
37LosAngeles11
38LosAngeles19
39LosAngeles21
40LosAngeles19
What I cannot figure out using a union or join is how to get output that would look more like
Week Newyork LosAngeles
36 null 8
37 2 11
38 6 19
39 10 21
40 7 19
Basically more "spreadsheet" like. and yes I have the potential of having a null (ie no sale at a given store for that week).
Now sure I can write a query with each one seperately but I have the issue of dealing with hundreds of stores so my query obviously isn't scalable as writing each one out.
I am trying to avoid building a temp table and populating which is my current work around.
Thoughts?
October 15, 2010 at 7:45 am
I worked on a problem just like this yesterday.
Use CTE to create 3 queries. 2 to sum for each store. One to get the a list of weeks. Then join them all.... Here is an example of what I did.
with Cnt_MJ as
(
select dldir, count(dldir) as MJ
from dbo.mj_DSleads
group by dldir
),
Cnt_Dir as
(
select dldir, count(dldir)as Dir
from dbo.dir_DSleads
group by dldir
),
dist_dir as
(
select distinct(dldir) as Dir
from dbo.dir_dsleads
)
Select dist_dir.Dir, Cnt_MJ.MJ,Cnt_Dir.Dir
from dist_dir
join Cnt_MJ
on dist_dir.dir=Cnt_MJ.dldir
join Cnt_Dir
on dist_dir.dir=Cnt_Dir.dldir
order by dist_dir.dir
Maybe this will help.
Simi
October 15, 2010 at 10:41 am
This is exactly what the PIVOT operator was designed for. Check it out in BOL.
That being said, using a crosstab is often more efficient.
select datepart(ww, created) as Week,
COUNT(CASE s.commonname WHEN 'NewYork' THEN s.storeid END) as NewYork,
COUNT(CASE s.commonname WHEN 'LosAngeles' THEN s.storeid END) as LosAngeles,
from
bystore b,
stores s,
rca r
where
b.store = s.storeid and
b.sr = r.sr and
created >= @startdate and
created <= @enddate + '23:59:59'
group by datepart(ww, created)
order by datepart(ww, created)
You will need a separate CASE statement for each store, so it does get unwieldy with a lot of values. You can use dynamic SQL to create the crosstab if you have a lot of values.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply