Post reply

eliminate duplicates

  • April 28, 2004 at 9:51 am

    #108290

    Please help

    in SQl 2000

    How do you return the first record for each ID in a select query.

    I Have a field Called STATE with 1 value = 'In Progress'

    I Have a Field ProgressDate eg. 04/04/2004

    The problem is an ID can go in and out of the 'In Progress' state many times

    I want to calculate the first time it enters the state so eliminate duplicates and then count the ID's that went into the 'In Progress' state for a month.

  • April 28, 2004 at 10:32 am

    #504539

    select * from #temp order by 1

    1 4/5/2004 inprogress

    1 4/6/2004 inprogress

    1 4/4/2004 inprogress

    2 4/4/2004 inprogress

    3 4/5/2004 inprogress

    select

    count(distinct intid) countdistinctid,

    datepart(month, progdate) currmonth

    from

    #temp

    where

    state = 'inprogress'

    group by datepart(month, progdate)

    3 4

    [font="Courier New"]ZenDada[/font]

  • April 28, 2004 at 11:22 am

    #504550

    Thanks for that

    but

    i dont think i explained myself properly

    we have a table as follows:

    ID STATE DATE

    1 InProgress 02/04/2004

    1 Resolved 02/04/2004

    1 Awaiting Feedback 06/04/2004

    1 InProgress 03/04/2004

    2 InProgress 02/04/2004

    2 Resolved 02/04/2004

    2 Awaiting Feedback 06/04/2004

    2 InProgress 03/04/2004

    i want the select query to return the ID and STATE and DATE for when STATE 'InProgress' for the first time an ID enters that state value and thus getting rid of the other records for that ID

    hope this makes sense

  • April 28, 2004 at 12:33 pm

    #504562

    SELECT     *

    FROM         dbo.TableA

    iID sState dDate

    1 InProgress 02/04/2004

    1 Resolved 02/04/2004

    1 Awaiting Feedback 06/04/2004

    1 InProgress 03/04/2004

    2 InProgress 02/04/2004

    2 Resolved 02/04/2004

    2 Awaiting Feedback 06/04/2004

    2 InProgress 03/04/2004

      

    select

    iID, sState, min(dDate) MinDate

    from tablea b

    group by

    iID, sState

    having

    sState = 'InProgress'

    iID sState MinDate

    1 InProgress 02/04/2004

    2 InProgress 02/04/2004

      

    If that's not what you want, show me the result set you expect based on the table above.

     

    [font="Courier New"]ZenDada[/font]

  • April 29, 2004 at 2:54 am

    #504623

    select

    * from temptable t where (t.ID,t.PROGDATE)

    in  (select id,min(progdate) from temptable group by(id))

    I hope this will solve urs problem.

    If this one won't help u then pls give me

    the result set which one you expect.

  • April 29, 2004 at 4:14 am

    #504631

    I've just tried

    select * from temptable t where (t.ID,t.PROGDATE)

    in (select id,min(progdate) from temptable group by(id))

    but get SQL error msg

    "Incorrect syntax near ',' line 1"

  • April 29, 2004 at 5:59 am

    #504646

    thanx a million you guys it works

Viewing 7 posts - 1 through 6 (of 6 total)

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