Returning Wrong Value

[Khanson82]

The field "d.dttime" is coming back null and it should have a date. Any ideas why it's not working?
Sql Server 2005 Management Studio Express


select b.clientname,a.agency, a.applid,a.caseno,d.dttime,a.applname, a.status, a.statusreason,'During Scheduling' as SchedulingProcess
from vwapp0 a
inner join VWAC b with(nolock)
on a.clientid = b.clientid
left outer join VWAP d with(nolock)
on a.serial = d.appserial
and d.clientid = a.clientid
and d.serial in (select max(serial) from VWAP with(nolock)
where status in ('%cancel%') or statusreason in('%cancel%')
group by appserial)
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 VWR0 c with(nolock) where c.requirement = 'appointment pending'
group by c.clientid, c.appserial)
and (a.status like '%cancel%' or a.statusreason like '%cancel%')

 

[SQLBill]

Are you sure every row has a value for dttime? With all of those joins and other criteria, you could be pulling back rows where dttime is null.

Try:
SELECT *
FROM VWAP
WHERE dttime IS NULL

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[Khanson82]

The table VWAP is basically a history table that has a timestamp(dttime) for every record. So the only reason it would be null would be if the case was not in a scheduled status, which would not account for any with this query..

This query above is pulling all cancelled cases that have been scheduld at one time, so there should be dttime for every record..

 

[Crowley16]

you're using a left join to get that column, hence if there are any records in your first 2 tables which do not have a matching record in your VWAP table, then you'll get a null for d.dttime.

I suggest you look at your join criterias to see why this is happening...

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

 

[Khanson82]

Unfortunately they will always match up because if something is in vwapp0 it will have a match in VWAP, due to vwap being basicaly a time log for every action that happens with all the cases in vwapp0..

 

[Crowley16]

are you sure?

try doing a select *, if you find that there are instances with null in every column from the VWAP table, then it will prove that there's a record missing.

remember, you've got a subquery as a criteria to join on, you may want to check the results of that as well, I think the error is most likely to be in there...

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

 

[Khanson82]

I'm going to keep checking all the subquery info, but the subquery checks for something else on the report... But for sure the VWAP does not have null values on any of the join fields nor the dttime fields when you do a select *.So we'll see..