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.

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.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.