Breaking News: Grepper is joining You.com. Read the official announcement!

gaps and island problem sql

Terrible Thrush answered on September 25, 2022 Popularity 2/10 Helpfulness 1/10

answer gaps and island problem sql

gaps and island problem sql

Comment

WITH
  partitioned AS
  (
    SELECT
      *
    , DATEDIFF(Date, '1970-01-01') - ROW_NUMBER() OVER (ORDER BY Date ASC) AS PartID
    FROM
      YourTable
  )
SELECT
  MIN(Date) AS StartDate
, COUNT(*)  AS DayCount
FROM
  partitioned
GROUP BY
  PartID
HAVING
  COUNT(*) > 1
ORDER BY
  PartID
;

Popularity 2/10 Helpfulness 1/10 Language sql

Source: dba.stackexchange.com

Tags: sql

Link to this answer
Share Copy Link