Question about CASE WHEN on a column

Question

Post reply

Question about CASE WHEN on a column

Zososql

Ten Centuries

Points: 1212
More actions
January 18, 2017 at 10:50 am

#339503

I am wanting to display a date when a certain condition is met in a column on a particular recordset. If on a given row if the EventCode column has the 'Open' value, then I want to display the date value that resides in the EventAdded (a datetime datatype) column
select EventID, EventName, EventSourceID,
,EventScheduled =
CASE EventCode WHEN "OPEN' THEN EventAdded
ELSE 'TBD'
...I can't figure out how to display the date for EventAdded
? thanks for looking and your help
Zo

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Thom A SSC Guru Points: 99126 More actions · Answer 1

Your CASE statement should look like this:
CASE Eventcode WHEN 'Open' THEN EventAdded ELSE NULL END

Edit: Got the laptop, so I can finish my post now.

The problem you have, is that EventAdded is the datatype datetime/date, but the value 'TBD' is varchar (which can't be converted to a datetime).

You'll therefore need to either convert your Date value to a varchar (not ideal), or in your presentation layer, change your NULLs to the value TBD (better option). The reason for doing this is your presentation layer means that you can keep your datatype for your field and do any maths you want with it (which you might not be able to do with a varchar).

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Thom A SSC Guru Points: 99126 More actions · Answer 2

No edit button on the mobile site is annoying....

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

SvetNas SSC-Addicted Points: 476 More actions · Answer 3

Zososql - Wednesday, January 18, 2017 10:50 AM
I am wanting to display a date when a certain condition is met in a column on a particular recordset. If on a given row if the EventCode column has the 'Open' value, then I want to display the date value that resides in the EventAdded (a datetime datatype) column
select EventID, EventName, EventSourceID,
,EventScheduled =
CASE EventCode WHEN "OPEN' THEN EventAdded
ELSE 'TBD'
...I can't figure out how to display the date for EventAdded
? thanks for looking and your help
Zo

You can use this:

-- creating a sample table
create table #Event -- drop table #Event
(EventID int identity(1,1)
, EventName varchar(10)
, EventCode varchar(10)
, EventSourceID int
, EventAdded datetime
)

-- sample data
insert into #Event (EventName,EventCode,EventSourceID,EventAdded)
values ('Event A','CLOSED', 100, getdate()-31), ('Event B','OPEN', 109, getdate()-12)
, ('Event C','CLOSED', 104, getdate()-18), ('Event D',NULL, 108, getdate()-7)
, ('Event E','OPEN', 108, getdate()-5)

select * from #Event

-- and here is your case statement
select EventID, EventName, EventSourceID
,CASE EventCode WHEN 'OPEN' THEN cast(cast(EventAdded as date) as varchar(12))
ELSE 'TBD' end as EventScheduled
from #Event

EventID EventName EventSourceIDEventScheduled
1 Event A 100 TBD
2 Event B 109 2017-01-06
3 Event C   104   TBD
4 Event D   108   TBD
5 Event E 108   2017-01-13

Sue_H SSC Guru Points: 90860 More actions · Answer 4

Zososql - Wednesday, January 18, 2017 10:50 AM
I am wanting to display a date when a certain condition is met in a column on a particular recordset. If on a given row if the EventCode column has the 'Open' value, then I want to display the date value that resides in the EventAdded (a datetime datatype) column
select EventID, EventName, EventSourceID,
,EventScheduled =
CASE EventCode WHEN "OPEN' THEN EventAdded
ELSE 'TBD'
...I can't figure out how to display the date for EventAdded
? thanks for looking and your help
Zo

I would guess you are getting a data type conversion error on the EventAdded datetime field.
You'd need to do something like:
select EventID, EventName, EventSourceID, EventScheduled = CASE WHEN EventCode like 'OPEN' THEN CONVERT(varchar(10), EventAdded, 101) ELSE 'TBD' END

Sue

DesNorton SSC-Insane Points: 24278 More actions · Answer 5

All the outputs of your case statement need to have the same (or implicitly convertable) data type.
Also, your case statement needs to be terminated with and end

SELECT EventID, EventName, EventSourceID, ,EventScheduled = CASE EventCode WHEN 'OPEN' THEN CONVERT(VARCHAR(10), EventAdded, 111) ELSE 'TBD' END

How to post data/code on a forum to get the best help.

Luis Cazares SSC Guru Points: 183698 More actions · Answer 6

SvetNas - Wednesday, January 18, 2017 11:15 AM
Zososql - Wednesday, January 18, 2017 10:50 AM
I am wanting to display a date when a certain condition is met in a column on a particular recordset. If on a given row if the EventCode column has the 'Open' value, then I want to display the date value that resides in the EventAdded (a datetime datatype) column
select EventID, EventName, EventSourceID,
,EventScheduled =
CASE EventCode WHEN "OPEN' THEN EventAdded
ELSE 'TBD'
...I can't figure out how to display the date for EventAdded
? thanks for looking and your help
Zo
You can use this:
-- creating a sample table
create table #Event -- drop table #Event
(EventID int identity(1,1)
, EventName varchar(10)
, EventCode varchar(10)
, EventSourceID int
, EventAdded datetime
)
-- sample data
insert into #Event (EventName,EventCode,EventSourceID,EventAdded)
values ('Event A','CLOSED', 100, getdate()-31), ('Event B','OPEN', 109, getdate()-12)
, ('Event C','CLOSED', 104, getdate()-18), ('Event D',NULL, 108, getdate()-7)
, ('Event E','OPEN', 108, getdate()-5)
select * from #Event
-- and here is your case statement
select EventID, EventName, EventSourceID
,CASE EventCode WHEN 'OPEN' THEN cast(cast(EventAdded as date) as varchar(12))
ELSE 'TBD' end as EventScheduled
from #Event
EventID EventName EventSourceIDEventScheduled
1 Event A 100 TBD
2 Event B 109 2017-01-06
3 Event C   104   TBD
4 Event D   108   TBD
5 Event E 108   2017-01-13

The problem with this is that the dates stop being dates to become strings. This will prevent any date functionality when consumed by the front end. The TBD is a display issue and should be handled by the front end, as well as any other format option.
Also, there's no need to use ELSE on Thom's code. When there's no ELSE and no condition is true, the CASE clause will return NULL. I understand that some people like to included for consistence on best practices.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

SvetNas SSC-Addicted Points: 476 More actions · Answer 7

Luis Cazares - Wednesday, January 18, 2017 11:23 AM
SvetNas - Wednesday, January 18, 2017 11:15 AM
Zososql - Wednesday, January 18, 2017 10:50 AM
I am wanting to display a date when a certain condition is met in a column on a particular recordset. If on a given row if the EventCode column has the 'Open' value, then I want to display the date value that resides in the EventAdded (a datetime datatype) column
select EventID, EventName, EventSourceID,
,EventScheduled =
CASE EventCode WHEN "OPEN' THEN EventAdded
ELSE 'TBD'
...I can't figure out how to display the date for EventAdded
? thanks for looking and your help
Zo
You can use this:
-- creating a sample table
create table #Event -- drop table #Event
(EventID int identity(1,1)
, EventName varchar(10)
, EventCode varchar(10)
, EventSourceID int
, EventAdded datetime
)
-- sample data
insert into #Event (EventName,EventCode,EventSourceID,EventAdded)
values ('Event A','CLOSED', 100, getdate()-31), ('Event B','OPEN', 109, getdate()-12)
, ('Event C','CLOSED', 104, getdate()-18), ('Event D',NULL, 108, getdate()-7)
, ('Event E','OPEN', 108, getdate()-5)
select * from #Event
-- and here is your case statement
select EventID, EventName, EventSourceID
,CASE EventCode WHEN 'OPEN' THEN cast(cast(EventAdded as date) as varchar(12))
ELSE 'TBD' end as EventScheduled
from #Event
EventID EventName EventSourceIDEventScheduled
1 Event A 100 TBD
2 Event B 109 2017-01-06
3 Event C   104   TBD
4 Event D   108   TBD
5 Event E 108   2017-01-13
The problem with this is that the dates stop being dates to become strings. This will prevent any date functionality when consumed by the front end. The TBD is a display issue and should be handled by the front end, as well as any other format option.
Also, there's no need to use ELSE on Thom's code. When there's no ELSE and no condition is true, the CASE clause will return NULL. I understand that some people like to included for consistence on best practices.

Completely agree - if EventScheduled needs to allow for date functionality the conversion needs to change. With the requirement for a 'TBD' value I assumed it doesn't.

Thom A SSC Guru Points: 99126 More actions · Answer 8

Luis Cazares - Wednesday, January 18, 2017 11:23 AM
Also, there's no need to use ELSE on Thom's code. When there's no ELSE and no condition is true, the CASE clause will return NULL. I understand that some people like to included for consistence on best practices.

Definitely force of habit 😉

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Zososql Ten Centuries Points: 1212 More actions · Answer 9

Thanks for all help, will be testing, but this is headed in the right direction!