Below is my T-SQL query and attached is a sample of results.
I want the result of the following clause in my query to display only one time:
SUM(IV30300.TRXQTY) OVER (PARTITION BY IV30300.ITEMNMBR, IV30300.TRXLOCTN) As [SUMQTY]
This clause gives a lump sum of quantity for each item at its respective location.
But, as you see in the Results screenshot that I attached, it keeps repeating this calculation for each record. Again, I just want this calculation to display once for each combination of item and location code. For the rest of the records for that field, I would like for those records to display as blank.
How can this be done?
select DISTINCT '60 Days' as [Date Range],
IV30300.ITEMNMBR As [Item Number], IV00101.ITEMDESC As [Item Description],
IV30300.DOCNUMBR AS [Doc],
SUM(IV30300.TRXQTY) OVER (PARTITION BY IV30300.ITEMNMBR, IV30300.TRXLOCTN) As [SUMQTY], IV30300.TRXLOCTN As [Location Code],
CASE WHEN IV30300.DOCTYPE = '1' THEN 'Adjustment'
WHEN IV30300.DOCTYPE = '2' THEN 'Variance'
WHEN IV30300.DOCTYPE = '3' THEN 'Transfer'
WHEN IV30300.DOCTYPE = '4' THEN 'Purchasing Receipt'
WHEN IV30300.DOCTYPE = '5' THEN 'Sales Return'
WHEN IV30300.DOCTYPE = '6' THEN 'Sales'
WHEN IV30300.DOCTYPE = '7' THEN 'Assembly'
AS [Document Type],
IV30300.DOCDATE As [Document Date],
IV30300.TRXQTY As [Qty], IV30300.UOFM As [U Of M], IV30300.UNITCOST As [Unit Cost],
IV30300.EXTDCOST As [Extended Cost], IV00101.USCATVLS_1 as [Use]
INNER JOIN DTZ..IV00101
DTZ..IV30300.ITEMNMBR = DTZ..IV00101.ITEMNMBR
INNER JOIN DTZ..IV00102
DTZ..IV30300.ITEMNMBR = DTZ..IV00102.ITEMNMBR and
DTZ..IV30300.TRXLOCTN = DTZ..IV00102.LOCNCODE
IV30300.DOCTYPE IN (1, 6)
and IV00102.QTYONHND > 0 and
IV00102.QTYONHND - IV00102.ATYALLOC > 0
AND IV00102.RCRDTYPE <> 1
AND IV00101.USCATVLS_1 IN ('FG', 'RAW')
and IV30300.TRXQTY < 0
and IV30300.DOCDATE < cast(getdate()-60 as datetime)