conditional total in stored procedure

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

General SQL Server Forums

New to SQL Server Programming

conditional total in stored procedure

Author

Topic

BCullenward
Starting Member

28 Posts

Posted - 2006-08-11 : 10:36:19

Is there a simple way to do conditional totaling?

I have a table that has incremental values, so the maximum value is the current total, however on occassion the value is reset and the increment begins anew. What I need to do is get the total the maximum value each time it is reset.

ie.

Value Date
----- -----
12 1-1
14 1-2
100 1-8
35 1-10
50 1-12
5 1-15

the total would be 155 (100 + 50 + 5)

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-08-11 : 10:45:27

try this

declare @table table
(
	Value 	int,
	Date	datetime
)

insert into @table
select	12, '1-1-2006'	union all
select	14, '1-2-2006'	union all
select	100, '1-8-2006'	union all
select	35, '1-10-2006'	union all
select	50, '1-12-2006'	union all
select	5, '1-15-2006'

select	sum(Value)
from
(
	select	*,
		prev_value = (select top 1 Value from @table x where x.Date < t.Date order by Date desc),
		next_value = (select top 1 Value from @table x where x.Date > t.Date order by Date)
	from	@table t
) a
where	(	Value > prev_value
	and	Value > next_value
	)
or	next_value	is null