July 7, 2003 at 7:28 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:24 am
You are cross posting - the site owners get cross about that kind of thang.
This is one way to create break totals and running totals without cursors or temp tables.
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
breakval break_total running_total
----------- ----------- -------------
1 5 5
2 20 25
3 7 32
July 8, 2003 at 6:53 am
I posted in
Far away is close at hand in the images of elsewhere.
Anon.
July 8, 2003 at 3:56 pm
I realized that after I sent out the first posting. Sorry about that. I'm new to the site but will get the hange of things shortly. Thanks for all the input, it fixed my little problem.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply