Sales Compared to Last Year using Date Math

Hi Howard,  I have a question about SQL. Suppose I have a table containing sales for each day of a year.

Then I have another table that describes our accounting year by week

Now I want a query that will yield results like the following, comparing the sales for a day in this year to the corresponding accounting day from last years week period.

How I can do it in SQL? -- Muhammad

Ok, Muhammad, this is a fun query because we get to take advantage of AS/400 date math, outer joins and general goofiness! I am going to include all of the create table and insert statements so that you can try this example yourself at home.  First, download a free SQLThing from www.sqlthing.com to execute the following commands, (or use Ops Navigator or Interactive SQL if you are so inclined)

CREATE TABLE SQLBOOK/HTSALES 
	(SDATE DATE, 
	SALETOTAL DECIMAL(10,2),
	PRIMARY KEY (sdate));

CREATE TABLE SQLBOOK/HTFISCAL (
	PHYR SMALLINT, 
	PHWK SMALLINT, 
	SD DATE, 
	ED DATE,
	PRIMARY KEY (PHYR, PHWK),
         CHECK (SD<ED));

Notice that I placed a check constraint into the HTFISCAL table to ensure that the SD is always less than the ED field when a record is entered. Next, execute the following insert statements to place test data into your files:

/* Data for the HTFISCAL table */
INSERT INTO SQLBOOK/HTFISCAL VALUES (2000,39,'10/25/2000','10/31/2000');
INSERT INTO SQLBOOK/HTFISCAL VALUES (2001,39,'10/27/2001','11/02/2001');
INSERT INTO SQLBOOK/HTFISCAL VALUES (2000,40,'11/01/2000','11/07/2000');
INSERT INTO SQLBOOK/HTFISCAL VALUES (2001,40,'11/03/2001','11/10/2001');
INSERT INTO SQLBOOK/HTFISCAL VALUES (2000,41,'11/08/2000','11/14/2000');
INSERT INTO SQLBOOK/HTFISCAL VALUES (2001,41,'11/11/2001','11/17/2001');
INSERT INTO SQLBOOK/HTFISCAL VALUES (2000,42,'11/15/2000','11/21/2000');
INSERT INTO SQLBOOK/HTFISCAL VALUES (2001,42,'11/18/2001','11/24/2001');

/* Data for the HTSALES table */

INSERT INTO SQLBOOK/HTSALES VALUES ('11/01/2000',5000);
INSERT INTO SQLBOOK/HTSALES VALUES ('11/02/2000',5500);
INSERT INTO SQLBOOK/HTSALES VALUES ('11/03/2001',5800);
INSERT INTO SQLBOOK/HTSALES VALUES ('11/04/2001',6000);
INSERT INTO SQLBOOK/HTSALES VALUES ('11/02/2001',7000);

Now, we are ready to cook with gasoline!  Consider the following query:

SELECT * FROM SQLBOOK/HTSALES a 
	INNER JOIN SQLBOOK/HTFISCAL c
 		ON (a.sdate >= c.sd
      AND a.sdate <= c.ed)

If you're at V5R1, you can use this form, if you prefer.

SELECT * FROM SQLBOOK/HTSALES a
            INNER JOIN SQLBOOK/HTFISCAL c
                ON (a.sdate BETWEEN c.sd AND c.ed);

This query asks for all of the fields from the HTFISCAL table and the HTSALES table and joins the two tables where the SDATE, (the date of the sale), is between the SD, (start date), and ED, (end date).  By executing this statement we now have a virtual table that consists of the total, date sold, accounting week, accounting year, accounting start period and accounting end period.  The results should look like this:

Now, we need to know the start date of the week period for the prior year, so we add another join to the query where the PHWK values are equal, but the year of the sales period is PHYR-1:

SELECT SDATE,SALETOTAL,C.SD,D.SD
  	FROM SQLBOOK/HTSALES a
     		INNER JOIN SQLBOOK/HTFISCAL c
       			ON (a.sdate >= c.sd AND a.sdate <= c.ed)                      
     		INNER JOIN SQLBOOK/HTFISCAL d
       			ON (c.phwk = d.phwk AND c.phyr-1 = d.phyr)

V5R1 users may prefer to use BETWEEN in the join, like this.

SELECT SDATE,SALETOTAL,C.SD,D.SD 
            FROM SQLBOOK/HTSALES a
            	INNER JOIN SQLBOOK/HTFISCAL c 
                    ON (a.sdate BETWEEN c.sd AND c.ed)
		INNER JOIN SQLBOOK/HTFISCAL d 
                    ON (c.phwk = d.phwk AND c.phyr-1 = d.phyr);

Notice that I am joining the HTFISCAL file to itself in order to find the previous year start date. The SQL statement should yield results like the following:

Now, the magic is to use date math to find the corresponding sales day for the previous year.  This is done by calculating the previous sales date using the following expression:

PreviousSalesDate = (ThisSalesDate – ThisWeekStart) + LastYearStart

Note that this will only work if the difference between the ThisSalesDate and the ThisWeekStart value is within a caldendar month or so.  This is because the AS/400 system returns results of date arithmetic in durations. However, for the purposes of this report, we should never have a start date greater than 7 days from the sales date, so it works. If we did have a period greater than  about a month[THH2] , we would have to use a formula to account for the greater durations. Since, we don’t need to do this, the query can remain this simple. For more information on durations and date math, check my website, www.sqlthing.com, or purchase my book, iSeries and AS/400 SQL at Work.

So, to get the final result, we just join again to the HTSALES table, but this time on our calculated previous year sales date in order to yield a table of the correct data:

SELECT a.sdate, a.saletotal, 
		b.sdate, b.saletotal 
	FROM SQLBOOK/HTSALES a 
	 INNER JOIN SQLBOOK/HTFISCAL c    
	     	ON (a.sdate >= c.sd AND a.sdate <= c.ed)  
	 INNER JOIN SQLBOOK/HTFISCAL d 
		ON (c.phwk = d.phwk AND c.phyr -1 = d.phyr)   
	 LEFT OUTER JOIN SQLBOOK/HTSALES b
     		ON (b.sdate = (a.sdate-c.sd) + d.sd)

V5R1 installations may use this form instead.

SELECT a.sdate, a.saletotal, 
		b.sdate, b.saletotal
	FROM SQLBOOK/HTSALES a 
            INNER JOIN SQLBOOK/HTFISCAL c 
                ON (a.sdate BETWEEN c.sd AND c.ed)
            INNER JOIN SQLBOOK/HTFISCAL d 
                ON (c.phwk = d.phwk AND c.phyr –1 = d.phyr)
            LEFT OUTER JOIN SQLBOOK/HTSALES b 
                ON (b.sdate = (a.sdate-c.sd) + d.sd);

The above statement uses a LEFT OUTER JOIN so that the AS/400 will return NULL for any dates and totals that do not exist in the previous years data.  The above statement should yield the following results:

See, joins and date math can be fun.