Optimising an nested SQL

[jase2006]

This query suppose to find all duplicates in the vContractDetails table. At the moment it is taking forever to process. Can any one spot any problems? Or help to optimise it? Thanks in advance

*********************************************
SELECT DISTINCTROW vContractDetails.Start, vContractDetails.End, vContractDetails.Project, tStaff.FirstName, tStaff.LastName, vCompany.name, vContractDetails.[Daily Rate], vContractDetails.[Hours per day], vContractDetails.[Hourly Rate], vContractDetails.StaffId
FROM (tStaff RIGHT JOIN vContractDetails ON tStaff.StaffId = vContractDetails.StaffId) LEFT JOIN vCompany ON vContractDetails.[Company Ref] = vCompany.[Account Number]
WHERE (((vContractDetails.StaffId) In (SELECT [StaffId] FROM [vContractDetails] As Tmp GROUP BY [StaffId],[Start],[End] HAVING Count(*)>1 And [Start] = [vContractDetails].[Start] And [End] = [vContractDetails].[End])))
ORDER BY vContractDetails.Start, vContractDetails.End;
******************************************

 

[r937]

i don't understand why you have the AND conditions after the HAVING COUNT(*)>1

try like this --

SELECT vContractDetails.Start
     , vContractDetails.End
     , vContractDetails.Project
     , tStaff.FirstName
     , tStaff.LastName
     , vCompany.name
     , vContractDetails.[Daily Rate]
     , vContractDetails.[Hours per day]
     , vContractDetails.[Hourly Rate]
     , vContractDetails.StaffId
  FROM (
       vContractDetails
LEFT OUTER 
  JOIN tStaff  
    ON tStaff.StaffId = vContractDetails.StaffId
       ) 
LEFT 
  JOIN vCompany 
    ON vCompany.[Account Number] = vContractDetails.[Company Ref]
 WHERE vContractDetails.StaffId In 
       ( SELECT [StaffId] 
           FROM [vContractDetails] As Tmp 
         GROUP 
             BY [StaffId]
              , [Start]
              , [End] 
         HAVING Count(*)>1  )
ORDER 
    BY vContractDetails.Start
     , vContractDetails.End

the only thing i'm not sure of is whether you wanted to check for dupes by StaffID only, or whether you wanted to find duplicate rows with the same StaffID, Start, End

r937.com | rudy.ca

 

[jase2006]

I wanted to find duplicate rows with the same StaffID, Start, End.

Thanks

 

[jase2006]

I like to add some extra info on this problem. The query is fine when there are duplicates otherwise it seems to go into a loop?

 

[jase2006]

Thanks r937. I know what the problem is now.