SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Group, temp table and join table issues
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

NAUSICAA
Starting Member

2 Posts

Posted - 02/15/2014 :  00:39:06  Show Profile  Reply with Quote
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
Constraint Violating Yak Guru

Romania
420 Posts

Posted - 02/15/2014 :  02:11:31  Show Profile  Reply with Quote


;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)




S


sabinWeb MCP
Go to Top of Page

NAUSICAA
Starting Member

2 Posts

Posted - 02/15/2014 :  11:20:58  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000