How to carry forward inventory balances

[Karen Middleton]

I have data stored in a table in the following format.
Product# Month Year Inventory Balance


For example the inventory balances for a Product A exists in the
table as follows:


Month Qty on Hand
======= ===========
001.2004 120
004.2004 235
006.2004 89
011.2004 42
003.2005 980

======= ===========


Now if the user is trying to report all inventory balances as of
001.2005 even though I do not have a value in the cube for product A I
expect the system to go and pick the last know stock on hand which is
is 42 the known stock on hand qty in 011.2004

Please advice how I can implement these kind of query to solve this
problem. Bottom line what I expect is for a given product A if there is
data in periods 1, 5 and 9 the system carry forward the balance in period
1 to periods 2,3,4 similarly the balance in period 5 must be carried
over to periods 6,7 & 8 similarly the balance in period 9 must be carried
forward to period 10, 11 and 12


Please help me coding the query SQL or the algorithm to arrive at this
kind of reporting.


Thanks
Karen

 

[Guest]

Try,

use northwind
go

create table t (
colA datetime,
quantity int
)
go

insert into t values('20040101', 120)
insert into t values('20040401', 235)
insert into t values('20040601', 89)
insert into t values('20040701', 42)
insert into t values('20050301', 980)
go

select top 12
identity(int, 1, 1) as number
into
number
from
sysobjects as a
go

create view my_view
as
select
*
from
(
select
ltrim(col_year) + right('00' + ltrim(number.number), 2) + '01'
from
(
select distinct
year(colA)
from
t
) as a(col_year)
cross join
number
) as ym(col_date)
left join
t
on t.colA = ym.col_date
go

select
a.col_date,
coalesce(a.quantity, b.quantity)
from
my_view as a
left join
my_view as b
on a.quantity is null and b.col_date = (select max(c.colA) from t as c
where c.colA < a.col_date and c.quantity is not null)
go

drop view my_view
go

drop table number, t
go


AMB