Skip to Content
0
Apr 23, 2014 at 05:27 AM

Help me finish my query - please ;)

34 Views

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