1

Please login or register to vote for this query.

(click on this box to dismiss)

Shows the number of (undeleted) sandbox posts for each of the last year

Q&A about the site for programming puzzle enthusiasts and code golfers

--Select * from Posts as post
--Where post.Id = 2140

Select 
  sum(case when CreationDate >= DATEADD(mm, -1, GETDATE()) then 1 else 0 end) as one,
  sum(case when CreationDate >= DATEADD(mm, -2, GETDATE()) AND CreationDate < DATEADD(mm, -1, GETDATE()) then 1 else 0 end) as two,
  sum(case when CreationDate >= DATEADD(mm, -3, GETDATE()) AND CreationDate < DATEADD(mm, -2, GETDATE()) then 1 else 0 end) as three,
  sum(case when CreationDate >= DATEADD(mm, -4, GETDATE()) AND CreationDate < DATEADD(mm, -3, GETDATE()) then 1 else 0 end) as four,
  sum(case when CreationDate >= DATEADD(mm, -5, GETDATE()) AND CreationDate < DATEADD(mm, -4, GETDATE()) then 1 else 0 end) as five,
  sum(case when CreationDate >= DATEADD(mm, -6, GETDATE()) AND CreationDate < DATEADD(mm, -5, GETDATE()) then 1 else 0 end) as six,
  sum(case when CreationDate >= DATEADD(mm, -7, GETDATE()) AND CreationDate < DATEADD(mm, -6, GETDATE()) then 1 else 0 end) as seven,
  sum(case when CreationDate >= DATEADD(mm, -8, GETDATE()) AND CreationDate < DATEADD(mm, -7, GETDATE()) then 1 else 0 end) as eight,
  sum(case when CreationDate >= DATEADD(mm, -9, GETDATE()) AND CreationDate < DATEADD(mm, -8, GETDATE()) then 1 else 0 end) as nine,
  sum(case when CreationDate >= DATEADD(mm, -10, GETDATE()) AND CreationDate < DATEADD(mm, -9, GETDATE()) then 1 else 0 end) as ten,
  sum(case when CreationDate >= DATEADD(mm, -11, GETDATE()) AND CreationDate < DATEADD(mm, -10, GETDATE()) then 1 else 0 end) as eleven,
  sum(case when CreationDate >= DATEADD(mm, -12, GETDATE()) AND CreationDate < DATEADD(mm, -11, GETDATE()) then 1 else 0 end) as twelve
From Posts
Where ParentId = 2140

view current version

edited may 29 17 NonlinearFruit

created may 29 17 anonymous