0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A about the site for science fiction and fantasy enthusiasts

with a as (
select 
   [Month],
   p2.CreationDate as post_date,
   v.CreationDate as vote_date, 
   (case 
         when VoteTypeId = 2 then 1.00 
         when VoteTypeId = 3 then -1.00 
         else 0.00
    end) Score,
   (case 
         when DATEDIFF(day, p2.CreationDate, v.CreationDate)
              BETWEEN 0 AND 7
              Then 1
         else 0
    end) early
from Votes v 
join PostsWithDeleted p2 
on p2.Id = v.postId
join ( 
  Select 
      CAST(cast(DATEPART(YYYY, CreationDate) as float) + cast(DATEPART(MM, CreationDate) as float)/12 as float) [Month],
      Id,
      CreationDate
  from PostsWithDeleted) p 
on p.Id = p2.ParentId 
where VoteTypeId in (2,3)
),

b as (
select 
   [Month],
   p2.CreationDate as post_date,
   v.CreationDate as vote_date, 
   (case 
         when VoteTypeId = 2 then 1.00 
         when VoteTypeId = 3 then -1.00 
         else 0.00
    end) Score,
   (case 
         when DATEDIFF(day, p2.CreationDate, v.CreationDate)
              BETWEEN 0 AND 7
              Then 1
         else 0
    end) early
from Votes v 
join Posts p2 
on p2.Id = v.postId
join ( 
  Select 
      CAST(cast(DATEPART(YYYY, CreationDate) as float) + cast(DATEPART(MM, CreationDate) as float)/12 as float) [Month],
      Id,
      CreationDate
  from Posts) p 
on p.Id = p2.ParentId 
where VoteTypeId in (2,3)
),

c as (

select
 Month,
 sum(case when early = 1 then 1 else 0 end) as "earlyVotes",
 sum(case when early = 0 then 1 else 0 end) as "lateVotes",
 count(early) as "totalVotes"
from a
where month < CAST(CAST(DATEPART(YYYY, getdate()) as float) +
CAST(DATEPART(MM,   getdate()) as float)/12 as float
                   )
group by Month
),

d as (

select
 Month,
 sum(case when early = 1 then 1 else 0 end) as "earlyVotes",
 sum(case when early = 0 then 1 else 0 end) as "lateVotes",
 count(early) as "totalVotes"
from b
where month < CAST(CAST(DATEPART(YYYY, getdate()) as float) +
CAST(DATEPART(MM,   getdate()) as float)/12 as float
                   )
group by Month
)

select Month, earlyVotes, lateVotes  from d
order by Month

fork query

edited nov 19

created oct 29 anonymous