Group, temp table and join table issues

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)

Group, temp table and join table issues

Author

Topic

NAUSICAA
Starting Member

2 Posts

Posted - 2014-02-15 : 00:39:06

Table 1 (ROOM INFORMAION)
RoomID, Floor, Room#, Condition of the room

Table 2 (LEASE INFORMATION)
LeaseID, LeaseType, RoomID, OccupantID

Table 3 (Lease Types)
LeaseTypeID, Lease Term

Table 4 (Occupant INFORMATION)
OccupantID, FirstName, LastName, Other Information

The problem is when I add them to temp Table with join

Temp Table will have like this:
LeaseID, LeaseType, OccupantFirstName, OccupantLastName, Room#, RoomCondition Information GROUP BY ROOM#

Currently
Lease|LeaseTypeID|OccupantFirstName|OccupantLastName|Room#|RoomCondition
1 | 1 | ABC | ABC | 101 | Fair
2 | 2 | BCD | BCD | 102 | GOOD
3 | 2 | CDE | CDE | 102 | GOOD
4 | 3 | DEF | DEF | 103 | Fair

LeaseType table:
LeaseTypeID | LeaseType
1 | Single Occupant
2 | Multiple Occupant
3 | Sub-leased

What I like to do display the results is
ROOM# | Occupants | RoomCondition
101 | ABC ABC | Fair
102 | BCD BCD, CDE CDE | Good

And Also Result to include Owner's name on sub-lease
103 | OWNERA (DEF DEF) | Fair

Please help show me as SQL Statement thanks!

stepson
Aged Yak Warrior

545 Posts

Posted - 2014-02-15 : 02:11:31

[code]

;with Currently
AS(
select 1 Lease, 1 LeaseTypeID,'ABC' OccupantFirstName,'ABC' OccupantLastName,101 [Room#],'Fair' RoomCondition union all
select 2 , 2 , 'BCD' , 'BCD' , 102 , 'GOOD' union all
select 3 , 2 , 'CDE' , 'CDE' , 102 , 'GOOD' union all
select 4 , 3 , 'DEF' , 'DEF' , 103 , 'Fair')

,LeaseType
AS (
select 1 LeaseTypeID,'Single Occupant' LeaseType union all
select 2 , 'Multiple Occupant' union all
select 3 , 'Sub-leased')

select
[Room#]
,
CASE WHEN
C1.LeaseTypeID=3 then 'OWNERA (' + STUFF(Ocupants,1,1,' ') +')'
ELSE STUFF(Ocupants,1,1,'')
END as Ocupants
,RoomCondition
from
(
select
LeaseTypeID
,[Room#]
,RoomCondition
from Currently
Group by
LeaseTypeID
,[Room#]
,RoomCondition) C1
inner join LeaseType LT
ON C1.LeaseTypeID=LT.LeaseTypeID

outer apply(
select ',' + OccupantFirstName + ' ' + OccupantLastName
from Currently C2
where C1.[Room#]=C2.[Room#]
for xml path('') ) O(Ocupants)

[/code]

S

sabinWeb MCP

NAUSICAA
Starting Member

2 Posts

Posted - 2014-02-15 : 11:20:58

Sorry I was not clear on that....
Here is my SQL Statement

Create PROCEDURE [dbo].[spLeaseReport]

AS
BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
Select tRI.RoomNo,
tLT.LeaseType
tRI.OccupantFirstName,
tRI.OccupantLastName,
tRI.RoomCondition
tON.OwnerFirstName,
tON.OwnerLastName
From tblLeaseInformation tLI with (nolock) -- including old 4000 Records
Inner Join tblOccupant tOp -- 2000 Occupant
On tLI.OccupantID = tOP.OccupantID
Inner Join tblRoomInfo tRI -- 500 ROOMS
On tLI.RoomID = tRI.RoomID
Inner Join tblLeaseType tLT
On tLI.LeaseTypeID = tLT.LeaseTypeID
JOIN tblOwners tOn -- 340 Owners
On tLI.RoomID = tOn.RoomID

GROUP BY tRI.RoomNo

END

LeaseType table:
LeaseTypeID | LeaseType
1 | Single Occupant
2 | Double Occupant
3 | Sub-leased

I want to display the results like:

RoomNo | LeaseType | Occupant | Room Condition | Owners
001 null null good Bill Gates
100 Double Occupant Jon DOe,JonDoe JR good Bill Gates
101 Single Occupant JOe DOE good Steve Ballmer
-- many more results ---
200 Sub-Lease Dave DOE, DICK DOE, good John Johnes

Thank you for your help in advance

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources