Searching algorithm solution needed...

[ozos]

Hello guys,

I have a timetabling system and I need to find a way to find x number of days where there are no appointments for a given person. For example, say person A wants to take a 5 day holiday but they need to first see if there are five consecutive days where they have no appointments in the database, the person could enter the number of days and the code would presumably load all of their appointment dates (connect to database and query using SQL) and use some sort of search to find if indeed there are some days free.

If anyone has any code or advice on this it would be greatly appreciated.

Best,
Ozos

[Shaggy Hiker]

For what you described, I don't think performance would be much of an issue, so you could do the query as you described, put the values into a List(of Date), then iterate through the list checking the gap between each two dates. For that to work, the list would have to be sorted, but that would best be done in the query itself. Comparing two dates is simple enough, as you can just subtract the smaller from the larger, which gives you a TimeSpan, and a TimeSpan has a TotalDays property, which would give you the information you needed.

[ozos]

That sounds like a great plan and makes perfect sense, appreciate the help, I will give it a go and see how I get on.

[szlamany]

You can actually make the query return the number of days between "each" appointment.

You SELECT the appt rows for a person and JOIN to the appt row for that person with the MIN() date that is GREATER than the date of that appt row.

Select A1.User,A1.ApptDate
                  ,A2.ApptDate "NextAppt"
                  , DateDiff(dd,A1.ApptDate,A2.ApptDate) "DiffDays"
   From Appt A1
   Left Join Appt A2 on A2.User=A1.User
                  and A2.Apptdate=(Select Min(AX.ApptDate) From Appt AX
                                     Where AX.User=A1.User and AX.ApptDate>A1.Apptdate)
   Where A1.User='smith'

If a User can have several appt's on a given date it would be better to use a VIEW that collapses all those rows into a single DISTINCT USER, APPTDATE row.

[ozos]

You can actually make the query return the number of days between "each" appointment.

You SELECT the appt rows for a person and JOIN to the appt row for that person with the MIN() date that is GREATER than the date of that appt row.

Select A1.User,A1.ApptDate
                  ,A2.ApptDate "NextAppt"
                  , DateDiff(dd,A1.ApptDate,A2.ApptDate) "DiffDays"
   From Appt A1
   Left Join Appt A2 on A2.User=A1.User
                  and A2.Apptdate=(Select Min(AX.ApptDate) From Appt AX
                                     Where AX.User=A1.User and AX.ApptDate>A1.Apptdate)
   Where A1.User='smith'

If a User can have several appt's on a given date it would be better to use a VIEW that collapses all those rows into a single DISTINCT USER, APPTDATE row.

Excellent, cheers guys. With a little tweek I got the SQL working. Clever stuff.

[szlamany]

Excellent, cheers guys. With a little tweek I got the SQL working. Clever stuff.

Once you get these tricks in your tool belt you have them for life!

Glad you got a solution that worked!