Is there something simple I'm missing. I can't get this union to work with union or union all?
select (a.status + ' / ' + a.statusreason) as Status, a.applid as Applid, a.caseno as Caseno, a.applname as [Member Name],
a.rcvdt as [Date Received],a.fudt as [Callback Date], a.state as State, a.agent as County, a.city as City, a.zip as [Zip Code], b.providerid as [Provider Id],
b.appt_DateTime as [Appointment Date], c.fudate as [Follow Up], d.requirement as Requirement, d.adddttm as [Note Date],
d.notes as Notes
from table1 a with(nolock)
left outer join table2 b with(nolock)
on a.serial = b.appserial
and b.coid = 'WLP0'
and b.serial in
(select max(serial) from table2 with(nolock)
where coid = 'WLP0'
and appt_status not in ('assessment complete qc','corrections submitted', 'paperwork submitted')
group by appserial)
left outer join table3 c with(nolock)
on a.serial = c.appserial
and c.company = 'WLP0'
left outer join table4 d with(nolock)
on a.serial = d.appserial
and d.serial in
(select max(serial) from table4 with(nolock)
where notes like '% %'
group by appserial)
order by status,applid
union
select (a.status + ' / ' + a.statusreason) as Status, a.applid as Applid, a.caseno as Caseno, a.applname as [Member Name],
a.rcvdt as [Date Received],a.fudt as [Callback Date], a.state as State, a.agent as County, a.city as City, a.zip as [Zip Code], b.providerid as [Provider Id],
b.appt_DateTime as [Appointment Date], c.fudate as [Follow Up], d.requirement as Requirement, d.adddttm as [Note Date],
d.notes as Notes
from table5 a with(nolock)
left outer join table6 b with(nolock)
on a.serial = b.appserial
and b.coid = 'LEP0'
and b.serial in
(select max(serial) from table6 with(nolock)
where coid = 'LEP0'
and appt_status not in ('assessment complete qc','corrections submitted', 'paperwork submitted')
group by appserial)
left outer join table7 c with(nolock)
on a.serial = c.appserial
and c.company = 'LEP0'
left outer join table8 d with(nolock)
on a.serial = d.appserial
and d.serial in
(select max(serial) from table8 with(nolock)
where notes like '% %'
group by appserial)
order by status,applid
select (a.status + ' / ' + a.statusreason) as Status, a.applid as Applid, a.caseno as Caseno, a.applname as [Member Name],
a.rcvdt as [Date Received],a.fudt as [Callback Date], a.state as State, a.agent as County, a.city as City, a.zip as [Zip Code], b.providerid as [Provider Id],
b.appt_DateTime as [Appointment Date], c.fudate as [Follow Up], d.requirement as Requirement, d.adddttm as [Note Date],
d.notes as Notes
from table1 a with(nolock)
left outer join table2 b with(nolock)
on a.serial = b.appserial
and b.coid = 'WLP0'
and b.serial in
(select max(serial) from table2 with(nolock)
where coid = 'WLP0'
and appt_status not in ('assessment complete qc','corrections submitted', 'paperwork submitted')
group by appserial)
left outer join table3 c with(nolock)
on a.serial = c.appserial
and c.company = 'WLP0'
left outer join table4 d with(nolock)
on a.serial = d.appserial
and d.serial in
(select max(serial) from table4 with(nolock)
where notes like '% %'
group by appserial)
order by status,applid
union
select (a.status + ' / ' + a.statusreason) as Status, a.applid as Applid, a.caseno as Caseno, a.applname as [Member Name],
a.rcvdt as [Date Received],a.fudt as [Callback Date], a.state as State, a.agent as County, a.city as City, a.zip as [Zip Code], b.providerid as [Provider Id],
b.appt_DateTime as [Appointment Date], c.fudate as [Follow Up], d.requirement as Requirement, d.adddttm as [Note Date],
d.notes as Notes
from table5 a with(nolock)
left outer join table6 b with(nolock)
on a.serial = b.appserial
and b.coid = 'LEP0'
and b.serial in
(select max(serial) from table6 with(nolock)
where coid = 'LEP0'
and appt_status not in ('assessment complete qc','corrections submitted', 'paperwork submitted')
group by appserial)
left outer join table7 c with(nolock)
on a.serial = c.appserial
and c.company = 'LEP0'
left outer join table8 d with(nolock)
on a.serial = d.appserial
and d.serial in
(select max(serial) from table8 with(nolock)
where notes like '% %'
group by appserial)
order by status,applid