SQL Server Proportionally split a field across rows

ejk8hzay  于 2023-10-15  发布在  其他
关注(0)|答案(2)|浏览(111)

I have a field that is doubling a value by populating it in both rows for the date/location/product/company id. The lost Sales is coming through into both rows, but it is actually the total for both. I need to remove the duplicate and proportionally split it across the rows to make the New Lost Sales column

New Lost Sales = (row omits / sum omits on all rows) * Lost Sales on row
| Date | Location | Product | CompanyID | Omits | Lost_Sales | New_Lost_Sales |
| ------------ | ------------ | ------------ | ------------ | ------------ | ------------ | ------------ |
| 1/9 | Loc1 | Prod1 | A | 0 | 50 | 0 |
| 1/9 | Loc1 | Prod1 | B | 40 | 50 | 50 |
| 1/9 | Loc2 | Prod1 | A | 8 | 40 | 16 |
| 1/9 | Loc2 | Prod1 | B | 12 | 40 | 24 |

atmip9wb

atmip9wb1#

Assuming Lost_Sales is recorded per date and location only, you can use SUM(Omits) OVER (Date,Location) to calculate the sum of omits for each key combination.

For example

Select 
    1.00*lost_sales*omits/SUM(Omits) OVER (Date,Location) As New_Lost_Sales
FROM
...

You may have to adjust the query in case SUM(Omits) can be null or 0 , eg SUM(ISNULL(Omits,0)) .

Without the actual tables and sample data one can only make guesses. What happens for example if SUM(Omits) is 0 but Lost_Sales isn't? Are fractional lost sales acceptable?

Let's say you want to retain Lost_Sales if SUM(INSULL(Omits,0)) is 0. To avoid repeating the SUM clause, you can use a CTE to calculate it once and use it in an IIF clause:

WITH omits as (
    Select 
        ...,
        SUM(Omits) OVER (Date,Location) as TotalOmits
    FROM
    ...
)
select *,
    IIF(TotalOmits=0,
        Lost_Sales,
        1.00*lost_sales*omits/TotalOmits) as New_Lost_Sales
FROM omits
k75qkfdt

k75qkfdt2#

do you want something like this

WITH CTE AS (
  SELECT
    Date,
    Location,
    Product,
    CompanyID,
    SUM(Omits) AS TotalOmits
  FROM YourTableName 
  GROUP BY Date, Location, Product, CompanyID
)

SELECT
  t.Date,
  t.Location,
  t.Product,
  t.CompanyID,
  t.Omits,
  t.Lost_Sales,
  (t.Omits * 1.0 / c.TotalOmits) * t.Lost_Sales AS New_Lost_Sales
FROM YourTableName t 
JOIN CTE c
  ON t.Date = c.Date
  AND t.Location = c.Location
  AND t.Product = c.Product
  AND t.CompanyID = c.CompanyID;

相关问题