SQL new column = difference of two months

[ecompa]

Hi,

I've a table listing data for different clients over the last two months
client year month Prod depot act instr
abc 2004 Dec BLUB 120 13 5
abc 2004 Dec TOC 110 10 4
abc 2005 Jan TOC 125 10 4
xyz 2004 Nov TOC 15 2 1
xyz 2004 Dec BLUB 13 1 10
xyz 2005 Jan BIM 1 2 6,5

what I would like to have is a new column, showing the difference between two last months, for each of the 3 "dimensions" (depot, activity, instructions).
That woulld be the minimu requirement.
Ideally, I need two show this evolution with a breakdown by product, over a longer period of time (last 12 months).
Some clues on this forum give me some insight: for instance making a join between the table with itself, and using crosstab queries. I'll have to make some tests.
However if someone had a similar problem and already have a good query (or module in VBA)I would gain a lot of time (and I am a bit under pressure...).
Thanks
ecompa

 

[r937]

by client? by product? which two last months?

could you show the expected results based on the sample data you have provided?

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (course starts January 9 2005)

 

[ecompa]

Sorry for the typo mistake(data give me 12 month of history) and for not being very clear.

To give you a bit of context, I use VBA to read data on DB2 and automatically generate some Access tables each month that will be used in VB script queries on an intranet.
These final table should be as close as possible to the final presentation, to be very efficient on client side.

1. The minimum requirement is to show for each client a monthly evolution, all products included:
client current_depot prev-curr dep ...
abc 125 -115
the same for 3 initial columns

2. What would be nice to have is the same kind of data by product
client, prod, currentAct, (previous-current_activity)

3. Ideally, I would like to show a 12 month evolution by client and by product

Could be a series of column giving the Depot(or other dimension) for each of the last 12 month. clt, prod, Depot12MthAgo, Depot_11_month_ago,... currDepot

In that case a simple crosstab would be OK, I guess.

But ideally I would need a series of column giving each time the difference between two month of Depot (or activity...)
client, Prod, (Depot12MthAgo-Dep11MthAgo),...(Depot1MthAgo-CurrentDepot)

Any suggestion or advice is OK.
Thanks
Ecompa

 

[r937]

i'm going to have to decline to take this any further

it's no longer a question about sql, it's an RFP (request for project)

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (course starts January 9 2005)