Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2011
    Posts
    82

    Unanswered: Help with Minimum Ward Number

    Hello

    I am hoping for some help or advice on how to get the ward number for where the WARD is 'PREWARD'

    The way the data works is:

    On the WARDtable there will be a row of data for each time a patient stayed on a different ward I.e.

    WARD START_DATE END_DATE PATIENT_NUMBER
    PREWARD 01/04/2013 14:13 01/04/2013 17:13 1
    D5 01/04/2013 17:13 01/04/2013 18:45 1
    PREWARD 01/04/2013 18:45 01/04/2013 19:50 1


    The WARD_DETAILS table will show you the Patient Number and the WARD_NUMBER I.e. in this instance the WARD_NUMBER for PREWARD would be 1 and 3 and D5 would be 2

    What I want to achieve 1 line per patient with the minimum ward number for only Preward

    I have tried various techniques, including Min(WARD_NUMBER) and having clause, but can not achieve the desired results

    Could someone kindly point me in the right direction?


    SELECT
    WE.PATIENT_NUMBER
    WE.WARD,
    WEE.WARD_NUMBER
    FROM WARD WE
    left outer join WARD_DETAILS WEE on
    WEE.PATIENT_NUMBER = WE.PATIENT_NUMBER
    AND WE.WARD_EPISODE_NO = WEE.WARD_EPISODE_NO
    WHERE WE.WARD = 'PREWARD'

    Many Thanks
    Helen

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    It would be helpful if you provided DDL (CREATE TABLE ...) for tables involved.
    It would be helpful if you provided DML (INSERT INTO ...) for test data.
    It would be helpful if you provided expected/desired results & a detailed explanation how & why the test data gets transformed or organized.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Dec 2011
    Posts
    82
    Hello

    I appreciate your comments however I do not have the rights to create table nor insert into in the organisation I work in

    I do however expect a result such as follow

    PATIENT NUMBER WARD WARD_NUMBER
    1 PREWARD 1

    Thanks for the suggestion

    Helen

    1

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    It seems that you didn't understand what Anacedent told you.

    You expect someone to assist, but we can't write queries that use tables we don't know. Therefore, you should provide a test case which includes CREATE TABLE ward, CREATE TABLE ward_detail, INSERT INTO ward, INSERT INTO ward_detail so that we'd have your tables and data to work with. Nobody expects dozens of records - provide several for each table. Then, based on input you gave us, show what is desired result and explain steps that lead to it (which you've already done, but in vain as we still don't have tables to work with).

    Finally, you are member of this forum for two years and you could/should have spotted the difference between formatted and unformatted information. Please, have a look at your initial post, especially the WARD table. It is an unreadable mess. Use [code] tags which will keep formatting.

Posting Permissions

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