0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for professional and enthusiast programmers

with Starts as (
  select distinct B.Date
  from Badges B
  where B.Name = 'Constituent'
  and Date >= '10 June 2012'
  and not exists (
      select 1
      from Badges C
      where C.Name = 'Constituent'
        and Date >= '10 June 2012'
        and C.Date < B.Date
        and DateAdd(dd, 30, C.Date) > B.Date
  )
), Elections as (
         select concat(
             Cast(count(B.Date) as VarChar), 
             ' ',
             Cast (DatePart(mm, S.Date) as VarChar),
             '/',
             Cast (DatePart(yy, S.Date) as VarChar)) as Election,
         S.Date as StartDate,
         DateAdd(dd, 30, S.Date) as EndDate
  from Starts S
  inner join Starts B on B.Date <= S.Date
  group by S.Date
), Buckets as (
  select E.Election, DateDiff(hh, E.StartDate, Badges.Date) as Hour, count(*) as Volume
  from Elections E
  inner join Badges on Name = 'Constituent' and Badges.Date >= E.StartDate and Badges.Date <= E.EndDate
  group by E.Election, DateDiff(hh, E.StartDate, Badges.Date)
), Totals as (
  select Election, Sum(Volume) as Total
  from Buckets
  group by (Election)
), YearPct as (
  select B.hour as Hour,
         Concat(B.Election, ' Percent of ', Cast(T.Total as Varchar)) as Label,
         Cast (Floor(Sum(P.Volume) * 100.0 / T.Total) as int) as "Value"
  from Buckets B
  inner join Totals T on B.Election = T.Election
  inner join Buckets P on B.Election = P.Election and B.Hour >= P.Hour
  group by B.Election, B.Hour, T.Total
), YearVolumes as (
  select B.hour as Hour,
         Concat(B.Election, ' Volume') as Label,
         B.Volume as "Value"
  from Buckets B
)
select *
from YearPct
union all
select *
from YearVolumes
order by Label, Hour

fork query

edited apr 8 18

created apr 7 18 rolfl