find gaps in sequential dates

Question

The challenge is to find gaps in sequential dates.

I tried something like this

WITH t AS (
  SELECT a a, d d, c, ROW_NUMBER() OVER(ORDER BY a) i
  FROM p
)
SELECT MIN(a),MAX(d), c
FROM t
GROUP BY DATEDIFF(day,i,d), c

I am using sql database. Anybody can help from this step?? Thanks in advance

You can group by cost, since it always different which will result in separated row for each cost, remove it from you query — sagi, Feb 04 '16 at 12:13

score 2 · Answer 1 · answered Feb 04 '16 at 12:17

You need to identify groups of ranges -- but you need to identify the groupings. Here is a method that works directly in SQL Server 2012+ (and can be modified to work in earlier versions):

Identify where new ranges begin and assign a range-start flag.
Take the cumulative sum of the range start.
Use the cumulative sum for aggregation.

So:

select patientid, min(admissiondate), max(dischargedate), sum(cost)
from (select p.*,
             sum(RangeFlag) over (partition by patientid order by admissiondate) as grp
      from (select p.*,
                   (case when admissiondate = dateadd(day, 1,
                                                      lag(dischargedate) over (partition by patientid order by admissiondate))
                         then 0
                         else 1
                    end) as RangeFlag
            from patients p
           ) p
      ) p
group by patientid, grp;

find gaps in sequential dates

1 Answers1

Linked