With con as ( SELECT p.owneruserid, DATEPART(Year, p.crea...

0

Please login or register to vote for this query.

(click on this box to dismiss)

Moderators

Q&A for community managers, administrators, and moderators

With con as (
SELECT 
  p.owneruserid,
  DATEPART(Year, p.creationdate) as year,
  CASE 
    WHEN sum(CASE WHEN DATEPART(Month, p.creationdate) in (1,2,3) THEN 1 END) IS NOT NULL THEN 1
    WHEN sum(CASE WHEN DATEPART(Month, c.creationdate) in (1,2,3) THEN 1 END) IS NOT NULL THEN 1
    ELSE 0
  END
  +
  CASE 
    WHEN sum(CASE WHEN DATEPART(Month, p.creationdate) in (4,5,6) THEN 1 END) IS NOT NULL THEN 1
    WHEN sum(CASE WHEN DATEPART(Month, c.creationdate) in (4,5,6) THEN 1 END) IS NOT NULL THEN 1
    ELSE 0 
  END
  +
  CASE 
    WHEN sum(CASE WHEN DATEPART(Month, p.creationdate) in (7,8,9) THEN 1 END) IS NOT NULL THEN 1
    WHEN sum(CASE WHEN DATEPART(Month, c.creationdate) in (7,8,9) THEN 1 END) IS NOT NULL THEN 1 
    ELSE 0 
  END
  +
  CASE 
    WHEN sum(CASE WHEN DATEPART(Month, p.creationdate) in (10,11,12) THEN 1 END) IS NOT NULL THEN 1
    WHEN sum(CASE WHEN DATEPART(Month, c.creationdate) in (10,11,12) THEN 1 END) IS NOT NULL THEN 1
    ELSE 0 
  END
    as consectiveTotal
FROM Posts p
INNER JOIN Comments c ON c.userid = p.owneruserid
WHERE p.owneruserid = 1 AND p.posttypeid in (1,2) AND DATEPART(year, p.creationdate) >= 2015 AND DATEPART(year, p.creationdate) <= 2020
GrOUP by p.owneruserid, DATEPART(Year, p.creationdate)
)
SELECT
  con.owneruserid,
  con.year,
  con.consectiveTotal
FROM con
order by con.owneruserid

Enter Parameters

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