Derive new fields from one column using SQL Join

The majority of tables that record transactional data contain many years of transactional data.  So if you wanted to compare sales in 2007 to sales and 2008, it may be a little tricky considering that the sales figure for any year are stored in the same column.

For example:

CLIENT            STMT_DATE       PURCHASES
BUTERBROD CO      2007-10-31      3490561
BUTERBROD CO      2007-11-30      3313900
BUTERBROD CO      2008-01-31      3292238
BUTERBROD CO      2008-02-29      3325060
BUTERBROD CO      2008-03-31      3680350

You will notice that this section of data in our cust_sales table 2007 and 2008 purchase values are in the same table.  So how can we query or create a data set that will compare January 2007 against January 2008 sales?  The answer is through SQL joins.  Essentially we will create two aggregate function queries.  One that calls for the sum sales in 2008 and we will join it with a second query that will call for the sum sales for sales that occured in the year 2007.  (Note - To test example, download SQL file and attempt on your database)

SELECT S08.CUST_ID, S08.STMT_DT, SUM(S08.PURCHASES) AS PURCH_08, S07.PURCH_07
FROM CUST_SALES S08
LEFT JOIN (SELECT CUST_ID, STMT_DT, SUM(PURCHASES) AS PURCH_07
                   FROM CUST_SALES
                   WHERE YEAR(STMT_DT) = 2007
                   GROUP BY CUST_ID, STMT_DT) S07
ON S07.CUST_ID = S08.CUST_ID
AND MONTH(S07.STMT_DT) = MONTH(S08.STMT_DT)
WHERE YEAR(S08.STMT_DT) = 2008
GROUP BY S08.CUST_ID, S08.STMT_DT, S07.PURCH_07 

The above query will provide us with the following results:

CLIENT            STMT_DATE       PURCH_08     PURCH_07
BUTERBROD CO      2008-03-31      3680350      3184136
BUTERBROD CO      2008-04-30      3714286      3027849
BUTERBROD CO      2008-05-31      4104875      3369345
BUTERBROD CO      2008-06-30      4217168      3462368
BUTERBROD CO      2008-07-31      4288708      3486339
BUTERBROD CO      2008-08-31      4308599      3590772
BUTERBROD CO      2008-09-30      4034318      3410853
BUTERBROD CO      2008-10-31      4090400      3490561
BUTERBROD CO      2008-11-30      3965514      3313900

If you are needing the calculate the difference between 2008 and 2007, you can create a new column by subtracting 2008 purchases from 2007.  The SQL for that is:

SELECT S08.CUST_ID, S08.STMT_DT, SUM(S08.PURCHASES) AS PURCH_08,  S07.PURCH_07,
             SUM(S08.PURCHASES) - S07.PURCH_07 AS DIFF
FROM CUST_SALES S08
LEFT JOIN (SELECT CUST_ID, STMT_DT, SUM(PURCHASES) AS PURCH_07
                   FROM CUST_SALES
                   WHERE YEAR(STMT_DT) = 2007
                   GROUP BY CUST_ID, STMT_DT) S07
ON S07.CUST_ID = S08.CUST_ID
AND MONTH(S07.STMT_DT) = MONTH(S08.STMT_DT)
WHERE YEAR(S08.STMT_DT) = 2008
GROUP BY S08.CUST_ID, S08.STMT_DT, S07.PURCH_07

Look for my REGR_SLOPE() SQL post.  Using it may be the next step you want to take in your analysis.