CODEWITHSUNDEEP
sqldate
Sneha Reddy
Sneha Reddy

Reputation: 61

find gaps in sequential dates

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

Upvotes: 1

Views: 103

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269703

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;

Upvotes: 2

Related Questions