Hi,
I am trying to write a query - which will return a list of sales orders that have a specific sales person assocaited with them. But also - only return a list of sales order which have a due date of today or below. Also - one of the columns should be the sum of the open line items. This is what I've got:
SELECT T0.[DocNum], T0.[DocDate], T0.[CardCode], T0.[CardName], T0.[DocDueDate], SUM(T2.[LineTotal]) AS [OPEN TOTAL]
FROM ORDR T0
INNER JOIN OSLP T1 ON T0.SlpCode = T1.SlpCode
INNER JOIN RDR1 T2 ON T0.DocEntry = T2.DocEntry
INNER JOIN OITM T3 ON T2.ItemCode = T3.ItemCode
INNER JOIN OITW T4 ON T3.ItemCode = T4.ItemCode
WHERE T1.[SlpName] = 'RICHARD THURLOW' AND
T0.[DocStatus] = 'O' AND
T0.[CANCELED] = 'N' AND
T0.[DocDueDate] <= [%0] AND
T2.[OpenQty] > 0 AND
T4.[WhsCode] = '01. Main' AND
T3.[OnHand] > 0
GROUP BY T0.[DocNum], T0.[DocDate], T0.[CardCode], T0.[CardName], T0.[DocDueDate]
ORDER BY T0.[DocNum], T0.[DocDate], T0.[CardCode], T0.[CardName], T0.[DocDueDate]
I am 95% of the way there - I just need help with ensuring that the SUM(T2.[LineTotal]) AS [OPEN TOTAL] column is only returning a value if the there is stock (OnHand - IsCommited) within a specific warehouse (01. Main).
Any help would be appreciated.
Regards
Rick