CODEWITHSUNDEEP
mysqlsqldate
Martin
Martin

Reputation: 63

SQL Query to find invoices where invoicedate is over due from a date range

I have two input fields [start_date] - [end_date]

Whenever you type in a date range in YYYY-MM-DD, f.e: [2015-12-01] - [2015-12-05]

I want to make an SQL query to find out which invoices that is due in that period.

There is no column in the database that stores the duedate, only the invoice_date.

Due date is always invoice_date + 17days

Is there anyway to extract the relevant invoices from this?

This is my current query, but it only picks invoices thats has been generated in the range.

I want it to find those who are DUE in the above entered range.

 select * from orders
        inner join 
        order_invoice on order_invoice.id_order = orders.id_order
        where (current_state = 14) and 
        orders.date_add >= "'.$start_date.'" and
        orders.date_add <= "'.$end_date.'" and
        now() >= DATE_ADD(order_invoice.date_add, INTERVAL 17 DAY)

Upvotes: 1

Views: 1916

Answers (1)

IVNSTN
IVNSTN

Reputation: 9299

Move your range ([start_date] and [end_date]) 17 days left (subtract from date params) and you will get range for date_add. No difference: to add 17 days to date_add or to subtract same value from both range bounds.

And I actually don't understand what is now() function doing in filter predicate. Seems it does not correspond task described. May be you have to think up something for [end_date] for proper current date/future handling.

Upvotes: 1

Related Questions