Case Statement Sql Multiple column check for dates

Question

I have a stored procdure that uses case statement as follows: What I am trying to do is evaluate 2 columns in the testTable for dates. So the below case statement says that if stop_date is null or greater than current date then set is_active cloumn is Y else N

What I am trying to do is also evaluate another date column say another_stop_date and check if it is null or has a date greater then today and use same logic to update the is_active column

I am not sure if we can use multiple case statement logic to update a single column?

I have commented the code below where I am not getting the right results

Basically need to evaluate stop_dt and another_stop_date columns from testTable!

USE [Test]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROC [dbo].[p_test]
@Blank_Row CHAR(1) = 'N'

AS
BEGIN

SET NOCOUNT ON

DECLARE @TD  DATETIME

SELECT @TD = GETDATE()

DECLARE @tempTable TABLE (

ID INT,
c_id   INT,
desc varchar(40),
date datetime,
s_col  TinyINT,
is_active char(1),
stuff VARCHAR(8))


 INSERT INTO @tempTable

 SELECT id, c_id, desc, max( date ), 1,

 CASE WHEN (stop_dt IS NULL OR stop_dt > @TD) THEN 'Y' 
 --//Case When (another_stop_date is NULL or another Stop_date > @TD) THEN 'Y'<-----confused here
 ELSE 'N' END,

 stuff

 FROM testTable

 GROUP BY id, stop_dt, c_id, desc, stuff, another_stop_date

 Select * from tempTable

You need to be a bit more specific. Should it only be 'Y' if both criteria are met? if so case when (stop_dt is null or stop_dt > @td) and (another_stop_date is null or another_stop_date > @td) Then 'Y' Else 'N' End — Laurence
– Laurence, Commented Sep 9, 2013 at 21:25
Thanks Laurence your solution was exactly what I needed, Thank you wish I could somehow mark your answer as correct. — CharlesMighty
– CharlesMighty, Commented Sep 10, 2013 at 13:30

Laurence · Accepted Answer · 2013-09-10 13:51:14Z

3

You can combine clauses in a case statement with the usual logical operators, as well as having separate cases:

Case 
    When 
        (stop_dt is null or stop_dt > @td) and
        (another_stop_date is null or another_stop_date > @td) 
    Then 'Y' 
    Else 'N' 
End

answered Sep 10, 2013 at 13:51

Laurence

11k1 gold badge28 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Twelfth · Accepted Answer · 2013-09-09 21:25:49Z

0

Case statement operate close to if statements and can have multiple clauses.

Case when condition_1 > 1 then 'hi'
when condition_1 < 14 then 'no'
when condition_89 > 12 then 'why is this here'
else 1
end

Apply it to your statement:

 CASE WHEN (stop_dt IS NULL OR stop_dt > @TD) THEN 'Y' 
 When (another_stop_date is NULL or another Stop_date > @TD) THEN 'Y'<-----confused here
 ELSE 'N' END

answered Sep 9, 2013 at 21:25

Twelfth

7,2083 gold badges30 silver badges35 bronze badges

Collectives™ on Stack Overflow

Case Statement Sql Multiple column check for dates

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related