help with exists

[wfd1bdb]

Here is my code I am working on:
Here is some background...I am looking at finding scheduled clients who have appointments in the future with a code of 'reentrmr30'. Of these people who have an appointment for a 'reentrmr30', how many do not have a flag of 'b' AND have not had a 'ev60' in the past year.

So my results need return results where there no EV60 at all. I'm not sure how to search for something that does not exist in a table! Thanks, for all help!

wfd1bdb

SELECT ACCOUNT, BOOK, REASON, USERFLAG, adate as test
FROM MWAPPTS
WHERE (ADATE < GETDATE()) AND (ADATE > GETDATE() - 365) AND (USERFLAG <> 'b') AND (REASON = 'ev60') AND (ACCOUNT IN
(SELECT DISTINCT account
FROM mwappts
WHERE adate >= getdate() AND reason = ('reentrmr30')and userflag ='u'))
ORDER BY adate desc, account

 

[SQLBill]

Can you provide a sample of the schema and data? Also provide what results you want to get based on the sample data.

-SQLBill

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

 

[SQLSister]

First stop using subqueries and the in keyword. They are will hurt perfomance most of the time and are only rarely needed. Start thinking about things in terms of joins.

What you have here are three groups of data and you want to find the record which are in all three. First you want the records between a certain date, then you want the people who have future appointsments and finally you want the people who have not had a reason 'ev60' in the past year.

This cries out for joins on derived tables since all this information is in the same table but the conditions are differnt enough that to try to get them right with one set of where clauses would be difficult. Where you want the records not in the group would be a left join where the join field is null in the right part of the query (you'll see what I mean better when you see my code)

SELECT     a.ACCOUNT, a.BOOK, a.REASON, a.USERFLAG, a.adate as test
FROM         MWAPPTS a
join 
(select distinct account from MWAPPTS where apptdate >getdate()) b on a.account = b.account
left join 
(select distinct account from MWAPPTS where ADATE < GETDATE()) AND (ADATE > GETDATE() - 365 
and reason = 'ev06' and USERFLAG <> 'b') c
where c.account is null

Now I'm not sure if this will give you the results you want (especially since I wasn't sure if the userflag<>'b' was meant to work with the reason to exclude records or if it should apply to the whole query). But the method should help you out. Create the queries to get the groups of records you want and then the group you want to make sure are excluded. Then use these queries as derived tables and join them together. Use inner joind for those records you want in the results and left joins where the join field is null for those you do not want.

"NOTHING is more important in a database than integrity." ESquared