Difficult Date Issue - 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)

Difficult Date Issue

Author

Topic

Bryce Covert
Starting Member

12 Posts

Posted - 2005-07-08 : 15:27:17

I am having the hardest time with this problem. Basically, I am developing a database that allows 'residents' to move into 'rooms.' However, there can be multiple residents in the same room at the same time. So for several reports, I just want to know when someone was in there at all or not, and that I don't care about who. Now, I store these sort of things in a transaction table with a room, resident, move in date, and a move out date.

Basically, my question is: Is it possible to summarize this data so that I'll get something like this?

Data:

Room Resident MoveInDate MoveOutDate
123 1 1/5/2005 1/10/2005
123 2 1/7/2005 1/15/2005
123 3 1/20/2005 1/25/2005
As you can see, there are two residents living in there at the same time for a few days.

I need to somehow group/filter/something to get this data to appear as a sort of "room" transaction rather than by the resident. So it would look like this.

Room MoveInDate MoveOutDate
123 1/5/2005 1/15/2005
123 1/20/2005 1/25/2005

Does this make sense? I can't seem to find any easy way of doing this whatsoever.

Thanks,

Bryce

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-07-08 : 16:21:48

How complex can overlaps be. How example, would this be a single transaction?


Room  Resident MoveInDate  MoveOutDate
123   1        01/05/2005  01/10/2005
123   2        01/07/2005  01/15/2005
123   3        01/14/2005  01/22/2005
123   4        01/04/2005  01/11/2005
123   5        01/21/2005  01/30/2005

CODO ERGO SUM

Bryce Covert
Starting Member

12 Posts

Posted - 2005-07-08 : 16:43:32

Yes. I'm afraid so. Although, the software limits there to be 4 residents at any given time. There still could be more than four records though, with something like this (imagine the lines are residents, and how they cross over).

1 -------------------
2 -------------------------
3 --------------------
4 ----------
5 -------

Any ideas?

Thanks.

Bryce Covert
Starting Member

12 Posts

Posted - 2005-07-08 : 16:46:30

Yeah... it didn't take that extra whitespace

1 -------------------
2 xxxxxxxxxxxxxx-------------------------
3 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx--------------------
4 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx ----------
5 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx-------

This all would have to come up as one "transaction".
thanks again,

Bryce

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-07-08 : 18:03:36

Create a table ROOM_DAY that is just a list of rooms and days, and join to the table you showed in you post where room is equal to room and date >= MoveInDate and day <= MoveOutDate.

Then with there results of that query, you can identify a transaction start point as a day where all MoveInDates = day and a transaction end point as a day where all MoveOutDates = date. Then you just have to match the transaction start points for each room with the earliest following transaction end point for the same room. Simple huh?

CODO ERGO SUM

nathans
Aged Yak Warrior

938 Posts

Posted - 2005-07-08 : 18:17:44

quote:
there can be multiple residents in the same room at the same time.

Sounds kinky... what kind of place are you running??!

Heres a way detailed in an old SQL Mag... You might Google it to try and find the article, I remember their solution being a bit more elegant than this :)

1. Find the move-ins of rooms that have no other move-ins started earlier and ended after that start time.

2. Then find the move-outs where no other move-out ended later and started before that start time.

declare @deez table (deezID INT identity(1,1), room int, resident int, moveInDate datetime, moveOutDate datetime)
insert into @deez (room, resident, moveInDate, moveOutDate)
        select	1, 69, '1/5/2005',	'1/10/2005' union	
	select  1, 23, '1/7/2005',	'1/15/2005' union
        select  1, 69, '1/20/2005',	'1/25/2005' union
        select	2, 34, '2/1/2005', 	'2/4/2005' union	
	select  2, 76, '2/2/2005',	'2/15/2005' union
        select  2, 76, '2/13/2005',	'2/20/2005'


-- 1. find move_ins
(SELECT DISTINCT room, moveInDate
FROM @deez AS d1
WHERE NOT EXISTS
  (SELECT *
   FROM @deez AS d2
   WHERE d2.room = d1.room
     AND d2.moveInDate < d1.moveInDate
     AND d2.moveOutDate >= d1.moveInDate))



-- 2. find move_outs
(SELECT DISTINCT room, moveOutDate
FROM @deez AS d1
WHERE NOT EXISTS
  (SELECT *
   FROM @deez AS d2
   WHERE d2.room = d1.room
     AND d2.moveOutDate > d1.moveOutDate
     AND d2.moveInDate <= d1.moveOutDate))





-- find contiguous intervals
SELECT room, moveInDate, (	SELECT MIN(moveOutDate)
   				FROM (	SELECT DISTINCT room, moveOutDate
					FROM @deez AS d1
					WHERE NOT EXISTS (	SELECT * 
								FROM @deez AS d2 
								WHERE d2.room = d1.room 
									AND d2.moveOutDate > d1.moveOutDate
									AND d2.moveInDate <= d1.moveOutDate)) AS mo
   				WHERE mo.room = mi.room
     					AND moveOutDate >= moveInDate) AS moveOutDate
FROM (	SELECT DISTINCT room, moveInDate
	FROM @deez AS d1
	WHERE NOT EXISTS (	SELECT * 
				FROM @deez AS d2
   				WHERE d2.room = d1.room
     					AND d2.moveInDate < d1.moveInDate
     				AND d2.moveOutDate >= d1.moveInDate)) AS mi

Nathan Skerl

Bryce Covert
Starting Member

12 Posts

Posted - 2005-07-11 : 11:11:54

Nathan, this works great!

I was deep into working on this on Friday. It's early in the morning here now, and I'm getting started on this problem again. To be honest, I don't even understand how this process works with the data. I'm guessing it's because it's so early in the morning :)

Thanks again,
Bryce

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources