Joining two Sql Statemets 1

[Khanson82]

Below is two sql statements separated by the line. They need to join on a.clientid=a.clientid and a.serial=a.serial and bring in the dttime field from the second sql statement and display all from the first..Any ideas. I'm in need of a sql answer. I've tried doing it in crystal where the end result will be and it takes way too long..







select b.clientname,a.serial,a.clientid,a.agency, a.applid,a.caseno,a.applname, a.status, a.statusreason,'During Scheduling' as SchedulingProcess
from VWAP a
inner join VWAC b with(nolock)
on a.clientid = b.clientid
where (Convert(varchar(8000),a.clientid) + '-' + Convert(varchar(8000),a.serial))
not in ( select distinct (Convert(varchar(8000),c.clientid) + '-' + Convert(varchar(8000),c.appserial)) from vwreqstatus0 c with(nolock) where c.requirement = 'appointment pending'
group by c.clientid, c.appserial)
and (a.status like '%cancel%' or a.statusreason like '%cancel%')


union all


select b.clientname,a.serial,a.clientid,a.agency, a.applid,a.caseno, a.applname, a.status, a.statusreason,'After Scheduling' as SchedulingProcess
from VWAP a
inner join VWAC b with(nolock)
on a.clientid = b.clientid
where (Convert(varchar(8000),a.clientid) + '-' + Convert(varchar(8000),a.serial))
in ( select distinct (Convert(varchar(8000),c.clientid) + '-' + Convert(varchar(8000),c.appserial)) from vwreqstatus0 c with(nolock) where c.requirement = 'appointment pending'
group by c.clientid, c.appserial)
and (a.status like '%cancel%' or a.statusreason like '%cancel%')




_______________________________________________________________________________________________________________________________________________________









select a.clientid,a.applname,a.applid,a.agency,a.caseno,a.serial,max(b.dttime)
from VWAP a with(nolock)
left outer join VWAPH b with(nolock)
on a.serial = b.appserial
and a.clientid = b.clientid
where b.status like ('%cancel%') or b.statusreason like ('%cancel%')
and a.status like ('%cancel%') or a.statusreason like ('%cancel%')
group by a.clientid,a.applname,a.applid,a.agency,a.caseno,a.serial

 

[markros]

SQL Server 2005 + solution:

;with cte1 as (myFirstComplex query), cte2 as (mySecondQuery)

select c1.fields, c2.Fields from cte1 C1 inner join cte2 c2
on ...

PluralSight Learning Library

 

[Khanson82]

That was amazing and worked.. Thank you!