Group All or Coalesce - 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 2000 Forums

Transact-SQL (2000)

Group All or Coalesce

Author

Topic

MBeal
Posting Yak Master

110 Posts

Posted - 2003-04-17 : 13:01:37

Problem: Need to return all room types, even if none were used, and a zero next to that room type in the count if that is the case. As it is now, I only get results for room types that were used.

Here is the query:

Select Case
When det.RmType = 'K' then 1
When det.RmType = 'AK' then 2
When det.RmType = 'MPS' then 3
When det.RmType = 'DQ' then 4
When det.RmType = 'ADQ' then 5
When det.RmType = '2Q' then 6
When det.RmType = '2K' then 7
When det.RmType = '2D' then 8
When det.RmType = '4K' then 9
When det.RmType = '4D' then 10
Else 11
End [Sort],
det.RmType,
det.StayDate,
det.[Year],
Count(det.RoomNo) [Sold]
From
(Select Distinct D.RoomNo,
R.RmType,
D.StayDate,
DatePart(DW,D.StayDate) [DayofWeek],
DatePart(YY,D.StayDate) [Year]
From Detail D inner join Rooms R
on D.RoomNo = R.RoomNo
Where D.StayDate between '05/01/2001' and Left(getdate()-1,11) and
R.RmType not in ('Group', 'Perm', 'Day', 'ROH'))det

Group by
det.RmType,
det.StayDate,
det.[Year]
Order by
det.StayDate,
Case
When det.RmType = 'K' then 1
When det.RmType = 'AK' then 2
When det.RmType = 'MPS' then 3
When det.RmType = 'DQ' then 4
When det.RmType = 'ADQ' then 5
When det.RmType = '2Q' then 6
When det.RmType = '2K' then 7
When det.RmType = '2D' then 8
When det.RmType = '4K' then 9
When det.RmType = '4D' then 10
Else 11
End,
det.RmType,
det.[Year]

Any ideas?

MBeal

X002548
Not Just a Number

15586 Posts

Posted - 2003-04-17 : 13:11:41

I'm not sure of why you have that sql structure,,,seems more complicated than it should be...

BUT

Make your derived tables join a RIGHT JOIN instead of an INNER JOIN.

That should do it.

Brett

8-)

MBeal
Posting Yak Master

110 Posts

Posted - 2003-04-17 : 13:20:19

That had no impact... any other suggestions?

MBeal

X002548
Not Just a Number

15586 Posts

Posted - 2003-04-17 : 13:30:39

OK, not sure what's going on with your code just yet, but this is what I'm driving at....maybe it'll help spark something:



USE Northwind



CREATE TABLE RoomType (Type varchar(2))
GO

CREATE TABLE Room (UsedDate datetime, Type varchar(2))
GO

INSERT INTO RoomType (Type) SELECT 'x' UNION ALL SELECT 'y' UNION ALL SELECT 'Z'
GO

INSERT INTO ROOM (UsedDate, Type)
SELECT '1/1/2000','X' UNION ALL
SELECT '1/1/2000','X' UNION ALL
SELECT '1/1/2000','X' UNION ALL
SELECT '1/1/2000','X' UNION ALL
SELECT '1/1/2000','Y' UNION ALL
SELECT '1/1/2000','Y'
GO

SELECT * FROM RoomType
GO
SELECT * FROM Room
GO
SELECT l.Type, Count(r.type) FROM RoomType l LEFT JOIN Room r ON l.Type = r.Type Group By l.Type
GO
DROP TABLE Room
Go
Drop TABLE RoomType
Go

Brett

8-)

Edited by - x002548 on 04/17/2003 13:31:59

MBeal
Posting Yak Master

110 Posts

Posted - 2003-04-17 : 13:53:44

Thank you for your input -- I am going to attack this from a different angle.

MBeal

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-04-17 : 22:33:02

First: Make sure you have a table of ROOM TYPES! And store the sort value IN THAT TABLE. I cannot stress that enough. Store all important data IN the tables in your database, NOT in the logic within your database objects.

After you have done that:

You said you want to return a record for EVERY room type, regardless of whether or not there is data in the details table. Start with that:

SELECT RmType
FROM
RoomTypes
ORDER BY Sort

Makes sense, right? And sort lives in your RoomTypes table, where you can easily maintain, update, and view the sort order. Now, just do a LEFT OUTER JOIN to the query you already have:

SELECT RmType, StayDate, Year, ISNULL(Sold,0) as Sold
FROM
RoomTypes
LEFT OUTER JOIN
(SELECT det.RmType,
det.StayDate,
det.[Year],
Count(det.RoomNo) [Sold]
From
(Select Distinct D.RoomNo,
R.RmType,
D.StayDate,
DatePart(DW,D.StayDate) [DayofWeek],
DatePart(YY,D.StayDate) [Year]
From Detail D inner join Rooms R
on D.RoomNo = R.RoomNo
Where D.StayDate between '05/01/2001' and Left(getdate()-1,11) and
R.RmType not in ('Group', 'Perm', 'Day', 'ROH')) et
Group by
det.RmType,
det.StayDate,
det.[Year]
)
a
on RoomTypes.RmType = a.RmType
ORDER BY Sort
------------

One more word of advice: you have the following filter:

R.RmType not in ('Group', 'Perm', 'Day', 'ROH')

Try to create attributes in your RoomTypes table that makes listing out values like this unnecessary; then, you just fitler by the attribute. that way as room types are added, again, you don't have to maintain your database OBJECTS, just your data.

Example:

Instead of saying

WHERE Status.Status NOT in ('Deleted', 'Removed', 'Closed')

to show only statuses that are Active, you would create an attribute in your status table called "IsActive" and filter like this:

WHERE Status.IsActive = 1

Good luck.

- Jeff

Edited by - jsmith8858 on 04/17/2003 22:34:10

X002548
Not Just a Number

15586 Posts

Posted - 2003-04-18 : 12:35:08

Guess Imissed that (simple, but basic) point. I just assumed there was a room type table.....

Silly me.

Brett

8-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources