Can any body help me - 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)

Can any body help me

Author

Topic

Sycopons
Starting Member

4 Posts

Posted - 2012-02-08 : 02:46:21

I would like to display all RoomType and the total room(s),available room(s), and occupied room(s)

Here is my sample table

Rooms

RoomNo RoomTypeID Availability

100 1 Yes
101 1 No
102 1 No
103 2 Yes
104 2 No

RoomType

RoomTypeID RoomName RoomPrice

1 Single 100
2 Double 200
3 Tripe 300

xSycopons

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-02-08 : 02:54:34

[code]
select rt.RoomTypeID, rt.RoomName,
total = count(*)
available = count(case when r.Availability = 'Yes' then r.RoomNo end),
occupied = count(case when r.Availability = 'No' then r.RoomNo end)
from RoomType rt
inner join Rooms r on rt.RoomTypeID = r.RoomTypeID
group by rt.RoomTypeID, rt.RoomName
[/code]

KH
[spoiler]Time is always against us[/spoiler]

Sycopons
Starting Member

4 Posts

Posted - 2012-02-08 : 03:49:30

Thank you so much khtan..God Bless. I really Appreciate your help..

xSycopons

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources