SELECT to find days between dates

[rojariggs]

Hi

I have been going round in circles a bit on this one so hopefully someone can help!

I have a table which contains records that have fields for start and finish dates. For most records, the start and finish dates are the same but there are some where the start and finish date may be a day or several days apart.

I am trying to write a SELECT statement that will find records for a given date. Obviously, the records that have start or finish dates on that given date are easy to find but I am struggling with finding those records that start and finish either side of the date.

For example:
- The user searches for {11/05/2010}.
- I want to find all records that start or finish on the {11/05/2010}
- A record has a StartDate of {03/10/2010} and a FinishDate of {15/10/2010}. I want this record to be found too and any others that start and finish either side of the desired date.

Does this make sense? Any thoughts?

Cheers

Rog

 

[rojariggs]

OK. I've sorted the between thing with the start and finish date now and works OK. I do however have another complication. The database in question has five date fields in total:

StartDate
PickupDate
ArrivalDate
ReturnDate
FinishDate

What I need to do is return any records where the date entered by the user falls between any of the five date fields. I think I need a command that mixes BETWEEN with INLIST in some way but I can't find anything.

Any advice appreciated.

Cheers

Rog

 

[rojariggs]

All done now! I think just writing it down on here gave me the clarity I needed to get the logic the right way round. It wasn't difficult but I couldn't see the wood for the trees (or the answer for the code if that's a better analogy!).

Anyway, in case it helps anyone else, this was the solution:

SELECT JobID,HireDate,StartTime,PickupTime,FinishTime,Driver1,Origin,Dest,Vehicle,Seats,Type,Single,Confirmed,Paid ;
			FROM Job ;
			WHERE (HireDate = jcDate ;
					OR BETWEEN(jcDate, MIN(StartDate, PickupDate, ArrivalDate, ReturnDate, FinishDate), MAX(StartDate, PickupDate, ArrivalDate, ReturnDate, FinishDate))) ;
					AND Confirmed = .T. ;
			ORDER BY HireDate ;
			INTO ARRAY jaSearchTemp

Sorry to have bothered you!

Cheers

Rog

 

[Mike Lewis]

Rog,

I know what you mean about "just writing it down here gave me the clarity I needed". That happens to all of us at times.

Anyway, I'm glad you solved the problem. Also, it's good that you posted the solution, in case anyone else has a similar problem.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro tips, advice, training, consultancy
Custom software for your business

 

[Olaf Doschke]

Looking at your code I still think there is some ill logic. You're simply taking MIN and MAX of all the date fields in your record, but certain dates might only be considered for MIN and some only for MAX. That will not hurt much, but what might hurt is if some of the dates are NULL or EMPTY.

EMPTY for example is considered lower than any other date, so MIN(...) would yield empty if any of the date fields is still empty and therefore wouldn't yield a limitation for a real minimum date.

Here some examples:

? Min({},Date())
? Min(.NULL.,{})
? Min({},.NULL.)
? Min(Date(),.NULL.)

If .NULL. is involved, this is what Between yields:
? Between(Date(),.NULL.,{})
? Between(Date(),{},.NULL.)

If Empty() is the minimum then any Date will be higher, but if the Startdate is all that is set and the Finishdate is empty, then Max would be Startdate too, and you wouldn't have that record in the result, when querying for any date after the startdate, even though the delivery has not yet finished.

It depends on what you want to query, if you only consider records with a start and finishdate anyway, then that might be fine, if not, EMPTY and NULL dates need to be considered.

Bye, Olaf.

 

[rojariggs]

You make a good point Olaf but all of the date fields are completed programatically when the records are created. If for some reason a field is blank (EMPTY or NULL), error handling after the SELECT has run then catches any blank variables.

Cheers

Rog