CODEWITHSUNDEEP
sql-server
Avegavalencia
Avegavalencia

Reputation: 55

Find the date difference within the same column of a table

So I have a table that contains the following example structure and items.

Type           Date                       ID      Type_Ref
Problem        2015-01-03 02:25:59.360    1002    1
Incident       2015-01-03 06:13:12.125    1003    2
Incident       2015-01-05 00:15:56.700    1004    2
Problem        2015-01-16 13:27:12.325    1005    1
Incident       2015-01-17 15:05:16.703    1006    2
Incident       2015-01-22 18:46:17.986    1007    2
Problem        2015-02-01 09:21:49.123    1008    1

And I'm trying to query so that I get the maximum difference between dates in terms of 'Days' between the 'Incidents'. So I'd like to get it to query for the list of 'Incidents' and then find the largest difference between times that an incident was recorded. So for this table, the result of the query I'm looking for would be 12 for the difference in incidents 1004 and 1006.

Any ideas?

For this I'm using SQL server

Upvotes: 2

Views: 123

Answers (2)

Eric
Eric

Reputation: 703

I assume by largest difference between dates, you mean "consecutive dates".

First, filter this down to Incidents:

select 
  [type],
  [date],
  [id],
  [type_ref]
from problem_log
where [type_ref] = '2' -- or, "where [type] = 'Incident'"
order by [date]

Now grab the differences between dates using a window function:

select 
  [type],
  [date],
  [id],
  [type_ref],
  datediff(d, lag([date],1) over (order by [date]), [date]) as days_since_last_incident
from problem_log
where [type_ref] = '2' -- or, "where [type] = 'Incident'"
order by [date]

here it is as a table:

select * from (
select 
  [type],
  [date],
  [id],
  [type_ref],
  datediff(d, lag([date],1) over (order by [date]), [date]) as days_since_last_incident
from problem_log
where [type_ref] = '2' -- or, "where [type] = 'Incident'"
) sq   
order by [days_since_last_incident] desc`

or filter that down to just the single value we want

select max(days_since_last_incident) as longest_safety_streak from ( 
select 
  [type],
  [date],
  [id],
  [type_ref],
  datediff(d, lag([date],1) over (order by [date]), [date]) as days_since_last_incident
from problem_log
where [type_ref] = '2' -- or, "where [type] = 'Incident'"
) sq

Upvotes: 0

Kittoes0124
Kittoes0124

Reputation: 5080

I'd start with a generic version that accomplishes your results without filtering or aggregation. You should be able to manipulate this in several ways:

select a.*
     , DateDiff(day, Lag(a.[Date]) over (partition by a.[Type] order by a.[Date] asc), A.[Date])
from (values
    ('Problem', '2015-01-03 02:25:59.360', 1002)
  , ('Incident', '2015-01-03 06:13:12.125', 1003)
  , ('Incident', '2015-01-05 00:15:56.700', 1004)
  , ('Problem', '2015-01-16 13:27:12.325', 1005)
  , ('Incident', '2015-01-17 15:05:16.703', 1006)
  , ('Incident', '2015-01-22 18:46:17.986', 1007)
  , ('Problem', '2015-02-01 09:21:49.123', 1008)
) as a ([Type], [Date], [Id]);

LAG will find the previous row's value based on a partition/ordering clause. In the example this results in the "previous type's date." You can learn more about windowing functions here.

Upvotes: 1

Related Questions