Query multiple deliveries from single sales order

[Former Member]

Hello,

I have a hard time to write a query to output all single sales orders with multiple deliveries. So far I came up with the following query.

SELECT DISTINCT T0.CardCode, T0.[CardName],T1.[Dscription], T2.Quantity AS [Delivery Qty],  T3.docnum 'Delivery #' ,  T5.docnum AS 'Invoice #', T1.[Quantity] AS [SO Qty],  T0.[DocNum] AS 'SO #', T5.DocDate, COUNT(*)

FROM ORDR T0 INNER JOIN RDR1 T1 ON T0.DocEntry = T1.DocEntry LEFT join DLN1 T2 on T2.baseentry = t0.docentry and t1.linenum = T2.baseline LEFT join ODLN T3 on T3.docentry = T2.docentry LEFT JOIN INV1 T4 ON T4.BaseEntry=T3.DocEntry LEFT JOIN OINV T5 ON T5.DocEntry=T4.DocEntry

GROUP BY T5.docnum, T2.Quantity, T3.docnum, T0.CardCode, T0.[CardName],T1.[Dscription],T1.[Quantity], T0.[DocNum], T5.DocDate

Having COUNT(*) > 1

I am not sure this would solve the problem.

Thank you for your help.

--Bill

[former_member773342]

late, but i hope this can help someone someday

SELECT 
	T0.DocNum AS SalesOrderNr,
	T0.DocEntry,
	FORMAT (T0.DocDate, 'dd.MM.yyyy') AS 'Posting Date (Formated)',
	FORMAT (T0.DocDueDate, 'dd.MM.yyyy') AS 'Delivery Date (Formated)',
	T0.CardName, 
	CASE T0.DocStatus 
		WHEN 'C' THEN 'Closed' 
		WHEN 'O' THEN 'Open' 
	END AS Status
FROM ORDR T0 --Sales Order
JOIN (
	/* sales order with multiple deliveries */
	SELECT 
	    T0.BaseEntry
	FROM (
		/* all Deliveries with associated Sales Order */
		SELECT DISTINCT
			T0.DocNum, -- deliveryNr
			T1.BaseEntry -- sales order DocEntry
		FROM ODLN T0 -- delivery
		JOIN DLN1 T1 ON T0.DocEntry = T1.DocEntry -- delivery items
		            AND T1.BaseType = 17 -- type sales order
		WHERE T0.CANCELED <> 'Y' -- delivery not canceled
	) T0
	GROUP BY T0.BaseEntry
	HAVING COUNT(*) > 1
) T1 ON T1.BaseEntry = T0.DocEntry
--now join more tables if needed
ORDER BY T0.DocNum