garykjos at gmail.com
garykjos at gmail.com
Fri Oct 12 08:14:43 CDT 2012
Yes, but he didn't mention individual products in his example so I didn't put that in mine either. GK On , jwcolby <jwcolby at colbyconsulting.com> wrote: > 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. > ie 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 > -- > AccessD mailing list > AccessD at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/accessd > Website: http://www.databaseadvisors.com