Reputation: 47
I am working on a report module. I have worked on comparing this week and last weeks report for certain agents.
This weeks Report query is as follows
SELECT COUNT(created_at) AS cust_count_new, agency_id, created_at FROM customers WHERE
(customers.created_at >= DATE(NOW()) - INTERVAL 6 DAY AND customers.created_at
< DATE(NOW()) + INTERVAL 1 DAY
Last Week Report query is as follows
SELECT COUNT(created_at) AS cust_count_old, agency_id, created_at FROM customers WHERE
(customers.created_at >= DATE(NOW()) - INTERVAL 13 DAY AND customers.created_at
< DATE(NOW()) - INTERVAL 6 DAY
What I am doing is comparing this weeks and last weeks report. Now how can i change the query to this month and last months (30 days). I am little confused, so any help is appreciated.
Upvotes: 1
Views: 1050
Reputation: 28834
You can use MONTH
as unit in the INTERVAL
. Also, you can use CURDATE()
instead of DATE(NOW())
, to get the current date.
This Month Report query will be:
SELECT COUNT(created_at) AS cust_count_new,
agency_id,
created_at
FROM customers
WHERE customers.created_at >= CURDATE() - INTERVAL 1 MONTH AND
customers.created_at < CURDATE() + INTERVAL 1 DAY
Last Month Report query is as follows
SELECT COUNT(created_at) AS cust_count_new,
agency_id,
created_at
FROM customers
WHERE customers.created_at >= CURDATE() - INTERVAL 2 MONTH AND
customers.created_at < (CURDATE() - INTERVAL 1 MONTH) + INTERVAL 1 DAY
Upvotes: 1