Summarizing prices in sql date ranges

0

Please login or register to vote for this query.

(click on this box to dismiss)

Summarizing prices in sql date ranges query answer for SO

Computational Science

Q&A for scientists using computers to solve scientific problems

-- tables
create table #prices (
PricelistID int,
periodID   int,
periodstart        datetime,
periodend         datetime,
price int)


create table #interval_price (
PricelistID int,
price int,
period datetime
)


-- inserts
insert into #prices values (1,1,convert(datetime, '2011/05/01', 111), convert(datetime, '2011/05/22', 111),50)
insert into #prices values (1,2,convert(datetime, '2011/06/01', 111), convert(datetime, '2011/06/30', 111),70)
insert into #prices values (1,3,convert(datetime, '2011/07/15', 111), convert(datetime, '2011/07/31', 111),80)
insert into #prices values (2,4,convert(datetime, '2011/05/01', 111), convert(datetime, '2011/05/31', 111),100)
insert into #prices values (2,5,convert(datetime, '2011/06/01', 111), convert(datetime, '2011/06/30', 111),110)
insert into #prices values (2,6,convert(datetime, '2011/07/01', 111), convert(datetime, '2011/07/31', 111),120)


-- variables
declare @periodStart datetime, @periodEnd datetime
select @periodStart = convert(datetime, '20110520', 112)
select @periodEnd = convert(datetime, '20110527', 112)

declare @period datetime
select @period = @periodStart

declare @price int, @PriceListID int



while (@period < @periodEnd)
begin   
   select @price = NULL, @PriceListID = NULL

   insert into #interval_price select PricelistID, price, @period from #prices where @period >= periodstart and @period < periodend

   select @period = dateadd(dd, 1, @period)
end

--select PriceListID, price, period from #interval_price

select PriceListID, sum(price) [total] from #interval_price group by PricelistID


drop table #prices
drop table #interval_price

Enter Parameters

Options:
Switch to meta site
loading Hold tight while we fetch your results