CODEWITHSUNDEEP
phpmysql
Dimitri
Dimitri

Reputation: 1966

MySQL BETWEEN DATE RANGE

I have a scenario where I need to pull up delivery dates based on a table below (Example)

job_id | delivery_date
1      | 2013-01-12
2      | 2013-01-25
3      | 2013-02-15

What I'm trying to do is show the user all the delivery dates that start with the earliest (in this case it would be 2013-01-12) and add an another 21 days to that. Basically, the output I would expect it to show of course, the earliest date being the starting date 2013-01-12 and 2013-01-25. The dates past the February date are of no importance since they're not in my 21 date range. If it were a 5 day range, for example, then of course 2013-01-25 would not be included and only the earliest date would appear.

Here is main SQL clause I have which only shows jobs starting this year forward:

SELECT date, delivery_date 
FROM `job_sheet` 
WHERE print_status IS NULL 
    AND job_sheet.date>'2013-01-01'

Is it possible to accomplish this with 1 SQL query, or must I go with a mix of PHP as well?

Upvotes: 1

Views: 4638

Answers (3)

Taryn
Taryn

Reputation: 247860

You can use the following:

select *
from job_sheet
where print_status IS NULL
  and delivery_date >= (select min(delivery_date)
                        from job_sheet)
  and delivery_date <= (select date_add(min(delivery_date), interval 21 day)
                        from job_sheet)

See SQL Fiddle with Demo

If you are worried about the dates not being correct, if you use a query then it might be best to pass in the start date to your query, then add 21 days to get the end date. Similar to this:

set @a='2013-01-01';

select *
from job_sheet
where delivery_date >= @a
  and delivery_date <= date_add(@a, interval 21 day)

See SQL Fiddle with Demo

Upvotes: 2

spencer7593
spencer7593

Reputation: 108500

SELECT j.job_id
     , j.delivery_date
  FROM `job_sheet` j
  JOIN ( SELECT MIN(d.delivery_date) AS earliest_date
           FROM `job_sheet` d
          WHERE d.delivery_date >= '2013-01-01'
       ) e
    ON j.delivery_date >= e.earliest_date
   AND j.delivery_date < DATE_ADD(e.earliest_date, INTERVAL 22 DAY)
   AND j.print_status IS NULL
 ORDER BY j.delivery_date

(The original query has a predicate on job_sheet.date; the query above references the d.delivery_date... change that if it is supposed to be referencing the date column instaed.)

If the intent is to only show delivery_date values from today forward, then change the literal '2013-01-01' to an expression that returns the current date, e.g. DATE(NOW())

Upvotes: 1

AndreKR
AndreKR

Reputation: 33697

SELECT date,
       delivery_date
FROM job_sheet
WHERE print_status IS NULL
AND job_sheet.date BETWEEN (SELECT MIN(date) FROM job_sheet) AND
                           (SELECT MIN(date) FROM job_sheet) + INTERVAL 21 DAY

Upvotes: 2

Related Questions