I have created a query that shows all customers deliveries (ODLN), Credit Memos (ORIN), Returns (ORDN) transactions. Please find query below:
Select dbo.ODLN.DocDate, dbo.ODLN.CANCELED, dbo.ODLN.CardName, dbo.DLN1.ItemCode, dbo.DLN1.Dscription, dbo.DLN1.Quantity, dbo.DLN1.LineTotal
From dbo.ODLN INNER JOIN DLN1 ON dbo.ODLN.DocEntry = dbo.DLN1.DocEntry
WHERE (CONVERT(date, dbo.ODLN.DocDate) BETWEEN {?FromDate} AND {?ToDate}) AND (dbo.ODLN.CANCELED = 'N')
UNION
Select dbo.ORDN.DocDate, dbo.ORDN.CANCELED, dbo.ORDN.CardName, dbo.RDN1.ItemCode, dbo.RDN1.Dscription, -dbo.RDN1.Quantity, dbo.RDN1.LineTotal
From dbo.ORDN INNER JOIN RDN1 ON dbo.ORDN.DocEntry = dbo.RDN1.DocEntry
WHERE (CONVERT(date, dbo.ORDN.DocDate) BETWEEN {?FromDate} AND {?ToDate}) AND (dbo.ORDN.CANCELED = 'N')
UNION
Select dbo.ORIN.DocDate, dbo.ORIN.CANCELED, dbo.ORIN.CardName, dbo.RIN1.ItemCode, dbo.RIN1.Dscription, -dbo.RIN1.Quantity, dbo.RIN1.LineTotal
From dbo.ORIN INNER JOIN RIN1 ON dbo.ORIN.DocEntry = dbo.RIN1.DocEntry
WHERE (CONVERT(date, dbo.ORIN.DocDate) BETWEEN {?FromDate} AND {?ToDate}) AND (dbo.ORIN.CANCELED = 'N')
But many delivery transactions did not appear. After an investigation I found the following: If we have two different delivery transactions, in the same date, with the same product, and the same quantity for one customer, only ONE of the transactions is appearing in the report. However we need both the appear.To be more clear show the example below:
Actual Problem:
Customer Name Item Code Quantity (Kg) Delivery Number Customer X Item001 10000 161801167 Customer Y Item002 5000 161801168Solution needed:
Customer Name Item Code Quantity (Kg) Delivery Number Customer X Item001 10000 161801167 Customer X Item001 10000 161801200 Customer Y Item002 5000 161801168 Customer Y Item002 5000 161801191Can somebody give me hints to help?
Thanks