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.
| 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 |
|
|
|
|
|
|
|
|
Group, temp table and join table issues