1

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?

Improve this question

3 Answers 3

Reset to default
2

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

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

I see how this would work. My only concern is that how would you show only the jobs created starting as of the new year? For some reason on my side, when I add AND delivery_date>='2013-01-01' nothing shows up...
If you have delivery dates before 2013-01-01, then the expression min(delivery_date) isn't going to do the Right Thing. Use a literal date instead.
@DimitriTopaloglou I agree with Catcall, if you are unsure of the date range, then you should use literal strings as the value for the ranges. So pass into your query the startdate and then add 21 day to that value for the end date. See my edit
2 
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
Improve this answer

Comments

1 
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())

Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.