how to sum accumutated values - SQL Server Forums

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

SQL Server Development (2000)

how to sum accumutated values

Author

Topic

CLages
Posting Yak Master

116 Posts

Posted - 2005-04-28 : 14:15:15

Hi , its me again
I have a table like this

Date(DD/MM/YYYY) Value etc
01-04-2005 100,00 ...
01-04-2005 200,00 ...

02-04-2005 230,00 ...
02-04-2005 130,00 ...
03-04-2005 200,00 ...

04-04-2005 120,00 ...
04-04-2005 220,00 ...

05-04-2005 300,00 ...

if the user type a range of data 02/04/2005 to 05-04-2005

if i do

select * from (
select 'Ant' as Data, sum(dup_valor) as Valor from duplicat
where dup_data < 02/04/2005
)

union all

select * from (
select cast(dup_data as char(12)) as Data, sum(dup_valor) as
Valor from duplicat
where dup_data Between 02/04/2005 and 05/04/2005
group by dup_data
)

order by dup_data

I get
Data Valor
Ant 300
02-04-05 360
03-04-05 200
04-04-05 340
05-04-05 300

works fine. but i would like get something like this

Data Valor Accumulated
Ant 300 300
02-04-05 360 660
03-04-05 200 860
04-04-05 340 1200
05-04-05 300 1500

How can i Implement the column Accumulated?

tks
Carlos Lages

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-04-28 : 17:27:20

Run this in a QA window to test:


--set up the table (if you did this I wouldn't need to guess the datatypes)
set nocount on
set dateformat dmy
declare @duplicat table (dup_data smalldatetime, dup_valor int)
insert @duplicat
select '01-04-2005', 100 union all
select '01-04-2005', 200 union all
select '02-04-2005', 230 union all
select '02-04-2005', 130 union all
select '03-04-2005', 200 union all
select '04-04-2005', 120 union all
select '04-04-2005', 220 union all
select '05-04-2005', 300

--user input
declare @dtLow smalldatetime
	,@dtHigh smalldatetime
select	@dtLow = convert(smalldatetime,'02/04/2005')
	,@dtHigh = convert(smalldatetime,'05/04/2005')


--sql statment
select	Data = case 
		when dat = @dtLow -1 then 'Ant'
		else convert(varchar,Dat,105) 
		end
	,valor = sum(valor)
	,accumulated = (select sum(dup_valor) from @duplicat where dup_data <= a.dat)
From	(
	select	Dat = case 
			when dup_data < @dtLow then @dtLow -1
			when dup_data between @dtLow and @dtHigh then dup_data
			else null
			end
		,valor = dup_valor
	from	@Duplicat
	Where	dup_data <= @dtHigh
	) a
group by dat
order by dat

Be One with the Optimizer
TG

CLages
Posting Yak Master

116 Posts

Posted - 2005-04-28 : 20:29:33

Tg, Thanks
fantastic your solution
Tks a lot
You get a fan

C. Lages

CLages
Posting Yak Master

116 Posts

Posted - 2005-04-28 : 20:45:54

Tg only one question

I did not Understand the statement you wrote
valor = sum(valor)

where did you declared the variable "Valor" ?

tks
again
C. Lages

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-04-28 : 21:05:53

You're welcome!

valor = sum(valor)
is the same as:
sum(valor) as Valor

valor is not a variable, it's a column alias

Be One with the Optimizer
TG

mpetanovitch
Yak Posting Veteran

52 Posts

Posted - 2005-04-29 : 13:17:25

This one is simplier; however it doesn't completely satisfy your requirements.



CREATE TABLE #temp (yak varchar(20), value int)
INSERT INTO #temp
VALUES('1/1/2005', 50)
INSERT INTO #temp
VALUES('1/2/2005', 50)
INSERT INTO #temp
VALUES('1/3/2005', 100)
INSERT INTO #temp
VALUES('1/4/2005', 50)
INSERT INTO #temp
VALUES('1/5/2005', 50)
INSERT INTO #temp
VALUES('1/6/2005', 50)
INSERT INTO #temp
VALUES('1/7/2005', 200)

DECLARE @startdate datetime, @enddate datetime
SET @startdate = '1/4/2005'
SET @enddate = '1/6/2005'

SELECT 
	t.yak,
	t.value,
	SUM(t1.value) AS running_total
FROM #temp t
LEFT OUTER JOIN #temp t1 ON t.yak >= t1.yak 
WHERE t.yak BETWEEN @startdate AND @enddate
GROUP BY t.yak, t.value
ORDER BY 1

RESULT SET:
yak             value   running_total
1/4/2005	50	250--<--running total - value = "Ant"
1/5/2005	50	300
1/6/2005	50	350

Here would be the complete result set:
yak             value   running_total
1/1/2005	50	50
1/2/2005	50	100
1/3/2005	100	200
1/4/2005	50	250
1/5/2005	50	300
1/6/2005	50	350
1/7/2005	200	550

Notice that the running total is still calculated even though the where clause removes them from view in the first result set.

The requrement does not satisfied is the "ant" line which totals all values before the startdate.

Mike Petanovitch

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources