March 18, 2015 at 10:48 am
Hello Team -
I have a data table in below format and the need the desired output in 2nd table format
TABLE1
RANKBOOLEANREVENUE
1TRUE100
2FALSE150
3FALSE200
4FALSE250
5FALSE300
6FALSE350
7FALSE400
8TRUE450
9FALSE500
10FALSE550
11FALSE600
12FALSE650
13FALSE700
14FALSE750
15FALSE800
16FALSE850
17FALSE900
18FALSE950
19FALSE1000
20FALSE1050
21FALSE1100
22FALSE1150
23TRUE1200
24FALSE1250
25FALSE1300
26FALSE1350
27FALSE1400
28FALSE1450
29FALSE1500
30FALSE1550
31FALSE1600
32FALSE1650
33FALSE1700
34FALSE1750
35FALSE1800
36FALSE1850
37FALSE1900
38TRUE1950
Desired Output to be:
RANKBOOLEANREVENUERUNNINGTOTAL
1TRUE 100250
2FALSE150400
3FALSE200600
4FALSE250850
5FALSE3001150
6FALSE3501500
7FALSE4001900
8TRUE450450
9FALSE500950
10FALSE5501500
11FALSE6002100
12FALSE6502750
13FALSE7003450
14FALSE7504200
15FALSE8005000
16FALSE8505850
17FALSE9006750
18FALSE9507700
19FALSE10008700
20FALSE10509750
21FALSE110010850
22FALSE115012000
23TRUE12001200
24FALSE12502450
25FALSE13003750
26FALSE13505100
27FALSE14006500
28FALSE14507950
29FALSE15009450
30FALSE155011000
31FALSE160012600
32FALSE165014250
33FALSE170015950
34FALSE175017700
35FALSE180019500
36FALSE185021350
37FALSE190023250
38TRUE19501950
Kindly help.:w00t::w00t:
March 18, 2015 at 10:56 am
This article should help:
Solving the Running Total and Ordinal Rank Problems[/url]
-- Itzik Ben-Gan 2001
March 18, 2015 at 11:31 am
Had a few moments to kill... Here's an example of how to solve this using the link that I provided.
Note that I don't understand where, in row 1 of your example, the number 250 comes from. That said, this should do the trick
IF OBJECT_ID('Tempdb..#TABLE1') IS NOT NULL DROP TABLE #TABLE1;
CREATE TABLE #TABLE1
(
xid int primary key,
isx char(5) not null
CHECK (isx IN ('TRUE','FALSE')),
Revenue int not null,
RunningTotal int null-- used for running total
);
INSERT INTO #TABLE1 (xid,isx,Revenue)
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)), *
FROM (VALUES
('TRUE',100),
('FALSE',150),
('FALSE',200),
('FALSE',250),
('FALSE',300),
('FALSE',350),
('FALSE',400),
('TRUE',450),
('FALSE',500),
('FALSE',550),
('FALSE',600),
('FALSE',650),
('FALSE',700),
('FALSE',750),
('FALSE',800),
('FALSE',850),
('FALSE',900),
('FALSE',950),
('FALSE',1000),
('FALSE',1050),
('FALSE',1100),
('FALSE',1150),
('TRUE',1200)) xx(c1,c2)
--SELECT * FROM #TABLE1;
DECLARE @runningTotal int = 0;
UPDATE #TABLE1
SET @runningTotal = RunningTotal = CASE WHEN isx = 'FALSE' THEN @runningTotal + Revenue ELSE Revenue END
FROM #TABLE1 WITH (TABLOCKX)
OPTION (MAXDOP 1);
SELECT * FROM #TABLE1;
-- Itzik Ben-Gan 2001
March 19, 2015 at 1:32 am
Using Alan's setup data:
Alan.B (3/18/2015)
IF OBJECT_ID('Tempdb..#TABLE1') IS NOT NULL DROP TABLE #TABLE1;
CREATE TABLE #TABLE1
(
xid int primary key,
isx char(5) not null
CHECK (isx IN ('TRUE','FALSE')),
Revenue int not null,
);
Given that this is SQL 2012, this is slightly slower but safer:
SELECT xid, isx, Revenue
,RunningTotal=SUM(Revenue) OVER
(PARTITION BY grp ORDER BY xid ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
FROM
(
SELECT xid, isx, Revenue
,grp=COUNT(CASE isx WHEN 'TRUE' THEN 1 END) OVER
(ORDER BY xid ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
FROM #TABLE1
) a;
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply