Query Help : Recursive Running Total OR Lead/Lag Function for N number of PartitionValue

  • USE tempdb

    -- Table Script

    CREATE TABLE TestTable

    (

    ID INT,

    Period CHAR(10),

    ProjectID INT,

    Proportion NUMERIC(10,4)

    )

    -- Insert Data in Table

    INSERT INTO TestTable

    SELECT

    3338,'Dec 2014',301,1550.0000

    UNION ALL

    SELECT

    3338,'Dec 2014',2118,1240.0000

    UNION ALL

    SELECT

    3338,'Dec 2014',2026,310.0000

    UNION ALL

    SELECT

    58842,'Dec 2014',346,775.0000

    UNION ALL

    SELECT

    58842,'Dec 2014',534,2325.0000

    SELECT * FROM TestTable

    -- Query for Result I.e Preparing Data in a query that i want to make using a Query. ( Only to be used for ResultSet ).

    SELECT * FROM

    (

    SELECT

    ID,

    Period,

    ProjectID,

    Proportion,

    1240.0000 AS OtherProportionInResultSet1,

    310 AS OtherProportionInResultSet2

    FROM TestTable

    WHERE ID=3338 AND ProjectID=301

    UNION ALL

    SELECT

    ID,

    Period,

    ProjectID,

    Proportion,

    1550.0000 AS OtherProportion1,

    310 AS OtherProportion2

    FROM TestTable

    WHERE ID=3338 AND ProjectID=2118

    UNION ALL

    SELECT

    ID,

    Period,

    ProjectID,

    Proportion,

    1550.0000 AS OtherProportion1,

    1240 AS OtherProportion2

    FROM TestTable

    WHERE ID=3338 AND ProjectID=2026

    UNION ALL

    SELECT

    ID,

    Period,

    ProjectID,

    Proportion,

    2325.0000 AS OtherProportion1,

    0.0000 AS OtherProportion2

    FROM TestTable

    WHERE ID=58842 AND ProjectID=346

    UNION ALL

    SELECT

    ID,

    Period,

    ProjectID,

    Proportion,

    775.0000 AS OtherProportion1,

    0.0000 AS OtherProportion2

    FROM TestTable

    WHERE ID=58842 AND ProjectID=534

    )A

    Required Result Explanation :

    For ID : 3338 ,there are 3 rows. And Result Set Includes rest of the two Valued for that ID. Likewise, for ID=58842, there are 2 rows. ( this can be implemented through Lead and lag function.

    but, since, original Table has Many ID and Each Id may have N number of Rows window for it, hence need to frame a query that will do it for all ID.

    Formula has to like this:

    Proportion / ( Proportion + OtherPropotionInResultSet1 + OtherPropotionInResultSet2 + OtherPropotionInResultSet(n) )

    calculated for each ID , for each row

  • Guys, Please Help with the problem , let me know if the problem is not well drafted Here.

  • Basically, you want to divide the proportion by the running total of proportion.

    Quick question. How do you define the order? I think I know the answer but want to be sure.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • Mr. Kapsicum (2/13/2015)


    Guys, Please Help with the problem , let me know if the problem is not well drafted Here.

    Nah... you did good. You just have to be a little patient if you want a good answer. Heh... and some of us do have full time jobs and can't actually do much during the day to answer your urgent needs. 😉

    On with the show...

    Here's your original test data setup with some rows added so we can see some other "possibilities" as well as what you're requested because, as we all know, the users will always ask for more than the original request. 😀

    DROP TABLE dbo.TestTable

    GO

    --===== Do this in a nice, safe place that everyone has

    USE tempdb

    ;

    --===== Create the test table

    CREATE TABLE dbo.TestTable

    (

    ID INT

    ,Period CHAR(10)

    ,ProjectID INT

    ,Proportion NUMERIC(10,4)

    )

    ;

    --===== Populate the test table with test data

    INSERT INTO dbo.TestTable

    (ID, Period, ProjectID, Proportion)

    SELECT 3338,'Dec 2014', 301,1550.0000 UNION ALL

    SELECT 3338,'Dec 2014',2118,1240.0000 UNION ALL

    SELECT 3338,'Dec 2014',2026, 310.0000 UNION ALL

    SELECT58842,'Dec 2014', 346, 775.0000 UNION ALL

    SELECT58842,'Dec 2014', 534,2325.0000 UNION ALL

    SELECT 3338,'Nov 2014', 301,1000.0000 UNION ALL --Added

    SELECT 3338,'Nov 2014',2118,2000.0000 UNION ALL --Added

    SELECT 3338,'Nov 2014',2026,3000.0000 UNION ALL --Added

    SELECT 3338,'Nov 2014',1234,4000.0000 --Added

    ;

    For the example table and data that you presented (thank you for that) and the extra data that I added, here's one possible solution along with some extra goodies that will be obvious by the names of the calculated columns. The calculation that you're looking for in your original post is labeled "PercentOfIDTotal".

    SELECT ID

    ,Period

    ,ProjectID

    ,Proportion

    ,PercentOfIDPeriod = CAST((Proportion*100.00)/SUM(Proportion)OVER(PARTITION BY ID,Period) AS NUMERIC(4,1))

    ,PercentOfIDTotal = CAST((Proportion*100.00)/SUM(Proportion)OVER(PARTITION BY ID) AS NUMERIC(4,1))

    ,PercentOfGrandTotal = CAST((Proportion*100.00)/SUM(Proportion)OVER(PARTITION BY (SELECT NULL)) AS NUMERIC(4,1))

    ,PercentOfProject = CAST((Proportion*100.00)/SUM(Proportion)OVER(PARTITION BY ProjectID) AS NUMERIC(4,1))

    FROM dbo.TestTable

    ORDER BY ID,CAST(Period AS DATETIME),ProjectID --Trick to sort alpha dates correctly

    ;

    The result is as follows.

    ID Period ProjectID Proportion PercentOfIDPeriod PercentOfIDTotal PercentOfGrandTotal PercentOfProject

    ----- -------- --------- ---------- ----------------- ---------------- ------------------- ----------------

    3338 Nov 2014 301 1000.0000 10.0 7.6 6.2 39.2

    3338 Nov 2014 1234 4000.0000 40.0 30.5 24.7 100.0

    3338 Nov 2014 2026 3000.0000 30.0 22.9 18.5 90.6

    3338 Nov 2014 2118 2000.0000 20.0 15.3 12.3 61.7

    3338 Dec 2014 301 1550.0000 50.0 11.8 9.6 60.8

    3338 Dec 2014 2026 310.0000 10.0 2.4 1.9 9.4

    3338 Dec 2014 2118 1240.0000 40.0 9.5 7.7 38.3

    58842 Dec 2014 346 775.0000 25.0 25.0 4.8 100.0

    58842 Dec 2014 534 2325.0000 75.0 75.0 14.4 100.0

    (9 row(s) affected)

    Some indexes would help this but I'll let you experience that particular joy. 😛

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Luis Cazares (2/14/2015)


    Basically, you want to divide the proportion by the running total of proportion.

    Quick question. How do you define the order? I think I know the answer but want to be sure.

    I could be wrong but looking at the formula in the original post, I don't believe that it's a running total problem. It looks more like a percent of partitioned total problem.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeff Moden (2/14/2015)


    Luis Cazares (2/14/2015)


    Basically, you want to divide the proportion by the running total of proportion.

    Quick question. How do you define the order? I think I know the answer but want to be sure.

    I could be wrong but looking at the formula in the original post, I don't believe that it's a running total problem. It looks more like a percent of partitioned total problem.

    First of all, Thank you very much Jeff Moden for the "Brilliant Query". It solved my problem.

    And, Mr. Luis is right , i wanted to divide the proportion by running total of proportion on the basis of ID and Period. But you query 99.99 Correct , just a little modification and i got my solution. ( I have been using window function and partition by, but never thought it can be use like this, A NEW learning of this week. )

    Thank you again for the Reply from both of you masters.

    I am posting my Query which gave me the required Result set.

    SELECT ID

    ,Period

    ,ProjectID

    ,Proportion

    ,SUM(Proportion)OVER(PARTITION BY ID,Period) AS RunningTotalofProportion

    ,CAST((Proportion)/SUM(Proportion)OVER(PARTITION BY ID,Period) AS NUMERIC(5,4)) AS DividedProportion

    FROM dbo.TestTable

    ORDER BY ID,CAST(Period AS DATETIME),ProjectID --Trick to sort alpha dates correctly

    ;

  • Thank you very much for the feedback. I really appreciate you posting your final code.

    Just as a clarification of terms, though... that's not a "running" total. It's a simple periodic total. A running total is where all data previous to the current period is aggregated and included in the current period. Like this (and that's not what you wanted)...

    Period PeriodTotal RunningTotal

    ======== =========== ============

    Dec 2014 1 1 --Total of all Prior periods

    Jan 2015 2 3 --Total of all Prior periods

    Feb 2015 3 6 --Total of all Prior periods

    Mar 2015 4 10 --Total of all Prior periods

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply