better idea than union

[timscronin]

I have a sp created by a developer from a 3rd party db (can't change the structure) that is used for a crystal report. I understand her need for this logic but it is pretty brutal on the db side. It has to do several unions. Anyone have a better approach?

CREATE procedure cmsproc_rpt_cc_enroll_list @location varchar (50)--passes to patient_.df_sitename
as
select 'CARD' as type, p.last_name, p.first_name,
ltrim(rtrim(upper(p.last_name))) + ', ' + ltrim(rtrim(upper(p.first_name))) as pat_name,
p.other_id_number, p.account_id, l.location_name as location,
p2.df_htn_pt_resp as doc, upper(p2.df_htn_status) as status,
case when isdate(p1.df_htn_enroll_dt)= 1 then convert(datetime,p1.df_htn_enroll_dt,101)
else null end as enroll_dt,
case when isdate(fv.df_nextphyscchtn)= 1 then convert(datetime,fv.df_nextphyscchtn,101)
else null end as next_visit,
case when isdate(p2.df_htn_data_dt)= 1 then convert(datetime,p2.df_htn_data_dt,101)
else null end as database_date
from patient p
inner join patient_ p1 on p1.account_id = p.account_id
inner join location l on l.location_key = p1.df_sitename
inner join follow_up_visits_ fv on fv.account_id = p.account_id
inner join patient2_ p2 on p2.account_id = p.account_id
where p.privacy_level = 10
and p1.df_patstatus = 'Active'
and (p1.df_htn_enroll_dt is not null
or p2.df_htn_pt_resp is not null
or p2.df_htn_status is not null
or fv.df_nextphyscchtn is not null
or p2.df_htn_data_dt is not null)
and (p2.df_htn_disen_dt is null
or p2.df_htn_disen_dt is not null
and p1.df_htn_enroll_dt is not null
and p2.df_htn_disen_dt < p1.df_htn_enroll_dt)
and l.location_name = @location

UNION

select 'ENDO' as type, p.last_name, p.first_name,
ltrim(rtrim(upper(p.last_name))) + ', ' + ltrim(rtrim(upper(p.first_name))) as pat_name,
p.other_id_number, p.account_id, l.location_name as location,
p2.df_dm_pt_resp as doc, upper(p2.df_dm_status) as status,
case when isdate(p1.df_dm_enroll_dt)= 1 then convert(datetime,p1.df_dm_enroll_dt,101)
else null end as enroll_dt,
case when isdate(fv.df_diabccphys)= 1 then convert(datetime,fv.df_diabccphys,101)
else null end as next_visit,
case when isdate(p2.df_dm_data_dt)= 1 then convert(datetime,p2.df_dm_data_dt,101)
else null end as database_date
from patient p
inner join patient_ p1 on p1.account_id = p.account_id
inner join location l on l.location_key = p1.df_sitename
inner join follow_up_visits_ fv on fv.account_id = p.account_id
inner join patient2_ p2 on p2.account_id = p.account_id
where p.privacy_level = 10
and p1.df_patstatus = 'Active'
and (p1.df_dm_enroll_dt is not null
or p2.df_dm_pt_resp is not null
or p2.df_dm_status is not null
or fv.df_diabccphys is not null
or p2.df_dm_data_dt is not null)
and (p2.df_dm_disen_dt is null
or p2.df_dm_disen_dt is not null
and p1.df_dm_enroll_dt is not null
and p2.df_dm_disen_dt < p1.df_dm_enroll_dt)
and l.location_name = @location

UNION

select 'PULM' as type, p.last_name, p.first_name,
ltrim(rtrim(upper(p.last_name))) + ', ' + ltrim(rtrim(upper(p.first_name))) as pat_name,
p.other_id_number, p.account_id, l.location_name as location,
p2.df_asth_pt_resp as doc, upper(p2.df_asth_status) as status,
case when isdate(p1.df_asth_enroll_dt)= 1 then convert(datetime,p1.df_asth_enroll_dt,101)
else null end as enroll_dt,
case when isdate(fv.df_asthma_phys)= 1 then convert(datetime,fv.df_asthma_phys,101)
else null end as next_visit,
case when isdate(p2.df_asth_data_dt)= 1 then convert(datetime,p2.df_dis_data_dt,101)
else null end as database_date
from patient p
inner join patient_ p1 on p1.account_id = p.account_id
inner join location l on l.location_key = p1.df_sitename
inner join follow_up_visits_ fv on fv.account_id = p.account_id
inner join patient2_ p2 on p2.account_id = p.account_id
where p.privacy_level = 10
and p1.df_patstatus = 'Active'
and (p1.df_asth_enroll_dt is not null
or p2.df_asth_pt_resp is not null
or p2.df_asth_status is not null
or fv.df_asthma_phys is not null
or p2.df_asth_data_dt is not null)
and (p2.df_asth_disen_dt is null
or p2.df_asth_disen_dt is not null
and p1.df_asth_enroll_dt is not null
and p2.df_asth_disen_dt < p1.df_asth_enroll_dt)
and l.location_name = @location

UNION

select 'INFDIS' as type, p.last_name, p.first_name,
ltrim(rtrim(upper(p.last_name))) + ', ' + ltrim(rtrim(upper(p.first_name))) as pat_name,
p.other_id_number, p.account_id, l.location_name as location,
p2.df_hiv_pt_resp as doc, upper(p2.df_hiv_status) as status,
case when isdate(p1.df_hiv_enroll_dt)= 1 then convert(datetime,p1.df_hiv_enroll_dt,101)
else null end as enroll_dt,
case when isdate(fv.df_hiv_phys)= 1 then convert(datetime,fv.df_hiv_phys,101)
else null end as next_visit,
case when isdate(p2.df_hiv_data_dt)= 1 then convert(datetime,p2.df_hiv_data_dt,101)
else null end as database_date
from patient p
inner join patient_ p1 on p1.account_id = p.account_id
inner join location l on l.location_key = p1.df_sitename
inner join follow_up_visits_ fv on fv.account_id = p.account_id
inner join patient2_ p2 on p2.account_id = p.account_id
where p.privacy_level = 10
and p1.df_patstatus = 'Active'
and (p1.df_hiv_enroll_dt is not null
or p2.df_hiv_pt_resp is not null
or p2.df_hiv_status is not null
or fv.df_hiv_phys is not null
or p2.df_hiv_data_dt is not null)
and (p2.df_hiv_disen_dt is null
or p2.df_hiv_disen_dt is not null
and p1.df_hiv_enroll_dt is not null
and p2.df_hiv_disen_dt < p1.df_hiv_enroll_dt)
and l.location_name = @location

UNION

select 'NEURO' as type, p.last_name, p.first_name,
ltrim(rtrim(upper(p.last_name))) + ', ' + ltrim(rtrim(upper(p.first_name))) as pat_name,
p.other_id_number, p.account_id, l.location_name as location,
p2.df_sz_pt_resp as doc, upper(p2.df_sz_status) as status,
case when isdate(p1.df_seiz_enroll_dt)= 1 then convert(datetime,p1.df_seiz_enroll_dt,101)
else null end as enroll_dt,
case when isdate(fv.df_phys_seizure)= 1 then convert(datetime,fv.df_phys_seizure,101)
else null end as next_visit,
case when isdate(p2.df_sz_data_dt)= 1 then convert(datetime,p2.df_sz_data_dt,101)
else null end as database_date
from patient p
inner join patient_ p1 on p1.account_id = p.account_id
inner join location l on l.location_key = p1.df_sitename
inner join follow_up_visits_ fv on fv.account_id = p.account_id
inner join patient2_ p2 on p2.account_id = p.account_id
where p.privacy_level = 10
and p1.df_patstatus = 'Active'
and (p1.df_seiz_enroll_dt is not null
or p2.df_sz_pt_resp is not null
or p2.df_sz_status is not null
or fv.df_phys_seizure is not null
or p2.df_sz_data_dt is not null)
and (p2.df_sz_disen_dt is null
or p2.df_sz_disen_dt is not null
and p1.df_seiz_enroll_dt is not null
and p2.df_sz_disen_dt < p1.df_seiz_enroll_dt)
and l.location_name = @location

UNION

select 'GI' as type, p.last_name, p.first_name,
ltrim(rtrim(upper(p.last_name))) + ', ' + ltrim(rtrim(upper(p.first_name))) as pat_name,
p.other_id_number, p.account_id, l.location_name as location,
p2.df_gi_pt_resp as doc, upper(p2.df_gi_status) as status,
case when isdate(p2.df_gi_enroll_dt)= 1 then convert(datetime,p2.df_gi_enroll_dt,101)
else null end as enroll_dt,
case when isdate(fv.df_phys_gastroint)= 1 then convert(datetime,fv.df_phys_gastroint,101)
else null end as next_visit,
case when isdate(p2.df_gi_data_dt)= 1 then convert(datetime,p2.df_gi_data_dt,101)
else null end as database_date
from patient p
inner join patient_ p1 on p1.account_id = p.account_id
inner join location l on l.location_key = p1.df_sitename
inner join follow_up_visits_ fv on fv.account_id = p.account_id
inner join patient2_ p2 on p2.account_id = p.account_id
where p.privacy_level = 10
and p1.df_patstatus = 'Active'
and (p2.df_gi_enroll_dt is not null
or p2.df_gi_pt_resp is not null
or p2.df_gi_status is not null
or fv.df_phys_gastroint is not null
or p2.df_gi_data_dt is not null)
and (p2.df_gi_disen_dt is null
or p2.df_gi_disen_dt is not null
and p2.df_gi_enroll_dt is not null
and p2.df_gi_disen_dt < p2.df_gi_enroll_dt)
and l.location_name = @location
UNION
select 'DIS' as type, p.last_name, p.first_name,
ltrim(rtrim(upper(p.last_name))) + ', ' + ltrim(rtrim(upper(p.first_name))) as pat_name,
p.other_id_number, p.account_id, l.location_name as location,
p2.df_dis_doc as doc, upper(p2.df_dis_status) as status,
case when isdate(p2.df_dis_enroll_dt)= 1 then convert(datetime,p2.df_dis_enroll_dt,101)
else null end as enroll_dt,
case when isdate(fv.df_phys_disability)= 1 then convert(datetime,fv.df_phys_disability,101)
else null end as next_visit,
case when isdate(p2.df_dis_data_dt)= 1 then convert(datetime,p2.df_dis_data_dt,101)
else null end as database_date
from patient p
inner join patient_ p1 on p1.account_id = p.account_id
inner join location l on l.location_key = p1.df_sitename
inner join follow_up_visits_ fv on fv.account_id = p.account_id
inner join patient2_ p2 on p2.account_id = p.account_id
where p.privacy_level = 10
and p1.df_patstatus = 'Active'
and (p2.df_dis_enroll_dt is not null
or p2.df_dis_doc is not null
or p2.df_dis_status is not null
or fv.df_phys_disability is not null
or p2.df_dis_data_dt is not null)
and (p2.df_dis_disen_dt is null
or p2.df_dis_disen_dt is not null
and p2.df_dis_enroll_dt is not null
and p2.df_dis_disen_dt < p2.df_dis_enroll_dt)
and l.location_name = @location
order by 1,2,3 -- type, p.last_name, p.first_name

 

[SQLDenis]

for starters change UNION to UNION ALL

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

 

[timscronin]

Thanks, I was looking more along the lines of a subselect

 

[bborissov]

If you can't change the structure, you are stuck with that

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[SQLDenis]

>>Thanks, I was looking more along the lines of a subselect

difficult since your WHERE clause is different and withou looking at the data it is kind of hard to do that

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

 

[ESquared]

You could change the structure by putting everything in a new table, and building an updatable view with the name of the old table. Otherwise you're pretty much stuck doing union all. My only suggestion is to separate the parts: do the type-specific stuff first, then the transformations on that, instead of performing each transformation on each type.

select
   type, last_name, first_name,
   pat_name = ltrim(rtrim(upper(last_name))) + ', ' + ltrim(rtrim(upper(first_name))),
   other_id_number, account_id, location,
   doc, upper(status) as status,
   enroll_dt = case when isdate(enroll_dt) = 1 then convert(datetime, enroll_dt, 101),
   next_visit = case when isdate(next_visit) = 1 then convert(datetime, next_visit, 101),
   database_date = case when isdate(database_date) = 1 then convert(datetime, database_date, 101)
from
   (
      select
         'CARD' as type, p.last_name, p.first_name,
         p.other_id_number, p.account_id, l.location_name,
         doc = p2.df_htn_pt_resp, status = p2.df_htn_status, enroll_dt = p1.df_htn_enroll_dt,
         next_visit = fv.df_nextphyscchtn, database_date = p2.df_htn_data_dt, disen_dt = p2.df_htn_disen_dt
      from
         patient p
         inner join patient_ p1 on p1.account_id = p.account_id
         inner join location l on l.location_key = p1.df_sitename
         inner join follow_up_visits_ fv on fv.account_id = p.account_id
         inner join patient2_ p2 on p2.account_id = p.account_id
      where
         p.privacy_level = 10
         and p1.df_patstatus = 'Active'
         and l.location_name = @location
      UNION ALL select
         'ENDO', p.last_name, p.first_name,
         p.other_id_number, p.account_id, l.location_name,
         p2.df_dm_pt_resp, p2.df_dm_status, p1.df_dm_enroll_dt,
         fv.df_diabccphys, p2.df_dm_data_dt, p2.df_dm_disen_dt
      from
         patient p
         inner join patient_ p1 on p1.account_id = p.account_id
         inner join location l on l.location_key = p1.df_sitename
         inner join follow_up_visits_ fv on fv.account_id = p.account_id
         inner join patient2_ p2 on p2.account_id = p.account_id
      where
         p.privacy_level = 10
         and p1.df_patstatus = 'Active'
         and l.location_name = @location
   ) X
where
   (enroll_dt is not null or doc is not null or status is not null or database_date is not null)
   and (
      disen_dt is null
      or (disen_dt is not null and enroll_dt is not null and disen_dt < enroll_dt)
   )

While this may be easier to maintain, it may not perform as well as your query because of the derived table. Also, your comparison disen_dt < enroll_dt could give spurious results on bad dates because the comparison is begin done (in your and my query) on the unscrubbed date values.

A last option is some dynamic SQL that can swap columns in based on the type you want, but that's probably not the best option. It may be a good option to use dynamic SQL to write this query for you so when something changes you can just run the code to recreate the query. But that's pretty complicated stuff that is probably not suitable to you right now.