June 27, 2007 at 1:35 am
(I am a VB6 programmer using SQL Server 2000 for my DBs.)
I have a view:
SELECT TOP 100 PERCENT dbo.room.roomname, dbo.ReservationRoom.Pax, dbo.ReservationRoom.GuestName, dbo.ReservationRoom.checkoutdate,
dbo.ReservationRoom.checkindate, dbo.CLIENTS.CLIENT, dbo.room.status, dbo.ReservationRoom.Conf
FROM dbo.room
LEFT OUTER JOIN dbo.ReservationRoom
ON dbo.ReservationRoom.room_no = dbo.room.room_no
LEFT OUTER JOIN dbo.CLIENTS
ON dbo.ReservationRoom.res_no = dbo.CLIENTS.res_no
ORDER BY dbo.room.room_no
which gives this 7-column recordset:
Pax Guest Chkout Chkin Client Stat Conf
COPY1MAIN1AMOL DSF23/06/200721/06/2007AMOL DSF30
MAIN1POWERCOMPUTERS27/06/200726/06/2007POWERCOMPUTERS33
SUBIN1AAAA1bilal dadar29/06/200727/06/2007AMOL DSF30
AAAA2Mr Bond27/06/200724/06/2007SDF ASDF33
This is a Hotel Reservations System DB, giving the different movements for every room for a particular day. A movement may be an Arrival, a Departure, or a Rollover. A Rollover is mutually exclusive with the other two.
The Sample Report looks like this:
Room Rollover Arrival Departure
101 ABC
102 DEF
103 GHI JKL
104 MNO
105 PQR
However, if a room has an Arrival and a Departure together, I want the resulting recordset to display them on a single row, currently I am getting distinct rows for each movement.
I read on UNION joins - they append rows to the recordset while I need to append columns.
Can anyone help me?
June 27, 2007 at 2:39 am
Hi,
I find it a little difficult to understand your example though I understand your question.
If you want data returned in a 'shape' that isn't contained in your database and which can't be formed in one stage by a simple join, I would recommend using either hash (temp) tables or table variables. For table variables the process is along the lines of:
1) Declare the table - create it in the shape that you may need data to be ultimately returned in, e.g. if your source table has six columns, but you may need to repeat some of these (an example would be flattening a level depth hierarchy from a self referencing table) then create the table with the extra columns that may be repeated.
2) Populate your table variable - get the key data that you need from your source table and put it into your table variable, to do this you treat the variable just like a normal table and run insert/select statements against it.
3) Update your table - this is where you make use of the extra columns, you join from the populated columns in the variable to your source tables and pull back data you want where there is a match, populating the extra ‘repeating’ columns.
If your doing all of this via vb it could be difficult, I'd recommend writing the logic in native sql and putting in into a stored procedure, then calling this from your code.
Also you may want to look up the differences between table variables and temp tables before making your choice as to which you want to use.
Hope the above helps.
Ian
June 27, 2007 at 3:24 am
I just looked at my first post, and saw it has been mangled by the editor so here is my sample report with dotted column spacing:
Room...Rollover...Arrival...Departure
101....ABC...........................
102................DEF...............
103................GHI.......JKL.....
104..........................MNO.....
105.....PQR..........................
Currently Room 103 will appear on two rows as follows:
103................GHI...............
103..........................JKL.....
Ian, thanks for your reply, but unfortunately I find it too dense/advanced for my immediate needs, esp since SQL Server is not a primary tool for us developers.
I have already set up the stored proc and am generating this recordset in a temp table which is being called from VB to display a report in Crystal. All I am looking for is to match the client's existing manual report from what I have.
June 27, 2007 at 5:15 am
Hi Goodguy,
Sorry if the reply was a bit overcooked, as it turns out I don't think I did understand your question previously, just to confirm then that you are after:
Room...Rollover...Arrival...Departure
103...............abc.......def
from
Room...Rollover...Arrival...Departure
103...............abc................
103.........................def
The code below makes the following assumptions:
1) The table in your example is called rooms
2) A room can only appear a maximum of two times in the table, in these two records either arrival or departure is populated (as in the example above).
3) If for example a room has an arrival value, its departure value will be null (as opposed to blank)
Copy and paste everything below into query analyser/management studio to review:
--Firstly get a list of rooms that appear more than once
declare @multiple_room_list table
(
room_number int
)
--Then
insert into @multiple_room_list
room_number
from rooms
group by room_number
having count(room) > 1
/*
The above is effctively therefore a list that we can use to find multiple rows, we want to now set either departure on one row equal to the departure value of the other or do the same with arrival, and then delete the row that has not been updated.
We can do this using a self referencing join, this is achived using different alises for the same table in the join clause as below
*/
update r1
set r1.arrival = r2.arrival
from rooms as r1 join rooms as r2
on r1.room_number = r2.room_number
--Now filter so we do this for just our multiple records:
where r1.room_number in (select room_number from @multiple_room_list)
--and ensure that we update the right value with the right value
and r2.arrival is not null
/*
At this point one of our records will have both the arrival and departure column filled in, now lets get rid of the partial record:
*/
delete from rooms
where room_number in (select room_number from @multiple_room_list)
and arrival is null
--Hope this helps.
June 28, 2007 at 4:47 am
Hi goodguy
Here's another way with some handy sample data.
--create some sample data SET DATEFORMAT DMY
DROP TABLE #ViewResult CREATE TABLE #ViewResult (roomname CHAR(4), Pax tinyint, GuestName VARCHAR(20), checkoutdate DATETIME, checkindate DATETIME, CLIENT VARCHAR(20) , status int, Conf int)
INSERT INTO #ViewResult
SELECT 'MAIN', 1, 'AMOL DSF', CAST ('23/06/2007' AS DATETIME), CAST ('21/06/2007' AS DATETIME), 'AMOL DSF', 3, 0 UNION ALL
SELECT 'MAIN', 1, 'POWERCOMPUTERS', CAST ('30/06/2007' AS DATETIME), CAST ('26/06/2007' AS DATETIME), 'POWERCOMPUTERS', 3, 3 UNION ALL
SELECT 'AAAA', 1, 'bilal dadar', CAST ('29/06/2007' AS DATETIME), CAST ('28/06/2007' AS DATETIME), 'AMOL DSF', 3, 0 UNION ALL
SELECT 'AAAA', 2, 'Mr Bond', CAST ('28/06/2007' AS DATETIME), CAST ('24/06/2007' AS DATETIME), 'SDF ASDF', 3, 3
-- check the sample data SELECT * FROM #ViewResult
-- set up a variable for today
DECLARE @Today DATETIME
SET @Today = CAST ('28/06/2007' AS DATETIME)
-- run the report query
SELECT roomname, MAX(CASE WHEN checkindate <> @Today AND checkoutdate <> @Today THEN GuestName ELSE CAST(NULL AS VARCHAR(20)) END) AS Rollover, MAX(CASE WHEN checkindate = @Today THEN GuestName ELSE CAST(NULL AS VARCHAR(20)) END) AS Arrival, MAX(CASE WHEN checkoutdate = @Today THEN GuestName ELSE CAST(NULL AS VARCHAR(20)) END) AS Departure --, * FROM #ViewResult WHERE @Today BETWEEN checkindate AND checkoutdate GROUP BY roomname
Cheers
ChrisM
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
June 28, 2007 at 5:57 am
June 28, 2007 at 7:10 am
June 29, 2007 at 7:13 am
I spoke too soon (sigh)!
I thought I could adapt your code to get my data from just a single table:
SET DATEFORMAT DMY
DECLARE @MyDate DateTime
SELECT @MyDate = GetDate()
SELECT RoomName,
MAX(CASE WHEN checkindate GetDate() THEN GuestName ELSE CAST(NULL AS VARCHAR(20)) END) AS Rollover,
MAX(CASE WHEN checkindate = @MyDate THEN GuestName ELSE CAST(NULL AS VARCHAR(20)) END) AS Arrival,
MAX(CASE WHEN checkoutdate = @Mydate THEN GuestName ELSE CAST(NULL AS VARCHAR(20)) END) AS Departure --,*
FROM RESERVATIONROOM
WHERE @MyDate BETWEEN CHECKINDATE AND CHECKOUTDATE
GROUP BY RoomName
GO
This is the data in teh ReservationRoom table:
Room....Guest...........CheckinDate.....Checkout
202.....COMPANY2........29/06/2007......01/07/2007 (Arr)
AAAA....GUEST1 GUEST1...05/07/2007......07/07/2007 (N/A)
AAAA....bilal dadar.....27/06/2007......29/06/2007 (Dep)
AAAA....Mr Bond.........24/06/2007......27/06/2007 (N/A)
COPY....NEW GUY.........29/06/2007......03/07/2007 (Arr)
JKHJ....BIG GROUP.......29/06/2007......03/07/2007 (Arr)
JKHJ....GUEST1 GUEST1...05/07/2007......06/07/2007 (N/A)
MAIN....NEW GUEST.......29/06/2007......30/06/2007 (Arr)
MAIN....POWERCOMPUTERS..26/06/2007......27/06/2007 (N/A)
MAIN....AMOL DSF........21/06/2007......23/06/2007 (N/A)
SUBN....GUEST1 GUEST1...29/06/2007......01/07/2007 (Arr)
TWO.....COMPANY2........29/06/2007......30/06/2007 (Arr)
http://WWW.....GUEST1 GUEST1...29/06/2007......01/07/2007 (Arr)
http://WWW.....MARK............26/06/2007......28/06/2007 (N/A)
I expect to see the 6 arrivals and single departure
Room....Rollover........Arrival......Departure
202.....................COMPANY2.....
AAAA.................................bilal dadar
COPY....................NEW GUY......
JKHJ....................BIG GROUP....
MAIN....................NEW GUEST....
SUBN....................GUEST1 GUEST1
TWO.....................COMPANY2.....
http://WWW.....................GUEST1 GUEST1
but strangely, this is the result I get:
MyDate..........Room....Rollover........Arrival....Departure
2007-06-29......202.....COMPANY2........NULL.......NULL
2007-06-29......COPY....NEW GUY.........NULL.......NULL
2007-06-29......JKHJ....BIG GROUP.......NULL.......NULL
2007-06-29......MAIN....NEW GUEST.......NULL.......NULL
2007-06-29......SUBIN...GUEST1 GUEST1...NULL.......NULL
2007-06-29......TWO.....COMPANY2........NULL.......NULL
2007-06-29......WWW.....GUEST1 GUEST1...NULL.......NULL
- the Departure record is missing
- all Arrivals are listed under Rollover column.
Did I miss something in the code?
June 29, 2007 at 7:21 am
Hi goodguy, please can you set up your data as a series of INSERTs into a temp table (as in my first reply) so that I can exactly match your table?
Cheers
ChrisM
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
June 29, 2007 at 7:41 am
Chris, my apologies!
INSERT INTO #ViewResult
'202','COMPANY2',CAST ('29/06/2007'AS DATETIME),CAST ('01/07/2007'AS DATETIME) UNION ALL
'AAAA','GUEST1 GUEST1',CAST ('05/07/2007'AS DATETIME),CAST ('07/07/2007'AS DATETIME) UNION ALL
'AAAA','bilal dadar',CAST ('27/06/2007'AS DATETIME),CAST ('29/06/2007'AS DATETIME) UNION ALL
'AAAA','Mr Bond',CAST ('24/06/2007'AS DATETIME),CAST ('27/06/2007'AS DATETIME) UNION ALL
'COPY','NEW GUY',CAST ('29/06/2007'AS DATETIME),CAST ('03/07/2007'AS DATETIME) UNION ALL
'JKHJ','BIG GROUP',CAST ('29/06/2007'AS DATETIME),CAST ('03/07/2007'AS DATETIME) UNION ALL
'JKHJ','GUEST1 GUEST1',CAST ('05/07/2007'AS DATETIME),CAST ('06/07/2007'AS DATETIME) UNION ALL
'MAIN','NEW GUEST',CAST ('29/06/2007'AS DATETIME),CAST ('30/06/2007'AS DATETIME) UNION ALL
'MAIN','POWERCOMPUTERS',CAST ('26/06/2007'AS DATETIME),CAST ('27/06/2007'AS DATETIME) UNION ALL
'MAIN','AMOL DSF',CAST ('21/06/2007'AS DATETIME),CAST ('23/06/2007'AS DATETIME) UNION ALL
'SUBIN','GUEST1 GUEST1',CAST ('29/06/2007'AS DATETIME),CAST ('01/07/2007'AS DATETIME) UNION ALL
'TWO','COMPANY2',CAST ('29/06/2007'AS DATETIME),CAST ('30/06/2007'AS DATETIME) UNION ALL
'WWW','GUEST1 GUEST1',CAST ('29/06/2007'AS DATETIME),CAST ('01/07/2007'AS DATETIME) UNION ALL
'WWW','MARK',CAST ('26/06/2007'AS DATETIME),CAST ('28/06/2007'AS DATETIME) UNION ALL
June 29, 2007 at 8:52 am
Hi goodguy
You need to check your arrival / departure dates in your sample data set to make sure that they don't cause double-bookings, also it looks like checkindate / checkout date have become switched (or maybe it's just me).
Here's some code which sets up a larger test data set incorporating all the data which you've provided so far. You will need to amend the dates so that checkindate is before checkoutdate, and also amend dates where necessary to eliminate double-booking - there are some!
Then run the query. If the query is still not producing the results you expect, it's easy for you to post everything back up here so it can be checked exactly as you are using it.
--create some sample data SET DATEFORMAT DMY DROP TABLE #ViewResult
CREATE TABLE #ViewResult (roomname CHAR(5), Pax tinyint, GuestName VARCHAR(20), checkoutdate DATETIME, checkindate DATETIME, CLIENT VARCHAR(20) , status int, Conf int)
INSERT INTO #ViewResult (roomname, Pax, GuestName, checkoutdate, checkindate, CLIENT, status, Conf)
SELECT 'MAIN', 1, 'AMOL DSF', CAST ('23/06/2007' AS DATETIME), CAST ('21/06/2007' AS DATETIME), 'AMOL DSF', 3, 0 UNION ALL
SELECT 'MAIN', 1, 'POWERCOMPUTERS', CAST ('30/06/2007' AS DATETIME), CAST ('26/06/2007' AS DATETIME), 'POWERCOMPUTERS', 3, 3 UNION ALL
SELECT 'AAAA', 1, 'bilal dadar1', CAST ('29/06/2007' AS DATETIME), CAST ('28/06/2007' AS DATETIME), 'AMOL DSF', 3, 0 UNION ALL
SELECT 'AAAA', 2, 'Mr Bond', CAST ('28/06/2007' AS DATETIME), CAST ('24/06/2007' AS DATETIME), 'SDF ASDF', 3, 3 UNION ALL
SELECT '202', 1, 'COMPANY2',CAST ('01/07/2007' AS DATETIME),CAST ('29/06/2007' AS DATETIME), 'COMPANY2', 3, 0 UNION ALL
SELECT 'AAAA', 1, 'GUEST1 GUEST1',CAST ('07/07/2007' AS DATETIME),CAST ('05/07/2007' AS DATETIME), 'GUEST1 GUEST1', 3, 0 UNION ALL
SELECT 'AAAA', 1, 'bilal dadar2',CAST ('29/06/2007' AS DATETIME),CAST ('27/06/2007' AS DATETIME), 'bilal dadar', 3, 0
INSERT INTO #ViewResult (roomname, Pax, GuestName, checkindate, checkoutdate, CLIENT, status, Conf)
SELECT 'AAAA', 1, 'Mr Bond',CAST ('24/06/2007' AS DATETIME),CAST ('27/06/2007' AS DATETIME), 'Mr Bond', 3, 0 UNION ALL
SELECT 'COPY', 1, 'NEW GUY',CAST ('29/06/2007' AS DATETIME),CAST ('03/07/2007' AS DATETIME), 'NEW GUY', 3, 0 UNION ALL
SELECT 'JKHJ', 6, 'BIG GROUP',CAST ('29/06/2007' AS DATETIME),CAST ('03/07/2007' AS DATETIME), 'BIG GROUP', 3, 0 UNION ALL
SELECT 'JKHJ', 1, 'GUEST1 GUEST1',CAST ('05/07/2007' AS DATETIME),CAST ('06/07/2007' AS DATETIME), 'GUEST1 GUEST1', 3, 0 UNION ALL
SELECT 'MAIN', 1, 'NEW GUEST',CAST ('29/06/2007' AS DATETIME),CAST ('30/06/2007' AS DATETIME), 'NEW GUEST', 3, 0 UNION ALL
SELECT 'MAIN', 1, 'POWERCOMPUTERS',CAST ('26/06/2007' AS DATETIME),CAST ('27/06/2007' AS DATETIME), 'POWERCOMPUTERS', 3, 0 UNION ALL
SELECT 'MAIN', 1, 'AMOL DSF',CAST ('21/06/2007' AS DATETIME),CAST ('23/06/2007' AS DATETIME), 'AMOL DSF', 3, 0 UNION ALL
SELECT 'SUBIN', 1, 'GUEST1 GUEST1',CAST ('29/06/2007' AS DATETIME),CAST ('01/07/2007' AS DATETIME), 'GUEST1 GUEST1', 3, 0 UNION ALL
SELECT 'TWO', 1, 'COMPANY2',CAST ('29/06/2007' AS DATETIME),CAST ('30/06/2007' AS DATETIME), 'COMPANY2', 3, 0 UNION ALL
SELECT 'WWW', 1, 'GUEST1 GUEST1',CAST ('29/06/2007' AS DATETIME),CAST ('01/07/2007' AS DATETIME), 'GUEST1 GUEST1', 3, 0 UNION ALL
SELECT 'WWW', 1, 'MARK',CAST ('26/06/2007' AS DATETIME),CAST ('28/06/2007' AS DATETIME), 'MARK', 3, 0
-- check the sample data SELECT * FROM #ViewResult ORDER BY roomname, checkoutdate
-- set up a variable for today
DECLARE @Today DATETIME
SET @Today = CAST ('29/06/2007' AS DATETIME)
-- run the report query SELECT roomname, MAX(CASE WHEN checkindate <> @Today AND checkoutdate <> @Today THEN GuestName ELSE CAST(NULL AS VARCHAR(20)) END) AS Rollover, MAX(CASE WHEN checkindate = @Today THEN GuestName ELSE CAST(NULL AS VARCHAR(20)) END) AS Arrival, MAX(CASE WHEN checkoutdate = @Today THEN GuestName ELSE CAST(NULL AS VARCHAR(20)) END) AS Departure --, * FROM #ViewResult WHERE @Today BETWEEN checkindate AND checkoutdate GROUP BY roomname ORDER BY roomname
Cheers
ChrisM
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
June 30, 2007 at 2:59 am
July 2, 2007 at 7:19 am
You're welcome. Zanzibar? Blimey! "I was in the middle of writing this really nasty sp when a warthog waddled past my window..."
Beats Surbiton, we only get parakeets.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
July 2, 2007 at 8:00 am
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply