关于sql 2000的日期问题，较复杂
我有一张表，字段为id,hpid,storage,date，意思是id，库存数和日期。
同一id的记录不是每天都有，现在我想查询出某段时间内的库存数量，该如何编写sql语句？
比如表内容如下：
id hpid storage date
1 1 1000 2012-10-10
2 1 2000 2012-10-12
3 1 5000 2012-10-13
4 1 5000 2012-10-14
5 1 4500 2012-10-18
6 1 5340 2012-10-22
7 1 684 2012-11-28
8 1 10000 2012-12-12

同一hpid，并不是每天都有记录，但是查询结果想要显示10~11月份每天的数据，如果当天没数据，则显示上条数据的记录。
搜索结果应该如下：
hpid storage date
  1 0 2012-10-1
  1 0 2012-10-2
  1 0 2012-10-2
  ......
  1 1000 2012-10-10
  1 1000 2012-10-11
  1 2000 2012-10-12
  1 5000 2012-10-13
  1 5000 2012-10-14
  1 5000 2012-10-15
  1 5000 2012-10-16
  ......
  1 684 2012-11-30

想得到上述结果，想了很久不知如何编写sql语句，望大家指教！

------解决方案--------------------
declare @库存表 table(id int,hpid int,storage varchar(50),[date] date)
insert into @库存表
select 1,1,1000,'2012-10-10'
union all
select 2,1,2000,'2012-10-12'
union all
select 3,1,5000,'2012-10-13'
union all
select 4,1,5000,'2012-10-14'
union all
select 5,1,4500,'2012-10-18'
union all
select 6,1,5340,'2012-10-22'
union all
select 7,1,684,'2012-11-28'
union all
select 8,1,10000,'2012-12-12'
select 
number
,hpid
,isnull(isnull(storage,
(
select top 1 storage from 
(
select 
d.number,h.hpid,s.storage,DATEADD(dd,d.number,'2012-10-01') [date]
from 
(select * from master..spt_values where type='p' and number between 0 and (select DATEDIFF(dd,'2012-10-01','2012-11-30'))) d 
cross join (select distinct hpid from @库存表) h
left join @库存表 s on h.hpid=s.hpid and DATEADD(dd,d.number,'2012-10-01')=s.date
) tt
where hpid=t.hpid and number<t.number and storage is not null order by number desc 
))
,0) storage
,[date]
from
(
select 
d.number,h.hpid,s.storage,DATEADD(dd,d.number,'2012-10-01') [date]
from 
(select * from master..spt_values where type='p' and number between 0 and (select DATEDIFF(dd,'2012-10-01','2012-11-30'))) d 
cross join (select distinct hpid from @库存表) h
left join @库存表 s on h.hpid=s.hpid and DATEADD(dd,d.number,'2012-10-01')=s.date
) t

------解决方案--------------------

SQL code

--> 测试数据：#tb
IF OBJECT_ID('TEMPDB.DBO.#tb') IS NOT NULL DROP TABLE #tb
GO 
CREATE TABLE #tb([id] INT,[hpid] INT,[storage] INT,[date] DATETIME)
INSERT #tb
SELECT 1,1,1000,'2012-10-10' UNION ALL
SELECT 2,1,2000,'2012-10-12' UNION ALL
SELECT 3,1,5000,'2012-10-13' UNION ALL
SELECT 4,1,5000,'2012-10-14' UNION ALL
SELECT 5,1,4500,'2012-10-18' UNION ALL
SELECT 6,1,5340,'2012-10-22' UNION ALL
SELECT 7,1,684,'2012-11-28' UNION ALL
SELECT 8,1,10000,'2012-12-12'
--------------开始查询--------------------------

DECLARE @begindate DATETIME,@endtime DATETIME
SET @begindate='2012-10-01'
SET @endtime='2012-11-30'

SELECT [id]=ISNULL((SELECT MAX([id]) FROM #tb WHERE  [date]<=b.[date] ),1),
[hpid]=ISNULL((SELECT MAX([hpid]) FROM #tb WHERE  [date]<=b.[date] ),1),
[storage]=ISNULL((SELECT MAX([storage]) FROM #tb WHERE  [date]<=b.[date]),0),
[date]
FROM  (
    SELECT DATEADD(dd,number,@begindate) AS [date] ,[id],[hpid],[storage]
    FROM master..spt_values 
    LEFT JOIN #tb b ON DATEADD(dd,number,@begindate)=b.[date]
    WHERE type=