April 10, 2023 at 1:33 am
Hello all,
I have a hierarchy of businesses. example can be created as follows:
create table #temp
(LeafLvl varchar(20), L1 varchar(20), L2 varchar(20), L3 varchar(20), L4 varchar(20), L5 varchar(20))
insert into #temp
(leafLvl, L1, L2, L3, L4, L5)
select 'L2-1', 'Top', NULL, NULL, NULL, NULL UNION ALL
select 'L3-1', 'Top', 'L2-1', NULL, NULL, NULL UNION ALL
select 'L3-1', 'Top', 'L2-1', NULL, NULL, NULL UNION ALL
select 'L4-1', 'Top', 'L2-1', 'L3-1', NULL, NULL UNION ALL
select 'L4-2', 'Top', 'L2-1', 'L3-1', NULL, NULL UNION ALL
select 'L4-3', 'Top', 'L2-1', 'L3-1', NULL, NULL UNION ALL
select 'L4-4', 'Top', 'L2-1', 'L3-2', NULL, NULL UNION ALL
select 'L4-5', 'Top', 'L2-1', 'L3-2', NULL, NULL UNION ALL
select 'L4-6', 'Top', 'L2-1', 'L3-2', NULL, NULL UNION ALL
select 'L5-1', 'Top', 'L2-1', 'L3-1', 'L4-1', NULL UNION ALL
select 'L5-2', 'Top', 'L2-1', 'L3-1', 'L4-1', NULL UNION ALL
select 'L5-3', 'Top', 'L2-1', 'L3-1', 'L4-2', NULL UNION ALL
select 'L5-4', 'Top', 'L2-1', 'L3-1', 'L4-2', NULL UNION ALL
select 'L5-5', 'Top', 'L2-1', 'L3-1', 'L4-2', NULL UNION ALL
select 'L5-6', 'Top', 'L2-1', 'L3-1', 'L4-3', NULL UNION ALL
select 'L5-7', 'Top', 'L2-1', 'L3-1', 'L4-3', NULL UNION ALL
select 'L6-1', 'Top', 'L2-1', 'L3-1', 'L4-1', 'L5-1' UNION ALL
select 'L6-2', 'Top', 'L2-1', 'L3-1', 'L4-1', 'L5-1' UNION ALL
select 'L6-3', 'Top', 'L2-1', 'L3-1', 'L4-1', 'L5-2' UNION ALL
select 'L6-4', 'Top', 'L2-1', 'L3-1', 'L4-1', 'L5-4' UNION ALL
select 'L6-5', 'Top', 'L2-1', 'L3-1', 'L4-1', 'L5-6' UNION ALL
select 'L6-6', 'Top', 'L2-1', 'L3-1', 'L4-1', 'L5-7'
This temp table contains a kind of hierarchy (in my environment, it can go up to level 10). I have another transaction table which contains revenue at leaf level only.
I have another process which works only on L3, L4 and L5 level. So I want to aggregate amount per month based on leaf level considering the the hierarchy ending only to L5 level. I am trying to create query based on join between transaction table with hierarchy level based on LeafLvl business and aggregating based on L5 node. However problem is coming when any part of hierarchy has leaf level only at L5 or L4 or L3. how should I handle this scenario ? Here is rest of my query for sample transaction table:
create table #tran
(business_day datetime, leaflvl varchar(20), revenue_amount numeric(28,2))
insert into #tran
select '2023-01-02', 'L5-5', 25.25 union all
select '2023-01-02', 'L5-3', 56.85 union all -- LealLvl at L5
select '2023-01-05', 'L5-3', 982.45 union all -- LealLvl at L5
select '2023-01-05', 'L5-5', 42.83 union all -- LealLvl at L5
select '2023-01-02', 'L6-1', 38.15 union all
select '2023-01-05', 'L6-1', 52.65 union all
select '2023-01-05', 'L6-2', 15.35 union all
select '2023-01-02', 'L6-4', 125.45 union all
select '2023-01-05', 'L6-6', 218.78 union all
select '2023-01-02', 'L6-5', 82.05 union all
select '2023-01-05', 'L6-5', 112.35 union all
select '2023-01-02', 'L4-3', 50.55 union all -- LealLvl at L4
select '2023-01-05', 'L4-3', 65.18 -- LealLvl at L4
I want to know the aggregate value for Jan month considering L5 as the leaf level i.e. if leaf level is at L5 or below, aggregation should be at L5 level but if the leaf level itself is at L4 then it should be considered for aggregation.
April 10, 2023 at 2:16 pm
Instead of doing it in one massive query, have you considered creating a Temp Table and collecting what you wish to aggregate and then just aggregate what is in that temp table.
Or use the Temp Table concept and simply aggregate L5s then L4s that were not caught in L5 then etc...
Or flip it over and simply aggregate only all L1s, then aggreate into that only all L2s etc...
The last has the added benefit of allowing you to pass in a parameter indicating the Leaf level you want to aggregate to and thus allow it to be a dynamic Leaf level request.
Or perhaps one of these suggestions might lead you to create some kind of hybrid or even a totally different concept. Just remember there can be many different correct ways to solve these kinds of puzzles. So just take a step outside the box and examine the box. Maybe the box is actually too big or too small or maybe you should have more than one box.
April 10, 2023 at 4:31 pm
From the tables provided what is the expected result?
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply