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
Rajneesh GuptaAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rajneesh GuptaAuthor Commented:
Please share the SQL query for the same
0
Mark WillsTopic AdvisorCommented:
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
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

Open in new window

0
Mark WillsTopic AdvisorCommented:
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

Open in new window

0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Rajneesh GuptaAuthor Commented:
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
0
Mark WillsTopic AdvisorCommented:
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 ?
0
Rajneesh GuptaAuthor Commented:
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
0
Mark WillsTopic AdvisorCommented:
This is really a new question. New table + Restructured table + New requirement = New question...

that's not too bad, just need to incorporate Customer for control breaks

As a cursor:
if object_id('tempdb..#results','u') is not null drop table #results

create table #results ([ID-Customer] int, [ID-Master] int, [ID-Brand] int, brand varchar(20), value int, qty int, total int)

declare @cid int
declare @mid int
declare @bid int
declare @csku varchar(20)
declare @mbrand varchar(20)
declare @bbrand varchar(20)
declare @cvalue int
declare @bvalue int
declare @qty int
declare @tgt int
declare @lcid int = 999999999
declare @lbrand varchar(20) = 'zzzzzzzz'

DECLARE brand_cursor CURSOR FOR 
        select c.id cid, c.sku csku, c.[value] cvalue, m.id mid, m.brand mbrand
              ,b.id bid, b.[value] bvalue, b.brand bbrand
        from  [customer] c
        inner join [master] m on c.sku = m.brand 
        inner join [brand] b on c.sku = b.brand
        order by c.id, m.id, c.[value], c.sku, b.brand, b.[value] desc

OPEN brand_cursor
FETCH NEXT FROM brand_cursor INTO @cid,@csku,@cvalue,@mid,@mbrand,@bid,@bvalue,@bbrand
WHILE @@FETCH_STATUS = 0
BEGIN

        if @lcid <> @cid or @lbrand <> @mbrand
        begin
             set @lbrand = @csku
             set @lcid = @cid
             set @tgt = @cvalue
        end

        set @qty = @tgt / @bvalue
        set @tgt = @tgt % @bvalue

        if @qty > 0
        begin
             insert #results		
             select @cid,@mid,@bid,@bbrand, @bvalue, @qty, @qty * @bvalue
        end
			
        FETCH NEXT FROM brand_cursor INTO @cid,@csku,@cvalue,@mid,@mbrand,@bid,@bvalue,@bbrand

END

CLOSE brand_cursor
DEALLOCATE brand_cursor

SELECT * FROM #results

Open in new window

As a recursive CTE :
;with CTE as
(  select row_number() over (partition by c.id, m.id, b.brand order by b.[value] desc) RN
         ,c.id cid, c.sku csku  
         ,m.id mid, m.brand mbrand   
         ,b.id bid, b.[value] bvalue, b.brand bbrand
         ,0 as qty, c.[value]  as tgt
   from  [customer] c
   inner join [master] m on c.sku = m.brand 
   inner join [brand] b on c.sku = b.brand
), RCTE as
(  select rn, cid, mid, bid ,mbrand, bvalue, qty, tgt
   from CTE 
   where rn = 1
   union all  
   select P.rn+1, R.cid, 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.cid = p.cid and R.mbrand = P.mbrand and R.rn = P.rn  
)  select cid,mid,bid,mbrand,bvalue,qty,qty*bvalue as total
   from rcte
   where qty > 0
   order by cid, mid,rn

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.