Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Chris Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

non booked rooms 1

Status
Not open for further replies.
mjcmkrsr

mjcmkrsr

Technical User
Nov 30, 2010
840
Hi,

This is a students home booking app and I try to find the non booked periods.

The data looks like this

SELECT cHCode, dEntry, dExit, cName .... into ... vFreeRooms. The result is ordered by cHCode, dExit

1107.05 1/12/2013 15/12/2013 xxxxx
1107.05 1/1/2014 31/1/2014 xxxxx
1107.05 1/2/2014 28/2/2014 xxxxx
1107.05 16/3/2014 31/3/2014 xxxxx

and I need these results - the dates a room is booked followed by the non booked period

1107.05 1/12/2013 15/12/2013 16/12/2013 - 31/12/2013
1107.05 1/2/2014 28/2/2014 1/3/2014 - 15/3/2014
1107.05 16/3/2014 31/3/2014 1/4/2014 -

I achieve this thru the code below, which I consider not very elegant. Is there a sql command which would produce the same result. Any hint is appreciated.

btw oApp.dBofMonth is the first day of the actual month

Code: 
SCAN
     dDateOut = dExit 
     cRoom1 = cHCode
     SKIP   
     dDateIn = dEntry 
     cRoom2 = cHCode
     SKIP -1
     
     IF cRoom1 = cRoom2
          IF dDateOut + 1 = dDateIn
                DELETE NEXT 1
          ELSE
                 IF dDateIn <= oApp.dBofMonth(DATE()) 
                      DELETE NEXT 1
                 ELSE
                      REPLACE vFreeRooms.cName WITH DTOC(dDateOut + 1) +" - "+ DTOC(dDateIn - 1)
                 ENDIF
          ENDIF
     ELSE
          REPLACE vFreeRooms.cName WITH DTOC(dDateOut + 1) +" - "

     ENDIF
ENDSCAN

Thks

MK
 
Sort by date Sort by votes
I had a similar problem a couple of years ago. The following is the code that I used to solve it. In this case, the table is called Events, and the start and end dates are in Start_Date and End_Date respectively.

Code: 
select b.End_date+1 as Gap_Start, min(a.Start_Date) as Gap_End
from events a
join
(
select a.End_date
from events a
where not exists 
  (select * from events b where a.End_date+1 between b.Start_Date and 
   b.End_Date)
and a.End_Date <> (select max(End_date) from Events) 
) b
on a.Start_Date > b.End_date + 1
group by b.End_Date
order by 2, 1

Note that I pasted the above directly from my application. It might not exactly match your requirements, but it should give you a good starting point.

That said, given that you already have Foxpro code that does what you want, you might prefer to stick with that. My code does the whole thing is a single command, but I'll bet that most people will find your version easier to understand.

Mike




__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads
 
Upvote 0 Downvote
Thanks Mike,

I'll give it a try and keep you updated

MK
 
Upvote 0 Downvote

This one is tested with your sample data:

Code: 
create table vfreeRooms ( cHCode c(7), dEntry d, dExit d ) 
odate=set('date')

set date dmy 

insert into vFreeRooms (chCode,dEntry,dExit ) values ( '1107.05', {01/12/2013},{15/12/2013})
insert into vFreeRooms (chCode,dEntry,dExit ) values ('1107.05', {01/01/2014},{31/1/2014}) 
insert into vFreeRooms (chCode,dEntry,dExit ) values ('1107.05', {01/02/2014},{28/2/2014}) 
insert into vFreeRooms (chCode,dEntry,dExit ) values ('1107.05', {16/03/2014},{31/3/2014})

set date (oDate)

select ;
chCode,dEntry,dExit,;
dExit+1 as next_dEntry,;
( select min(b.dEntry)-1 from vFreeRooms b ;
where b.dEntry > a.dExit ) as next_dExit ;
from ;
vFreeRooms a ;
where ;
dExit+1 not in ( select dEntry from vFreeRooms )

Marco Plaza
 
Upvote 0 Downvote
Hi Marco,

Works like a charm. Thanks a million.

br

MK
 
Upvote 0 Downvote
Hi Marco,

I was sort of too quick. Your code does not give the desired results - your Next_dExit is in fact the highest dEntry + 1 which might be true in certain cases but not always. Please see below.

Code: 
LOCAL dDateOut, dDateIn, cRoom1, cRoom2

CREATE CURSOR vvFreeRooms (cHCode C(7), dEntry D, dExit D, cOpen C(25))

INSERT INTO vvFreeRooms (cHCode, dEntry, Dexit, cOpen) VALUES ("1107-01",{^2014-03-01},{^2014-06-30},"")
INSERT INTO vvFreeRooms (cHCode, dEntry, Dexit, cOpen) VALUES ("1107-02",{^2014-01-01},{^2014-06-30},"")
INSERT INTO vvFreeRooms (cHCode, dEntry, Dexit, cOpen) VALUES ("1107-03",{^2013-09-01},{^2014-08-31},"")
INSERT INTO vvFreeRooms (cHCode, dEntry, Dexit, cOpen) VALUES ("1107-04",{^2013-09-16},{^2014-08-31},"")
INSERT INTO vvFreeRooms (cHCode, dEntry, Dexit, cOpen) VALUES ("1107-05",{^2014-03-01},{^2014-06-30},"")
INSERT INTO vvFreeRooms (cHCode, dEntry, Dexit, cOpen) VALUES ("1107-06",{^2013-10-01},{^2013-12-15},"")
INSERT INTO vvFreeRooms (cHCode, dEntry, Dexit, cOpen) VALUES ("1107-06",{^2014-01-01},{^2014-06-30},"")
INSERT INTO vvFreeRooms (cHCode, dEntry, Dexit, cOpen) VALUES ("1107-07",{^2014-02-01},{^2014-06-30},"")
INSERT INTO vvFreeRooms (cHCode, dEntry, Dexit, cOpen) VALUES ("1107-08",{^2014-03-01},{^2014-05-31},"")
INSERT INTO vvFreeRooms (cHCode, dEntry, Dexit, cOpen) VALUES ("1107-09",{^2014-05-01},{^2014-05-31},"")
INSERT INTO vvFreeRooms (cHCode, dEntry, Dexit, cOpen) VALUES ("1107-10",{^2014-04-01},{^2014-08-15},"")
INSERT INTO vvFreeRooms (cHCode, dEntry, Dexit, cOpen) VALUES ("1107-10",{^2014-09-01},{^2014-09-30},"")
INSERT INTO vvFreeRooms (cHCode, dEntry, Dexit, cOpen) VALUES ("1107-10",{^2014-10-01},{^2014-11-30},"")


SCAN
     dDateOut = dExit 
     cRoom1 = cHCode
     SKIP   
     dDateIn = dEntry 
     cRoom2 = cHCode
     SKIP -1
     
     IF cRoom1 = cRoom2
          IF dDateOut + 1 = dDateIn
                DELETE NEXT 1
          ELSE
                 IF dDateIn <= {^2013-12-01} 
                      DELETE NEXT 1
                 ELSE
                      REPLACE vvFreeRooms.cOpen WITH DTOC(dDateOut + 1) +" - "+ DTOC(dDateIn - 1)
                 ENDIF
          ENDIF
     ELSE
          REPLACE vvFreeRooms.cOpen WITH DTOC(dDateOut + 1) +" - "

     ENDIF
ENDSCAN 

LOCATE 

BROWSE NOWAIT 

select ;
chCode,dEntry,dExit,;
dExit+1 as next_dEntry,;
( select min(b.dEntry)-1 from vvFreeRooms b ;
where b.dEntry > a.dExit ) as next_dExit ;
from ;
vvFreeRooms a ;
where ;
dExit+1 not in ( select dEntry from vvFreeRooms ) 

WAIT WINDOW "Hi"
CLOSE ALL
RETURN

Thanks

MK
 
Upvote 0 Downvote
Hi Mike,

Your code throws error SQL: Correlating fields. Please see below

Code: 
LOCAL dDateOut,dDateIn,cRoom1, cRoom2

CREATE CURSOR vvFreeRooms (cHCode C(7), dEntry D, dExit D, cOpen C(25))

INSERT INTO vvFreeRooms (cHCode, dEntry, Dexit, cOpen) VALUES ("1107-01",{^2014-03-01},{^2014-06-30},"")
INSERT INTO vvFreeRooms (cHCode, dEntry, Dexit, cOpen) VALUES ("1107-02",{^2014-01-01},{^2014-06-30},"")
INSERT INTO vvFreeRooms (cHCode, dEntry, Dexit, cOpen) VALUES ("1107-03",{^2013-09-01},{^2014-08-31},"")
INSERT INTO vvFreeRooms (cHCode, dEntry, Dexit, cOpen) VALUES ("1107-04",{^2013-09-16},{^2014-08-31},"")
INSERT INTO vvFreeRooms (cHCode, dEntry, Dexit, cOpen) VALUES ("1107-05",{^2014-03-01},{^2014-06-30},"")
INSERT INTO vvFreeRooms (cHCode, dEntry, Dexit, cOpen) VALUES ("1107-06",{^2013-10-01},{^2013-12-15},"")
INSERT INTO vvFreeRooms (cHCode, dEntry, Dexit, cOpen) VALUES ("1107-06",{^2014-01-01},{^2014-06-30},"")
INSERT INTO vvFreeRooms (cHCode, dEntry, Dexit, cOpen) VALUES ("1107-07",{^2014-02-01},{^2014-06-30},"")
INSERT INTO vvFreeRooms (cHCode, dEntry, Dexit, cOpen) VALUES ("1107-08",{^2014-03-01},{^2014-05-31},"")
INSERT INTO vvFreeRooms (cHCode, dEntry, Dexit, cOpen) VALUES ("1107-09",{^2014-05-01},{^2014-05-31},"")
INSERT INTO vvFreeRooms (cHCode, dEntry, Dexit, cOpen) VALUES ("1107-10",{^2014-04-01},{^2014-08-15},"")
INSERT INTO vvFreeRooms (cHCode, dEntry, Dexit, cOpen) VALUES ("1107-10",{^2014-09-01},{^2014-09-30},"")
INSERT INTO vvFreeRooms (cHCode, dEntry, Dexit, cOpen) VALUES ("1107-10",{^2014-10-01},{^2014-11-30},"")


SCAN
     dDateOut = dExit 
     cRoom1 = cHCode
     SKIP   
     dDateIn = dEntry 
     cRoom2 = cHCode
     SKIP -1
     
     IF cRoom1 = cRoom2
          IF dDateOut + 1 = dDateIn
                DELETE NEXT 1
          ELSE
                 IF dDateIn <= {^2013-12-01} 
                      DELETE NEXT 1
                 ELSE
                      REPLACE vvFreeRooms.cOpen WITH DTOC(dDateOut + 1) +" - "+ DTOC(dDateIn - 1)
                 ENDIF
          ENDIF
     ELSE
          REPLACE vvFreeRooms.cOpen WITH DTOC(dDateOut + 1) +" - "

     ENDIF
ENDSCAN 

LOCATE 

BROWSE NOWAIT 

select a.cHCode, b.dExit + 1 as Gap_Start, min(a.dEntry) as Gap_End ;
	from vvFreeRooms a ;
	JOIN (select a.dExit from vvFreeRooms a ;
		where not exists ;
			(select * from vvFreeRooms b where a.dExit + 1 between b.dEntry and b.dExit) ;
		and a.dExit <> (select max(dExit) from vvFreeRooms)) b ;
	on a.dEntry > b.dExit + 1 ;
	group by a.cHCode, b.dExit ;
	order by 1, 2, 3


WAIT WINDOW "Hi"
CLOSE ALL
RETURN

Thanks for your input

MK
 
Upvote 0 Downvote

Forgot you have multiple rooms.. now fixed:


Code: 
CREATE CURSOR vvFreeRooms (cHCode C(7), dEntry D, dExit D, cOpen C(25))

INSERT INTO vvFreeRooms (cHCode, dEntry, Dexit, cOpen) VALUES ("1107-01",{^2014-03-01},{^2014-06-30},"")
INSERT INTO vvFreeRooms (cHCode, dEntry, Dexit, cOpen) VALUES ("1107-02",{^2014-01-01},{^2014-06-30},"")
INSERT INTO vvFreeRooms (cHCode, dEntry, Dexit, cOpen) VALUES ("1107-03",{^2013-09-01},{^2014-08-31},"")
INSERT INTO vvFreeRooms (cHCode, dEntry, Dexit, cOpen) VALUES ("1107-04",{^2013-09-16},{^2014-08-31},"")
INSERT INTO vvFreeRooms (cHCode, dEntry, Dexit, cOpen) VALUES ("1107-05",{^2014-03-01},{^2014-06-30},"")
INSERT INTO vvFreeRooms (cHCode, dEntry, Dexit, cOpen) VALUES ("1107-06",{^2013-10-01},{^2013-12-15},"")
INSERT INTO vvFreeRooms (cHCode, dEntry, Dexit, cOpen) VALUES ("1107-06",{^2014-01-01},{^2014-06-30},"")
INSERT INTO vvFreeRooms (cHCode, dEntry, Dexit, cOpen) VALUES ("1107-07",{^2014-02-01},{^2014-06-30},"")
INSERT INTO vvFreeRooms (cHCode, dEntry, Dexit, cOpen) VALUES ("1107-08",{^2014-03-01},{^2014-05-31},"")
INSERT INTO vvFreeRooms (cHCode, dEntry, Dexit, cOpen) VALUES ("1107-09",{^2014-05-01},{^2014-05-31},"")
INSERT INTO vvFreeRooms (cHCode, dEntry, Dexit, cOpen) VALUES ("1107-10",{^2014-04-01},{^2014-08-15},"")
INSERT INTO vvFreeRooms (cHCode, dEntry, Dexit, cOpen) VALUES ("1107-10",{^2014-09-01},{^2014-09-30},"")
INSERT INTO vvFreeRooms (cHCode, dEntry, Dexit, cOpen) VALUES ("1107-10",{^2014-10-01},{^2014-11-30},"")

set nulldisplay to ''
select ;
chCode,dEntry,dExit,;
dExit+1 as next_dEntry,;
( select min(b.dEntry)-1 from vvFreeRooms b ;
where b.dEntry > a.dExit and b.chCode = a.chCode ) as next_dExit ;
from ;
vvFreeRooms a ;
where ;
chCode+dtos(dExit+1) not in ( select chCode+dtos(dEntry) from vvFreeRooms )

Marco
 
Upvote 0 Downvote
Hi Marco,
Thanks a million.
BR
MarcK
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

AlliMac
  • Locked
  • Question
find non vfp file open in a non vfp application
2
Replies
32
Views
357
AlliMac
AlliMac
Rajesh Karunakaran
  • Locked
  • Question
Toolbar: How to make it non-dockable 2
Replies
6
Views
160
Chris Miller
Chris Miller
Rick C. Hodgin
  • Locked
  • Question
Greatest (non-illegal) Crime in Software
Replies
4
Views
123
Rick C. Hodgin
Rick C. Hodgin
Sadlr
  • Locked
  • Question
Need help to develop old FoxPro App made by non existing developer
Replies
18
Views
143
Sadlr
Sadlr
Goofus828
  • Locked
  • Question
Microsoft Outlook - Set Reminder to &quot;NONE&quot;
Replies
2
Views
68
Goofus828
Goofus828

Part and Inventory Search

Sponsor

Back
Top