June 22, 2011 at 12:09 pm
Dear Good People,
I got stucked getting the following done.
The board has always been helpful.
I need help once more.
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
Note: Three levels of hierarchy
Note: The ratios add up to 1
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
Note: Less Attention on trans_id, tran_date is to get data by period
Work Flow
@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
Many thanks
June 22, 2011 at 2:50 pm
This is a very rough first attempt. Ran out of time to clean it up today (and as a result it's pretty messy with bad aliases and data types etc), but wanted to go ahead and post it before I forgot. Note that I added a row to allocations with a source of NULL and a target of 52Q.
DECLARE @Allocation table (source varchar(20),target varchar(20), ratio decimal(32,2) )
insert into @Allocation (source ,target, ratio)
SELECT NULL,'52Q',1.00 UNION ALL
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
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
;WITH
Sums AS (
SELECT team, DATEPART(mm,tran_date) Mon, SUM( amount) Shares
FROM @Cost_trans
GROUP BY Team, DATEPART(mm,tran_date)),
Pre AS (
SELECT Team, Mon, Shares, TARGET, SOURCE, Ratio
FROM Sums
INNER JOIN @Allocation A ON Sums.Team = A.Target),
R AS (
SELECT [Target], Source, Ratio, Mon, CAST([Target] AS varchar(MAX)) Sort, 0 EmpLevel, CAST(Shares AS float) Shares
FROM Pre
WHERE Source IS NULL
UNION ALL
SELECT A.[Target], A.Source, A.Ratio, A.Mon, CAST(R.Sort + ',' + A.[Target] AS varchar(MAX)) Sort, R.EmpLevel + 1,
CAST(A.Shares + (R.Shares * A.Ratio) AS float)
FROM Pre A
INNER JOIN R ON R.Target = A.SOURCE AND R.Mon = A.Mon
)
SELECT *
FROM R
ORDER BY Mon, Sort
OPTION (MAXRECURSION 50)
June 23, 2011 at 5:51 am
Boss,
Am very greatful.
Aside the code, you have given me more idea on the sharing process..
Thanks
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply