Get duplicate Records

See more:

SQL-Server-2005

SQL-Server

Hi all,

I have two tables one of the table columns like

tbl_emp
Id                        Total stops   Total Emp
100816080630010P   5                     6

tbl_Details
Id                        Stopnumbers   EmpId
100816080630010P     1     BACS136
100816080630010P     3     EmpHydTest118
100816080630010P     2     EmpHydTest13
100816080630010P     2     EmpHydTest19
100816080630010P     4     EmpHydTest52
100816080630010P     1     hdtest177

Form above tbl_trips total employees are 6 and in the tbl_tripdet empid count is 6 and stopnumbers should not repeat like this and employees count should be same. If the count is less than or grater than then those details should be get. It should be like 1,2,3,4,5,6. If the case is like as show above stopnumbers repeating then we should get those records.

I tried like this:

SELECT fld_tripid FROM tbl_trips tb
WHERE (fld_totalemps != (SELECT COUNT(fld_EMPID ) FROM tbl_tripdet
WHERE fld_tripid = tb.fld_tripid) OR
 ((tb.fld_TotalEmps * (tb.fld_TotalEmps+1))/2) !=
(SELECT SUM(fld_StopNumber) FROM tbl_tripdet
WHERE fld_tripid = tb.fld_tripid))
AND (SELECT COUNT(fld_EMPID) from tbl_TripDet where fld_TripID = tb.fld_TripID) >0

When I used this query it's taking time to run.

Thanks & Regards,
Jeevan.