October 21, 2011 at 9:56 am
I've created a SSRS report that retrieves a summary of customer sales for a period of time. The parameters are: date range, Salesperson and state of sale. The report was correct until I added the state parameter. After further review I found each customer is duplicated in the address table (CRD1) from which I'm pulling the state. Consequently, I need to filter, I assume, by using 'unique' relating to the CRD1.State field. I've tried various syntax with no success. Below is the query with duplication and any assistance would be appreciated:
SELECT
[Salesperson]
, [State]
, [Cust ID]
, [Customer]
, MAX ([Invoices]) [Invoices]
, MAX ([Returns]) [Returns]
, MAX ([Credits]) [Credits]
FROM (
SELECT
OSLP.SlpName AS [Salesperson]
, CRD1.State AS [State]
, OINV.CardCode AS [Cust ID]
, OINV.CardName AS [Customer]
, SUM(INV1.LineTotal) AS [Invoices]
, NULL AS [Returns]
, NULL AS [Credits]
FROM
OSLP JOIN OINV ON OSLP.SlpCode = OINV.SlpCode
RIGHT OUTER JOIN CRD1 ON OINV.CardCode = CRD1.CardCode
LEFT OUTER JOIN INV1 ON OINV.DocEntry = INV1.DocEntry
WHERE
(OINV.CANCELED = 'N') AND OINV.DocDate BETWEEN @BeginDate AND @EndDate AND OSLP.SlpName = @SalesRep AND CRD1.State = @State
GROUP BY
OSLP.SlpName
, CRD1.State
, OINV.CardCode
, OINV.CardName
UNION ALL
SELECT
OSLP.SlpName
, CRD1.State
, ORDN.CardCode
, ORDN.CardName
, NULL AS [Invoices]
, SUM(ORDN.DocTotal*-1) AS [Returns]
, NULL AS [Credits]
FROM
OSLP JOIN ORDN ON OSLP.SlpCode = ORDN.SlpCode
RIGHT OUTER JOIN CRD1 ON ORDN.CardCode = CRD1.CardCode
WHERE
(ORDN.CANCELED = 'N') AND ORDN.DocDate BETWEEN @BeginDate AND @EndDate AND OSLP.SlpName = @SalesRep AND CRD1.State = @State
GROUP BY
OSLP.SlpName
, CRD1.State
, ORDN.CardCode
, ORDN.CardName
UNION ALL
SELECT
OSLP.SlpName
, CRD1.State
, ORIN.CardCode
, ORIN.CardName
, NULL AS [Invoices]
, NULL AS [Returns]
, SUM(ORIN.DocTotal*-1)AS [Credits]
FROM
OSLP JOIN ORIN ON OSLP.SlpCode = ORIN.SlpCode
RIGHT OUTER JOIN CRD1 ON ORIN.CardCode = CRD1.CardCode
WHERE
(ORIN.CANCELED = 'N') AND ORIN.DocDate BETWEEN @BeginDate AND @EndDate AND OSLP.SlpName = @SalesRep AND CRD1.State = @State
GROUP BY
OSLP.SlpName
, CRD1.State
, ORIN.CardCode
, ORIN.CardName) AS T
GROUP BY
[Salesperson]
, [State]
, [Cust ID]
, [Customer]
ORDER BY
[Customer]
October 21, 2011 at 10:00 am
Try adding the state code to your join to CRD1
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply