0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for professional and enthusiast programmers

WITH date AS (
    SELECT DISTINCT 
      CAST(CreationDate AS DATE) as date,
      CAST(CreationDate - 1 AS DATE) as min1, 
      CAST(CreationDate + 1 AS DATE) as plus1
    FROM Posts
    WHERE OwnerUserId = ##UserId##
  ),
firstdate AS (  
   SELECT * from date as d1 where NOT EXISTS(SELECT * FROM date d2 Where d1.min1 = d2.date)
),
lastdate AS (  
   SELECT * from date as d1 where NOT EXISTS(SELECT * FROM date d2 Where d1.plus1 = d2.date)
),
dates AS (
  SELECT firstdate.date as first, min(lastdate.date) as last FROM firstdate, lastdate WHERE firstdate.date <= lastdate.date
  GROUP BY firstdate.date 
)
SELECT first, last, DATEDIFF(dd,first, last)+1 as days FROM dates order by days desc

fork query

edited oct 18 19 a1oleg

created nov 7 15 anonymous