My SupplyCosts table contains a list of supplies and the cost associated with that supply for every month:
SupplyCosts: mth item cost 10-04 bandaid .20 11-04 bandaid .25 12-04 bandaid .30 01-05 bandaid .30
Some months the cost remains the same and the only change is the month field. Other months, the cost changes. I want to select only the months where the cost has changed and the previous cost. From the example data above, I only want the records for the 11-04 and 12-04 months. SELECT DISTINCT doesn't work because I need the month field and it returns every record. Thanks so much.
The trick with this type of problem, where you need to compare a value on one row with a value on another row, is to use a self-join. This particular scenario is complicated by the fact that you need a pretty hairy join clause, to match the current month with the previous month.
To see why, let's ask ourselves how we should compare 12-04 and 01-05. We can not simply say:
where t1.mth = t2.mth - 1
The reason we can't is obvious: 12-04 and 01-05 aren't numbers. They aren't dates, either. They're strings.
However, all is not lost. We can convert strings to dates easily enough, although the actual mechanism is different in just about every database system. Here's the solution using MySQL syntax:
select curr.item , curr.mth , curr.cost as current_cost , prev.cost as previous_cost from SupplyCosts as prev inner join SupplyCosts as curr on prev.item = curr.item and date_add( cast( concat('20',substr(prev.mth,4,2) ,'-',substr(prev.mth,1,2) ,'-01') as date) , interval 1 month) = cast( concat('20',substr(curr.mth,4,2) ,'-',substr(curr.mth,1,2) ,'-01') as date) where prev.cost <> curr.cost order by curr.item , curr.mth
The expressions involving DATE_ADD and CAST will change from one database system to another. Basically you convert the strings to dates (using the first of the month), and then use date arithmetic (in this case the DATE_ADD function) to add 1 month.
Dig Deeper on Oracle and SQL
Related Q&A from Rudy Limeback
Read an example of an SQL case expression from our SQL expert Rudy Limeback. Continue Reading
Read SQL expert Rudy Limeback's advice for counting combinations in a table with SQL's GROUP BY clause Continue Reading
Read about the Mimer Validator, a tool used to verify your SQL code, in this tip from SQL expert Rudy Limeback. Continue Reading