October 8, 2011 at 7:44 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
SELECTOSLP.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:53 pm
You might need to use your current query as the source of a CrossTab query as described in the related link in my signature.
October 10, 2011 at 5:24 am
SELECT
[Salesperson]
, [Cust ID]
, [Customer]
, MAX([Sales1]) [Sales1]
, MAX([Sales2]) [Sales2]
, MAX([Sales3]) [Sales3]
FROM (
SELECT
OSLP.SlpName AS [Salesperson]
, OINV.CardCode AS [Cust ID]
, OINV.CardName AS [Customer]
, SUM(INV1.LineTotal) AS [Sales1]
, NULL AS [Sales2]
, NULL AS[Sales3]
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
, NULL AS [Sales1]
, SUM(ORDN.DocTotal*-1) AS [Sales2]
, NULL AS [Sales3]
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
, NULL AS [Sales1]
, NULL AS [Sales2]
, SUM(ORIN.DocTotal*-1)AS [Sales3]
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) AS T
GROUP BY
[Salesperson]
, [Cust ID]
, [Customer]
ORDER BY
Salesperson, [Cust ID]
?
I Have Nine Lives You Have One Only
THINK!
October 10, 2011 at 6:53 am
The most logical way to do this is like so:
SELECT
OSLP.SlpName AS [Salesperson],
invoice.,
d.*,
i.*
FROM OSLP
LEFT JOIN (
SELECT
OINV.SlpCode,
OINV.CardCode AS [Cust ID],
OINV.CardName AS [Customer],
SUM(INV1.LineTotal) AS [Sales]
FROM OINV
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 OINV.SlpCode, OINV.CardCode, OINV.CardName
) invoice ON invoice.SlpCode = OSLP.SlpCode
LEFT JOIN (
SELECT
ORDN.SlpCode,
OCRD.CardCode,
OCRD.CardName,
SUM(ORDN.DocTotal*-1) AS [Sales]
FROM ORDN
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 ORDN.SlpCode, OCRD.CardCode, OCRD.CardName
) d ON d.SlpCode = OSLP.SlpCode
LEFT JOIN (
SELECT
ORIN.SlpCode,
OCRD.CardCode,
OCRD.CardName,
SUM(ORIN.DocTotal*-1)AS [Sales]
FROM ORIN
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 ORIN.SlpCode, OCRD.CardCode, OCRD.CardName
) i ON i.SlpCode = OSLP.SlpCode
However, without dml/ddl it's not possible to test. Quite a few columns in those derived tables are left in for testing only, and it looks like at least one join may be redundant.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
October 14, 2011 at 4:32 am
Thank you! Worked great!
October 14, 2011 at 4:33 am
Thank you! Worked great!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply