;with cte help

[Khanson82]

I have this::

;with Monster as (select a.caseno,a.applid, a.applname, s.clientname,a.clientid, a.serial,
(a.status +'/'+ a.statusreason) as Status,
b.Appt_DateTime as Rescheduled, b.providerid as currentprovider,c.fudate
from vwapp0 a with(nolock)
inner join vwaccount s
on s.clientid=a.clientid
Left outer join hhappexam b with(nolock)
on a.serial = b.appserial
and b.coid=s.clientname
and b.serial in
(select max(serial) from hhappexam with(nolock)
group by appserial)
left outer join vwvendschd c with(nolock)
on a.serial = c.appserial
and a.clientid=c.clientid
where a.status in ('Scheduled','Delinquent','Paperwork Submitted','Member','Provider','Appointment Met'))

Select monster.*,d.providerid as originalprovider,
d.Appt_DateTime as Scheduled
from Monster with(nolock)
left outer join hhappexam d with(nolock)
on monster.serial = d.appserial
and monster.clientname = d.coid
and d.serial in
(select min(serial) from hhappexam with(nolock)
group by appserial)
____________________________________________________________

The first part comes back perfect with no null values for the dates..But when i do the second part of the query, I mostly get null values... The join seeem simple enough and just for reference d.Appt_DateTime should never be null as there is not one field record that this null in the hhappexam table....
Also, if if take out
and monster.clientname = d.coid
from the last statement all the date fields populate but since it is not matching the records by clientname i get wrong dates.


Any ideas??

sql server 2005..

 

[Crowley16]

well, you're doing a left join, so any records not matching the join from the hhappexam table will be treated as null...

looking at your left join criterias, and not knowing about your actual data, I can only make guesses based on names...

1) ClientName = CoId - are you sure these 2 are equivalent?
2) serial in (select min(... - you're filtering on just the minimum serial number from hhappexam, i.e. only 1 serial number...

--------------------
Procrastinate Now!

 

[Khanson82]

Yep, clientname is a 4 letter code and coid is the same 4 letter code. Who knows why the fields are named different. This database has all types of things like that.. Neither field is null in either table..

And by selecting the min serial, that would give me the ealiest activity date for that case for that client..

Kind of one of those things where everything should work but doesn't...