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

Bitcoin

Q&A for Bitcoin crypto-currency enthusiasts

;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