MS SQL SERVER 2014: Get Number of Days Between Occurrences

Question

I have a table with the following structure (Occurrence Table):

ID    EmployeeID      DateOfOccurrence
--------------------------------------
1     999999          2017-02-14
2     999999          2017-03-02
3     999999          2017-03-23
4     999999          2017-05-10

I would like to create a query to return the result (grouped by employeeid):

999999     2017-02-14 to 2017-03-02     16 days
999999     2017-03-02 to 2017-03-23     21 days
999999     2017-03-23 to 2017-05-10     48 days
999999     2017-05-10 to <today>        xx days 

* <today> = current day (i.e., getdate())

Thank you for your help in advance.

No they are not. There could be gaps between occurrences for an employee. Occurrences are added each morning for all employees (if they have one). — klbass68, May 24 '17 at 16:50
why is the first row 16 days, but the second row 20 days (instead of 21)? — SqlZim, May 24 '17 at 16:52

score 1 · Accepted Answer · answered May 24 '17 at 16:56

1

using concat(), lead(), isnull(), and datediff():

select 
    id
  , range = concat(
      convert(char(10),dateofoccurence,120)
    , ' to '
    , convert(char(10),isnull(lead(dateofoccurence) over (partition by employeeid order by dateofoccurence),getdate()),120)
  )
  , days = concat(
      datediff(
         day
       , dateofoccurence
       , isnull(lead(dateofoccurence) over (partition by employeeid order by dateofoccurence),getdate())
      )
    , ' days')
from t

rextester demo: http://rextester.com/FNQ48539

returns:

+----+--------------------------+---------+
| id |          range           |  days   |
+----+--------------------------+---------+
|  1 | 2017-02-14 to 2017-03-02 | 16 days |
|  2 | 2017-03-02 to 2017-03-23 | 21 days |
|  3 | 2017-03-23 to 2017-05-10 | 48 days |
|  4 | 2017-05-10 to 2017-05-24 | 14 days |
+----+--------------------------+---------+

answered May 24 '17 at 16:56

SqlZim

37,248
6
41
59

Worked PERFECTLY. Thank you so much. – klbass68 May 24 '17 at 17:03
And you applauded my deciphering skillz... your's are better +1 – S3S May 24 '17 at 17:52
@scsimon deciphering wasn't really necessary since desired results were provided, but thanks! – SqlZim May 24 '17 at 18:01
@klbass68 You are going to have to explain what you mean for me to be able to respond in any meaningful way. – SqlZim Jun 21 '17 at 15:55
@SqlZim Is there a way to use over partition with a date range? – klbass68 Jun 21 '17 at 16:00
@SqlZim When I add the following where clause to the query you provided it still returns ALL dates. Not just those between: dateofoccurrence between '01-01-2017' and '04-03-2017 23:59:59.999' – klbass68 Jun 21 '17 at 16:07
@klbass68 that conflicts with the `... to ` part of your question. If you want to have a predefined end date instead of `` then you can use parameters like so: http://rextester.com/SHHT77390 – SqlZim Jun 21 '17 at 16:10
@SqlZim DUH! Sorry to bother you. Thanks again. – klbass68 Jun 21 '17 at 16:18

MS SQL SERVER 2014: Get Number of Days Between Occurrences

1 Answers1