If Statement - New, please help!

26 Views

SELECT DISTINCT(T1.[DocNum]) , T2.[ShipDate] AS 'Order Delivery Date', T1.[CardName], T5.SlpName, T3.[ItemCode], T2.[Dscription], T3.OnHand,T2.[Quantity] AS 'Order Quantity', T2.[DelivrdQty], ((T2.[Quantity] - T2.[DelivrdQty])* T2.Price) as 'Value', T6.[DocDueDate] AS 'Expected Delivery Date' , T6.[U_U_BookDate], T1.[Comments] FROM ORDR T1 INNER JOIN RDR1 T2 ON T1.DocEntry = T2.DocEntry INNER JOIN OITM T3 ON T2.ItemCode = T3.ItemCode LEFT OUTER JOIN POR1 T4 ON T3.ItemCode = T4.ItemCode AND T4.LINESTATUS = 'o' INNER JOIN OSLP T5 ON T1.SlpCode = T5.SlpCode Left Outer JOIN OSHP T7 ON T1.TrnspCode = T7.TrnspCode LEFT OUTER JOIN OPOR T6 ON T4.DocEntry = T6.DocEntry WHERE T2.[LineStatus] = 'O' AND T2.[ShipDate] <=[%0] AND T1.[U_CRD_STS] <> 'H' and t3.[QryGroup16] = 'y' and T2.[DelivrdQty] = 0 and T1.[CardName] <> 'Hornbead Ltd' and T1.Trnspcode <> '1' ORDER BY T1.[CardName]

So I have this report here, basically I need to add in there somewhere that:

T3. Onhand - if this is above "0" Expected delivery date shouldn't show

T3 Onhand - if this is below "o" Expected Delivery Date should show.

I'm very new to all this and have been given this task, really could do with some help as I dont have a clue!

Basically if there is stock of an item we don't want to see the expected delivery date for new stock, only the expected delivery date for items we're out of stock. Because we're getting a lot of duplicate rows in this report.

Thanks!