Please know that I am very new to SQL coding, so please forgive any incorrect terms or info that I give you to help me.  I really appreciate any suggestions.   I have spent quite a bit of time on the web looking for a solution and have yet to find any that have worked.   Thanks!

The select command below will output one patients information in 1 row:

Patient id

Last name

First name

Address 1

OP Coverage Plan 1

OP Policy # 1

OP Coverage Plan 2

OP Policy # 2

OP Coverage Plan 3

OP Policy # 3

IP Coverage Plan 1

IP Policy # 1

IP Coverage Plan 2

IP Policy # 2

IP Coverage Plan 3

IP Policy # 3

This works great if there is at least one OP coverage.   There are 3 tables in which to get information which are the patient table, the coverage table, and the coverage history table.   The coverage table links to the patient table via pat_id and it tells me the patient's coverage plan and in which priority to bill.  The coverage history table links to the patient and coverage table via patient id and coverage plan and it gives me the effective date.  

select src.pat_id, lname, fname, addr1,


      max(case when rn = 1 then src.coverage_plan_ end) as OP_Coverage1,

      max(case when rn = 1 then src.policy_id end) as OP_Policy1,


      max(case when rn = 2 then src.coverage_plan end) as OP_Coverage2,

      max(case when rn = 2 then src.policy_id end) as OP_Policy2,

            

      max(case when rn = 3 then src.coverage_plan end) as OP_Coverage3,

      max(case when rn = 3 then src.policy_id end) as OP_Policy3,

            

      max(case when rn1 = 1 then src1.coverage_plan_id end) as IP_Coverage1,

      max(case when rn1 = 1 then src1.policy_id end) as IP_Policy1,


      max(case when rn1 = 2 then src1.coverage_plan end) as IP_Coverage2,

      max(case when rn1 = 2 then src1.policy_id end) as IP_Policy2,

            

      max(case when rn1 = 3 then src1.coverage_plan end) as IP_Coverage3,

      max(case when rn1 = 3 then src1.policy_id end) as IP_Policy3


from ( select p.*, ch.coverage_plan, ch.hosp_status, c.policy_id,

                  row_number() over (partition by p.pat_id order by ch.priority) as rn

       from patient p 

            left join coverage_history ch on ch.pat_id = p.pat_id and 

                              ch.hosp_status = 'OP'


            left join coverage c on c.pat_id = ch.pat_id and c.coverage_plan =                                          ch.coverage_plan and c.hosp_status = 'OP' and ch.effective_to is                          NULL

        where ch.hosp_status = 'OP'

       ) as src


       left join (select p1.pat_id, ch1.coverage_plan, ch1.hosp_status,                                      ch1.policy_id,

 row_number() over (partition by p1.pat_id order by ch1.priority) as rn1

                  from patient p1 

left join coverage_history ch1 on ch1.pat_id = p1.pat_id and ch1.hosp_status = 'IP'

                        left join coverage c1 on c1.pat_id = ch1.pat_id and 

c1.coverage_plan = ch1.coverage_plan and c1.hosp_status = 'IP' and ch1.effective_to is NULL


                where ch1.effective_to is NULL and ch1.hosp_status_code = 'IP'


                ) as src1 on src.patient_id = src1.patient_id


group by src.pat_id, lname, fname, addr1

It is possible that the patient may only have IP coverage and this statement will not pull the patient.   I want the OP columns to contain nothing if this is the case and vice versa.  Below is some code indicating the type of data:

create table patient 

( pat_id char(10),

lname varchar(20),

fname varchar(20),

addr1 varchar(30) )

insert into patient values ('00092000', 'Miller','Sara','1203 western ' )

insert into patient values ('00092020', 'Smitty','Sonny','1900 nicenesse' )

insert into patient values ('00092030', 'Wallace','Gerald','34444 turkey')

insert into patient values ('00092040', 'Marker','Cindy','12122 lantern ' )

insert into patient values ('00092050', 'Japlin','Betty','403 orville' )

insert into patient values ('00092065', 'Japlin','Betty','403 laurel' )


create table coverage

( pat_id char(10), 

coverage_plan char(10),

hosp_status char(2),

policy_id char(20),

priority char(1) )

insert into coverage values ( '00092000', 'TNC', 'OP', 'ZEC122', '1' )

insert into coverage values ( '00092000', 'TNC', 'IP', 'ZEC122', '1' )

insert into coverage values ( '00092000', 'BCBS','OP', 'X122', '2' )

insert into coverage values ( '00092000', 'BCBS','IP', 'x122', '2' )

insert into coverage values ( '00092030', 'BCBS', 'OP', 'ZEM191', '1' )

insert into coverage values ( '00092030', 'AETNA', 'OP', 'JD393', '2' )

insert into coverage values ( '00092030', 'DDH', 'OP', 'DD902345678', '3' )

insert into coverage values ( '00092040', 'TNC', 'OP', 'ZEC0900', '1' )

insert into coverage values ( '00092040', 'BCBSKA', 'OP', '409883939', '2' )

insert into coverage values ( '00092050', 'TNC', 'OP', 'ZEC887347', '1' )

insert into coverage values ( '00092050', 'TNC', 'IP', 'ZEC887347', '1' )

insert into coverage values ( '00092050', 'BCBSAK', 'OP', 'ZEC887347', '2' )

insert into coverage values ( '00092050', 'TNC', 'IP', 'ZEC887347', '2' )

insert into coverage values ( '00092050', 'TNC', 'OP', 'ZEC887347', '3' )

insert into coverage values ( '00092050', 'TNC', 'IP', 'ZEC887347', '3' )

create table coverage_history

( pat_id, 

  hosp_status char(2),

  coverage_plan char(10),

  priority char(1),

  effective_date char(8) )

insert into coverage_history values ( '00092000', 'IP', 'TNC', '1', '20150101' )

insert into coverage_history values ( '00092000', 'OP', 'TNC', '1', '20150101' )

insert into coverage_history values ( '00092000', 'IP', 'BCBS', '2', '20150101' )

insert into coverage_history values ( '00092000', 'OP', 'BCBS', '2', '20150101' )

insert into coverage_history values ( '00092020', 'IP', 'AETNA', '1', '20150101' )

insert into coverage_history values ( '00092020', 'OP', 'AETNA', '1', '20150101' )

insert into coverage_history values ( '00092030', 'IP', 'DDH', '1', '20150101' )

insert into coverage_history values ( '00092040', 'IP', 'TNC', '1', '20150101' )

insert into coverage_history values ( '00092040', 'OP', 'TNC', '1', '20150101' )

insert into coverage_history values ( '00092040', 'IP', 'AETNA', '2', '20150101' )

insert into coverage_history values ( '00092040', 'OP', 'AETNA', '2', '20150101' )

insert into coverage_history values ( '00092040', 'IP', 'BCBSAK', '3', '20150101' )

insert into coverage_history values ( '00092040', 'OP', 'BCBSAK', '3', '20150101' )

insert into coverage_history values ( '00092050', 'OP', 'TNC', '1', '20150101' )

 

You don't need 2 Src files. You only need to LEFT JOIN with your tables once, but use 2 different partitions within ROW_NUMBER().

Do you see what I

This is what you need:

select src.pat_id, lname, fname, addr1,


      max(case when rn = 1 and hosp_status = 'OP' then src.coverage_plan_ end) as OP_Coverage1,

      max(case when rn = 1 and hosp_status = 'OP' then src.policy_id end) as OP_Policy1,


      max(case when rn = 2 and hosp_status = 'OP' then src.coverage_plan end) as OP_Coverage2,

      max(case when rn = 2 and hosp_status = 'OP' then src.policy_id end) as OP_Policy2,

            

      max(case when rn = 3 and hosp_status = 'OP' then src.coverage_plan end) as OP_Coverage3,

      max(case when rn = 3 and hosp_status = 'OP' then src.policy_id end) as OP_Policy3,

            

      max(case when rn = 1 and hosp_status = 'IP' then src.coverage_plan_id end) as IP_Coverage1,

      max(case when rn = 1 and hosp_status = 'IP' then src.policy_id end) as IP_Policy1,


      max(case when rn = 2 and hosp_status = 'IP' then src.coverage_plan end) as IP_Coverage2,

      max(case when rn = 2 and hosp_status = 'IP' then src.policy_id end) as IP_Policy2,

            

      max(case when rn = 3 and hosp_status = 'IP' then src.coverage_plan end) as IP_Coverage3,

      max(case when rn = 3 and hosp_status = 'IP' then src.policy_id end) as IP_Policy3


from ( select p.*, ch.coverage_plan, ch.hosp_status, c.policy_id,

                  row_number() over
 (partition by p.pat_id,c.hosp_status 
order by COALESCE(ch.priority,999999999)) as rn

       from patient p 

            left join coverage_history ch on ch.pat_id = p.pat_id and 

                              ch.hosp_status IN ('OP','IP')


            left join coverage c on c.pat_id = ch.pat_id and c.coverage_plan =                                          ch.coverage_plan and ch.effective_to is                          NULL

       ) as src

Thank you!   I am so close.   I may have missed something.   If there is something with priority 1 and 2 with OP coverage and there is no 3, it puts the priority 2 info in the info for column 3.   I hope you followed that???   I took just part of the code which does the following:

I don't understand your question.

Also, why did you put ch.effective_date IS NULL condition back in the where clause if you already have it in the JOIN c

Change ORDER BY in ROW_NUMBER() expression to be

ORDER BY coalesce(ch.Priority, 99999999) -- big enough number that can not be in the real table

It's not putting the priority 2 in the priority 3 for no-priority 3, it has two priority 2s with different group_no and so both are taking precedence over anything that follows