April 24, 2009 at 9:39 am
I have a sales table.
CREATE TABLE #Sales
(
Customer varchar(5),
Sales money,
Type char(1),
Period varchar(75)
)
INSERT INTO #Sales
VALUES ('1',10.00,'A','2009/04/08 - 2009/04/14')
INSERT INTO #Sales
VALUES ('2',10.00,'B','2009/04/15 - 2009/04/21')
1 10.00 A 2009/04/08 - 2009/04/14
2 10.00 B 2009/04/15 - 2009/04/21
I have a period table.
CREATE TABLE #Period
( Period varchar(75))
INSERT INTO #Period
VALUES ('2009/04/01 - 2009/04/07')
INSERT INTO #Period
VALUES ('2009/04/08 - 2009/04/14')
INSERT INTO #Period
VALUES ('2009/04/15 - 2009/04/21')
I need to see the sales as zero for the period which has no sales. Like
1 0.00 A 2009/04/01 - 2009/04/07
1 10.00 A 2009/04/08 - 2009/04/14
1 0.00 A 2009/04/15 - 2009/04/21
2 0.00 B 2009/04/01 - 2009/04/07
2 0.00 B 2009/04/08 - 2009/04/14
2 10.00 B 2009/04/15 - 2009/04/21
I was thinking of using cross join, but not sure.
Can anybody please help me?
April 24, 2009 at 9:47 am
An Outer Join from Periods to Sales will get you what you need.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 24, 2009 at 9:57 am
Hi, I tried it before but it was not giving the output the way I desired.
NULL NULL NULL 2009/04/01 - 2009/04/07
1 10.00 A 2009/04/08 - 2009/04/14
2 10.00 B 2009/04/15 - 2009/04/21
April 24, 2009 at 11:25 am
select SUM(IsNull(s.Sales,0)) Sales,
s.[Type],
p.Period
from #Period p
LEFT OUTER JOIN #Sales s ON s.Period = p.Period
group by p.Period, s.Type
returns:
Sales Type Period
--------------------- ---- --------------------
0.00 NULL 2009/04/01 - 2009/04/07
10.00 A 2009/04/08 - 2009/04/14
10.00 B 2009/04/15 - 2009/04/21
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply