I need help with a T-SQL Query

[isthisthingon]

Here's my problem. I wrote a query and it gives me exactly the results I am looking for:

CREATE PROCEDURE sp_Last_Call
@date smalldatetime
AS

SELECT DISTINCT confermer, status, COUNT(status) as statusCount
FROM TS_DATA
WHERE appt_date = @date AND Status <> 'none' AND Status <> 'never call'
GROUP BY confirmer

Now, I have a need to exclude two things from the results of this query. If (status = 'confirmed' and showed = 'n') and also if (status = 'pending*' and showed = 'n') I need to exclude them from the results. Any help with the easiest way to do this would be appreciated. Thanks for any help!!!!

 

[amrita418]

and not (Status = 'confirmed' and showed = 'n')
and not (status = 'pending*' and showed = 'n')

Hope this helps
AA

 

[isthisthingon]

Wow, you're quick! Thanks for the information. This is something I tried already and it excludes everything with status = 'confirmed' regardless of showed being equal to 'n' or not. It also excludes everything with status = 'pending*' regardless of showed being equal to 'n' or not. Any other ideas here?

 

[SQLBill]

How about:

--everything that is not confirmed AND anything confirmed
--but 'n'ot showed.
AND (Status <> 'confirmed' AND Showed <> 'n')
--everything that is not pending* AND anything pending*
--but not showed.
AND (Status <> 'pending*' AND Showed <> 'n')

-SQLBill

Posting advice: FAQ481-4875

 

[Catadmin]

Or, just a variation on SQLBill's code:

AND (Status = 'confirmed' AND Showed <> 'n')
AND (Status = 'pending*' AND Showed <> 'n')

That should show confirmed and pending where showed isn't n.



Catadmin - MCDBA, MCSA
"If a person is Microsoft Certified, does that mean that Microsoft pays the bills for the funny white jackets that tie in the back???

 

[SQLBill]

Catadmin,

The poster doesn't want to see where:

Status = 'confirmed AND Showed = 'n'

and they also don't want to see where:

Status = 'pending*' AND Showed = 'n'

-SQLBill

Posting advice: FAQ481-4875

 

[Catadmin]

SQLBill,

What's wrong with my code that it shows Status = 'confirmed AND Showed = 'n' and Status = 'pending*' AND Showed = 'n'? I don't understand since I didn't use = on the SHOWED part of my code.

Please explain.

Thanks,




Catadmin - MCDBA, MCSA
"If a person is Microsoft Certified, does that mean that Microsoft pays the bills for the funny white jackets that tie in the back???

 

[SQLBill]

Catadmin,

My mistake. This was a case of "I was looking at it from the wrong perspective".

I was looking at 'changing' the existing WHERE.

WHERE appt_date = @date AND Status <> 'none' AND Status <> 'never call'

to

WHERE appt_date = @date
AND (Status <> 'confirmed' AND Showed <> 'n')
AND (Status <> 'pending*' AND Showed <> 'n')

But I never bothered to state that. Where you are adding the code to the existing WHERE.

WHERE appt_date = @date
AND Status <> 'none'
AND Status <> 'never call'
AND (Status = 'confirmed' AND Showed <> 'n')
AND (Status = 'pending*' AND Showed <> 'n')

Taken by itself, your solution would have provided to little, but added to the existing script (which I forgot about doing) should provide the required result.

Please accept my apology.

-SQLBill

Posting advice: FAQ481-4875

 

[Catadmin]

Funny you should talk about perspective, because mentally I was adding your code to the bottom of the user's script the way that I was adding mine to the end of it. @=)

Thank you for explaining that. I was seriously confused for a moment, trying for the life of me what I did wrong in my code.

Hope everyone's day goes really really fast. Mine is taking entirely too long.



Catadmin - MCDBA, MCSA
"If a person is Microsoft Certified, does that mean that Microsoft pays the bills for the funny white jackets that tie in the back???

 

[isthisthingon]

Thanks for all the input, Catadmin and SQLBill. I might be missing something, so pardon me if I sound like an idiot. Your suggestions are not working. I tried all of them and they return no results at all.

How can you have:
status <> 'none'
and status <> 'never call'
and (status = 'confirmed' and showed <> 'n')
and (status = 'pending' and showed <> 'n')

No records will fit that criteria, or the similar criteria suggested because no record will satisfy both of the last statements? Am I missing something here?

Thanks for all the help!!

 

[Catadmin]

Add this line to the end of your original query:

Compute Count(confermer)

This is the number of records this query picks up.

Now, run your query with one of my two lines (not both) included in the where statement. Leave the Compute so you can see the difference in the #. If you get no records, run the query by removing all of your previous WHERE criteria, but leaving in my line. How many records?

Repeat this by substituting the other (unused) line of my code in place of the one you tested first.

This will tell you which of these two conditions are nulling out the recordset. I don't advise changing the AND between the two to an OR because then it will pull up all 'pending*' and 'confirmed' with a status of 'n'.

Essentially, you are correct. None of your records fit all the criteria in your where statement now. Hence, nothing is being returned. But the above method will tell you what records are having the problem.



Catadmin - MCDBA, MCSA
"If a person is Microsoft Certified, does that mean that Microsoft pays the bills for the funny white jackets that tie in the back???

 

[SQLBill]

Run this for us and let us know the result...

SELECT DISTINCT Status
FROM TS_Data

-SQLBill

Posting advice: FAQ481-4875

 

[isthisthingon]

I have not yet tried your last suggestion Catadmin, I thought this might shed some light on the problem. When I execute this in Query Analyzer, I get what I need:

SELECT DISTINCT confermer, status, COUNT(status) as statusCount, showed
FROM TS_DATA
WHERE LEFT(CONVERT(varchar, _lastcall, 120), 10) = '2005-05-25' and Status <> 'none' and Status <> 'never call'
and not (status = 'confirmed' and showed = 'n')
and not (status = 'pending*' and showed = 'n')
and confermer <> 'null'
GROUP BY confermer, status, showed

Yet, when I change it in my Stored Procedure so that the date is dynamic according to the date selected in the form, it returns no results:

CREATE PROCEDURE sp_Last_Call
@date smalldatetime
AS

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

It's probably something stupid I'm doing.

 

[isthisthingon]

Please disregard last post. This is what fixed the problem. Thanks to all!!!!!

CREATE PROCEDURE sp_Last_Call
@date smalldatetime
AS

SELECT DISTINCT confermer, status, COUNT(status) as statusCount, showed
FROM TS_DATA
WHERE LEFT(CONVERT(varchar, _lastcall, 120), 10) = @date
and Status <> 'none' and Status <> 'never call'
and not (status = 'confirmed' and showed = 'n')
and not (status = 'pending*' and showed = 'n')
and confermer <> 'null'
GROUP BY confermer, status, showed

 

[SQLBill]

AHA......SMALLDATETIME is DATE AND TIME.

When you provide 2005-05-25 as a SMALLDATETIME value it is really 2005-05-25 00:00:00.000. And that isn't matching anything.

CONVERT it to the format you want. There are a couple of FAQs about using DATETIME and they also apply to SMALLDATETIME.

-SQLBill

Posting advice: FAQ481-4875

 

[SQLBill]

Check out these two FAQs on using DATETIME/SMALLDATETIME...

FAQ183-5834
FAQ183-5842

-SQLBill

Posting advice: FAQ481-4875