How to carry forward inventory balances

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
KarenTry,
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
"Karen Middleton" wrote:

> 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
>