CODEWITHSUNDEEP
phpmysql
user2823107
user2823107

Reputation: 99

Getting next 15 days data from current date

I am trying to get next 15 days data from current date. And i have used my query like this

SELECT bom.bid, 
       bom.item, 
       bom.cost, 
       bom.location, 
       work_order_line_items.id, 
       work_order_line_items.item, 
       Date(work_order_line_items.required_date) AS date, 
       Sum(work_order_line_items.quantity)       AS TQTY, 
       items.item_id, 
       items.in_stock 
FROM   bom 
       INNER JOIN work_order_line_items 
               ON bom.item = work_order_line_items.item 
       INNER JOIN items 
               ON work_order_line_items.item = items.item_id 
WHERE  Date(work_order_line_items.required_date) = 
       Date_add(Curdate(), INTERVAL 15 day) 
GROUP  BY work_order_line_items.required_date

in required_date column i have data .. 2014-12-28, 2014-12-31, 2015-01-03, 2015-01-07

But i am not getting any result after running the query. Can somebody please suggest me where i am wrong?

Upvotes: 1

Views: 2098

Answers (3)

O. Jones
O. Jones

Reputation: 108641

You have three problems in this query.

First, you're using equality rather than inequality in your WHERE clause. So you'll only pick up dates that are exactly fifteen days in the future.

Second, your WHERE clause defeats the use of an index on the required_date column, necessitating a table scan. If you have dozens of items in your table, this doesn't matter, but if you get tens of thousands, your performance will suffer.

A better WHERE clause might be this:

WHERE  work_order_line_items.required_date >= CURDATE()
  AND  work_order_line_items.required_date < CURDATE() + INTERVAL 16 DAY

This will pick up all the items starting at midnight in the present day and ending just before midnight sixteen days out. And it can do it with a range scan on an index on your required_date column.

Third, you are misusing the MySQL extension to GROUP BY. You're going to get unpredictable values in your result set. Read this. http://dev.mysql.com/doc/refman/5.6/en/group-by-handling.html

Here's a better version of your GROUP BY clause. Notice that it mentions all the columns in your result set except the aggregated ones.

GROUP BY bom.bid, 
         bom.item, 
         bom.cost, 
         bom.location, 
         work_order_line_items.id, 
         work_order_line_items.item, 
         DATE(work_order_line_items.required_date), 
         items.item_id, 
         items.in_stock

Upvotes: 3

DoctorKraz
DoctorKraz

Reputation: 66

As it stands now - its only looking at records where your date in the table matches 15 days from now. You may want to try changing your Where clause to:

WHERE DATE(work_order_line_items.required_date) between CURDATE() and DATE_ADD(CURDATE(),INTERVAL 15 DAY)

That should then give you a date range through the next 15 days.

You could also use >= and <=:

WHERE (DATE(work_order_line_items.required_date) >= CURDATE()) AND (DATE(work_order_line_items.required_date) <= DATE_ADD(CURDATE(),INTERVAL 15 DAY))

These will get you the next 15 days, and preclude any data prior to today ( where as <= will give you everything including days in the past ).

Upvotes: 0

Ramsay Smith
Ramsay Smith

Reputation: 1108

The problem is in your WHERE clause. You are using = which means you are checking only for items with a required_date 15 days in the future. Change the = to <=.

TIP: If you only want work orders from now to 15 days in the future, add another condition that only selects date after today: Date(work_order_line_items.required_date) >= Curdate().

Upvotes: 0

Related Questions