SUM all until second last value

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 2005 Forums

Transact-SQL (2005)

SUM all until second last value

Author

Topic

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-08-04 : 21:30:40

[code]DECLARE @tempfun TABLE
(
col1 int,
col2 DATETIME
)

INSERT INTO @tempfun
SELECT 1, '20070101' UNION ALL
SELECT 2, '20070102' UNION ALL
SELECT 3, '20070201' UNION ALL
SELECT 4, '20070202' UNION ALL
SELECT 5, '20070301' UNION ALL
SELECT 6, '20070302' UNION ALL
SELECT 7, '20070401' UNION ALL
SELECT 8, '20070402' UNION ALL
SELECT 9, '20070501' UNION ALL
SELECT 10, '20070502'

SELECT SUM(col1) as TotalSUM
FROM @tempfun
WHERE col2<='20070502'[/code]

This will get
TotalSUM
55

How i can get
TotalSUM | PrevousTotalSUM
55 | 45

Edit. wrong calculation..

Hope can help...but advise to wait pros with confirmation...

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-08-04 : 21:46:51

[code]SELECT SUM(a.col1)TotalSUM,
(SELECT SUM(col1)
FROM (SELECT row_number() over (order by col1, col2)col0, col1
FROM @tempfun
where col2 <='20070502'
)a
WHERE col0<=(SELECT count(*) FROM @tempfun where col2 <='20070502')-1)PreviousTotalSUM
FROM @tempfun a
WHERE a.col2 <='20070502'[/code]
solved but slow...

Hope can help...but advise to wait pros with confirmation...

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-08-04 : 22:27:07

anyone with better query will be helpful...thx first

Hope can help...but advise to wait pros with confirmation...

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-08-04 : 22:38:56

[code]
SELECT TotalSUM = SUM(col1),
PreviousTotalSUM = SUM(CASE WHEN row_no > 1 THEN col1 ELSE 0 END)
FROM
(
SELECT col1, col2, row_no = row_number() OVER (ORDER BY col2 DESC)
FROM @tempfun
WHERE col2 <= '20070502'
) t
[/code]

KH
[spoiler]Time is always against us[/spoiler]

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-08-04 : 22:54:45

thx sifu...i seems to be never remember got "DESC" this key word

Hope can help...but advise to wait pros with confirmation...

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-08-04 : 22:58:20

KH
[spoiler]Time is always against us[/spoiler]

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-08-05 : 00:53:08

Hi try this once,


DECLARE @tempfun TABLE
(
	col1 int,
	col2 DATETIME
)

INSERT INTO @tempfun
SELECT	1, '20070101' UNION ALL
SELECT	2, '20070102' UNION ALL
SELECT	3, '20070201' UNION ALL
SELECT	4, '20070202' UNION ALL
SELECT	5, '20070301' UNION ALL
SELECT	6, '20070302' UNION ALL
SELECT	7, '20070401' UNION ALL
SELECT	8, '20070402' UNION ALL
SELECT	9, '20070501' UNION ALL
SELECT	10, '20070502'

declare @maxvalue datetime
select @maxvalue = max(col2) from @tempfun
select sum(col1) from @tempfun where col2< @maxvalue

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-08-05 : 03:11:06

Can I play too?

-- Peso 1
SELECT	SUM(Col1) AS Total,
	SUM(COALESCE(NULLIF(Col1, theMAX), 0)) AS Previous
FROM	(
		SELECT	Col1,
			MAX(Col1) OVER () AS theMax
		FROM	@TempFun
	) AS d

-- Peso 2
SELECT	SUM(Col1) AS Total,
	SUM(CASE Col2 WHEN theMAX THEN 0 ELSE Col1 END) AS Previous
FROM	(
		SELECT	Col1,
			Col2,
			MAX(Col2) OVER () AS theMax
		FROM	@TempFun
	) AS d