I have a table with entries for different accounts and each account has different products listed in it with a date next to each entry. An example of the structure is below.

Account | Product | Holding | Date
A1 | Car1 | 2 | 2010-12-31
A1 | Car2 | 4 | 2010-12-31
A2 | Car3 | 2 | 2010-12-31
A2 | Car1 | 5 | 2010-12-31
A1 | Car1 | 7 | 2011-03-31
A1 | Car2 | 3 | 2011-03-31
A2 | Car3 | 1 | 2011-03-31
A2 | Car1 | 2 | 2011-03-31

I want to be able to inner join this table on itself where one date is 2010-12-31 and the other is 2011-03-31 then inner join on the accounts and product so i can get a change in holdings. Is this possible and how would I do it? I have searched the internet and a lot of forumns and have come up with nothing.

Thanks in advance.

7 Years
Discussion Span
Last Post by Ezzaral

Either of these work (I named the temp table qtest for no good reason)

SELECT *, q2.holding-q1.holding AS change 
FROM (SELECT * FROM qtest WHERE date=DATE'2010-12-31') q1,
(SELECT * FROM qtest WHERE date=DATE'2011-03-31') q2 
WHERE q1.account=q2.account and q1.product=q2.product
SELECT *, q2.holding-q1.holding AS change 
FROM qtest q1 INNER JOIN qtest q2 ON q1.account=q2.account AND q1.product=q2.product 
AND q1.date=DATE'2010-12-31' AND q2.date=DATE'2011-03-31'

I left the "*" selection there just so you could verify the join result. You certainly don't need all those displayed.

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.