Doug Murphy
dw-murphy at cox.net
Thu Oct 11 15:10:37 CDT 2012
An interesting approach to this type of requirement is shown at http://www.rafael-salas.com/2008/05/t-sql-lead-and-lag-functions.html Doug -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Philippe lelynx Sent: Thursday, October 11, 2012 12:32 PM To: Access Developers discussion and problem solving Subject: Re: [AccessD] sql to get stock variation thank you, John, I understand what you mean, I will go this way. Regards, Philippe 2012/10/11 jwcolby <jwcolby at colbyconsulting.com> > One way would be to put a FK in the record which holds the PK of the > previously stored record for a given stock. That requires looking up > the previous record before updating the new record but it then gives > you a dead simple inner join to get the difference between the current > and the previous. Just join the table on itself using the PK and that FK. > > It is called a "single linked list" when done in code. > > John W. Colby > Colby Consulting > > Reality is what refuses to go away > when you do not believe in it > > > On 10/11/2012 1:15 PM, Philippe lelynx wrote: > >> Hi all, >> >> I need your help regarding a sql query. >> >> I have a table, say STOCK(stock_date, stock_qty) >> >> It records the stock level at the end of each day. >> >> I need to calculate, for each day, the stock variation. >> >> i.e for the day n => stock(n) - stock(n-1) >> >> How would you build such a query?? >> >> Regards, >> >> Philippe Pons >> >> > -- > AccessD mailing list > AccessD at databaseadvisors.com > http://databaseadvisors.com/**mailman/listinfo/accessd<http://database > advisors.com/mailman/listinfo/accessd> > Website: > http://www.databaseadvisors.**com<http://www.databaseadvisors.com> > -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com