Comparing two date ranges in one table

Question

I have a table with sales for the 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 compare the two date ranges in this table. Suppose I need to know the difference in sales from January 1, 2009 to January 10, 2009 from January 1, 2010 to January 10, 2010.

I would 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 is no sales in one range, it should just fill in the empty space using NULL.

So far I have come up with this quick query, but I "dates" from sales to sales2 sometimes differ on each line:

 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?

Answer 1

Using IBM Informix Dynamic Server 11.50.FC6, I can use this SQL sequence to get the desired result:

Customization

 CREATE TABLE sales ( id INTEGER NOT NULL, id_store INTEGER NOT NULL, date DATE NOT NULL, total DECIMAL(10,2) NOT NULL ); INSERT INTO sales VALUES( 1, 1, '2010-01-01', 500.00); INSERT INTO sales VALUES( 2, 1, '2010-01-02', 185.00); INSERT INTO sales VALUES( 3, 1, '2010-01-03', 135.00); INSERT INTO sales VALUES( 4, 1, '2009-01-01', 165.00); INSERT INTO sales VALUES( 5, 1, '2009-01-02', 175.00); INSERT INTO sales VALUES( 6, 5, '2010-01-01', 130.00); INSERT INTO sales VALUES( 7, 5, '2010-01-02', 135.00); INSERT INTO sales VALUES( 8, 5, '2010-01-03', 130.00); INSERT INTO sales VALUES( 9, 6, '2010-01-01', 100.00); INSERT INTO sales VALUES(10, 6, '2010-01-02', 12.00); INSERT INTO sales VALUES(11, 6, '2010-01-03', 85.00); INSERT INTO sales VALUES(12, 6, '2009-01-01', 135.00); INSERT INTO sales VALUES(13, 6, '2009-01-02', 400.00); INSERT INTO sales VALUES(14, 6, '2009-01-07', 21.00); INSERT INTO sales VALUES(15, 6, '2009-01-08', 45.00); INSERT INTO sales VALUES(16, 8, '2009-01-09', 123.00); INSERT INTO sales VALUES(17, 8, '2009-01-10', 581.00); 

Query

 SELECT * FROM (SELECT s1.id AS s1id, NVL(s1.id_store, s2.id_store) AS s1store, NVL(s1.date, MDY(MONTH(s2.date), DAY(s2.date), YEAR(s2.date)+1)) AS s1date, s1.total AS s1total, s2.id AS s2id, NVL(s2.id_store, s1.id_store) AS s2store, NVL(s2.date, MDY(MONTH(s1.date), DAY(s1.date), YEAR(s1.date)-1)) AS s2date, s2.total AS s2total FROM sales AS s1 FULL JOIN sales AS s2 ON s1.id_store = s2.id_store AND s1.date BETWEEN '2010-01-01' AND '2010-01-10' AND s2.date BETWEEN '2009-01-01' AND '2009-01-10' AND DAY(s1.date) = DAY(s2.date) AND MONTH(s1.date) = MONTH(s2.date) ) AS s3 WHERE s1_date BETWEEN '2010-01-01' AND '2010-01-10' AND s2_date BETWEEN '2009-01-01' AND '2009-01-10' ORDER BY s1_id_store ASC, s1_date ASC; 

Result

 s1id s1store s1date s1total s2id s2store s2date s2total 1 1 2010-01-01 500.00 4 1 2009-01-01 165.00 2 1 2010-01-02 185.00 5 1 2009-01-02 175.00 3 1 2010-01-03 135.00 1 2009-01-03 6 5 2010-01-01 130.00 5 2009-01-01 7 5 2010-01-02 135.00 5 2009-01-02 8 5 2010-01-03 130.00 5 2009-01-03 9 6 2010-01-01 100.00 12 6 2009-01-01 135.00 10 6 2010-01-02 12.00 13 6 2009-01-02 400.00 11 6 2010-01-03 85.00 6 2009-01-03 6 2010-01-07 14 6 2009-01-07 21.00 6 2010-01-08 15 6 2009-01-08 45.00 8 2010-01-09 16 8 2009-01-09 123.00 8 2010-01-10 17 8 2009-01-10 581.00 

Explanation

It took a lot of experimentation to make the right choice. Informix has a DATE MDY () constructor function that takes three integer arguments: month, day, and year (this is the name of the mnemonics). It also has three analysis functions: DAY (), MONTH (), and YEAR (), which return the day, month, and year of the date argument. An internal query with FULL JOIN yields results with zeros on both the left and right sides. The 5-part criterion in the ON clause seems necessary; otherwise, the criteria for an external request should be more complex and confusing - if it can be made to work at all. The criteria in the external selection then provide the selection of the correct data. One of the advantages of NVL () expressions in an internal query is that the storage identifier columns are the same, not null, and not a single date column is zero, so the order by clause can be simpler - on the store identifier and date column.

Informix could also rework date expressions as:

 NVL(s1.date, s2.date + 1 UNITS YEAR) NVL(s2.date, s1.date - 1 UNITS YEAR) 

Actually, there are several type conversions behind the scenes with this notation, but this gives you the same result, and the extra calculation is probably not that significant.

There is also a pending crash in Informix; You cannot add or subtract 1 year before or from February 29, because February 29 is not the next or previous year. You need to be careful with your data; if you do not, you can compare data for 2008-02-29 with 2009-02-28 (as well as compare data for 2008-02-28 with 2009-02-28). There is a process called “double-entry”, but that does not mean that it is meant by this, and your calculations may be confused if “2008-02-29 plus 1 year” is 2009-02-28. Informix generates an error; it is not very useful. You can probably code the stored procedure to return NULL for 2008-02-29 plus 1 year, as there is no date to compare its sales with.

You should easily adapt date arithmetic to MySQL; the rest of the code does not need to be changed.

Answer 2

I think the problem is in your connection state. I have not tested it, but I think you could try how

 ... ON ( sales.id_store = sales2.id_store AND sales.date = ADDDATE(sales2.date, INTERVAL 1 YEAR) ... )