July 7, 2003 at 3:25 pm
I have a stored procedure that utilizes a temp table to insert values from a cursor and then calculates a running total based on the records. My problem is, I need to group by at least two of the records and then have the running total re-zero itself for each group. In other words a running total for each group. Below is the script for the sp. This script keeps a running total for all groups based on the sort order. Not really what I am looking for. Any ideas would be appreciated.
--CREATE procedure sp_ngst as
Create table #ngst -- Build temp table
(
pointclassvarchar(32),
pointvarchar(32),
volumenumeric(15),
maxstoragenumeric(15,4),
minstoragenumeric(15,4),
quantitytypevarchar(32),
dividernumeric(5),
timeunitvarchar(6),
daysdatetime,
RunningTotalnumeric(15)
)
Declare @pointclassvarchar(32), -- Declare all variables used in the cursor.
@pointvarchar(32),
@volumenumeric(15),
@maxstoragenumeric(15,4),
@minstoragenumeric(15,4),
@quantitytypevarchar(32),
@dividernumeric(5),
@timeunitvarchar(6),
@daysdatetime,
@RunningTotalnumeric(15)
Set @RunningTotal = 0 -- Set running total initially to zero.
Declare a_cursor CURSOR -- Declare and open the base select statement using CASE to return calculated results.
for
Select isnull(ngp.pointclass,'None') pointclass, ngp.point,
CASE
When ngq.timeunit = 'MONTH' THEN ngq.volume/(datediff(dd, ngq.begtime, ngq.endtime))
When ngq.timeunit = 'DAY' THEN ngq.volume*24
When ngq.timeunit = 'TOTAL' and datediff(dd, ngq.begtime, ngq.endtime) > 0 THEN ngq.volume/(datediff(dd, ngq.begtime, ngq.endtime))
Else ngq.volume
End As volume
,ngp.maxstorage, ngp.minstorage, ngq.quantitytype, datediff(dd, ngq.begtime, ngq.endtime) AS divider, ngq.timeunit, tc.days
from ngpoint ngp, ngposition ngptn, ngquantity ngq, temp_calendar tc
where ngq.position = ngptn.position
and ngq.seq = ngptn.seq
and ngp.point = ngptn.point
and tc.days >= ngq.begtime
and tc.days <= ngq.endtime
and ngq.posstatus = 'Y'
order by ngp.point, tc.days
Open a_cursor
Fetch next from a_cursor into @pointclass, @point, @volume, @maxstorage, @minstorage, @quantitytype, @divider, @timeunit, @days
While @@FETCH_STATUS = 0 -- Loop through the cursor and insert the variables into the temp table.
Begin
Set @RunningTotal = @RunningTotal + @volume
Insert #ngst values (@pointclass, @point, @volume, @maxstorage, @minstorage, @quantitytype, @divider, @timeunit, @days, @RunningTotal)
Fetch next from a_cursor into @pointclass, @point, @volume, @maxstorage, @minstorage, @quantitytype, @divider, @timeunit, @days
End
Close a_cursor
Deallocate a_cursor
Select * from #ngst -- Select the result of the insert.
order by point
compute sum(volume) by point
drop table #ngst -- Drop the temp table and start over.
GO
July 8, 2003 at 3:15 am
Here is a technique to calculate break totals (sum of a group) and running totals (sum of all groups to date. Perhaps you could see a different way to solve your problem then.
--
T-SQL
set nocount on
create table #running
(keyval int identity(1,1) not null
, breakval int not null
, runval int not null)
insert into #running (breakval, runval) values (1,1)
insert into #running (breakval, runval) values (2,3)
insert into #running (breakval, runval) values (3,2)
insert into #running (breakval, runval) values (2,6)
insert into #running (breakval, runval) values (3,5)
insert into #running (breakval, runval) values (2,2)
insert into #running (breakval, runval) values (1,1)
insert into #running (breakval, runval) values (2,9)
insert into #running (breakval, runval) values (1,3)
set nocount off
select
r.breakval
, (select sum(r1.runval) from #running r1 where r1.breakval=r.breakval) as break_total
, (select sum(r2.runval) from #running r2 where r2.breakval<=r.breakval) as running_total
from #running r
group by r.breakval
order by r.breakval asc
drop table #running
--
OUTPUT
breakval break_total running_total
----------- ----------- -------------
1 5 5
2 20 25
3 7 32
(3 row(s) affected)
July 8, 2003 at 6:29 am
For a solution without cursors
create the temp table (as above)
select into the temp table (as per your select for the first cursor)
update the temp table using
declare@RunningTotal numeric(15),
@point varchar(32),
@days datetime
set @RunningTotal = 0
set @point = ''
set @days = ''
update x
set @RunningTotal = (case when (x.point<>@point or x.days <> @days) then volume else @RunningTotal+volume end),
x.RunningTotal = @RunningTotal,
@point = x.point,
@days = x.days
from #ngst x
then run your final query
Far away is close at hand in the images of elsewhere.
Anon.
July 8, 2003 at 10:27 am
That seems to be what I am looking for. I will try that to see. Thanks.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply