July 19, 2013 at 10:02 pm
In the statement below, there is a problem where some records are occasionally duplicated. I have not been able to figure out where my problem is.
When I complete the query below, it returns the correct number of records without any duplicates.
SELECT COUNT(*) AS Expr1
FROM salesExport AS SAL LEFT OUTER JOIN
tipTransactionPivot AS PIV ON SAL.rvcID = PIV.rvcID AND SAL.microsEmployeeNumber = PIV.microsEmployeeNumber AND
SAL.businessDate = PIV.businessDate
WHERE (SAL.businessDate = '7/10/2013')
When I add the EMP table, it duplicates two of my records.
SELECT COUNT (*)
FROM employee AS EMP1 RIGHT OUTER JOIN
salesExport AS SAL ON EMP1.microsEmployeeNumber = SAL.microsEmployeeNumber LEFT OUTER JOIN
tipTransactionPivot AS PIV ON SAL.rvcID = PIV.rvcID AND SAL.microsEmployeeNumber = PIV.microsEmployeeNumber AND
SAL.businessDate = PIV.businessDate
WHERE SAL.businessDate = '7/10/2013'
I've tried a couple different approaches, but keep breaking something else...hopefully its just tired eyes. Any help is appreciated.
(BTW, the actual query is not a count statement, but this returns the same problem and is significantly shorter.)
July 20, 2013 at 3:52 am
mkswanson (7/19/2013)
In the statement below, there is a problem where some records are occasionally duplicated. I have not been able to figure out where my problem is.When I complete the query below, it returns the correct number of records without any duplicates.
SELECT COUNT(*) AS Expr1FROM salesExport AS SAL LEFT OUTER JOIN
tipTransactionPivot AS PIV ON SAL.rvcID = PIV.rvcID AND SAL.microsEmployeeNumber = PIV.microsEmployeeNumber AND
SAL.businessDate = PIV.businessDate
WHERE (SAL.businessDate = '7/10/2013')
When I add the EMP table, it duplicates two of my records.
SELECT COUNT (*)FROM employee AS EMP1 RIGHT OUTER JOIN
salesExport AS SAL ON EMP1.microsEmployeeNumber = SAL.microsEmployeeNumber LEFT OUTER JOIN
tipTransactionPivot AS PIV ON SAL.rvcID = PIV.rvcID AND SAL.microsEmployeeNumber = PIV.microsEmployeeNumber AND
SAL.businessDate = PIV.businessDate
WHERE SAL.businessDate = '7/10/2013'
I've tried a couple different approaches, but keep breaking something else...hopefully its just tired eyes. Any help is appreciated.
(BTW, the actual query is not a count statement, but this returns the same problem and is significantly shorter.)
can you please provide ddl statments with some sample data......
As per me I think as your using frist RIGHT OUTER JOIN THEN LEFT OUTER JOIN for salesexport table that is causing the duplicates values...
oNce you post sample data n table structure we will try to solve your porblem
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply