[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
Solved

# SQL query: calculation not working

Posted on 2011-10-26
Medium Priority
144 Views
Hi,

Can anybody telle why the following part doesn't work?
Here the VerzendStatus will always be 0.
``````CASE WHEN ((AantalTotaal - (AantalVerzonden + AantalDoublures)) = 0) THEN 1 ELSE 0 END AS VerzendStatus
``````
``````SELECT ISNULL(MOTot.AantalTotaal, 0) AS AantalTotaal
, ISNULL(MO2.AantalVerzonden, 0) AS AantalVerzonden
, ISNULL(MO3.AantalDoublures, 0) AS AantalDoublures
, CASE WHEN ((AantalTotaal - (AantalVerzonden + AantalDoublures)) = 0) THEN 1 ELSE 0 END AS VerzendStatus
FROM dbo.Mailings LEFT OUTER JOIN
dbo.Medewerkers ON dbo.Mailings.Gebruikersnr = dbo.Medewerkers.Persoonsnr
LEFT OUTER JOIN (SELECT MailingID, COUNT(*) AS AantalTotaal
FROM dbo.MailingsDetails
GROUP BY MailingID) AS MOTot ON dbo.Mailings.MailingID = MOTot.MailingID
LEFT OUTER JOIN (SELECT MailingID, COUNT(*) AS AantalVerzonden
FROM dbo.MailingsDetails AS MailingsDetails_4
WHERE (VerzendStatus = 2)
GROUP BY MailingID) AS MO2 ON dbo.Mailings.MailingID = MO2.MailingID
LEFT OUTER JOIN (SELECT MailingID, COUNT(*) AS AantalDoublures
FROM dbo.MailingsDetails AS MailingsDetails_3
WHERE (VerzendStatus = 3)
GROUP BY MailingID) AS MO3 ON dbo.Mailings.MailingID = MO3.MailingID
``````
0
Question by:Stef Merlijn
• 2
• 2

LVL 15

Assisted Solution

tim_cs earned 2000 total points
ID: 37032030
Do any of the three values in the calculation contain a null?  If so it will always be false.

0

Author Comment

ID: 37032038
Doesn't this cover that?
ISNULL(MO3.AantalDoublures, 0) AS AantalDoublures
0

LVL 15

Accepted Solution

tim_cs earned 2000 total points
ID: 37032049
You have to put ISNULL around the fields in the calculation too.

((ISNULL(AantalTotaal,0) - (ISNULL(AantalVerzonden,0) + ISNULL(AantalDoublures,0))) = 0)
0

LVL 70

Expert Comment

ID: 37032051
i would try:
CASE WHEN ((isnull(AantalTotaal, 0) - (isnull(AantalVerzonden, 0) + isnull(AantalDoublures,0))) = 0) THEN 1 ELSE 0 END AS VerzendStatus
0

Author Closing Comment

ID: 37032087
Thank you.
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
Loops Section Overview
###### Suggested Courses
Course of the Month20 days, left to enroll