存储过程 每15分钟分配一个区间 并相加

   ALTER proc [dbo].[sp_hyfx_SoldOutDistributiones] 
(@thedate bigint)
as
begin
--   create table #houres 
--(houres int )
--declare @i int 
--set @i=1
--while @i<=24
--begin 
--insert #houres values (@i-1)
--set @i =@i+1
--end 


select datepart( hour , OperationDate ) as houres, datepart( minute , OperationDate ) as minutess,AuctionNum,  OperationDate ,thedate into #hhh from   hyfx_SoldOutDistribution  where thedate=@thedate
  
  select '1-15' AS times , houres ,sum(AuctionNum) as AuctionNum into #tb1   from #hhh where thedate=@thedate AND MINUTESS  BETWEEN 1 AND 15 group by houres 
  UNION
  select '16-30' AS times , houres ,sum(AuctionNum) as AuctionNum   from #hhh where thedate=@thedate AND MINUTESS  BETWEEN 16 AND 30 group by houres 
   UNION
   select '30-45' AS times , houres ,sum(AuctionNum) as AuctionNum  from #hhh where thedate=@thedate AND MINUTESS  BETWEEN 30 AND 45 group by houres 
      UNION
   select '45-60' AS times , houres ,sum(AuctionNum) as AuctionNum  from #hhh where thedate=@thedate AND MINUTESS  BETWEEN 45 AND 60 group by houres 
  --   select T.houres ,cast( isnull( a.AuctionNum ,0) as varchar)  +'+' +cast ( isnull(b.AuctionNum ,0) as varchar)+'+' +cast ( isnull(c.AuctionNum ,0) as varchar)+'+' +cast ( isnull(d.AuctionNum ,0) as varchar)  as section
--from #houres t left join  #tb1 a  on  t.houres=a.houres and a.times ='1-15'  left join #tb1 b on t.houres =b.houres and b.times='16-30' left join #tb1 c on  t.houres=c.houres and c.times='30-45' left join #tb1 d on  t.houres=d.houres
--and d.times='45-60'




   select distinct a.houres ,cast( isnull( a.AuctionNum ,0) as varchar)  +'+' +cast ( isnull(b.AuctionNum ,0) as varchar)+'+' +cast ( isnull(c.AuctionNum ,0) as varchar)+'+' +cast ( isnull(d.AuctionNum ,0) as varchar)  as section
into #num from   #tb1 a   left join #tb1 b on a.houres =b.houres and b.times='16-30' left join #tb1 c on  a.houres=c.houres and c.times='30-45' left join #tb1 d on  a.houres=d.houres 
and d.times='45-60'  and a.times ='1-15'  
  select distinct houres ,sum(AuctionNum) as AuctionNumsum into #ber  from #hhh group by houres 
select distinct n.houres,n.section,b.AuctionNumsum from #num n,#ber b where n.houres=b.houres
  --select distinct a.houres ,cast( a.AuctionNum as varchar)  +'+' +cast (b.AuctionNum as varchar)+'+' +cast (c.AuctionNum as varchar) +'+' +cast (d.AuctionNum as varchar)   from #tb1 a  ,#tb1 b ,#tb1 c,#tb1 d where a.houres =b.houres and a.houres=c.houres and a.houres=d.houres and a.times ='1-15' and b.times ='16-30' and c.times ='30-45' and d.times ='45-60'
 
  end;


     exec sp_hyfx_SoldOutDistributiones 20171018