Nesting Case statement in SQL

Posted on 2014-10-28
Last Modified: 2014-10-28
I am tring to write a case statement where if today's date is between the InitDate and Stop Date field value = 1.

Some of my records have a StopDate with a 'NULL' value and some have a date.

Here is my code:

CASE WHEN dbo.NurInterventionOccurrences.InitDateTime <= getdate()
                      AND dbo.NurInterventionOccurrences.StopDateTime IS NULL THEN 1 ELSE

CASE WHEN dbo.NurInterventionOccurrences.InitDateTime <= getdate()
                      AND dbo.NurInterventionOccurrences.StopDateTime > getdate() then 1 else

0 END AS Cnt

There is something wrong with the syntax.

Can someone help me out?


Question by:GPSPOW
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
LVL 65

Accepted Solution

Jim Horn earned 500 total points
ID: 40409032
Give this a whirl (btw there was another END missing) ..
 -- Better to calculate it once here then 1000's of times in a query
Declare @dt datetime = GETDATE()  

SELECT blah, blah, blah, 
   WHEN InitDateTime <= @dt AND StopDateTime IS NULL THEN 1 
         WHEN InitDateTime <= @dt THEN 1
         ELSE 0 END 
   END as Cnt

Open in new window

btw I have an article called SQL Server CASE Solutions, and in the middle there's a demo of nested CASE blocks.
LVL 69

Expert Comment

by:Scott Pletcher
ID: 40409115
I think the way you described it -- "if today's date is between the InitDate and Stop Date field value = 1" -- is clearer than your existing code.  Thus, I changed the code to match the description, while simplifying the code:

CASE WHEN getdate() >= dbo.NurInterventionOccurrences.InitDateTime AND
                      getdate() < ISNULL(dbo.NurInterventionOccurrences.StopDateTime, '20790601') then 1 else 0 END AS Cnt

Btw, GETDATE() will only be evaluated once in any given SELECT statement.

Author Closing Comment

ID: 40409640

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Procedure 7 49
Backing up Large SQL Server VM Best practice [using Veeam Backup] 8 69
VM SQL server license. 1 63
Datatable / Dates ? 4 29
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

732 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