CTE Help!

• ganeshmuthuvelu

  SSCrazy

  Points: 2177

  More actions

  May 29, 2013 at 10:28 pm

  #293935

  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.

• Soonyu-163551

  SSC Eights!

  Points: 811

  More actions

  May 29, 2013 at 11:56 pm

  #1619555

  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