Q&A about the site for expert genealogists and people interested in genealogy or family history
--Calculates the number of consecutive days a user has a post --Try it with 22656 WITH distinct_days AS (SELECT DISTINCT DateAdd(day, datediff(day,0, creationdate), 0) post_day FROM posts WHERE owneruserid = ##userid##), islands AS (SELECT post_day, Datediff(DAY, 0, post_day) - Dense_rank() OVER (ORDER BY Datediff(DAY , 0 , post_day)) AS g FROM distinct_days) SELECT MIN(post_day) start_day, MAX(post_day) end_day, COUNT(DISTINCT post_day) daycount, MIN(p.id) [Post Link], MAX(p.id) [Post Link] FROM islands i INNER JOIN posts p ON p.owneruserid = ##userid## AND i.post_day = DateAdd(day, datediff(day,0, p.creationdate), 0) GROUP BY g ORDER BY g