Getting the difference - 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 2005 Forums

Transact-SQL (2005)

Getting the difference

Author

Topic

abacusdotcom
Posting Yak Master

133 Posts

Posted - 2009-05-03 : 21:17:40

Guys,
How there guys..

I got this code by Peso

quote:

-- 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

This gives running total.
But I want the running differences.. does that made the delta?
or can I get the delta of this table..
Thanks guys

I sign for fame not for shame but all the same, I sign my name.

aprichard
Yak Posting Veteran

62 Posts

Posted - 2009-05-04 : 05:06:23

Try this u may get idea

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

;WITH Fligt_CTE (AircraftNo,Date,Flight_Hrs, Pre_Flight_Hrs)
AS
(
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,
(select max(x.Flight_Hrs) from @test x where x.AircraftNo = t.AircraftNo and x.date < t.date) PreFlightHr
From @test t
)

SELECT AircraftNo, Date,Flight_Hrs,Pre_Flight_Hrs,
(select sum( Flight_Hrs- ISNULL(x.Pre_Flight_Hrs,0)) from Fligt_CTE x where x.AircraftNo = t.AircraftNo and x.date <= t.date and Pre_Flight_Hrs is not null ) PreFlightHr
FROM Fligt_CTE T
ORDER BY AircraftNo,Date

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-05-04 : 06:41:06

[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

-- Query
;with data (AircraftNo, [date], Flight_Hrs, row_no)
as
(
select AircraftNo, [date], Flight_Hrs, row_no = row_number() over (partition by AircraftNo order by [date])
from @test
)
select d1.*, delta = d1.Flight_Hrs - d2.Flight_Hrs
from data d1
left join data d2 on d1.AircraftNo = d2.AircraftNo
and d1.row_no = d2.row_no + 1

/*
AircraftNo date Flight_Hrs row_no delta
---------- ---------- ------------ -------------------- ------------
38 2006-03-01 1.0000 1 NULL
38 2006-05-10 1.5000 2 .5000
38 2006-07-10 2.0000 3 .5000
100 2003-08-19 4.0000 1 NULL
100 2004-10-04 3.0000 2 -1.0000
100 2006-09-17 1.5000 3 -1.5000

(6 row(s) affected)
*/
[/code]

KH
[spoiler]Time is always against us[/spoiler]

abacusdotcom
Posting Yak Master

133 Posts

Posted - 2009-05-04 : 11:04:11

I bow!!!!!!

Thanks peoples..

Works like charm!!!

I sign for fame not for shame but all the same, I sign my name.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources