Post reply

Get duplicate Records

  • October 10, 2010 at 11:39 pm

    #241512

    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

  • October 11, 2010 at 1:41 am

    #1234810

    Hi , Sorry i am not understanding your issue. Can you restate it ?


    Clear Sky SQL
    My Blog[/url]

  • October 11, 2010 at 4:08 am

    #1234864

    Hi,

    Thanks for you replay, where I have two tables. I want get the records which are repeating stopnumbers, and fld_empid from the second table based on first table transportid. fld_stopnumbers should be unique like 1,2,3,4 and equal to the first table fld_totalstops. fld_empid count should be equal to the fld_totalemps. I am able to get the result but when i am executing this in sever it's taking time to run. Can you please guide to how over come this.

    Thanks & Regards,

    Jeevan.

  • October 11, 2010 at 4:20 am

    #1234868

    You do seem to of overcompilcated this somewhat, have you used the "having" clause before ?

    select id,Stopnumber,count(*)

    from tbl_Details

    group by id,Stopnumber

    having count(*) >1


    Clear Sky SQL
    My Blog[/url]

  • October 11, 2010 at 4:21 am

    #1234870

    Taking a shot at what i think you need.

    select ID, Stopnumbers

    from tbl_Details

    group by ID, Stopnumbers

    having count(*) > 1

    This would give you all ID/Stopnumbers which have more then 1 rows (duplicate "IDs" you might say).

    Hay no fair Dave your post wasnt there when i read it. 😛

    /T

  • October 11, 2010 at 4:49 am

    #1234885

    Hi,

    I tried this even...

    Thanks & Regards,

    Jeevan.

  • October 11, 2010 at 4:50 am

    #1234886

    Hi,

    I tried this even...

    Thanks & Regards,

    Jeevan.

  • October 11, 2010 at 4:58 am

    #1234888

    So that gives you the tripid and the stopnumber of the duplicated data.

    Why is that not what you need ?


    Clear Sky SQL
    My Blog[/url]

  • October 11, 2010 at 5:04 am

    #1234892

    Okay replicating the tables and then running your code.

    select distinct fld_tripid

    from tbl_tripdet

    group by fld_tripid, fld_StopNumber

    having count(*) > 1

    Produces the exact same result as your code. If thats wrong... your going to have to put in more examples and what result you expect from that.

    /T

  • October 11, 2010 at 5:07 am

    #1234894

    Hi,

    I need to check on both conditions fld_totalstopnumbers and fld_empid. fld_totalstopnumbers should not have the numbers like as shown bellow.

    1,3,3,4 here total stops 4 and employees are 4

    1,5,6 here total stops 3 and employees are 5

    fld_totalstopnumbers should have like this

    1,2,3,4 here total stops 4 and employees are 4

    1,2,3,4,5 here total stops 5 and employees are 5

    Thanks & Regards,

    Jeevan.

  • October 11, 2010 at 5:13 am

    #1234899

    Try investigating the ranking functions.

    http://www.sqlservercentral.com/articles/T-SQL/69717/

    Count also supports the over clause.


    Clear Sky SQL
    My Blog[/url]

  • October 11, 2010 at 6:24 am

    #1234930

    yjeevanrao (10/11/2010)

    Hi,

    I need to check on both conditions fld_totalstopnumbers and fld_empid. fld_totalstopnumbers should not have the numbers like as shown bellow.

    1,3,3,4 here total stops 4 and employees are 4

    1,5,6 here total stops 3 and employees are 5

    fld_totalstopnumbers should have like this

    1,2,3,4 here total stops 4 and employees are 4

    1,2,3,4,5 here total stops 5 and employees are 5

    Thanks & Regards,

    Jeevan.

    Sorry still not following exactly what you want. However your problem might be connected to having possible ALOT of Table Scans.

    Then maybe this could work. Sum up everything you need from the details and then join to that to find the rows that you want. 1 Table Scan is better then many 🙂

    declare @tbl table

    (

    fld_tripid varchar(32), EmpCount int, StopCount int primary key (fld_tripid)

    )

    insert into @tbl

    select fld_tripid, COUNT(fld_EMPID) EmpCount, sum(fld_StopNumber) StopCount

    from tbl_tripdet

    group by fld_tripid

    having count(fld_StopNumber) > 0

    SELECT tb.fld_tripid, *

    FROM tbl_trips tb

    join @tbl t on t.fld_tripid = tb.fld_tripid

    where ( tb.fld_totalemps != t.EmpCount

    OR

    ( (tb.fld_TotalEmps * (tb.fld_TotalEmps+1))/2 != t.StopCount

    )

    )

    AND t.EmpCount > 0

    /T

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply