Post reply

sql script help

  • December 10, 2009 at 11:43 am

    #98351

    Hi,

    I have a sql query to get a list of people in a file. In the query I need to get only the the last/greatest TimeofMaintenance record for each room/meaning the record with the greatest TimeofMaintenance for each room. I have everything but I cannot figure out what to do to get that.

    I would appreciate any help you can give me.

    This is the script

    SELECT dbo.fn_get_person_name(TPM300_PAT_VISIT.psn_int_id) AS 'PATIENT NAME', TPM300_PAT_VISIT.vst_ext_id AS 'PATIENT NUMBER',

    dbo.fn_get_loc_ext_id(TPM300_PAT_VISIT.loc_lvl_3_id)AS 'FLOOR ',

    dbo.fn_get_loc_ext_id(TPM300_PAT_VISIT.loc_lvl_4_id) AS 'ROOM ',

    dbo.fn_get_loc_ext_id(TPM300_PAT_VISIT.loc_lvl_5_id) AS 'BED ', TSM180_MST_COD_DTL_1.cod_dtl_ds AS 'SEX',

    dbo.fn_get_attend_dr_name(TPM300_PAT_VISIT.vst_int_id) AS 'ATTENDING DOCTOR NAME',

    CONVERT(varchar(10), TSM040_PERSON_HDR.bth_ts,

    101) AS 'DOB', TPM950_ASSIGN_BED_SPACE.asgn_bed_spa_ts AS TimeOfMaintenance,

    TSM950_LOCATION_REF.loc_ds AS ChangedTo

    FROM TPM300_PAT_VISIT INNER JOIN

    TSM180_MST_COD_DTL ON TPM300_PAT_VISIT.vst_sta_cd = TSM180_MST_COD_DTL.cod_dtl_int_id INNER JOIN

    TSM040_PERSON_HDR ON TPM300_PAT_VISIT.psn_int_id = TSM040_PERSON_HDR.psn_int_id INNER JOIN

    TSM180_MST_COD_DTL AS TSM180_MST_COD_DTL_1 ON TSM040_PERSON_HDR.sex_cd = TSM180_MST_COD_DTL_1.cod_dtl_int_id INNER JOIN

    TPM950_ASSIGN_BED_SPACE ON TPM300_PAT_VISIT.vst_int_id = TPM950_ASSIGN_BED_SPACE.vst_int_id INNER JOIN

    TSM950_LOCATION_REF ON TPM950_ASSIGN_BED_SPACE.loc_int_id = TSM950_LOCATION_REF.loc_int_id

    WHERE (TSM180_MST_COD_DTL.cod_dtl_ext_id = 'ADMIT') AND (NOT (TPM300_PAT_VISIT.loc_lvl_3_id = 321)) OR

    (TSM180_MST_COD_DTL.cod_dtl_ext_id = 'PENDING_DISCHARGE')

    ORDER BY 'FLOOR ', 'ROOM ', 'BED ', TimeOfMaintenance

  • December 10, 2009 at 11:48 am

    #1090765

    Please edit your post and use the [ code ] tags.

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.     [/font]    [font="Verdana"] "Performance is our middle name."[/font]

Viewing 2 posts - 1 through 2 (of 2 total)

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