Professional Web Applications Themes

Selecting Dates for a Schedule - Assistance Please! - MySQL

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 ...

  1. #1

    Default 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!





    Good Man Guest

  2. #2

    Default 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'


    Paul Lautman Guest

  3. #3

    Default Re: Selecting Dates for a Schedule - Assistance Please!

    "Paul Lautman" <paul.lautmanbtinternet.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.
    Good Man Guest

  4. #4

    Default Re: Selecting Dates for a Schedule - Assistance Please!

    Good Man wrote:
    > "Paul Lautman" <paul.lautmanbtinternet.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?


    Paul Lautman Guest

  5. #5

    Default Re: Selecting Dates for a Schedule - Assistance Please!

    "Paul Lautman" <paul.lautmanbtinternet.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
    Good Man Guest

Similar Threads

  1. Schedule Tasks with SSL
    By Leonce in forum Coldfusion Server Administration
    Replies: 3
    Last Post: February 22nd, 03:07 PM
  2. selecting times from a schedule
    By wisangler in forum Macromedia ColdFusion
    Replies: 3
    Last Post: May 26th, 06:14 PM
  3. Creating a TV schedule
    By midimidi in forum Macromedia ColdFusion
    Replies: 2
    Last Post: March 21st, 11:54 AM
  4. Selecting a range of dates in MySql
    By Jeff Roe in forum PHP Development
    Replies: 1
    Last Post: October 1st, 11:37 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139