Date Diff query - 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 2000 Forums

Transact-SQL (2000)

Date Diff query

Author

Topic

Gary Costigan
Yak Posting Veteran

95 Posts

Posted - 2006-08-15 : 13:49:54

I've been given the task of trying to show how many days a station has gone without posting an injury. The data on the server is simple which show the station ID and the date of the reported injuries. Here is a sample of a region for this month.

Station Injury Date

702 2006-08-13 00:00:00.000
702 2006-08-12 00:00:00.000
701 2006-08-12 00:00:00.000
340 2006-08-12 00:00:00.000
702 2006-08-12 00:00:00.000
340 2006-08-12 00:00:00.000
340 2006-08-12 00:00:00.000
701 2006-08-11 00:00:00.000
310 2006-08-09 00:00:00.000
310 2006-08-08 00:00:00.000
701 2006-08-08 00:00:00.000
310 2006-08-08 00:00:00.000
701 2006-08-07 00:00:00.000
701 2006-08-07 00:00:00.000
701 2006-08-07 00:00:00.000
701 2006-08-03 00:00:00.000
701 2006-08-03 00:00:00.000
701 2006-08-03 00:00:00.000
310 2006-08-02 00:00:00.000
702 2006-08-02 00:00:00.000

Is there a way of getting a query to identify the most recent date for each indivual station, and then do a Date Diff to show how many days they have gone injury free?

This one has me stumped, many thanks for any assistance.

GC

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-15 : 14:06:08

[code]-- prepare test data
declare @test table (Station int, InjuryDate datetime)

insert @test
select 702, '2006-08-13 00:00:00.000' union all
select 702, '2006-08-12 00:00:00.000' union all
select 701, '2006-08-12 00:00:00.000' union all
select 340, '2006-08-12 00:00:00.000' union all
select 702, '2006-08-12 00:00:00.000' union all
select 340, '2006-08-12 00:00:00.000' union all
select 340, '2006-08-12 00:00:00.000' union all
select 701, '2006-08-11 00:00:00.000' union all
select 310, '2006-08-09 00:00:00.000' union all
select 310, '2006-08-08 00:00:00.000' union all
select 701, '2006-08-08 00:00:00.000' union all
select 310, '2006-08-08 00:00:00.000' union all
select 701, '2006-08-07 00:00:00.000' union all
select 701, '2006-08-07 00:00:00.000' union all
select 701, '2006-08-07 00:00:00.000' union all
select 701, '2006-08-03 00:00:00.000' union all
select 701, '2006-08-03 00:00:00.000' union all
select 701, '2006-08-03 00:00:00.000' union all
select 310, '2006-08-02 00:00:00.000' union all
select 702, '2006-08-02 00:00:00.000'

-- do the work
select station,
datediff(day, z.idate, getdate()) DaysSinceLastInjury,
datediff(hour, z.idate, getdate()) / 24.0 DaysSinceLastInjury,
datediff(minute, z.idate, getdate()) / 1440.0 DaysSinceLastInjury,
datediff(second, z.idate, getdate()) / 86400.0 DaysSinceLastInjury
from (
select station,
max(injurydate) idate
from @test
group by station
) z[/code]

Peter Larsson
Helsingborg, Sweden

Gary Costigan
Yak Posting Veteran

95 Posts

Posted - 2006-08-15 : 14:32:59

Peter,

Thank you very much!! Completely forgot about the MAX function.

Regards.

GC

druer
Constraint Violating Yak Guru

314 Posts

Posted - 2006-08-15 : 14:37:52

If you modify 'select station,'
to be 'select station, z.idate as LastInjuryDate,

from Peter's command you'll also get the date of the most recent injury for each station.

Hope it helps,
Dalton

Blessings aren't so much a matter of "if they come" but "are you noticing them."

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-15 : 15:16:34

Sorry, forgot about one of the requirements.


select		station,
		z.idate DateLastInjury,
		datediff(day, z.idate, getdate()) DaysSinceLastInjury,
		datediff(hour, z.idate, getdate()) / 24.0 DaysSinceLastInjury,
		datediff(minute, z.idate, getdate()) / 1440.0 DaysSinceLastInjury,
		datediff(second, z.idate, getdate()) / 86400.0 DaysSinceLastInjury
from		(
			select		station,
					max(injurydate) idate
			from		@test
			group by	station
		) z

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources