Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2003
    Posts
    20

    Unanswered: SQL - If Then Else Statement

    My SQL Query is as follows:

    Select pt_no, enc_no, adm_date, disch_date
    from patient_encounter

    Output:
    pt_no enc no adm_date disch_date
    5014 905690 01/02/04 01/14/04
    5014 104780 01/16/04 01/16/04
    5024 905700 12/10/03 12/12/03
    5024 104790 01/08/04 01/08/04
    5024 106782 01/15/04 01/15/04

    Desired Output:
    pt_no enc no adm_date disch_date adj disch date
    5014 905690 01/02/04 01/14/04 01/14/04
    5014 104780 01/16/04 01/16/04 01/31/04

    columns continued from above:
    Earliest_adm_date Latest_Adj_Dsch_Date
    01/02/04 01/16/04
    01/02/04 01/31/04


    What modifications to my SQL is needed to obtain the "Adj Dsch Date" that should be the last day of the month for all "10" encounters (encounters that begin with "10") and provide the earliest admit date and the latest adjusted discharge date?

    Thanks in advance!

  2. #2
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    Oracle,

    select t.pt_no, t.enc_no, t.adm_date, t.disch_date,
    (case when enc_no LIKE '10%' Then last_day(adm_date) ELSE adm_date END) As ADJ_DIS, v.min_am_date, v.max_disch_date
    from table t
    INNER JOIN
    (select pt_no, min(t.adm_date) As min_am_date,
    max(t.disch_date) As max_disch_date
    from table t
    group by pt_no) v ON
    t.pt_no = v.pt_no;
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  3. #3
    Join Date
    Oct 2003
    Posts
    20

    Question

    r123456,

    Thank you.

    I will try the sql script.

    My initial response is that the sql script that you provided does not incorporate a logic to output the "adjusted discharge date" as the last day of the month for the "10" encounters. At the same time, the query should give me the earliest admit date for all encounters, those encounters beginning with "9", "10", etc.

    For example, the following additional information highlights three scenarios:

    Disch Date Adj Disch Date Should Be
    1/11/03 1/31/03
    3/22/03 3/31/03
    4/18/03 4/30/03

  4. #4
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    select v2.pt_no, v2.enc_no, v2.adm_date, v2.disch_date,
    (CASE WHEN enc_no LIKE '10%' THEN last_day(disch_date) ELSE disch_date END) As ADJ_DISCH,
    v2.min_am_date As Earliest_Adj_Dsch_Date,
    (CASE WHEN v2.enc_no LIKE '10%' THEN last_day(disch_date) ELSE max_disch_date End) As Latest_Adj_Dsch_Date
    from
    (select t.pt_no, t.enc_no, t.adm_date, t.disch_date,
    v.min_am_date, v.max_disch_date
    from table t
    INNER JOIN
    (select pt_no, min(t.adm_date) As min_am_date,
    max(t.disch_date) As max_disch_date
    from table t
    group by pt_no) v ON
    t.pt_no = v.pt_no) v2;
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •