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 |
2条答案
按热度按时间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
You may have to adjust the query in case
SUM(Omits)
can be null or0
, egSUM(ISNULL(Omits,0))
.Without the actual tables and sample data one can only make guesses. What happens for example if
SUM(Omits)
is 0 butLost_Sales
isn't? Are fractional lost sales acceptable?Let's say you want to retain
Lost_Sales
ifSUM(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:k75qkfdt2#
do you want something like this