January 20, 2004 at 4:18 pm
I have a rather interesting task of creating a sales snapshot of our products for
our DW. There is a summary table that keeps track of sales per item per sales period,
shown below:.
CREATE TABLE [WeeklySales] (
[UPC] [char] (5) NOT NULL ,
[SalesWeek] [int] NOT NULL ,
[Sales] [numeric](18, 0) NOT NULL
) ON [PRIMARY]
GO
insert into dbo.WeeklySales
Select 'X', 1, 10 UNION
Select 'X', 3, 10 UNION
Select 'X', 4, 20 UNION
Select 'X', 5, 40 UNION
Select 'Y', 1, 10 UNION
Select 'Y', 2, 10 UNION
Select 'Y', 3, 40 UNION
Select 'Y', 4, 20 UNION
Select 'Y', 5, 60 UNION
Select 'Z', 4, 20 UNION
Select 'Z', 5, 80
What I need is to show a cumulative sales for the last 3 sales period for each item.
I was able to do this using SQL below:
SELECT Weekly1.UPC,
Weekly1.SalesWeek,
SUM (Weekly2.Sales) SalesSum
FROM dbo.WeeklySales Weekly1,
dbo.WeeklySales Weekly2
WHERE Weekly1.UPC = Weekly2.UPC
AND Weekly2.SalesWeek BETWEEN
Weekly1.SalesWeek - 2 AND Weekly1.SalesWeek
GROUP BY Weekly1.UPC,
Weekly1.SalesWeek
Order By Weekly1.UPC,
Weekly1.SalesWeek
Which returns
UPC SalesWeek SalesSum
----- ----------- ----------------------------------------
X 1 10
X 3 20 <--- Data for period 2 missing for Item x
X 4 30
X 5 70
Y 1 10
Y 2 20
Y 3 60
Y 4 70
Y 5 120
Z 4 20 <--- Data for period 1, 2,3 missing for item Z
Z 5 100
The trick is, I need data for the sales periods even when there is no sale, but show
the same SalesSum as the salessum for the last period.
I.e, I need these additional sales data.
UPC SalesWeek SalesSum
----- ----------- ----------------------------------------
X 2 10
Z 1 0
Z 2 0
Z 3 0
Any ideas what is the best way to go about this?
January 20, 2004 at 5:36 pm
You need to use seperate dimension tables for time and your UPC field.
Run the following script for the purpose of this example...
CREATE TABLE [UPC] (
[UPC] [char] (5) NOT NULL
) ON [PRIMARY]
GO
insert into dbo.UPC
Select 'X' UNION
Select 'Y' UNION
Select 'Z'
CREATE TABLE [SalesWeeks] (
[SalesWeek] [int] NOT NULL
) ON [PRIMARY]
GO
insert into dbo.SalesWeeks
Select 1 UNION
Select 2 UNION
Select 3 UNION
Select 4 UNION
Select 5
Now the following query will do what you want...
select
X.UPC,
X.SalesWeek,
isnull(Y.SalesSum,0) SalesSum
from
(
select
UPC,
SalesWeek
from
UPC
cross join SalesWeeks
) X
left join
(
select
W.UPC,
SW.SalesWeek,
sum(W.Sales) SalesSum
from
WeeklySales W
cross join SalesWeeks SW
where
W.SalesWeek between SW.SalesWeek-2 and SW.SalesWeek
group by
W.UPC,
SW.SalesWeek
) Y on X.UPC=Y.UPC and X.SalesWeek=Y.SalesWeek
order by
X.UPC,
X.SalesWeek
Cheers,
Kevin
January 21, 2004 at 8:17 am
There are other ways to do this, but they result in complicated SQL that is hard to read, write, adn work with. Kevin's solution is the best method and if the dimension table is small, then the left join doesn't affect performance very much.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply