I am asking something that I am not even sure is even possible therefore I will appreciate any suggestion or advice.
I have a SQL code that works just fine. But I have an issue
My calculations for Inventory On Hand and On Order are done using LOC column (it is basically a store number). Inventory and what is on hand is counted per a store.
There is a table SCPOMGR.LOC from where I match stores with a chain.
However, I have one more calculation 'Forecast Demand' and it is done on a chain level, because we don't have it on a store level.
It looks like this.
It is pretty confusing because after i am done querying I am inserting SQL code into a data visualization tool and it sums inventory on hand and on order for a Chain correctly, but Forecast Demand which should be in this example just 59.618 for the whole Chain gets multiplied a number of times.
I am not even sure if that somehow can be overturned in SQL so that the number of forecast doesn't get repeated on each new line for the same item.
SELECT n.U_CHAINNAME AS 'Chain Name', s.LOC, s.ITEM,
sk.OH AS 'Inventory On Hand',
ISNULL(sc.QTY, 0) AS 'On Order',
ISNULL(f.TOTFCST, 0) AS 'Forecast Demand (sales order)'
FROM [BYIntegration].[SCPOMGR].[UDT_SKUPROJ] s
LEFT OUTER JOIN [BYIntegration].[SCPOMGR].[LOC] n
LEFT OUTER JOIN SCPOMGR.DMDUNIT d
LEFT OUTER JOIN (select loc, item,SUM(OH) AS OH FROM [BYIntegration].[SCPOMGR].[SKU] sk group by loc, item) sk
ON s.[LOC]=sk.[LOC] AND s.[ITEM]=sk.[ITEM]
LEFT OUTER JOIN (select loc, dmdunit, SUM([TOTFCST]) as TOTFCST from [BYIntegration].[SCPOMGR].[FCSTPERFSTATIC] group by loc, dmdunit) f
ON n.[U_CHAINNAME]=f.[LOC] AND s.[ITEM]=f.[DMDUNIT]
LEFT OUTER JOIN (select loc, item, sum(qty) as QTY from [BYIntegration].[SCPOMGR].[SCHEDRCPTS] group by loc, item) sc
ON s.[LOC]=sc.[LOC] AND s.[ITEM]=sc.[ITEM]
WHERE s.LOC LIKE 'ST%'
GROUP BY n.U_CHAINNAME, s.LOC, s.ITEM, sk.OH, sc.QTY, f.TOTFCST
ORDER BY n.U_CHAINNAME, s.LOC, s.ITEM