jwcolby
jwcolby at colbyconsulting.com
Fri Oct 12 06:22:16 CDT 2012
This assumes that there is only a single stock in the table else you also have to join on the stock id as well. John W. Colby Colby Consulting Reality is what refuses to go away when you do not believe in it On 10/11/2012 4:50 PM, Gary Kjos wrote: > If you had a simple date table with todays date in one column and > yesterdays date - or perhaps a Friday date on a Monday to skip a weekend > etc, you could then just join that table to your stock table on each of the > two dates using an alias for the second copy of the stock table calling it > something like previous_day_stock and you could then report > stock_variation = stock(stock_qty) - previous_day_stock(stock_qty) > > So a table called ReportingDates with these columns and values > Date_of_day Previous_date > 10/1/2012 9/28/2012 > 10/2/2012 10/1/2012 > 10/3/2012 10/2/2012 > > And a Stock table with these values > Stock_date Stock_qty > 9/28/2012 999 > 10/1/2012 432 > 10/2/2012 124 > 10/3/2012 753 > > And This sql > > SELECT Stock.Stock_date, Stock.Stock_qty, Previous_Day_Stock.Stock_date, > Previous_Day_Stock.Stock_qty, > [Stock].[Stock_qty]-[Previous_Day_Stock].[Stock_qty] AS Stock_Variance > FROM (ReportingDates INNER JOIN Stock ON ReportingDates.Date_of_day = > Stock.Stock_date) INNER JOIN Stock AS Previous_Day_Stock ON > ReportingDates.Previous_date = Previous_Day_Stock.Stock_date; > > would product these results > Stock.Stock_date Stock.Stock_qty Previous_Day_Stock.Stock_date > Previous_Day_Stock.Stock_qty Stock_Variance > 10/1/2012 432 9/28/2012 999 -567 > 10/2/2012 124 10/1/2012 432 -308 > 10/3/2012 753 10/2/2012 124 629 > > GK > > On Thu, Oct 11, 2012 at 12:15 PM, Philippe lelynx <phpons at gmail.com> 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 >> Website: http://www.databaseadvisors.com >> > > >