Selecting Dates for a Schedule - Assistance Please!

[Good Man]

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!

[Paul Lautman]

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'

[Good Man]

"Paul Lautman" <pa**********@btinternet.com> 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.

[Paul Lautman]

Good Man wrote:

"Paul Lautman" <pa**********@btinternet.com> 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?

[Good Man]

"Paul Lautman" <pa**********@btinternet.com> 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