Post reply

Picking up the first and last record

  • July 5, 2007 at 7:28 pm

    #174307

    Hi guys

    I have got a table as shown below.

    FunderStart_dateEnd_dateClient_NHIFMISCOde
    Canterbury DHB21/06/200427/06/2004BHY09996630
    Canterbury DHB28/06/20044/07/2004BHY09996630
    Canterbury DHB5/07/200411/07/2004BHY09996630
    Canterbury DHB12/07/200418/07/2004BHY09996630
    Canterbury DHB1/07/20052/07/2005BHY09996640
    Canterbury DHB3/07/200516/07/2005ABCD6640
    Canterbury DHB17/07/200530/07/2005ABCD6640
    Canterbury DHB31/07/200513/08/2005ABCD6640
    Canterbury DHB14/08/200527/08/2005ABCD6640
    Canterbury DHB28/08/200510/09/2005ABCD6640
    Canterbury DHB11/09/200524/09/2005ABCD6640

     

    I need to extract the first Start_date and last start_date for each client_NHI

    For ex I want the output like this..

    Client_nhi    Start_date      End_date

    BHY0999        27/06/2004          2/07/2005

    ABCD             16/07/2005          24/09/2005

     

    Could you help me in that please

     

     

    Thanks

  • July 5, 2007 at 8:06 pm

    #717546

    Not sure why you have the first EndDate becoming a start date but whatever... the following will do what you ask for...

     SELECT Client_NHI, MIN(End_Date) AS StartDate, MAX(End_Date) AS EndDate
   FROM yourtable
  GROUP BY Client_NHI

    Not trying to be a smart guy about this but this type of question is typically asked by someone very new to SQL... I recommend a bit of time be spent at the following...

    http://www.w3schools.com

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • July 8, 2007 at 3:43 am

    #717868

    Hi Jeff

    Thanks for your help but this problem is not as straight forward as it sounds.The dates are not in any particular order(asc or desc)Which means I have to pick the last record as the start date and first record keeping in mind the dates are in the random order.

     

    Looking forward 2 your reply

     

     

    Cheers

  • July 8, 2007 at 8:46 am

    #717874

    How do you define "First and Last record"?  If not by date (Dates are not in any particular order according to you), then by what?  I can help if you have another date column (CreatedDate or some such) or an IDENTITY column (not always a guarantee but better than nothing), but there is no guarantee that rows stored in a table will keep the order of insertion.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • July 8, 2007 at 2:58 pm

    #717886

    Hi Jeff

    The first entry of start date for an NHI becomes the end_date and last entry of start date of that NHI becomes the start date

     

    Hope this helps

  • July 8, 2007 at 3:55 pm

    #717888

    And you still claim Jeff's query won't work?

     SELECT Client_NHI, MIN(Start_Date) AS StartDate, MAX(End_Date) AS EndDate

       FROM yourtable

      GROUP BY Client_NHI

     SELECT Client_NHI, MIN(Start_Date) AS StartDate, MAX(Start_Date) AS EndDate

       FROM yourtable

      GROUP BY Client_NHI

     SELECT Client_NHI, MIN(End_Date) AS StartDate, MAX(End_Date) AS EndDate

       FROM yourtable

      GROUP BY Client_NHI

     SELECT Client_NHI, MAX(Start_Date) AS StartDate, MIN(End_Date) AS EndDate

       FROM yourtable

      GROUP BY Client_NHI

     

    N 56°04'39.16"
    E 12°55'05.25"

  • July 8, 2007 at 9:25 pm

    #717902

    Hi Guys

    I am sorry if I could not explain my problem correctly.

    The sample which I posted for my table has got dates in the ascending order which is just by chance, but the fact is the dates are not always in ascending or descending order, I have to pick out the first entry of the NHI and last entry of the same NHI.

     

    Am I clear this time??

  • July 9, 2007 at 4:51 am

    #717972

    Yes, we understand you now.

    But you still haven't answered the question of how you define the order of records. In a relational database, the order of records can not be guaranteed unless you select them with an order by.

    So, the question still remains "How do you define the sequence order for your records?".

     

    N 56°04'39.16"
    E 12°55'05.25"

  • July 9, 2007 at 2:30 pm

    #718101

    Basically, the order of records is through a different field called the patient_id which I have not included in my sample data...

     

     

    Thanks

     

  • July 9, 2007 at 5:40 pm

    #718139

    Heh... now THAT's important... so, lemme ask... is Patient_ID an autonumbering column (or, at least, an incrementing sequence number)?

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • July 9, 2007 at 8:03 pm

    #718152

    Hi

    Yes Patient_ID is an incrementing sequence number

  • July 10, 2007 at 12:12 am

    #718183

    SELECT Funder, Start_date, End_date, Client_NHI, FMISCode FROM (SELECT Funder, Start_date, End_date, Client_NHI, FMISCode, ROW_NUMBER() OVER (PARTITION BY Client_NHI ORDER BY Patient_ID) AS RecID, ROW_NUMBER() OVER (PARTITION BY Client_NHI ORDER BY Patient_ID DESC) AS RowID, Patient_ID FROM <YourTableNameHere>) AS d WHERE 1 IN (RecID, RowID) ORDER BY Patient_ID

     

    N 56°04'39.16"
    E 12°55'05.25"

  • July 10, 2007 at 2:38 pm

    #718424

    Hi Peter

    Thanks for your query.I ran the above query but I am gettin the following erro message:

    "The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified."

     

     

    ThaN

  • July 11, 2007 at 12:49 am

    #718510

    Yes, and what is your problem now? The error message has explained to you what is wrong.

    You haven't given us more information to work with and until you do, you're stuck or have to fix it yourself!

    For example, where is this query needed?

     

    N 56°04'39.16"
    E 12°55'05.25"

  • July 16, 2007 at 12:18 pm

    #719764

    I just love people who take the effort to post feedback to their problems and the suggestions they get!

     

    N 56°04'39.16"
    E 12°55'05.25"

Viewing 15 posts - 1 through 14 (of 14 total)

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