SQL Help: Running row Total with Thresholds.

Question

SQL Help: Running row Total with Thresholds.

subahan_syed

Right there with Babe

Points: 721
More actions
March 18, 2015 at 7:31 am

#315847

Hello Team -
I have a data table in below format and the need the desired output in 2nd table format
TABLE1
RANKBOOLEANREVENUE
1TRUE 100
2FALSE150
3FALSE200
4FALSE250
5FALSE300
6FALSE350
7FALSE400
8TRUE 450
9FALSE500
10FALSE550
11FALSE600
12FALSE650
13FALSE700
14FALSE750
15FALSE800
16FALSE850
17FALSE900
18FALSE950
19FALSE1000
20FALSE1050
21FALSE1100
22FALSE1150
23TRUE 1200
24FALSE1250
25FALSE1300
26FALSE1350
27FALSE1400
28FALSE1450
29FALSE1500
30FALSE1550
31FALSE1600
32FALSE1650
33FALSE1700
34FALSE1750
35FALSE1800
36FALSE1850
37FALSE1900
38TRUE 1950
Desired Output to be:
RANKBOOLEANREVENUERUNNINGTOTAL
1TRUE 100250
2FALSE150400
3FALSE200600
4FALSE250850
5FALSE3001150
6FALSE3501500
7FALSE4001900
8TRUE 450450
9FALSE500950
10FALSE5501500
11FALSE6002100
12FALSE6502750
13FALSE7003450
14FALSE7504200
15FALSE8005000
16FALSE8505850
17FALSE9006750
18FALSE9507700
19FALSE10008700
20FALSE10509750
21FALSE110010850
22FALSE115012000
23TRUE 12001200
24FALSE12502450
25FALSE13003750
26FALSE13505100
27FALSE14006500
28FALSE14507950
29FALSE15009450
30FALSE155011000
31FALSE160012600
32FALSE165014250
33FALSE170015950
34FALSE175017700
35FALSE180019500
36FALSE185021350
37FALSE190023250
38TRUE 19501950
Kindly help.

Viewing 6 posts - 1 through 5 (of 5 total)

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

pietlinden SSC Guru Points: 63503 More actions · Answer 1

Okay, what version of SQL Server are you using, because this has zero to do with SSIS? This works in 2012, and I think in 2008.

This is a windowing function answer:

use tempdb;

go

SELECT rnk

, bool

, Revenue

, SUM(Revenue)

OVER (PARTITION BY bool ORDER BY rnk

ROWS BETWEEN UNBOUNDED PRECEDING

AND CURRENT ROW) AS rsRevenue

FROM

(SELECT 1 As Rnk,'True' As Bool,100 As Revenue

UNION ALL

SELECT 2,'FALSE',150

UNION ALL

SELECT 3,'FALSE',200

UNION ALL

SELECT 4,'FALSE',250

UNION ALL

SELECT 5,'FALSE',300

UNION ALL

SELECT 6,'FALSE',350

UNION ALL

SELECT 7,'FALSE',400

UNION ALL

SELECT 8,'TRUE',450

UNION ALL

SELECT 9,'FALSE',500

UNION ALL

SELECT 10,'FALSE',550) x

ORDER BY rnk;

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

pietlinden (3/18/2015)
Okay, what version of SQL Server are you using, because this has zero to do with SSIS? This works in 2012, and I think in 2008.
This is a windowing function answer:

Window functions (except ranking functions) were introduced on 2012. The code will work fine on 2012+ but not on 2008.

Please don't cross post. There was an answer already in here:

http://www.sqlservercentral.com/Forums/Topic1669592-3077-1.aspx

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: 1004343 More actions · Answer 3

subahan_syed (3/18/2015)
Hello Team -
I have a data table in below format and the need the desired output in 2nd table format
TABLE1
RANKBOOLEANREVENUE
1TRUE 100
2FALSE150
3FALSE200
4FALSE250
5FALSE300
6FALSE350
7FALSE400
8TRUE 450
9FALSE500
10FALSE550
11FALSE600
12FALSE650
13FALSE700
14FALSE750
15FALSE800
16FALSE850
17FALSE900
18FALSE950
19FALSE1000
20FALSE1050
21FALSE1100
22FALSE1150
23TRUE 1200
24FALSE1250
25FALSE1300
26FALSE1350
27FALSE1400
28FALSE1450
29FALSE1500
30FALSE1550
31FALSE1600
32FALSE1650
33FALSE1700
34FALSE1750
35FALSE1800
36FALSE1850
37FALSE1900
38TRUE 1950
Desired Output to be:
RANKBOOLEANREVENUERUNNINGTOTAL
1TRUE 100250
2FALSE150400
3FALSE200600
4FALSE250850
5FALSE3001150
6FALSE3501500
7FALSE4001900
8TRUE 450450
9FALSE500950
10FALSE5501500
11FALSE6002100
12FALSE6502750
13FALSE7003450
14FALSE7504200
15FALSE8005000
16FALSE8505850
17FALSE9006750
18FALSE9507700
19FALSE10008700
20FALSE10509750
21FALSE110010850
22FALSE115012000
23TRUE 12001200
24FALSE12502450
25FALSE13003750
26FALSE13505100
27FALSE14006500
28FALSE14507950
29FALSE15009450
30FALSE155011000
31FALSE160012600
32FALSE165014250
33FALSE170015950
34FALSE175017700
35FALSE180019500
36FALSE185021350
37FALSE190023250
38TRUE 19501950
Kindly help.

Kindly help us help you in the future. 😛 Please read an heed the article at the first link under "Helpful links" in my signature line below for your future posts.

--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: 1004343 More actions · Answer 4

pietlinden (3/18/2015)
Okay, what version of SQL Server are you using, because this has zero to do with SSIS? This works in 2012, and I think in 2008.

Credits to Navy Beans says it'll be 2005.

--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)

pietlinden SSC Guru Points: 63503 More actions · Answer 5

Good point, Luis. Posting a question like this in an integration services group just made me wonder. Hence my question.