May 29, 2013 at 10:28 pm
I have two tables,
#allowance
#actual
with sample data as below
create table #allowance(id int, free_quantity numeric(18,4))
insert into #allowance (id, free_quantity)
select 947, 8.0
insert into #allowance (id, free_quantity)
select 950, 12.0
Create table #actual(id int, actual_quantity numeric(18,4), start_dt datetime)
insert into #actual(id, actual_quantity, start_dt)
select 947, 5.0, '1/1/2013'
insert into #actual(id, actual_quantity, start_dt)
select 947, 2.0, '2/1/2013'
insert into #actual(id, actual_quantity, start_dt)
select 947, 3.0, '3/1/2013'
insert into #actual(id, actual_quantity, start_dt)
select 950, 15.0, '3/1/2013'
select * from #allowance
select * from #actual
The #allowance contains the total free quantity for each "id"
The #actual contains the actual used quantities for "id" across different dates.
Now, I need a "billable_quantity" column in the "#actual" table that is basically the used quantity less the free quantity. For example, the total free quantity for "id" 947 is 8.
The #actual table should be updated with a billable_quantity which is the free_quantity taken from #allowance table and distributed across the #actual table for the same "id".
The result of the #actual table should be:
idactual_quantitystart_dt billable_quantity
9475.0000 2013-01-01 0.0 (total actual qty 5.0 < 8.0 therefore 0.0)
9472.0000 2013-02-01 0.0 (total actual qty 7.0 < 8.0 therefore 0.0)
9473.0000 2013-03-01 2.0 (total actual qty 10.0 > 8.0 therefore 10.0 - 8.0 = 2.0)
95015.0000 2013-03-01 3.0 (total actual qty 15.0 > 12.0 therefore 15.0 - 12.0 = 3.0)
I am sure CTE can be used for this but do not know how to distribute the free quantity across each row.
Can someone please help?
Thanks.
May 29, 2013 at 11:56 pm
with cte (id,srno,acc_quantity)
as
(select a.id,A.SrNo,isnull(sum(b.actual_quantity),0) as acc_quantity from
(Select ROW_NUMBER() OVER(PARTITION BY ID order by start_dt) as SrNo,id ,actual_quantity, start_dt from #actual ) A
left join (Select ROW_NUMBER() OVER(PARTITION BY ID order by start_dt) as SrNo,id ,actual_quantity, start_dt from #actual ) b
ON A.id=B.id AND A.SrNo>=B.SrNo+1
group by a.id,A.SrNo)
select b.id,b.actual_quantity,b.start_dt,
case when 0-(c.free_quantity-b.actual_quantity-a.acc_quantity)<=0 then 0
else 0-(c.free_quantity-b.actual_quantity-a.acc_quantity) end as billable_quantity
from cte a
left join
(select ROW_NUMBER() OVER(PARTITION BY ID order by start_dt) as SrNo,id ,actual_quantity, start_dt from #actual) b
ON A.id=B.id AND A.SrNo=B.SrNo
left join #allowance c
on c.id=a.id
order by a.id,a.srno
please verify with more data
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply