Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Nested Case When statement

Author  Topic 

rbara902
Starting Member

2 Posts

Posted - 2014-04-16 : 12:46:39
Hello,

I'm fairly new to SQL. I have a function in Excel that i'm trying to translate to SQL to run my query.

Basically what i want to do is if Date1 and Date2 are blank then display Date Not Tracked.
Else if Date1 is blank however Date2 has a value then pull Date2 other wise Date1

This is my Excel function:
=IF(AND(A2="",B2=""),"Date Not Tracked",IF(A2="",B2,A2))

A2 = Product.Date1
B2 = Product.Date2

Can anyone help me?

Becky

sqlsaga
Yak Posting Veteran

93 Posts

Posted - 2014-04-16 : 13:02:32
Hi rbara902, is this something what you are looking for.


SELECT CASE WHEN Product.Date1 = '' THEN CASE WHEN Product.Date2 = '' THEN 'Date Not Tracked' ELSE Product.Date2 END
ELSE Product.Date1 END
FROM Table_name


Visit www.sqlsaga.com for more t-sql code snippets and BI related how to articles.
Go to Top of Page

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2014-04-16 : 14:58:34
Becky,
what are the data types of Date1 and Date2?
Assuming they are some kind of date like DATETIME then your "blanks" must be NULLs rather than empty string ('').
And in that case the suggested code won't work because 'Date Not Tracked' is varchar and you can't conditionally return different data types in a CASE expression.




Be One with the Optimizer
TG
Go to Top of Page

rbara902
Starting Member

2 Posts

Posted - 2014-04-16 : 15:11:39
TG you are correct Date1 and Date2 are date fields. Any suggestions on how i could accomplish this?

Sqlsage - thank you for the suggestion! i will try it out :)

Becky
Go to Top of Page

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2014-04-16 : 15:13:54
Here's one way:
This means that the return type is varchar and can no longer treat the output as dates but here ya go. And you can always chose a different date format code.

select coalesce(convert(varchar(23), coalesce(date1, date2), 121), 'Date Not Tracked')

EDIT:
for more info on format options and converting data types in general see Cast and Convert

Be One with the Optimizer
TG
Go to Top of Page

sqlsaga
Yak Posting Veteran

93 Posts

Posted - 2014-04-16 : 15:28:11
quote:
Originally posted by TG

Becky,
what are the data types of Date1 and Date2?
Assuming they are some kind of date like DATETIME then your "blanks" must be NULLs rather than empty string ('').
And in that case the suggested code won't work because 'Date Not Tracked' is varchar and you can't conditionally return different data types in a CASE expression.




Be One with the Optimizer
TG



Yes, I thought the same too. But when the TS specifically mentioned about Blankspaces I refrained myself from using COALESCE..

Visit www.sqlsaga.com for more t-sql code snippets and BI related how to articles.
Go to Top of Page
   

- Advertisement -