Running a select query not returning the correct results

[isthisthingon]

I am running a stored procedure in SQL. I am looking to get all records that were called with a specific call date, regardless of the disposition. This query works fine to get me all the results:

CREATE PROCEDURE sp_Last_Call
@date smalldatetime
AS

SELECT DISTINCT confirmer, status, COUNT(status) as statusCount, showed
FROM TS_DATA
WHERE( LEFT(CONVERT(varchar, _lastcall, 120), 10) = @date AND Status <> 'none' and Status <> 'never call')
GROUP BY confirmer, status, showed

Recently, I needed to exclude anything with a status of confirmed and a showed of n. I also need to exclude anything with a status of pending* and a showed of n. This is what I came up with to do that:

CREATE PROCEDURE sp_Last_Call
@date smalldatetime
AS

SELECT DISTINCT confirmer, status, COUNT(status) as statusCount, showed
FROM TS_DATA
WHERE (LEFT(CONVERT(varchar, _lastcall, 120), 10) = '2005-05-18') and

((status in (select status from ts_data where status <> 'null' and status <> 'none' and status <> 'never call')) and
(status not in (select status from ts_data where status = 'confirmed' and showed ='n')) and
(status not in (select status from ts_data where status = 'pending*' and showed = 'n')))

GROUP BY confirmer, status, showed


The problem with my new query is that it excludes all records with a status of confirmed and pending*, regardless of the showed field being 'n', 'y' or null. I wish to keep anything with a status of pending* and a showed of 'y' or null, excluding only the ones with a showed of n. same thing for those records with a status of confirmed. Anything with status = 'confirmed' and showed <> 'n' is acceptable. Anything with status = 'pending*' and showed <>'n' is acceptable.

Any help is tremendously appreciated.

 

[fredericofonseca]

Why are you doing all those selects.

The first one is
status not in ('null', 'none', 'never call')
and
status not in ( 'confirmed')
and
status not in ( 'pending')

so it would never work.


Try something like


select f1.* from t1 f1
where not exists
(select * from t1 f2 where ((f2.status = 'confirmed' and f2.showed = 'n')
or (f2.status = 'pending*' and f2.showed = 'n'))
and f1.tx = f2.tx)

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[isthisthingon]

Thanks for the quick response. Please go easy on me. I am a VB Noobie. what is the f1 and f2? What is f1.tx and f2.tx

Could you reformat that response so it would be a little more straightforward to my simple mind? Thanks.