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
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 * from #hhh
--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
(@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
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 * from #hhh
--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