All the distinct days on which I created posts

0

Please login or register to vote for this query.

(click on this box to dismiss)

IT Security Meta

Q&A about the site for information security professionals

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 = 521799
  ),
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

Enter Parameters

Options:
Switch to main site
loading Hold tight while we fetch your results