Post reply

SQL Code Question (Temp Table?)

  • June 14, 2006 at 9:13 am

    #91966

    Could someone help me with this one?  This SQL code is pulling what I need that includes records with or without null values for any of the patev* fields.   I only want to keep any pp_pat_id_r records that have data in any of the patev* fields, but need to exclude any records for that same pp_pat_id_r with null values on that same day .   And yet I need to keep any pp_pat_id_r with null values on that same day that are <> the pp_pat_id_r records that are not null values.   

     

    Do I need to create a temp table first and then remove my unwanted records from that point?  Or any suggestions for a Stored Procedure on this since I am a newbie to that arena? 

     

    select

    pp_pat_id_r,

    pp_req_no,

    pp_op_seq_no,

    patev_asn_req_no,

    patev_asn_seq_no,

    patev_event_time,

    patev_event_code,

    cproc_id,

    cproc_code,

    cproc_desc,

    wp_imloc_id,

    patev_loc_id,

    prsn_lname,

    imloc_desc

     

    from

    risdb_Rch08_stag..performed_procedure pp1 left join risdb_rch04_stag..PATIENT_EVENT on pp_asn_req_no = PATEV_ASN_REQ_NO and pp_asn_seq_no = PATEV_ASN_SEQ_NO

     

    ,risdb_Rch04_stag..work_procedure left join risdb_Rch04_stag..wp_person

        on wpprsn_req_no = wp_req_no and wpprsn_op_seq_no = wp_op_seq_no

        and wp_seq_no_s = wpprsn_wp_seq_no

     

        ,rdwdb_rch00_prod..person

        , risdb_rch01_stag..campus_procedure cp

        , risdb_rch01_stag..imaging_location

        , risdb_rch01_stag..facility

     

    where

    pp_service_time_r >= '04/03/2006' and pp_service_time_r < '04/05/2006'

    and pp_status_v = 'CP'

    and pp_rep_id > 0

    and (patev_event_code in (‘PB’, ‘PL’) or patev_event_code is null)

    and wp_req_no = pp.pp_req_no

    and wp_op_seq_no = pp.pp_op_seq_no

    and wp_status = 'NT'

    and wpprsn_prsn_id = prsn_id

    and wpprsn_prsn_id != 13525581

    and wpprsn_role = 'Tech' and wpprsn_prsn_primary = 1

    and cproc_id = pp_cproc_id_r

    and cproc_code not in ('07398',  'ERCP', '07258B', '07258C', '07258D',

     'DG031', 'DG034', 'DG035', 'DG090', 'DG044')

    and wp_imloc_id = imloc_id

    and facility_id = pp_facility_id_r

    and imloc_id in (99, 19, 17, 23, 9, 11, 94, 101, 103, 15, 10)

    and cproc_modality_code <> 'OS'

  • June 14, 2006 at 9:17 am

    #643679

    Please simplify the query and post some sample data and desired output

  • June 14, 2006 at 9:26 am

    #643683

    Quickly looking a the query showed some poor practices:

    You should use the old style join syntax (table1, table2 where table1.id = table2.id2).  You should never mix the two styles.

    Use table aliases and qualify all columns (select t1.name, t2.name from table1 t1 inner join table2 on t1.id = t2.id2)

    Move as much as possible from the where clause (select t1.name, t2.name from table1 t1 inner join table2 on t1.id = t2.id2 and t2.type = 'NT' and t2.code in (1, 2, 3))

  • June 14, 2006 at 9:53 am

    #643697

    Here are a few examples of my results.  I will not need the first two records here for pp_pat_id_r #12345, but I will need to keep #67890.  So, a recordset with values takes precedence for each unique pp_pat_id_r over the null for that same pp_pat_id_r and I only want to keep the pp_pat_id_r that only have null values.

    This rule would apply for each day.  So, if I ran a month's range of data, I need to be concerned about each day for this step.

    pp_pat_id_rpp_req_nopp_op_seq_nopatev_asn_req_nopatev_asn_seq_nopatev_event_timepatev_event_code
    1234567891013                [NULL]               [NULL][NULL][NULL]
    1234567891015                [NULL]               [NULL][NULL][NULL]
    1234567891014678910114/3/06 2:47 PMPB
    1234567891014678910114/3/06 3:09 PMPL
    6789056879982                 [NULL]               [NULL][NULL][NULL]

     

  • June 15, 2006 at 6:49 am

    #643936

    Perhaps a bit off topic. I am very new to database design, etc.; but have been around programming in gerneral for awhile.  One thing I have noticed as being common: table names, column headers, etc. are almost never written in English.  The output and even the query posted here I think would be horrific to read even for someone familiar with the metadata.

     

    Why is that?  That is, is this common and acceptable practice; and why?

    HTH,

    doco
    xlseer.com

  • June 15, 2006 at 8:02 am

    #643977

    It is all too common due to a person's misconception that their abbreviation scheme "makes sense" to everyone.

    When developing data models, I follow standards that I believe are best practices such as using complete works for names, using consistent case and avoiding Hungarian notation.  CREATE TABLE Shipment(ShipmentID int, ShipmentDate datetime, CarrierID int) is much clearer than create table tbl_shp(id int, shp_dt datetime, car_id int)

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

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