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

x
?
Solved

SQL query: calculation not working

Posted on 2011-10-26
5
Medium Priority
?
144 Views
Last Modified: 2012-05-12
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

Open in new window

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

Open in new window

0
Comment
Question by:Stef Merlijn
  • 2
  • 2
5 Comments
 
LVL 15

Assisted Solution

by:tim_cs
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

by:Stef Merlijn
ID: 37032038
Doesn't this cover that?
ISNULL(MO3.AantalDoublures, 0) AS AantalDoublures
0
 
LVL 15

Accepted Solution

by:
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

by:Éric Moreau
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

by:Stef Merlijn
ID: 37032087
Thank you.
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

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

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question