When did the candidates nominate in an election?

0

Please login or register to vote for this query.

(click on this box to dismiss)

after how many hours after the first nomination of that election was in

Android Enthusiasts Meta

Q&A about the site for enthusiasts and power users of the Android operating system

;with nominations as 
(select row_number() over(order by creationdate asc) num
      , creationdate
from posts
where posttypeid = 6
)

select *
into #nominations
from nominations

select f.num
     , f.creationdate
     , datediff(
         d
       , coalesce(
          (select creationdate 
           from #nominations
           where num = f.num -1)
          , f.creationdate)
       , f.creationdate) [diff]
into #nom_diff
from #nominations f
order by f.num

select row_number() over(order by num) [election]
     , *
into #elections
from (
select *
from #nom_diff
where num = 1
union
select *
from #nom_diff
where diff > 7
) data


select elec, [1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12]
from (
select e.election as elec
     , e.election
     , n.num
     , datediff(hh, e.creationdate, n.creationdate) [uren]
    -- , datediff(hh, e.creationdate, n.creationdate) [x]
from #elections e
inner join #nominations n 
   on n.num between e.num 
   and coalesce(
         (select min(num)-1 from #elections where election = e.election +1)
         , (select max(num) from #nominations))
-- order by e.election, n.num
) data
pivot 
(
  min(uren) 
  for election in ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])
) as pvt
order by 1

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results