[AccessD] sql to get stock variation

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
>



-- 
Gary Kjos
garykjos at gmail.com