CODEWITHSUNDEEP
sql-serversql-server-2012
Emorse
Emorse

Reputation: 33

SQL Mathematical task

I have this table #Mytable

Product StartYear   NewYear Amount
ProdA   2002    2002    110.0
ProdA   2002    2003    170.0
ProdA   2003    2003    200.0
ProdB   2000    2000    45.2
ProdB   2000    2001    64.8
ProdB   2000    2003    37.0
ProdB   2001    2001    50.0
ProdB   2001    2002    75.0
ProdB   2001    2003    25.0
ProdB   2002    2002    55.0
ProdB   2002    2003    85.0
ProdB   2003    2003    100.0

I am required to produce the output below for a report

Product StartYear   NextYear    Amount
Comp    2000    2000    0
Comp    2000    2001    0
Comp    2000    2002    0
Comp    2000    2003    0
Comp    2001    2001    0
Comp    2001    2002    0
Comp    2001    2003    0
Comp    2002    2002    110
Comp    2002    2003    280
Comp    2003    2003    200
Non-Comp    2000    2000    45.2
Non-Comp    2000    2001    110
Non-Comp    2000    2002    110
Non-Comp    2000    2003    147
Non-Comp    2001    2001    50
Non-Comp    2001    2002    125
Non-Comp    2001    2003    150
Non-Comp    2002    2002    55
Non-Comp    2002    2003    140
Non-Comp    2003    2003    100

I used the query below to get the cumulative sum for each product in a given startyear

SELECT
    Product,
    StartYear,
    NewYear,
    SUM(Amount) OVER (PARTITION BY StartYear ORDER BY StartYear
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS Amount
FROM #mytable t1
WHERE t1.Product = 'ProdB'

UNION ALL

SELECT
    Product,
    StartYear,
    NewYear,
    SUM(Amount) OVER (PARTITION BY StartYear ORDER BY StartYear
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS Amount
FROM #Mytable t1
WHERE t1.Product = 'ProdB'

This gives me a result-set with the cumulative amount as required but doesn't include the years with zero.Please how can I also have the years with zero ( no amount) in the output?

Any idea how this can be done in SQL?

Upvotes: 1

Views: 58

Answers (1)

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67311

I don't know if I got this correctly, but - if you want to have all years - you might use a tally table. In my example I just create one as CTE:

DECLARE @tbl TABLE(Product VARCHAR(100), StartYear INT, NewYear INT, Amount DECIMAL(6,2));
INSERT INTO @tbl VALUES
 ('ProdA',2002,2002,110.0)
,('ProdA',2002,2003,170.0)
,('ProdA',2003,2003,200.0)
,('ProdB',2000,2000,45.2)
,('ProdB',2000,2001,64.8)
,('ProdB',2000,2003,37.0)
,('ProdB',2001,2001,50.0)
,('ProdB',2001,2002,75.0)
,('ProdB',2001,2003,25.0)
,('ProdB',2002,2002,55.0)
,('ProdB',2002,2003,85.0)
,('ProdB',2003,2003,100.0);

;WITH RunningYears AS --Years from 2000 to 2003
(
    SELECT TOP 4 ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) + 1999 AS YearIndex
    FROM sys.objects
)
SELECT MyProducts.* 
FROM
(
    SELECT * 
    FROM RunningYears AS y
    LEFT JOIN (SELECT * FROM @tbl WHERE Product='ProdA') AS tblA ON y.YearIndex=tblA.StartYear
    UNION ALL
    SELECT * 
    FROM RunningYears AS y
    LEFT JOIN (SELECT * FROM @tbl WHERE Product='ProdB') AS tblB ON y.YearIndex=tblB.StartYear
) AS MyProducts
ORDER BY Product,YearIndex

The result

YearIndex   Product StartYear   NewYear Amount
2000    NULL    NULL    NULL    NULL
2001    NULL    NULL    NULL    NULL
2002    ProdA   2002    2002    110.00
2002    ProdA   2002    2003    170.00
2003    ProdA   2003    2003    200.00
2000    ProdB   2000    2000    45.20
2000    ProdB   2000    2001    64.80
2000    ProdB   2000    2003    37.00
2001    ProdB   2001    2001    50.00
2001    ProdB   2001    2002    75.00
2001    ProdB   2001    2003    25.00
2002    ProdB   2002    2002    55.00
2002    ProdB   2002    2003    85.00
2003    ProdB   2003    2003    100.00

Upvotes: 1

Related Questions