Help on using combining several CASE functions

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 2012 Forums

Transact-SQL (2012)

Help on using combining several CASE functions

Author

Topic

zicitron
Starting Member

5 Posts

Posted - 2014-06-08 : 05:30:05

Hi all,

I have the following query which splits the number of "nights" spent by a guest, by using the CheckInDate and CheckOutDate.

SELECT reservationstayid,checkindate,checkoutdate,
CASE WHEN ('2014-7-1' > Checkoutdate) OR ('2014-7-31' < Checkindate) THEN 0
WHEN ('2014-7-1' >= Checkindate) AND ('2014-7-31' <= Checkoutdate) THEN DATEDIFF(day, '2014-7-1', '2014-7-31') +1
WHEN ('2014-7-1' >= Checkindate) AND ('2014-7-31' > Checkoutdate) THEN DATEDIFF(day, '2014-7-1', Checkoutdate)
WHEN ('2014-7-1' < Checkindate) AND ('2014-7-31' < Checkoutdate) THEN DATEDIFF(day, Checkindate, '2014-7-31') +1
WHEN ('2014-7-1' < Checkindate) AND ('2014-7-31' = Checkoutdate) THEN DATEDIFF(day, Checkindate, '2014-7-31')
WHEN ('2014-7-1' < Checkindate) AND ('2014-7-31' > Checkoutdate) THEN DATEDIFF(day, Checkindate, Checkoutdate)
END AS '2014-July'

FROM GuestStaySummary

WHERE CheckOutDate>='2014-06-01'

ORDER by CheckOutDate

It outputs the following columns: reservationstayid,checkindate,checkoutdate and 2014-July

My problem is that I need several CASE functions in the SAME query to cater for August 2014 to December 2014! I want my output to look like this: reservationstayid,checkindate,checkoutdate, 2014-July, 2014-August, 2014-September,......2014-December.

Can anyone please help on how to proceed?

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-06-09 : 11:40:00

Just add another case expression (as many as you need) like the one you already have, but change the date values.

zicitron
Starting Member

5 Posts

Posted - 2014-06-10 : 06:24:49

Hi Lamprey,

I tried that but it didn't work. In fact, I copied and pasted the whole query just after the end and changed the dates. Problem is it now outputs 2014-July and 2014-August in 2 separate windows!

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2014-06-10 : 06:45:49

don't copy the whole query.
just copy from CASE to END AS '2014-July'
then add a comma and then past it
then change the dates.
you will get a new column in the same query.

select
case...end as '2014-July',
case...end as '2014-August,
case...end as '2014-September',
...
from ...

Too old to Rock'n'Roll too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources