Date closest to another date - 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)

Date closest to another date

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2007-01-04 : 09:43:43

Sandra writes "I have two view files - View_One that has

aircraft date hours
38 4/19/06 451
38 4/20/06 455
38 6/19/06 715
40 1/20/06 15
40 8/1/06 35

View_two has

aircraft inspect_date Return_Hours (from view one)

38 5/16/06 455
40 1/20/06 15

The Return_hours is what I'm trying to get. I want to get the hours from view one and return the hours that is closest to the inspect_date in view two.

Any ideas?????

Thanks"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-04 : 10:03:06

[code]-- prepare sample data
declare @t1 table (aircraft int, dt datetime, hours int)

insert @t1
select 38, '4/19/06', 451 union all
select 38, '4/20/06', 455 union all
select 38, '6/19/06', 715 union all
select 40, '1/20/06', 15 union all
select 40, '8/1/06', 35

declare @t2 table (aircraft int, dt datetime)

insert @t2
select 38, '5/16/06' union all
select 40, '1/20/06'

-- pick the hours
select t2.aircraft,
t2.dt,
(select top 1 t1.hours from @t1 t1 where t1.aircraft = t2.aircraft order by abs(datediff(day, t1.dt, t2.dt)))
from @t2 t2[/code]

Peter Larsson
Helsingborg, Sweden

crackerbox
Starting Member

21 Posts

Posted - 2007-01-04 : 13:31:04

This is great but I have another piece of information. Each Aircraft has multiple inspections and each inspection has it's own date. I modified your script as:

-- prepare sample data
declare @t1 table (aircraft int, dt datetime, hours int)
insert @t1
select 38, '4/19/06', 451 union all
select 38, '4/20/06', 455 union all
select 38, '6/19/06', 715 union all
select 40, '1/20/06', 15 union all
select 40, '8/1/06', 35
declare @t2 table (aircraft int, inspection varchar (20),dt datetime)
insert @t2
select 38, 'Gen Task','5/16/06' union all
select 38, 'Engines','3/16/06' union all
select 38, 'Oil Change','11/16/06' union all
select 38, 'Propeller Change','1/16/06' union all
select 40, 'Gen Task','4/16/06' union all
select 40, 'Oil Change','2/19/06' union all
select 40, 'Wire','12/16/06' union all
select 40, 'Wheels','1/20/06'
-- pick the hours
select t2.aircraft,t2.inspection,
t2.dt,
(select top 1 t1.hours from @t1 t1 where t1.aircraft = t2.aircraft order by abs(datediff(day, t1.dt, t2.dt)))
from @t2 t2

This is working except for the Propeller Change on 38 for 1/16. It should return a 0 since there was no flying time up until 4/19/06. Do you know how to account for this. Since I'm going to pull the information from an existing database, will having the Union All or not having the Union ALL make a difference?

Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-04 : 14:19:53

You wrote you wanted NEAREST date. You did not specify in what time, future or past.
Do you want nearest date in the past?

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources