CODEWITHSUNDEEP
sqlsql-serversql-server-2008
user3314399
user3314399

Reputation: 325

Simple SQL Query for date ranges

I need a way to find out the number of days for each set of same IDs set. For example, if there are two rows of ID1 with the first date being the 1st and the second date the 4th, the calculation for the 1st would be Sept 4th - the Sept 1st (since the 4th is the next date for ID1) and the calculation for the 4th would be Today (Sept 28th) - Sept 4th (since 4th is the last date for ID1 and there is no more dates after the 4th for ID1). I included the formula for calculation of each column next to it below.

IDs    Date          
ID1    09/01/2015    
ID1    09/04/2015  
ID2    09/04/2015    
ID2    09/09/2015   
ID2    09/15/2015  
ID3    09/09/2015 
ID4    09/15/2015

To 

IDs    Date           Days      Formula...
ID1    09/01/2015     3         Sept 4th - Sept 1st
ID1    09/04/2015     22        Today - Sept 4th
ID2    09/04/2015     5         Sept 9th - Sept 4th
ID2    09/09/2015     6         Sept 15th - Sept 9th  
ID2    09/15/2015     13        Today - Sept 15th
ID3    09/09/2015     19        Today - Sept 9th
ID4    09/15/2015     13        Today - Sept 15th

Upvotes: 1

Views: 182

Answers (4)

Gordon Linoff
Gordon Linoff

Reputation: 1269445

You are basically looking for lead() functionality. And, you don't have it, so you have to improvise. Here is a method using cross apply:

select t.*,
       datediff(day, t.date, coalesce(n.date, getdate())
from table t outer apply
     (select top 1 t2.date
      from table t2
      where t2.id = t.id and t2.date > t.date
      order by t2.date
     ) n;

This should have reasonable performance with an index on (id, date).

Upvotes: 3

Kendall
Kendall

Reputation: 381

Using a common table expression, you can arrange your table into a temporary view with a start date and end date for each ID record, like this: http://sqlfiddle.com/#!3/af078/7

with t1 (id, dt, ndx) as (
  select id, dt, row_number() over (partition by id order by dt)
  from id_dates
)
,
t2 (id, startdt, enddt) as (
  select t1.id, t1.dt, coalesce(t2.dt, convert(date, getdate()))
  from t1
  left join t1 t2
  on t2.id = t1.id
  and t2.ndx = t1.ndx + 1
)
select * from t2

Make sure to use the coalesce function to get the current date for the last row (it would otherwise be null from the left join).

From there, it's a simple matter of using the datediff() function: http://sqlfiddle.com/#!3/af078/8

with t1 (id, dt, ndx) as (
  select id, dt, row_number() over (partition by id order by dt)
  from id_dates
)
,
t2 (id, startdt, enddt) as (
  select t1.id, t1.dt, coalesce(t2.dt, convert(date, getdate()))
  from t1
  left join t1 t2
  on t2.id = t1.id
  and t2.ndx = t1.ndx + 1
)
select id, startdt, datediff(day, startdt, enddt) as dtcount
from t2

Upvotes: 2

Paul Spain
Paul Spain

Reputation: 549

Here is a working example, You can do it without any joins. Just use the Lead windowed function.

--Here is your data
DECLARE @Data TABLE(ID NVARCHAR(8),Date DATE)
INSERT INTO @Data(ID,Date)
VALUES
('ID1','09/01/2015'),
('ID1','09/04/2015'),
('ID2','09/04/2015'),   
('ID2','09/09/2015'), 
('ID2','09/15/2015'),
('ID3','09/09/2015'),
('ID4','09/15/2015')

--This is all you need, Assuming your using a version of SQL that supports LEAD!!!
SELECT  ID,DATEDIFF(D,Date,ISNULL(LEAD(Date) OVER(PARTITION BY ID ORDER BY Date),GETDATE()))
FROM        @Data

Upvotes: 1

Felix Pamittan
Felix Pamittan

Reputation: 31879

You can do this using ROW_NUMBER together with LEFT JOIN:

SQL Fiddle

WITH Cte AS(
    SELECT *, 
    rn = ROW_NUMBER() OVER(PARTITION BY ID ORDER BY [Date])
    FROM tbl
)
SELECT
    t1.*,
    Days = DATEDIFF(DAY, t1.Date, ISNULL(t2.Date, GETDATE()))
FROM Cte t1
LEFT JOIN Cte t2
    ON t1.ID = t2.ID
    AND t1.rn  = t2.rn - 1

Upvotes: 1

Related Questions