2

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for database professionals who wish to improve their database skills and learn from others in the community

with Years as (
  select DatePart(year, "Date") as Year, Min(Date) as Start
  from Badges
  where Name = 'Constituent'
  group by DatePart(year, "Date")
), Buckets as (
  select Years.Year as Year, DateDiff(hh, Years.Start, Badges.Date) as Hour, count(*) as Volume
  from Years
  inner join Badges on Name = 'Constituent' and DatePart(year, Badges.Date) = Years.Year
  group by Years.Year, DateDiff(hh, Years.Start, Badges.Date)
), Totals as (
  select Year, Sum(Volume) as Total
  from Buckets
  group by (Year)
), YearPct as (
  select B.hour as Hour,
         Concat(Cast (B.Year as Varchar), ' Percent') as Label,
         Cast (Floor(Sum(P.Volume) * 100.0 / T.Total) as int) as "Value"
  from Buckets B
  inner join Totals T on B.Year = T.Year
  inner join Buckets P on B.Year = P.Year and B.Hour >= P.Hour
  group by B.Year, B.Hour, B.Volume, T.Total
), YearVolumes as (
  select B.hour as Hour,
         Concat(Cast (B.Year as Varchar), ' 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 7 18

created apr 3 18 rolfl