Hello all,
I'm working with a sub-query that's started to give me unexpected results after a process change at my company.
My main query is looking at allocated qty for each sales contract. My sub-query below ties each sales contract to an allocation batch in our system.
In the past, there was only one batch for each sales contract. Recent;y new batches were created. I discovered that on rare occasions, sales contracts were allocated to two separate batches generating two different batch codes associated with each allocation.
My query attempts to sum the allocated qty for each contract but my problem now (I think) is that each batch generates an item code. If there are two batches then two item codes and the allocated qty numbers will not sum correctly.
SELECT
CO_ALLOCATION_TAIL.SO_KEY,
SUM(CO_ALLOCATION_TAIL.SO_ALLOC_QTY) AS so_sum_ALLOC_QTY
FROM CO_ALLOCATION_TAIL
INNER JOIN CO_ALLOCATION_HEAD
ON CO_ALLOCATION_TAIL.BATCH_CODE = CO_ALLOCATION_HEAD.BATCH_CODE
GROUP BY
CO_ALLOCATION_TAIL.SO_ALLOC_QTY,
CO_ALLOCATION_TAIL.SO_KEY,
What I would expect from query:
| SO_KEY | SO_SUM_ALLOC_QTY |
| 112558 | 12 |
What I'm getting from query (even without Batch_Code in select statement):
| SO_KEY | BATCH_CODE | SO_SUM_ALLOC_QTY |
| 112558 | 16956 | 3 |
| 112558 | 4364 | 9 |
I tried removing Batch_code from any select statement but I'm running into trouble.
Does anyone know how I can get around this summing issue?
Thank you