Reputation: 105
I have the following query which works well. However, there are problems with the data and I need to remove the duplicate sVisitID
's from the result. This is the SELECT
I am using and the screenshot shows the result. I need to edit the select in order to only return one of the duplicates.
SELECT
DISTINCT(MyReps.[sName]) as 'Rep Name',
MyData.[sVisitID] as 'ID',
CAST(MyData.[sDate] AS DATE) as 'Date',
MyData.[sClientName] as 'Store Name',
MyData.[sState] as 'Region',
CAST(MyData.[sLatitudeStart] AS VARCHAR(100)) + ',' + CAST(MyData.[sLongitudeStart] as varchar(100)) as 'Map'
FROM
(SELECT *
FROM [tblRepresentatives]
WHERE [sActive] = 'True') as MyReps
FULL OUTER JOIN
(SELECT *
FROM [tblVisits]
WHERE CAST([sDate] AS DATE) = CAST(GETDATE()-1 AS DATE)) AS MyData ON MyReps.[sName] = MyData.[sRepresentativeName]
WHERE
MyData.[sClientName] IS NOT NULL
AND [sDateAndTimeStart] <> [sDateAndTimeEnd]
ORDER BY
MyData.[sClientName] DESC
Results:
Upvotes: 1
Views: 64
Reputation: 17943
Another approach can be using ROW_NUMBER()
PARTITION BY MyData.[sVisitID]
like following.
SELECT * FROM
(
SELECT (MyReps.[sName]) as 'Rep Name',
MyData.[sVisitID] as 'ID',
CAST(MyData.[sDate] AS DATE) as 'Date',
MyData.[sClientName] as 'Store Name',
MyData.[sState] as 'Region',
CAST(MyData.[sLatitudeStart] AS VARCHAR(100)) + ',' + CAST(MyData.[sLongitudeStart] as varchar(100)) as 'Map',
ROW_NUMBER() OVER (PARTITION BY MyData.[sVisitID] ORDER BY MyData.[sVisitID]) AS RN
FROM (
SELECT *
FROM [tblRepresentatives]
WHERE [sActive] = 'True') as MyReps
FULL OUTER JOIN (
SELECT *
FROM [tblVisits]
WHERE CAST([sDate] AS DATE) = CAST(GETDATE()-1 AS DATE)) AS MyData
On MyReps.[sName] = MyData.[sRepresentativeName]
WHERE MyData.[sClientName] IS NOT NULL AND [sDateAndTimeStart] <> [sDateAndTimeEnd]
)T
WHERE RN=1
ORDER BY T.[Store Name] DESC
Upvotes: 2
Reputation: 522762
One option would be to use GROUP BY
to resolve to a single record:
SELECT DISTINCT
MyReps.[sName] AS [Rep Name],
MyData.[sVisitID] AS [ID],
CAST(MyData.[sDate] AS DATE) AS [Date],
MyData.[sClientName] AS [Store Name],
MyData.[sState] AS [Region],
MIN(CAST(MyData.[sLatitudeStart] AS VARCHAR(100)) + ',' +
CAST(MyData.[sLongitudeStart] AS varchar(100))) AS [Map]
FROM your_cte
GROUP BY
[Rep Name], [ID], [Date], [Store Name], [Region];
Upvotes: 0