Vouchagram India
asked on
Logic to caluculate the value based splitting
Hi Team,
Attached excel having 2 tables and final output required.
Please let me know the best and optimized way of doing it at database level.
Thanks in advance
Query.xlsx
Attached excel having 2 tables and final output required.
Please let me know the best and optimized way of doing it at database level.
Thanks in advance
Query.xlsx
you probably need a cursor or recursive CTE to
1) sort the branch details into descending value
2) use integer arithmetic to get the top detail value divided into master value
3) then the modulus becomes the target for the next instance of detail
Probably easy as a cursor, so assuming source tables are master and brand, with results being created in a temp table, have a look at
1) sort the branch details into descending value
2) use integer arithmetic to get the top detail value divided into master value
3) then the modulus becomes the target for the next instance of detail
Probably easy as a cursor, so assuming source tables are master and brand, with results being created in a temp table, have a look at
if object_id('tempdb..#results','u') is not null drop table #results
create table #results (mid int, bid int, bbrand varchar(20), bvalue int, qty int, total int)
declare @mid int
declare @bid int
declare @mbrand varchar(20)
declare @bbrand varchar(20)
declare @mvalue int
declare @bvalue int
declare @qty int
declare @tgt int
declare @lbrand varchar(20) = 'zzzzzzzz'
DECLARE brand_cursor CURSOR FOR
select m.id mid, m.[value] mvalue, m.brand mbrand
,b.id bid, b.[value] bvalue, b.brand bbrand
from [master] m
inner join brand b on m.brand = b.brand
order by m.id, m.brand, b.[value] desc
OPEN brand_cursor
FETCH NEXT FROM brand_cursor INTO @mid,@mvalue,@mbrand,@bid,@bvalue,@bbrand
WHILE @@FETCH_STATUS = 0
BEGIN
if @lbrand <> @mbrand
begin
set @lbrand = @mbrand
set @tgt = @mvalue
end
set @qty = @tgt / @bvalue
set @tgt = @tgt % @bvalue
if @qty > 0
begin
insert #results
select @mid,@bid,@bbrand, @bvalue, @qty, @qty * @bvalue
end
FETCH NEXT FROM brand_cursor INTO @mid,@mvalue,@mbrand,@bid,@bvalue,@bbrand
END
CLOSE brand_cursor
DEALLOCATE brand_cursor
SELECT * FROM #results
And, as an option, the recursive CTE
;with CTE as
( select row_number() over (partition by m.id, b.brand order by b.[value] desc) RN
,m.id mid, m.brand mbrand
,b.id bid, b.[value] bvalue, b.brand bbrand
,0 as qty, m.[value] as tgt
from [master] m
inner join brand b on m.brand = b.brand
), RCTE as
( select rn, mid, bid ,mbrand, bvalue, qty, tgt
from CTE
where rn = 1
union all
select P.rn+1, R.mid, P.bid ,P.mbrand, P.bvalue, R.tgt / P.bvalue , R.tgt % P.bvalue
from RCTE R
inner join CTE P on R.mbrand = P.mbrand and R.rn = P.rn
) select mid,bid,mbrand,bvalue,qty,qty*bvalue as total
from rcte
where qty > 0
order by mid,rn
ASKER
We are facing issue if i insert one more entry in master table with same brand.
We are getting wrong results. Please let us know the solution for the same
We are getting wrong results. Please let us know the solution for the same
So, master can have multiple occurrences of a Brand ?
Maybe they should be grouped by in the first instance, because there isnt any relationship to Brand Details other than Brand...
So, is it OK to SUM the Master grouped by Brand ?
Then what happens with Master ID in your desired results ?
Maybe they should be grouped by in the first instance, because there isnt any relationship to Brand Details other than Brand...
So, is it OK to SUM the Master grouped by Brand ?
Then what happens with Master ID in your desired results ?
ASKER
Please find attached updated sheet for your refeence. I have added one table of customer for your easy understanding.
Value shifted to Customer table and data to be populated customer wise, brand wise, value wise.
Query.xlsx
Value shifted to Customer table and data to be populated customer wise, brand wise, value wise.
Query.xlsx
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER