Cummulative multiplication in T-SQL

Question

GTS Table

CCP months   QUART   YEARS  GTS
----  ------  -----    ----- ---
CCP1    1       1   2015    5
CCP1    2       1   2015    6
CCP1    3       1   2015    7
CCP1    4       2   2015    4
CCP1    5       2   2015    2
CCP1    6       2   2015    2
CCP1    7       3   2015    3
CCP1    8       3   2015    2
CCP1    9       3   2015    1
CCP1    10      4   2015    2
CCP1    11      4   2015    3
CCP1    12      4   2015    4

Baseline table

CCP BASELINE   YEARS    QUART
----  --------   -----  -----
CCP1    5       2015    1

Expected result

CCP months  QUART    YEARS  GTS   result
----  ------  -----    ----- ---   ------

CCP1    1       1   2015    5     25   -- 5 * 5 (here 5 is the baseline)
CCP1    2       1   2015    6     30   -- 6 * 5 (here 5 is the baseline)
CCP1    3       1   2015    7     35   -- 7 * 5 (here 5 is the baseline)
CCP1    4       2   2015    4     360  -- 90 * 4(25+30+35 = 90 is the basline)
CCP1    5       2   2015    2     180  -- 90 * 2(25+30+35 = 90 is the basline)
CCP1    6       2   2015    2     180  -- 90 * 2(25+30+35 = 90 is the basline)
...
next quarter baseline will be (720)

SQLFIDDLE

Explantion

Baseline table has single baseline value for each CCP.

The baseline value should be applied to first quarter of each CCP and for the next quarters previous quarter sum value will be the basleine.

Yes i can do this using While Loop or cursor but am looking for a better way of doing it So far i have tried something like this. But didnt wotk out what am i missing here.

Query

;WITH RECURSIVE_CTE
     AS (SELECT B.CCP,
                G.months,
                g.years,
                g.quart,
                Cast(( B.BASELINE + GTS ) AS NUMERIC(38, 15))AS BASE,
                Sum(Cast(( B.BASELINE + GTS ) AS NUMERIC(38, 15)))OVER(PARTITION BY b.CCP) AS Q_SUM
         FROM   BASELINE B
                INNER JOIN GTS G
                        ON B.CCP = G.CCP
                           AND b.QUART = g.QUART
                           AND b.YEARS = g.YEARS
         UNION ALL
         SELECT G.CCP,
                G.months,
                g.years,
                g.quart,
                Cast(( Q_SUM + GTS ) AS NUMERIC(38, 15)),
                Sum(Cast(( Q_SUM + GTS ) AS NUMERIC(38, 15)))OVER(PARTITION BY g.CCP, g.years, g.quart) AS Q_SUM
         FROM   RECURSIVE_CTE C
                INNER JOIN GTS G
                        ON C.CCP = G.CCP
                           AND G.YEARS = C.YEARS and g.QUART = c.quart + 1)
SELECT *
FROM   RECURSIVE_CTE

Note : Am looking for a suggestion in Sql Server 2008

I would be inclined to suggest that you don't change the code until you upgrade to SQL Server 2012+. The cumulative `sum()` function will be a significant improvement over anything that you do in SQL Server 2008. — Gordon Linoff, Sep 24 '15 at 11:10
@GordonLinoff - yeah i agree with you but is there any way to do this so that i can compare. Can you add one answer if possible — Pரதீப், Sep 24 '15 at 11:12
@GordonLinoff - I have managed to upgrade from 2008 to 2012. Can you please add a 2012 answer or Do i have to ask a new question — Pரதீப், Sep 25 '15 at 05:45
@GordonLinoff - I have a raised a new question for Sql Server 2012+ solution. Can you please have a look at it http://stackoverflow.com/questions/32787506/running-multiplication-in-t-sql — Pரதீப், Sep 25 '15 at 17:41

GarethD · Accepted Answer · 2015-09-24T12:27:21.187

I think if you add a row number to the recursive anchor, then limit the join in the cte to only the one of the 3 rows per quarter you can achieve your desired result:

WITH CTE AS
(   SELECT  b.CCP,
            Baseline = CAST(b.Baseline AS DECIMAL(15,2)),
            b.Years,
            b.Quart,
            g.Months,
            g.GTS,
            Result = CAST(b.Baseline * g.GTS AS DECIMAL(15,2)),
            NextBaseline = SUM(CAST(b.Baseline * g.GTS AS DECIMAL(15, 2))) OVER(PARTITION BY g.CCP, g.years, g.quart),
            RowNumber = ROW_NUMBER() OVER(PARTITION BY g.CCP, g.years, g.quart ORDER BY g.Months)
    FROM    #GTS AS g
            INNER JOIN #Base AS b
                ON B.CCP = g.CCP
               AND b.QUART = g.QUART
               AND b.YEARS = g.YEARS
    UNION ALL
    SELECT  b.CCP,
            CAST(b.NextBaseline AS DECIMAL(15, 2)),
            b.Years,
            b.Quart + 1,
            g.Months,
            g.GTS,
            Result = CAST(b.NextBaseline * g.GTS AS DECIMAL(15,2)),
            NextBaseline = SUM(CAST(b.NextBaseline * g.GTS AS DECIMAL(15, 2))) OVER(PARTITION BY g.CCP, g.years, g.quart),
            RowNumber = ROW_NUMBER() OVER(PARTITION BY g.CCP, g.years, g.quart ORDER BY g.Months)
    FROM    #GTS AS g
            INNER JOIN CTE AS b
                ON B.CCP = g.CCP
               AND b.Quart + 1 = g.QUART
               AND b.YEARS = g.YEARS
               AND b.RowNumber = 1
)
SELECT  CCP, Months, Quart, Years, GTS, Result, Baseline
FROM    CTE;

This yields:

CCP     Months  Quart   Years   GTS     Result      Baseline
-------------------------------------------------------------
CCP1    1       1       2015    5       25.00       5.00
CCP1    2       1       2015    6       30.00       5.00
CCP1    3       1       2015    7       35.00       5.00
CCP1    4       2       2015    4       360.00      90.00
CCP1    5       2       2015    2       180.00      90.00
CCP1    6       2       2015    2       180.00      90.00
CCP1    7       3       2015    3       2160.00     720.00
CCP1    8       3       2015    2       1440.00     720.00
CCP1    9       3       2015    1       720.00      720.00
CCP1    10      4       2015    2       8640.00     4320.00
CCP1    11      4       2015    3       12960.00    4320.00
CCP1    12      4       2015    4       17280.00    4320.00

Sample Data

CREATE TABLE #GTS (CCP VARCHAR(4), months INT, QUART INT, YEARS INT, GTS INT);
INSERT INTO #GTS (CCP, months, QUART, YEARS, GTS)
VALUES
    ('CCP1', 1, 1, 2015, 5),
    ('CCP1', 2, 1, 2015, 6),
    ('CCP1', 3, 1, 2015, 7),
    ('CCP1', 4, 2, 2015, 4),
    ('CCP1', 5, 2, 2015, 2),
    ('CCP1', 6, 2, 2015, 2),
    ('CCP1', 7, 3, 2015, 3),
    ('CCP1', 8, 3, 2015, 2),
    ('CCP1', 9, 3, 2015, 1),
    ('CCP1', 10, 4, 2015, 2),
    ('CCP1', 11, 4, 2015, 3),
    ('CCP1', 12, 4, 2015, 4);

CREATE TABLE #Base (CCP VARCHAR(4), BASELINE INT, YEARS INT, QUART INT);
INSERT #Base (CCP, BASELINE, YEARS, QUART) VALUES ('CCP1', 5, 2015, 1);

Hi, Another help can you add an answer for `sql server 2012`. — Pரதீப், Sep 25 '15 at 06:57
I have asked a new question for `Sql Server 2012+` solution. Can you have a look at it http://stackoverflow.com/questions/32787506/running-multiplication-in-t-sql — Pரதீப், Sep 25 '15 at 17:44

score 1 · Answer 2 · answered Sep 24 '15 at 11:37

Maybe there is other, better way to do It, but this should work for you:

SAMPLE DATE

CREATE TABLE #gts
(
    CCP NVARCHAR(20),
    months INT,   
    QUART INT,  
    YEARS NVARCHAR(20),  
    GTS INT
)

CREATE TABLE #BASELINE
(
    CCP NVARCHAR(20),
    baseline INT,     
    YEARS NVARCHAR(20),
    QUART INT

)

INSERT INTO #gts VALUES
('CCP1',    1 ,      1,  '2015',    5),
('CCP1',    2 ,      1,  '2015',    6),
('CCP1',    3 ,      1,  '2015',    7),
('CCP1',    4 ,      2,  '2015',    4),
('CCP1',    5 ,      2,  '2015',    2),
('CCP1',    6 ,      2,  '2015',    2),
('CCP1',    7 ,      3,  '2015',    3),
('CCP1',    8 ,      3,  '2015',    2),
('CCP1',    9 ,      3,  '2015',    1),
('CCP1',    10,      4,  '2015',    2),
('CCP1',    11,      4,  '2015',    3),
('CCP1',    12,      4,  '2015',    4)

INSERT INTO #baseline VALUES ('CCP1', 5, '2015', 1)

QUERY

SELECT          
    B.CCP,
    G.months,
    g.years,
    g.quart,
    Cast(( B.BASELINE * GTS ) AS NUMERIC(38, 15))AS BASE,
    Sum(Cast(( B.BASELINE * GTS ) AS NUMERIC(38, 15)))OVER(PARTITION BY b.CCP) AS Q_SUM
INTO #temp
FROM   #BASELINE B
    INNER JOIN #GTS G
            ON B.CCP = G.CCP
                AND b.QUART = g.QUART
                AND b.YEARS = g.YEARS        
DECLARE @i INT
SET @i = 1
WHILE @i <= 3
BEGIN
INSERT INTO #temp 
SELECT DISTINCT 
    G.CCP,
    G.months,
    g.years,
    g.quart,                
    Cast(( Q_SUM * GTS ) AS NUMERIC(38, 15)),
    Sum(Cast(( BASE * GTS ) AS NUMERIC(38, 15)))OVER(PARTITION BY g.CCP, g.years, g.quart) AS Q_SUM

FROM   #temp C
    INNER JOIN #GTS G
            ON C.CCP = G.CCP
                AND G.YEARS = C.YEARS 
                AND g.QUART = c.quart + 1
SET @i = @i +1
END


SELECT DISTINCT CCP,
                months,
                years,
                quart,
                BASE
INTO #result
FROM #temp

SELECT * 
FROM #result

drop table #baseline
drop table #gts
drop table #temp
drop table #result

OUTPUT

CCP months  years   quart   BASE
CCP1    1   2015    1       25.000000000000000
CCP1    2   2015    1       30.000000000000000
CCP1    3   2015    1       35.000000000000000
CCP1    4   2015    2       360.000000000000000
CCP1    5   2015    2       180.000000000000000
CCP1    6   2015    2       180.000000000000000
CCP1    7   2015    3       2160.000000000000000
CCP1    8   2015    3       1440.000000000000000
CCP1    9   2015    3       720.000000000000000
CCP1    10  2015    4       8640.000000000000000
CCP1    11  2015    4       12960.000000000000000
CCP1    12  2015    4       17280.000000000000000

Thanks for the contribution dude but am looking to avoid loops — Pரதீப், Sep 24 '15 at 12:33

Cummulative multiplication in T-SQL

GTS Table

Baseline table

2 Answers2