October 8, 2011 at 9:45 am
The following is probably a simple question for most of you but I'm learning by trial and error. I need to sum into one row (Customer) records from three tables; ie, line item totals for invoices, doc total for returns, doc total for Credit Memos. The below query retrieves the correct data but a row for each document type; invoice, return, credit memo. Can someone please tell me what needs to change to pull one row for each customer?
SELECT OSLP.SlpName AS [Salesperson], OINV.CardCode AS [Cust ID], OINV.CardName AS [Customer],
SUM(INV1.LineTotal) AS [Sales]
FROM OSLP JOIN OINV ON OSLP.SlpCode = OINV.SlpCode
LEFT OUTER JOIN INV1 ON OINV.DocEntry = INV1.DocEntry
WHERE (OINV.CANCELED = 'N') AND (OINV.DocDate BETWEEN CONVERT(DATETIME, '2010-11-01 00:00:00', 102) AND CONVERT(DATETIME, '2011-09-30 00:00:00', 102))
GROUP BY OSLP.SlpName, OINV.CardCode, OINV.CardName
UNION ALL
SELECT OSLP.SlpName, OCRD.CardCode,OCRD.CardName,
SUM(ORDN.DocTotal*-1) AS [Sales]
FROM OSLP LEFT OUTER JOIN ORDN ON OSLP.SlpCode = ORDN.SlpCode
LEFT OUTER JOIN OCRD ON ORDN.CardCode = OCRD.CardCode
WHERE (ORDN.CANCELED = 'N') AND (ORDN.DocDate BETWEEN CONVERT(DATETIME, '2010-11-01 00:00:00', 102) AND CONVERT(DATETIME, '2011-09-30 00:00:00', 102))
GROUP BY OSLP.SlpName, OCRD.CardCode,OCRD.CardName
UNION ALL
SELECT OSLP.SlpName, OCRD.CardCode, OCRD.CardName,
SUM(ORIN.DocTotal*-1)AS [Sales]
FROM OSLP LEFT OUTER JOIN
ORIN ON OSLP.SlpCode = ORIN.SlpCode
LEFT OUTER JOIN OCRD ON ORIN.CardCode = OCRD.CardCode
WHERE (ORIN.CANCELED = 'N') AND (ORIN.DocDate BETWEEN CONVERT(DATETIME, '2010-11-01 00:00:00', 102) AND CONVERT(DATETIME, '2011-09-30 00:00:00', 102))
GROUP BY OSLP.SlpName, OCRD.CardCode, OCRD.CardName
ORDER BY Salesperson, [Cust ID]
October 8, 2011 at 2:50 pm
duplicate post. no replies please.
Original post: http://www.sqlservercentral.com/Forums/FindPost1187493.aspx
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply