0

I am having a hard time listing all the clients for which no invoice has been created in the last 6 weeks.Two tables are involved, client and invoice. My current query looks like this:

select MAX(invoice.created_date) 'Last Invoice Date', invoice.invoice_no 'Invoice No.', DATEDIFF(curdate(), max(invoice.created_date)) 'Days since last invoice created', client.name 'Client'  
from invoice 
left join
client on invoice.client_id = client.id 
where 
datediff (curdate(), (select MAX(invoice.created_date) from invoice left join client on invoice.client_id = client.id)) >  42
group by client.id;

But it returns an empty set. Any help would be appreciated

Erind Pepi
  • 25
  • 4

1 Answers1

0

Starting the join from invoice means that straight away any clients with no invoices are excluded. You can start from client and join onto invoice to get the information you need in the select and then use an EXISTS clause to only grab clients with no invoice in the last x days:

SELECT MAX(invoice.created_date) 'Last Invoice Date', invoice.invoice_no 'Invoice No.', DATEDIFF(curdate(), max(invoice.created_date)) 'Days since last invoice created', client.name 'Client'  
FROM client c
LEFT JOIN invoice i ON (i.client_id = c.id)
WHERE NOT EXISTS(
    SELECT 1 FROM invoice i2
    WHERE i2.client_id = c.id
    AND DATE_SUB(NOW(), INTERVAL 42 DAY) < i2.created_date
)
GROUP BY c.id;
Jim
  • 22,354
  • 6
  • 52
  • 80
  • Hi Jim! Let's assume that all the clients have an invoice because they practically do. Every invoice is saved individually and has its own date. The query as you suggested it returns also clients with invoices in the last 42 days, which I do not want. I think the problem is within the EXIST because not the latest created_date is taken into account with "(datediff (curdate(),i2.created_date) > 42 )" I want something like max(i2.created_date) but that is of course not allowed there. Any idea? – Erind Pepi Jan 17 '14 at 15:06
  • @ErindPepi There's no need for a max in the exists because if there is any matching invoice in the last 42 days then the condition will be false. I'll get an sql fiddle put together to show the query being used. – Jim Jan 17 '14 at 15:10
  • @ErindPepi The `datediff` part seemed to be misbehaving so I've changed that. Example available [here](http://sqlfiddle.com/#!2/e5c70/2). – Jim Jan 17 '14 at 15:32