CASE WHEN - SQL Server Forums

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

SQL Server 2005 Forums

Transact-SQL (2005)

CASE WHEN

Author

Topic

Jonny1409
Posting Yak Master

133 Posts

Posted - 2007-09-04 : 11:44:24

Hello,

Is is possible to use CASE WHEN with an AND Statement ?
If so, what is the syntax please ? (I've googled this but got no joy)

I want something along the lines of this :

case
when StartHalfDay = 'Afternoon' then StartHalfDay = '0' and EndHalfDay = '1'
when StartHalfDay = 'Morning' then StartHalfDay = '1' and EndHalfDay = '0'
end as StartHalfDay

Can anyone help please ?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-04 : 11:47:19

[code]case
when StartHalfDay = 'Afternoon' then StartHalfDay = '0'
when StartHalfDay = 'Morning' then StartHalfDay = '1'
end as StartHalfDay,
case
when StartHalfDay = 'Afternoon' then EndHalfDay = '1'
when StartHalfDay = 'Morning' then EndHalfDay = '0'
end as EndHalfDay[/code]

E 12°55'05.25"
N 56°04'39.16"

Jonny1409
Posting Yak Master

133 Posts

Posted - 2007-09-04 : 11:59:05

Thanks Peso.
That didn't quite work as I had to remove the StartHalfDay text after the "Then" part, but I got your gist.

However now that it works fine, I would like to combine the two together.
What I mean is as follows :

Start Half Day can be either 0, 1 or null
End Half Day can be either 0, 1 or null

This is accomplished by your code above.

However, they could both be 0, 1, or null at the same time - So Start Half Day could be 1 and End Half day could also be 1.

The code above assumes that if one is 0, then the other is 1 when this isn't necessarily the case.

Does this make sense ?
If so, how can I do this ?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-04 : 12:21:37

With only one check (afternoon/morning) there can be only two alternatives.

case
	when StartHalfDay = 'Afternoon' then 0
	when StartHalfDay = 'Morning' then 1
end as StartHalfDay,
case
	when StartHalfDay = 'Afternoon' then 1
	when StartHalfDay = 'Morning' then 0
end as EndHalfDay

Maybe you should try to explain again what you are after?

E 12°55'05.25"
N 56°04'39.16"

Jonny1409
Posting Yak Master

133 Posts

Posted - 2007-09-06 : 03:09:46

Hi Peso,

Thanks for coming back to me.
I'll certainly try my best to explain what I'm after and how it works.

Basically it's all about holidays.

In my table I have 4 fields :

Start Date
Half Day Start
End Date
Half Day End

If I book 1 full days holiday for today, my fields would show :

Start Date = 06/09/2007
Half Day Start = Null
End Date = 06/09/2007
Half Day End = Null

If I book just this morning off, the table would show :

Start Date = 06/09/2007
Half Day Start = Morning
End Date = 06/09/2007
Half Day End = Null

If I book 2 full days for today and tomorrow off, the table would show :

Start Date = 06/09/2007
Half Day Start = Null
End Date = 07/09/2007
Half Day End = Null

If I book this afternoon and tomorrow morning off, the table would show :

Start Date = 06/09/2007
Half Day Start = Afternoon
End Date = 07/09/2007
Half Day End = Morning

Now I want to transfer this data into a table that has only 3 fields :

Date
Morning
Afternoon

This is where the code you gave wouldn't work because as you can see by the last example, both morning and afternoon would be TRUE (i.e. 1) whereas your code assumes that if morning is 1 then afternoon is 0.

Does that make sense ?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources