CODEWITHSUNDEEP
sqlsql-serversql-server-2008common-table-expression
vpbot
vpbot

Reputation: 2487

Distribute values to several rows in SQL Server

I need help with SQL Server on how to distribute a row value to several rows with the same id. To illustrate,

ForDistribution:

Id |   Qty  | TotalNoOfBranchesWithId
---+--------+-------------------------
1  |   40   |     2
2  |   33   |     3
3  |   21   |     2

A table that will receive the distributed values

Id | BranchCode |    Qty | QtyFromForDistributionTable
-------------------------------------------------------
1       101          13            20
1       102           8            20
2       101          10            11
2       102           2            10
2       103           3            12
3       101           1            11
3       102          12            10

As much as possible the distribution should be near equal for each id and branches.

I got something like below, but somewhat got confused and lost path.

with rs as 
(
    select 
        r.*, cume.cumequantity, 
        coalesce(s.shipped, 0) AS shipped
    from 
        tmpForDistribution r
    cross apply
        (SELECT SUM([QuantityInStock]) AS cumequantity
         FROM tmpForDistribution r2
         WHERE r2.ProductInventoryCode = r.ProductInventoryCode) cume 
    left join
        (SELECT ProductInventoryCode, COUNT(ProductInventoryCode) AS shipped
         FROM tmpDistributed s      
         GROUP BY s.ProductInventoryCode) s ON r.ProductInventoryCode = s.ProductInventoryCode      
)
select 
    rs.ProductInventoryCode, rs.cumequantity, rs.QuantityInStock,
    ***"how to distribute"***
from rs

I'm currently using SQL Server 2008

Here's a sample screen output

enter image description here

The upper result is 145 Branches, below we use to distribute the ForDistributionQty field which is 3130, I am ending up with a fraction (DistVal = 21.586) which is not correct for this problem, it should be a whole number such as 21, however, if its just 21, then 21 x 145 is just 3045 which is shy of 85 units.

Upvotes: 3

Views: 5234

Answers (2)

John Cappelletti
John Cappelletti

Reputation: 81960

Here we distribute the values, and then make a final "adjustment" to the record which has the largest quantity (arbitrary). But at the end of the day, the math works and the distributed values are square.

Note: Not sure why in your sample why ID 2 did not get an even distribution

Declare @Table table (Id int,BranchCode int,Qty int)
Insert Into @Table values
(1,       101,          13),
(1,       102,           8),
(2,       101,          10),
(2,       102,           2),
(2,       103,           3),
(3,       101,           1),
(3,       102,          12)

Declare @Dist table (ID int,Qty int)
Insert Into @Dist values
(1,40),
(2,33),
(3,49)

;with cte0 as (
        Select A.*
              ,ToDist  = cast(D.Qty as int)
              ,DistVal = cast(D.Qty as int)/C.Cnt 
              ,RN      = Row_Number() over (Partition By A.ID Order By cast(D.Qty as int)/C.Cnt Desc,A.Qty Desc)
         From  @Table A
         Join  (Select ID,Cnt=count(*) from @Table Group By ID) C on A.ID=C.ID
         Join  @Dist D on A.ID=D.ID  )
, cte1 as (
        Select ID,AdjVal=Sum(DistVal)-max(ToDist) From cte0 Group By ID
)
Select A.ID
      ,A.BranchCode
      ,A.Qty
      ,DistVal = DistVal - case when A.RN<=abs(AdjVal) then 1*sign(AdjVal) else 0 end
 From cte0 A
 Join cte1 B on (A.ID=B.Id)
 Order By 1,2

Returns

ID  BranchCode  Qty DistVal
1   101         13  20
1   102         8   20
2   101         10  11
2   102         2   11
2   103         3   11
3   101         1   24
3   102         12  25

Upvotes: 3

Dan Field
Dan Field

Reputation: 21641

If you can tolerate decimal values, a subquery seems to give a better query plan (tested on SQL 2014, with some sensible keys in place, this avoids a table spool and some additional index scans):

Declare @Table table (Id int,BranchCode int,Qty int, primary key(id, branchcode))
Insert Into @Table values
(1,       101,          13),
(1,       102,           8),
(2,       101,          10),
(2,       102,           2),
(2,       103,           3),
(3,       101,           1),
(3,       102,          12)

Declare @Dist table (ID int primary key,Qty int)
Insert Into @Dist values
(1,40),
(2,33),
(3,21)


SELECT
    t.id
   ,t.BranchCode
   ,t.Qty
   ,(d.Qty / CAST((SELECT COUNT(*) as cnt FROM @table t2 where t.id = t2.id) AS decimal(10,2))) as DistributedQty   
FROM @Table t
INNER JOIN @Dist d
ON d.id = t.Id

outputs:

Id  BranchCode  Qty DistributedQty
1   101         13  20.00000000000
1   102         82  20.00000000000
2   101         10  11.00000000000
2   102         21  11.00000000000
2   103         31  11.00000000000
3   101         11  10.50000000000
3   102         12  10.50000000000

If you need DistributedQty to be an int and retain remainders then I can't think of a better solution than @John Cappelletti's, noting that uneven quantities may not be as exactly even as you might hope (e.g. 32 distributed by three would result in a 12/10/10 distribution instead of an 11/11/10 distribution).

Upvotes: 2

Related Questions