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

Question

Post reply

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

Mr. Kapsicum

SSCertifiable

Points: 6128
More actions
February 13, 2015 at 3:35 am

#314583

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

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

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

Mr. Kapsicum SSCertifiable Points: 6128 More actions · Answer 1

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

pietlinden SSC Guru Points: 63506 More actions · Answer 2

Start here: http://www.sqlservercentral.com/articles/Best+Practices/61537/

Luis Cazares SSC Guru Points: 183706 More actions · Answer 3

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

Jeff Moden SSC Guru Points: 1004402 More actions · Answer 4

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)

Jeff Moden SSC Guru Points: 1004402 More actions · Answer 5

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)

Mr. Kapsicum SSCertifiable Points: 6128 More actions · Answer 6

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

;

Jeff Moden SSC Guru Points: 1004402 More actions · Answer 7

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)