How to find the last known value in the following dataset

  • Thread starter Thread starter Karen Middleton
  • Start date Start date
K

Karen Middleton

Hello All

I have the following scenario:


Material Month StockMovement
======== ===== =============
ABC001 001 120
ABC001 002
ABC001 003
ABC001 004 158
ABC001 005
ABC001 006

XYZ001 001
XYZ001 002 28
XYZ001 003
XYZ001 004
XYZ001 005 32
XYZ001 006 18


In this case in the month of Oct 2005 when I take my stock movement
since the last known movement for material ABC001 in 158 that should
be my movement for ABC001 in Period 010. Similarly, for material
XYZ001 the last known movement in period 006 is 18 that must be the
stock movement for material XYZ001 the stock movement in Period 004 is
28 which is the last known movement in period 002.


Can you kindly share the code and ideas for programming this scenario
in both Access and SQL Server. Is there a built-in function that can
assist here.

Thanks
Karen
 
select Material, Month, StockMovement from table inner join
( select material, max(month) as themonth from table where where
stockmovement is not null group by material) as a
on table.material= a.material and table.Month=a.themonth


I didn't check this, so it is on the fly , but try it... The inside query
finds the maximum month for each material with a non-null stockmovement.
Then that is joined back to the orig table to pull the row...


--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)

I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
 
Back
Top