DECLARE @Allocation table (source varchar(20),target varchar(20), ratio decimal(32,2) )
insert into @Allocation (source ,target, ratio)
SELECT '52Q'	'12Q'	0.50 UNION ALL
SELECT '52Q'	'13Q'	0.50 UNION ALL
SELECT '12Q'	'T006A'	0.2  UNION ALL
SELECT '12Q'	'T007A'	0.4  UNION ALL
SELECT '12Q'	'T015A'	0.3  UNION ALL
SELECT '12Q'	'T026A'	0.0  UNION ALL
SELECT '12Q'	'T030A'	0.1  UNION ALL
SELECT '13Q'	'T001A'	0.75 UNION ALL
SELECT '13Q'	'T034A'	0.25 UNION ALL

DECLARE @Cost_trans table (trand_id varchar(20), tran_date dateTIME, team varchar(20), amount decimal(32,2))

insert into @Cost_trans (trand_id , tran_date, team, amount)
 SELECT '100' '2011-05-01' '52Q' 200 UNION ALL
 SELECT '200' '2011-05-05' '12Q' 300 UNION ALL
 SELECT '300' '2011-05-10' '12Q' 700 UNION ALL
 SELECT '400' '2011-05-06' '13Q' 8900 UNION ALL
 SELECT '500' '2011-05-14' 'T006A' 1100 UNION ALL
 SELECT '300' '2011-05-10' '12Q' 9000 UNION ALL
 SELECT '400' '2011-05-06' 'T007A' 50 UNION ALL
 SELECT '500' '2011-05-14' '52Q' 300 UNION ALL
 SELECT '600' '2011-05-31' 'T001A' 200 UNION ALL
 SELECT '300' '2011-05-10' '12Q' 300 UNION ALL
 SELECT '400' '2011-05-06' 'T007A' 40 UNION ALL
 SELECT '500' '2011-05-14' 'T034A' 20 UNION ALL
 SELECT '600' '2011-05-31' 'T001A' 220 UNION ALL

 SELECT '200' '2011-06-03' '12Q' 500 UNION ALL
 SELECT '400' '2011-06-04' '12Q' 60 UNION ALL
 SELECT '500' '2011-06-16' '13Q' 540 UNION ALL
 SELECT '700' '2011-06-08' '13Q' 300 UNION ALL
 SELECT '500' '2011-06-12' 'T007A' 400 UNION ALL
 SELECT '200' '2011-06-30' '12Q' 95 UNION ALL
 SELECT '100' '2011-06-22' 'T007A' 800 UNION ALL
 SELECT '100' '2011-06-04' '52Q' 2300 UNION ALL
 SELECT '400' '2011-06-21' 'T006A' 320 UNION ALL
 SELECT '700' '2011-06-10' 'T034A' 10 UNION ALL
 SELECT '900' '2011-06-23' '52Q' 140 UNION ALL
 SELECT '400' '2011-06-14' 'T034A' 440 UNION ALL
 SELECT '500' '2011-06-18' 'T001A' 90 UNION ALL

• @cost_trans.team joins with @Allocation.source to fetch target and ratio



• Base on the ratio, to share amount of topmost level with the next level e.g In month of May, 52Q has 500 in total, it will give both 12Q and 13Q 250 each base on the rate in allocation table.
  


• Add the share with figures of the level.
  In month of May, 12Q has 10,300, then add share from it first level, 10,300 + 250 = 10,550, while 13Q has 8,900 + 250 = 9,150


• Repeat 3 for the next level
  In month of May, T006A will earn 0.2 of 10,550 from the share the share of 12Q. i.e 2,110 then add it, to its original entries = 1,100 + 2,110 = 3,210
  In month of May, T007A will earn 0.4 of 10,550 from the share the share of 12Q. i.e 4,220 then add it, to its original entries = 90 + 4,220 = 4,310
  In month of May, T015A will earn 0.3 of 10,550 from the share the share of 12Q. i.e 3,165 then add it, to its original entries = 0 + 3,165 = 3,165
  In month of May, T026A will not earn from the share but it maintains it 1100
  In month of May, T030A will earn 0.1 of 10,550 from the share the share of 12Q. i.e 1,055 then add it, to its original entries = 0 + 1,055 = 1,055
  In month of May, T001A will earn 0.75 of 9,150 from the share the share of 13Q. i.e 6,862.5 then add it, to its original entries = 420 + 6,862.5 = 7,282.5
  In month of May, T034A will earn 0.25 of 9,150 from the share the share of 13Q. i.e 2,287.5 then add it, to its original entries = 20 + 2,287.5 = 2,307.5


• Same will be done of other months


• The solution, will take care in case the hierarchy is more than 3 levels.. Am expecting more than 3 levels