help with nested totals and running totals

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

help with nested totals and running totals

Author

Topic

dpais
Yak Posting Veteran

60 Posts

Posted - 2007-12-05 : 10:40:59

Please help with the following query.

TABLE
DATE____________LIST-1___LIST-2_____R-TOTAL
12/5/2007________5_______5__________10
12/6/2007________4_______14_________28
12/7/2007________5_______33_________66
12/8/2007________6_______72_________144
12/9/2007________1_______145________290
12/10/2007_______2_______292________584
12/11/2007_______9_______593________1186
12/12/2007_______3_______1189_______2378
12/13/2007_______1_______2379_______4758

for each day
LIST_1 = GIVEN STATIC DATA
LIST_2 = calculated by previous r_total + current list_1
R_TOTAL = previous r_total + current list_1 + current list_2

Thanks in advance.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-05 : 10:47:02

We only help with homework question if you also post you have accomplished this far.

E 12°55'05.25"
N 56°04'39.16"

dpais
Yak Posting Veteran

60 Posts

Posted - 2007-12-05 : 10:51:22

Fair Enough Peso : Thanks for the quick reply -

I know how to do a running total without cursors .... that much i can handle - so the column R_total is a walk in the park for me - but where i get stuck is when you have 2 columns in a table that use the previous row values ... sort of like a nested running total.

any direction is appreciated.

thanks again.

quote:
Originally posted by Peso

We only help with homework question if you also post you have accomplished this far.

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-05 : 11:05:59

[code]-- Prepare sample data
DECLARE @Sample TABLE ([DATE] SMALLDATETIME PRIMARY KEY CLUSTERED, [LIST-1] INT, [LIST-2] INT, [R-TOTAL] INT)

INSERT @Sample
(
[DATE],
[LIST-1]
)
SELECT '12/5/2007', 5 UNION ALL
SELECT '12/6/2007', 4 UNION ALL
SELECT '12/7/2007', 5 UNION ALL
SELECT '12/8/2007', 6 UNION ALL
SELECT '12/9/2007', 1 UNION ALL
SELECT '12/10/2007', 2 UNION ALL
SELECT '12/11/2007', 9 UNION ALL
SELECT '12/12/2007', 3 UNION ALL
SELECT '12/13/2007', 1

-- Clear any previous value for LIST-2 and R-TOTAL
UPDATE @Sample
SET [LIST-2] = NULL,
[R-TOTAL] = NULL

-- Initialize staging control mechanism
DECLARE @List2 INT,
@rTotal INT

SELECT @List2 = 0,
@rTotal = 0

-- Update the missing values
UPDATE @Sample
SET @List2 = [LIST-2] = @rTotal + [LIST-1],
@rTotal = [R-TOTAL] = @rTotal + [LIST-1] + @List2

-- Show the expected output
SELECT [DATE],
[LIST-1],
[LIST-2],
[R-TOTAL]
FROM @Sample
ORDER BY [DATE][/code]

E 12°55'05.25"
N 56°04'39.16"

dpais
Yak Posting Veteran

60 Posts

Posted - 2007-12-05 : 11:17:40

PESO -

your a sql-god.

thank you Sir.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources