I have a requirement to make a sales report from 1-aug-2014 to 24-aug-2014 like this:
Product Name First Week Second Week Third Week
a 12 6 2
b 0 0 4
c 0 3 0
My table like this.
products(
prod_name varchar(200),
quantity int,
qty_ordereddate datetime
)
my sql query for this:
`SELECT `main_table`.`prod_name`, `main_table`.`qty_ordered`,
SUM(main_table.qty_ordered) AS `week1`
FROM `products` AS `main_table`
WHERE (qty_ordereddate BETWEEN NOW()-INTERVAL 1 WEEK AND NOW())
GROUP BY `prod_name`
But the above returns for only 1 week. If I change the qty_ordereddate BETWEEN NOW()-INTERVAL 1 WEEK AND NOW() to qty_ordereddate BETWEEN NOW()-INTERVAL 2 WEEK AND NOW() then it returns 2 weeks sales.. But I want the result as shown in the above format. So how can I customize this. Help me on this
