May 23, 2012 at 10:46 pm
Hi Experts,
Can you please provide me a solution regarding below mentioned problem?
I have a table with some data like
ProductTotal SalesYearPeriod
Product A 75 2012 10
Product B 80 2012 10
Product C 32 2012 10
Product D 56 2012 10
Product B 25 2012 11
Product C 90 2012 11
Product A 65 2012 12
Product C 50 2012 12
Product D 45 2012 12
Now I want to create a view like this
Product Total Sales (Current Period) Total Sales (Last Period) Year Period
Product A 75 0 2012 12
Product B 80 25 2012 12
Product C 32 90 2012 12
Product D 56 0 2012 12
Product A 0 65 2012 11
Product B 25 0 2012 11
Product C 90 50 2012 11
Product D 0 45 2012 11
Product A 65 0 2012 10
Product B 0 0 2012 10
Product C 50 0 2012 10
Product D 45 0 2012 10
Please help me.
Thanks and Regards,
P Paul
May 24, 2012 at 2:03 am
I had hoped to be able to help, but I've run out of time.
I am happy to share with you what I have so far, in the hope that you can carry it forward to a full solution.
SAMPLE DATA
CREATE TABLE Atable (
Product CHAR(9)
,Total INT
,[YEAR] INT
,period TINYINT
)
go
INSERT INTO Atable(Product,Total,[Year],period)
SELECT 'Product A',75,2012,10
UNION ALL
SELECT 'Product B',80,2012,10
UNION ALL
SELECT 'Product C',32,2012,10
UNION ALL
SELECT 'Product D',56,2012,10
UNION ALL
SELECT 'Product B',25,2012,11
UNION ALL
SELECT 'Product C',90,2012,11
UNION ALL
SELECT 'Product A',65,2012,12
UNION ALL
SELECT 'Product C',50,2012,12
UNION ALL
SELECT 'Product D',45,2012,12
VIEW
CREATE VIEW ViewA
AS
SELECT
C.Product AS [Product]
,C.Total AS [Total Sales (Current period)]
,ISNULL(D.total,0) AS [Total Sales (Last Period)]
,C.[YEAR] AS [Year]
,C.Period
FROM
(
SELECT
B.Product
,ISNULL(A.total,0) AS [total]
,b.[Year]
,b.[Period]
FROM
ATAble AS A
RIGHT OUTER JOIN
(
SELECT DISTINCT
cj.product
,atable.[year]
,atable.[Period]
FROM
Atable
CROSS JOIN
(SELECT DISTINCT product FROM ATable) AS CJ
) AS B
ON a.product = B.product
AND a.[year] = B.[Year]
AND a.[Period]= B.[period]
) AS C
LEFT OUTER JOIN
(
SELECT
B.Product
,ISNULL(A.total,0) AS [total]
,b.[Year]
,b.[Period]
FROM
ATAble AS A
RIGHT OUTER JOIN
(
SELECT DISTINCT
cj.product
,atable.[year]
,atable.[Period]
FROM
Atable
CROSS JOIN
(SELECT DISTINCT product FROM ATable) AS CJ
) AS B
ON a.product = B.product
AND a.[year] = B.[Year]
AND a.[Period]= B.[period]
) AS D
ON C.Product = D.Product
AND C.[year] = D.[Year]
AND C.[Period] = D.[Period] +1
go
to use
SELECT * FROM VIEWA
ORDER BY [year] DESC, [Period] DESC, Product asc
As it stands it will not roll over a change in years.
If nobody else picks this up I will come back and look at it later
regards
Ian
May 24, 2012 at 3:15 am
This was removed by the editor as SPAM
May 24, 2012 at 3:53 am
Stewart,
looks good,I like the case statement on the join, if only I had thought of that ?
but adding in another year kinda throws the results out of squew
INSERT ProductSales
VALUES('Product A', 65, 2013, 1)
,('Product C', 50, 2013, 1)
,('Product D', 45, 2013, 1)
SELECT * FROM ProductSalesPeriodComparison
ORDER BY YEAR DESC , period DESC, productcode ASC
returns
ProductCode TotalSales(CurrentPeriod) TotalSales(LastPeriod) Year Period
----------- ------------------------- ---------------------- ------ ------
Product A 0 0 2013 12
Product B 0 0 2013 12
Product C 0 0 2013 12
Product D 0 0 2013 12
Product A 0 0 2013 11
Product B 0 0 2013 11
Product C 0 0 2013 11
Product D 0 0 2013 11
Product A 0 0 2013 10
Product B 0 0 2013 10
Product C 0 0 2013 10
Product D 0 0 2013 10
Product A 65 0 2013 1
Product B 0 0 2013 1
Product C 50 0 2013 1
Product D 45 0 2013 1
Product A 65 0 2012 12
Product B 0 25 2012 12
Product C 50 90 2012 12
Product D 45 0 2012 12
Product A 0 75 2012 11
Product B 25 80 2012 11
Product C 90 32 2012 11
Product D 0 56 2012 11
Product A 75 0 2012 10
Product B 80 0 2012 10
Product C 32 0 2012 10
Product D 56 0 2012 10
Product A 0 0 2012 1
Product B 0 0 2012 1
Product C 0 0 2012 1
Product D 0 0 2012 1
May 24, 2012 at 4:19 am
This was removed by the editor as SPAM
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply