Running Total on Rows between Groups

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

Running Total on Rows between Groups

Author

Topic

crackerbox
Starting Member

21 Posts

Posted - 2006-12-05 : 16:12:19

I want to write a stored procedure to add all of the flying time on a given airplane for each flight until now and then reset the total when it gets to a new airplane. I want to be able to pull from the table all flying time from a given date for a given airplane.

For example:

AircraftNo Date Flight_Hrs Total
38 3/1/06 1 4.5
38 5/10/06 1.5 3.5
38 7/10/06 2 2

100 8/19/03 4 8.5
100 10/04/04 3 4.5
100 9/17/06 1.5 1.5

I pulled the following code from the internet but I'm getting errors.

select AircraftNo
,Actual_departing_date,Flight_Hours
,sum(Flight_hrs) over partition by AircraftNo
order by Date
rows between current row and unbounded following) total
from (Select Distinct
F.AircraftNo
,F.Date
,C.Flight_Hrs
From GDB_01_4_Test.dbo.Flight_Log F,
GDB_01_4_Test.dbo.Flight_Cycle_Count C
Where F.Doc_No = C.Flight_log_Doc_No

Am I doing something wrong. I'm getting the following syntax errors (Using SQL 2000):

Server: Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'over'.
Server: Msg 170, Level 15, State 1, Line 18
Line 18: Incorrect syntax near 'Flight_log_Doc_No'.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-05 : 16:21:59

[code]-- prepare test data
declare @test table (AircraftNo tinyint, Date datetime, Flight_Hrs smallmoney)

insert @test
select 38, '3/1/06', 1 union all
select 100, '8/19/03', 4 union all
select 38, '7/10/06', 2 union all
select 100, '10/04/04', 3 union all
select 38, '5/10/06', 1.5 union all
select 100, '9/17/06', 1.5

-- do the work
select t.AircraftNo,
t.Date,
t.Flight_Hrs,
(select sum(x.Flight_Hrs) from @test x where x.AircraftNo = t.AircraftNo and x.date <= t.date) Total
From @test t
order by t.AircraftNo,
t.Date[/code]

Peter Larsson
Helsingborg, Sweden

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-12-05 : 16:22:21

You're getting an error because that code you have is for SQL Server 2005, the OVER operator isn't supported on SQL Server 2000.

To get running totals on SQL Server 2000 is a bit more tricky, see
http://sqlteam.com/item.asp?ItemID=3856

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-05 : 16:26:58

Not that tricky with a correlated subquery.

Peter Larsson
Helsingborg, Sweden

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-12-05 : 16:31:24

Nope, just a bit

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-12-06 : 10:16:27

Solution 4 : If you use front end application, Do Running Total there

Madhivanan

Failing to plan is Planning to fail