Comparing two date ranges within the same table
- by Danny Herran
I have a table with sales per store as follows:
SQL> select * from sales;
ID ID_STORE DATE TOTAL
---------- -------- ---------- --------------------------------------------------
1 1 2010-01-01 500.00
2 1 2010-01-02 185.00
3 1 2010-01-03 135.00
4 1 2009-01-01 165.00
5 1 2009-01-02 175.00
6 5 2010-01-01 130.00
7 5 2010-01-02 135.00
8 5 2010-01-03 130.00
9 6 2010-01-01 100.00
10 6 2010-01-02 12.00
11 6 2010-01-03 85.00
12 6 2009-01-01 135.00
13 6 2009-01-02 400.00
14 6 2009-01-07 21.00
15 6 2009-01-08 45.00
16 8 2009-01-09 123.00
17 8 2009-01-10 581.00
17 rows selected.
What I need to do is to compare two date ranges within that table. Lets say I need to know the differences in sales between 01 Jan 2009 to 10 Jan 2009 AGAINST 01 Jan 2010 to 10 Jan 2010.
I'd like to build a query that returns something like this:
ID_STORE_A DATE_A TOTAL_A ID_STORE_B DATE_B TOTAL_B
---------- ---------- --------- ---------- ---------- -------------------
1 2010-01-01 500.00 1 2009-01-01 165.00
1 2010-01-02 185.00 1 2009-01-02 175.00
1 2010-01-03 135.00 1 NULL NULL
5 2010-01-01 130.00 5 NULL NULL
5 2010-01-02 135.00 5 NULL NULL
5 2010-01-03 130.00 5 NULL NULL
6 2010-01-01 100.00 6 2009-01-01 135.00
6 2010-01-02 12.00 6 2009-01-02 400.00
6 2010-01-03 85.00 6 NULL NULL
6 NULL NULL 6 2009-01-07 21.00
6 NULL NULL 6 2009-01-08 45.00
6 NULL NULL 8 2009-01-09 123.00
6 NULL NULL 8 2009-01-10 581.00
So, even if there are no sales in one range or another, it should just fill the empty space with NULL.
So far, I've come up with this quick query, but I the "dates" from sales to sales2 sometimes are different in each row:
SELECT sales.*, sales2.*
FROM sales
LEFT JOIN sales AS sales2 ON (sales.id_store=sales2.id_store)
WHERE
sales.date >= '2010-01-01'
AND sales.date <= '2010-01-10'
AND sales2.date >= '2009-01-01'
AND sales2.date <= '2009-01-10'
ORDER BY sales.id_store ASC, sales.date ASC, sales2.date ASC
What am I missing?
