Compare periods of time within a table 3

[german12]

Compare periods of time.

I have a table of periods
(fields "von" - "bis" in german means "from ... to")
The table is sorted by the beginning of a period.
see photo.
Structure:

this is the file:

As you can see, some dates (not all) collide - or they overlap with others in the table.
The file contains the memo field "note" for this purpose.
Now I would like to have a program that writes into the memo field "note" whether ... and if so ... the respective date overlaps with other (which?) Periods in the table.
I think that you can do this with the BETWEEN function - and have also tried Scan..Endscan, but the data pointer
is immediately at the end of the file when I use Scan..endscan.

Does anyone have any idea what the loop or a code should look like?

Thank you in advance.
Klaus


Peace worldwide - it starts here...

 

[mm0000]

 USE termine
 replace ALL note WITH "" && initialize note field 
 LOCATE 
  DO WHILE NOT EOF()
    STORE RECNO() to m.rec
    STORE von TO m.von
    STORE bis TO m.bis
    STORE location TO m.location
    STORE event TO m.event
    IF NOT EOF()
       skip
    ELSE
       EXIT
    endif   
    DO WHILE NOT EOF() &&check every record for overlapping dates
       STORE  von TO m.von1
       STORE bis TO m.bis1
       STORE RECNO() TO m.rec1
       STORE location TO m.location1
       STORE event TO m.event1
       IF BETWEEN(m.von,von,bis) OR BETWEEN(m.bis,von,bis) && if overlapping date found
          replace note WITH ALLTRIM(note) +"Overlapping with "+ALLTRIM(m.location)  + " "+ALLTRIM(m.event) + " " +DTOC(m.von) +" "+ DTOC(m.bis)+CHR(13) &&Replace reference record "Note" field with info 
          GO m.rec
          replace note WITH ALLTRIM(note) +"Overlapping with "+ALLTRIM(m.location1) + " "+ALLTRIM(m.event1) + " "+DTOC(m.von1) + " "+ DTOC(m.bis1) + CHR(13) && replace current record "Note" field with info
          GO m.rec1
       ENDIF
       skip
   ENDDO
       GO m.rec
       IF NOT EOF()
          SKIP
       endif   
  ENDDO

This is my understanding based on your description. Not tested - let me know if there are any issues with the code.

 

[mjcmkrsr]

Hi,

Please have a look at the code below

PUBLIC go_Form

SET MULTILOCKS ON 

go_Form = CreateObject("frmForm")
go_Form.Visible = .T.
go_Form.Show


READ Events
CLOSE ALL
CLEAR ALL


DEFINE CLASS frmForm As Form
	Width = 480
	MaxWidth = 480
	MinWidth = 480
	Height = 480
*!*		MaxHeight = 480
	MinHeight = 480
	AutoCenter = .T.
	Themes = .F.
	Caption = "Distinct dates - overlapping dates are not allowed"

	Add Object grdDates as Grid WITH ;
	    RecordSource = "tblDates", ColumnCount = 2, Visible = .T., Top = 24, Left = 24, Height = 480 - (24 + 12 + 30 + 12), ;
    	RowHeight = 24, Width = 456, DeleteMark = .F., BackColor = RGB(186, 186, 186), Anchor = 15
    	
    	PROCEDURE grdDates.Init()
    		
    		WITH This
    			.Column1.Header1.Caption = "Entry date"
    			.Column2.Header1.Caption = "Exit date"
    		ENDWITH 

    	ENDPROC 
    	
	ADD OBJECT cmdSave as CommandButton WITH ;
		Width = 60, Height = 30, Left = 24, Top = 480 - 42, Caption = "Save", Anchor = 6    	
    	
    	PROCEDURE cmdSave.Click()
    		LOCAL llOK, liReverted 
    	   	LOCAL ARRAY laDates[1]
    	   	
    	   	llOK = .T.
    	   	
				Begin TransAction
					= TableUpdate(.T., .T., "tblDates")

					Select dDate1, dDate2 ;
						From tblDates ;
						Order By dDate1 ;
						Into Array laDates
						
					For i = 1 to ALEN(laDates) - 1
						If laDates(i) >= laDates(i + 1) 
							= MessageBox(TRANSFORM(laDates(i)) +" overlaps " + TRANSFORM(laDates(i + 1)), 64, "Modification")
							llOk = .F.
						Endif
					Endfor


				If llOK
					End TransAction
					
				Else
					RollBack
					liReverted = TableRevert(.T.,"tblDates")
					
					WAIT WINDOW + TRANSFORM(liReverted) NOWAIT && just fyi

				Endif	
				
			ThisForm.grdDates.Refresh()
			
	    ENDPROC 	
    	
	ADD OBJECT cmdExit As CommandButton WITH ;
    	Width = 60, Height = 30, Left = 156, Top = 480 - 42, Caption = "Exit", Anchor = 6
    
		PROCEDURE cmdExit.Click()
			CLEAR Events
			ThisForm.Release
		ENDPROC
	  

		PROCEDURE Destroy()
			CLEAR Events
			ThisForm.Release
		ENDPROC

		PROCEDURE Load
			IF NOT FILE("dbcRooms.dbc")
				CREATE DATABASE dbcRooms
			
				CREATE TABLE tblDates (dDate1 D, dDate2 D)
			
				INSERT INTO tblDates VALUES (DATE() - 10, DATE() - 7)
				INSERT INTO tblDates VALUES (DATE() - 6, DATE() - 1)
				INSERT INTO tblDates VALUES (DATE(), DATE() + 1)
				
				LOCATE 

			ELSE
				OPEN DATABASE dbcRooms
				USE tblDates 

			ENDIF 

			CURSORSETPROP("Buffering",5,"tblDates")

		ENDPROC	    
ENDDEFINE

hth

Mark

 

[german12]

mmm0000 and Mark:

Many thanks for you both and your very quick answers.
I didn't think it would be that fast. Hats off!
First of all, I tested your mm0000 program.
(Mark: I'll test your program tonight.)

mm000:
To see the result of your program, I have added a "result" field to the table and added
two program-lines at the end of your code. (orange marked)

The table now also shows under "result" what has landed in the "Note" memo field.

Your program is almost ok, but as you can see, under record No.4 (Impfung,result has a mark) there is no entry, although there is a collosion with record No. 3. (When I am in Magdeburg,I can't be at the same time available for "Impfung" = german word for "vaccination.")
That's all I can see in the moment, I think this little bug can still be eliminated.

Regards
Klaus



Peace worldwide - it starts here...

 

[mm0000]

Modified code which i think eliminates the bug....

 USE termine
 replace ALL note WITH "" && initialize note field 
 LOCATE 
  DO WHILE NOT EOF()
    STORE RECNO() to m.rec
    STORE von TO m.von
    STORE bis TO m.bis
    STORE location TO m.location
    STORE event TO m.event
    IF NOT EOF()
       skip
    ELSE
       EXIT
    endif   
    locate
    DO WHILE NOT EOF()
        IF RECNO() = m.rec
          SKIP
       endif    
       STORE  von TO m.von1
       STORE bis TO m.bis1
       STORE RECNO() TO m.rec1
       STORE location TO m.location1
       STORE event TO m.event1
       IF BETWEEN(m.von,von,bis) OR BETWEEN(m.bis,von,bis) && if overlapping date found
          replace note WITH ALLTRIM(note) +"Overlapping with "+ALLTRIM(m.location)  + " "+ALLTRIM(m.event) + " " +DTOC(m.von) +" "+ DTOC(m.bis)+CHR(13) &&Replace reference record note with info 
          GO m.rec
          replace note WITH ALLTRIM(note) +"Overlapping with "+ALLTRIM(m.location1) + " "+ALLTRIM(m.event1) + " "+DTOC(m.von1) + " "+ DTOC(m.bis1) + CHR(13) && replace current record note with info
          GO m.rec1
       ENDIF
       IF NOT EOF()
          skip
       endif
   ENDDO
       GO m.rec
       IF NOT EOF()
          SKIP
       endif   
  ENDDO

 

[Chris Miller]

No idea why my answers here all get deleted. Just note SQL has a simpler solution here.




Chriss

 

[german12]

mm0000 - Congratulations - you did it!

As you can see on the hardcopy under "Result", it is immediately apparent whether a period overlaps with an independent period - or not.
If there is nothing there, then the appointment can be accepted.

You don't even have to leaf through a calendar - with many appointments, in my opinion, it is even faster to check availability and can also be managed and maintained by many people at the same time.
And - also welcome - the file hast not to be sorted by date or so.
I tested that too.

Thanks/Danke once again!

Klaus

Peace worldwide - it starts here...

 

[german12]

Chriss - it is very interesting - and especially for me as a beginner - maybe also for other readers - very instructive, how many possible solutions there are for this case. I very much hope that your contribution will still appear here.

Klaus

Peace worldwide - it starts here...

 

[Chris Miller]

Ok, now that my post remained here, I trust this will also last:

Close Tables All

Cd (Getenv('TEMP'))

Erase termine.*

Create Table termine (Id Int Autoinc, von D, bis D, Note M)

Insert Into termine (von, bis) Values ({^2021-01-01}, {^2021-03-31})
Insert Into termine (von, bis) Values ({^2021-02-01}, {^2021-02-28})
Insert Into termine (von, bis) Values ({^2021-02-28}, {^2021-03-31})
Insert Into termine (von, bis) Values ({^2021-04-01}, {^2021-04-30})

Select First.Id As firstid, Second.Id As secondid From termine As First, termine As Second;
   Where Second.Id>First.Id And Not (First.bis<Second.von And First.von<Second.bis);
   into Cursor overlaps

Scan
   SELECT termine
   LOCATE FOR id=overlaps.firstid
   Replace note WITH note+'conflict with id '+TRANSFORM(overlaps.secondid)+CHR(13)+CHR(10)
   
   LOCATE FOR id=overlaps.secondid
   Replace note WITH note+'conflict with id '+TRANSFORM(overlaps.firstid)+CHR(13)+CHR(10)
Endscan

This relies on every range having von<=bis dates, but that's surely the case and could be enforced with a table rule.
More interesting: The query done for just one (new) event range (von,bis) put into variables can tell you if there are overlaps:

newvon={2022-01-01}
newbis={2023-01-01}

Select Termine.Id As terminid, From termine;
   Where Not (Termine.bis<m.newvon And Termine.von<m.newbis);
   into Cursor overlaps

If _tally = 0 
   ? 'ok, no conflicts'
else
   ? 'the new event overlaps with these events'
   browse
endif

Doing such checks on a whole table will not scale up very well, while only needing to check single new events with all existing stays fast (with indexes on von and bis). And this is a very general concept that's also applied to referential inttegrity cheks, or table and field rules. An update of course also needs the same care, but just for the (one) updated event.

If you'd use datetimes you could have multiple events at the same dates, unless the time between events is too short to commute from one to the other location. I'd change dates to dateteimes for that reason, because the overlap check logic is the same, allows same day events (in the morning and afternoon, for example) and so is more precise.



Chriss

 

[german12]

Mark:
Thank you also for your contribution.
I have tested your program with different time periods - and could not find any errors. Your program prevents overlapping periods of time at the latest when they should be saved.
For me, the structure with object-oriented commands in your program is also very instructive.
I still have a lot to learn here, but of course I can also try out a lot based on your example.
Many Thanks.
Klaus

Peace worldwide - it starts here...

 

[german12]

Chriss -
I would also like to try out your program.
But I'm on my way back from vacation at the moment.
I'll come back to that later.
But now thank you for your effort.
Klaus

Peace worldwide - it starts here...

 

[german12]

Hi Chris,
Now I've had time to test your program. Thanks a lot for this.
As I've seen, it works flawlessly, and it's also surprisingly short.

For me as a beginner, the SQL statement is very sophisticated.
In order to better understand this SQL statement, I have shown the consequences in the program step by step -4 steps- for myself

This is the sample "Termine" with 4 records.
(I added one field more to see, what happens later)

Then I used a part of your SQL-Statement (without a condition)

Select First.Id As firstid, Second.Id As secondid From termine As First, termine As Second into Cursor overlaps

This creates a comparison from one date to all others.
(Could lead to a long list, depends on how many dates the file termine has, but your code immediately reduces the output due to its condition.

...but your filter (condition)
Where Second.Id>First.Id And Not (First.bis<Second.von And First.von<Second.bis
reduces that amount of possibilities to those where there is a conflict.

then you compare with a scan...endscan all records in file termine with your temporary cursor overlaps to find conflicts and writes them into field note of file termine.
Notes are visible in Field "result" which I added.

.Phew ...

It's really very efficient, but I think I have to
internalized in me more often - but it is a good example of the tremendous possibilities of SQL with the use of a temporary and an editable file.

Thank you very much, Chris!

Klaus

PS
I would also like to thank you for the additional hint (using date / time), which enables an even more precise comparison between time periods.
Also the reference to a quick comparison because of the indexing and the comparison of a new data record to be entered
with the already existing records is very convincing.






Peace worldwide - it starts here...

 

[Chris Miller]

Thanks Klaus!

You gave a very nice breakdown of the single steps.

Note that Rushmore optimization of Second.Id>First.Id alone already reduces the result to 50%, as you surely will always have an index on an id field. So indexes don't just optimize equal comparisons with = , also larger or small than. And the VFP query engine doesn't just do this as aftermath, it reduces the fetched records before they are fetched. For the price of reading in the CDXes, but in the usual case that's less costs than actually first fetching rows and checking conditions as aftermath.

Amusingly, I now don't get my own overlap check conditions, but as you did you can always add new events for seeing whether their overlap with others is detected correctly.



Chriss