how can i run a SQL query to retrieve weekly and monthly sales reports.
Table: orders
1 1 34 2 9/10/12
2 1 34 2 8/10/12
3 4 34 2 07/10/12
4 3 34 2 22/09/12
5 1 34 2 9/10/12
6 1 34 2 22/09/12
7 4 34 2 9/08/12
8 3 34 2 22/09/12
Table: Products
ID | Name | description
1 A ksfjsdkf
2 B ksfjsdkf
3 C ksfjsdkf
1 A ksfjsdkf
Expecting result :the following is an example result not accurate
product_name Weekly Sum(QTY), weekly revenue, Monthly SUM(QTY), monthly revenue
A 120 3000 2345 234343
Ok here a simple Query for the Quantities and Order Price
SELECT p.Description, psw.qtysum, psw.opsum, psm.qtysum, psm.opsum
FROM Products p
LEFT JOIN
( SELECT sw.product_id, SUM( sw.QTY ) AS qtysum, SUM( sw.`order price` ) AS opsum
FROM orders sw
WHERE WEEK( sw.`purchase date`) = WEEK( current_date )
AND YEAR( sw.`purchase date`) = YEAR( current_date )
GROUP BY sw.product_id ) psw
ON p.id = psw.product_id
LEFT JOIN
( SELECT sm.product_id, SUM( sm.QTY ) AS qtysum, SUM( sm.`order price` ) AS opsum
FROM orders sm
WHERE MONTH( sm.`purchase date`) = MONTH( current_date )
AND YEAR( sm.`purchase date`) = YEAR( current_date )
GROUP BY sm.product_id ) psm
ON p.id = psm.product_id
You can use php datetime("W") to get the weeknumber and datetime("m") to get the month number then convert all dates in your database to weeknumber and monthnumber and sort it.
SELECT p.name, week.qty, week.price, month.qty, month.price
FROM products p LEFT JOIN
(SELECT p.id AS prod_id, SUM(o.qty) AS qty, SUM(o.price) AS price
FROM products p LEFT JOIN orders o ON p.id = o.prod_id
WHERE o.date >= DATE_SUB(CURDATE(), INTERVAL 7 day)
GROUP BY p.id) AS week ON p.id = week.prod_id LEFT JOIN
(SELECT p.id AS prod_id, SUM(o.qty) AS qty, SUM(o.price) AS price
FROM products p LEFT JOIN orders o ON p.id = o.prod_id
WHERE o.date >= DATE_SUB(CURDATE(), INTERVAL 1 month)
GROUP BY p.id) AS month ON p.id = month.prod_id
Not too optimized, but I don't have a mysql cli here so I won't post any more intricate stuff that might not even work without having the ability to testi t.