Selecting Dates for a Schedule - Assistance Please!

Hi there

I have a list of jobs scheduled in a MySQL table, with start dates and
end dates, like so:

SchedID | JobID | StartDate | EndDate |
----------------------------------------------
1 5 2006-05-08 2006-05-09
2 8 2006-05-10 2006-05-12
3 3 2006-05-01 2006-05-19
4 9 2006-05-09 2006-05-11
5 6 2006-05-14 2006-05-19


In my web application, I have a request to show scheduled jobs between
certain dates, usually in 1-week or 2-week views.

I thought this was super easy, but I've run into a problem:
How do I show jobs scheduled for the week of 2006-05-07 to 2006-05-13?

Originally, my query was something along the line of

"SELECT * FROM SchedJobs WHERE EndDate < 2006-05-13"
....or...
"SELECT * FROM SchedJobs WHERE StartDate BETWEEN '2006-05-07' AND '2006-
05-13'"

.... but of course, both queries will fail to include the job with SchedID
3 in the result, that starts on 2006-05-01 and ends on 2006-05-19, as it
begins before the requested start date, and ends after the requested end
date.

How can I get a list of job results for everything taking place within
two selected dates?

Much thanks in advance!

Re: Selecting Dates for a Schedule - Assistance Please!

Good Man wrote:
> Hi there
>
> I have a list of jobs scheduled in a MySQL table, with start dates and
> end dates, like so:
>
> SchedID | JobID | StartDate | EndDate |
> ----------------------------------------------
> 1 5 2006-05-08 2006-05-09
> 2 8 2006-05-10 2006-05-12
> 3 3 2006-05-01 2006-05-19
> 4 9 2006-05-09 2006-05-11
> 5 6 2006-05-14 2006-05-19
>
>
> In my web application, I have a request to show scheduled jobs between
> certain dates, usually in 1-week or 2-week views.
>
> I thought this was super easy, but I've run into a problem:
> How do I show jobs scheduled for the week of 2006-05-07 to 2006-05-13?
>
> Originally, my query was something along the line of
>
> "SELECT * FROM SchedJobs WHERE EndDate < 2006-05-13"
> ...or...
> "SELECT * FROM SchedJobs WHERE StartDate BETWEEN '2006-05-07' AND
> '2006- 05-13'"
>
> ... but of course, both queries will fail to include the job with
> SchedID 3 in the result, that starts on 2006-05-01 and ends on
> 2006-05-19, as it begins before the requested start date, and ends
> after the requested end date.
>
> How can I get a list of job results for everything taking place within
> two selected dates?
>
> Much thanks in advance!

SELECT * FROM SchedJobs WHERE StartDate <= '2006-05-13' and EndDate >=
'2006-05-07'

Re: Selecting Dates for a Schedule - Assistance Please!

"Paul Lautman" wrote in news:4cc08cF14situU1
@individual.net:

> Good Man wrote:
>> How can I get a list of job results for everything taking place within
>> two selected dates?
>>
>> Much thanks in advance!
>
> SELECT * FROM SchedJobs WHERE StartDate <= '2006-05-13' and EndDate >=
> '2006-05-07'

Thanks very much Paul. Depressingly simple answer.

Re: Selecting Dates for a Schedule - Assistance Please!

Good Man wrote:
> "Paul Lautman" wrote in
> news:4cc08cF14situU1 @individual.net:
>
>> Good Man wrote:
>>> How can I get a list of job results for everything taking place
>>> within two selected dates?
>>>
>>> Much thanks in advance!
>>
>> SELECT * FROM SchedJobs WHERE StartDate <= '2006-05-13' and EndDate
>> >= '2006-05-07'
>
> Thanks very much Paul. Depressingly simple answer.

Questions are always simple when you know the answers!

How are your scheduled jobs triggered and run?

Re: Selecting Dates for a Schedule - Assistance Please!

"Paul Lautman" wrote in news:4cc11vF154ch8U1
@individual.net:

> Good Man wrote:
>>> SELECT * FROM SchedJobs WHERE StartDate <= '2006-05-13' and EndDate
>>> >= '2006-05-07'
>>
>> Thanks very much Paul. Depressingly simple answer.
>
> Questions are always simple when you know the answers!
>
> How are your scheduled jobs triggered and run?

it's more of a display-only thing; ie: a Gantt chart is being created on
the fly that displays scheduling info & other details about jobs during
requested dates....

thanks again