向高手求教进销存系统的账龄算法sql

Question

--新建表@t，求写成一个存储过程，传递一个截至日期的参数，比如2009-3-1怎么实现这种格式的账龄表，要按物资类别分类，因为同一种物资财务有时候要按耗材，有时按材料入账。
--物资类别  物料编码   账龄（天）   数量   总金额 
--测试数据，我插入的数据记录比较多，大家也可以自定义些数据，不要那么多。
declare  @t table(
物料编码  varchar(20),
出入库类别  varchar(20) ,
出入库日期  datetime,
物资类别  varchar(20),
数量  float,
单价  money,
总金额 money
)
insert @t
select    'A004'  ,'出库'  ,'2008-8-5'  ,'耗材'  ,-40  ,5  ,-200
union select   'A004'  ,'出库'  ,'2008-8-5'  ,'耗材'  ,-100  ,5  ,-500
union select   'A004'  ,'出库'  ,'2008-9-25'  ,'耗材'  ,-5  ,5  ,-25
union select   'A004'  ,'出库'  ,'2008-9-25'  ,'耗材'  ,-7  ,5  ,-35
union select   'A004'  ,'出库'  ,'2008-10-24'  ,'耗材'  ,-10  ,5  ,-50
union select   'A004'  ,'出库'  ,'2009-1-15'  ,'耗材'  ,-5  ,5  ,-25
union select   'A004'  ,'出库'  ,'2009-2-11'  ,'耗材'  ,-4  ,5  ,-20
union select   'A004'  ,'出库'  ,'2009-4-15'  ,'耗材'  ,-10  ,5  ,-50
union select   'A004'  ,'入库'  ,'2008-6-5'  ,'耗材'  ,962  ,5  ,4810
union select   'A004'  ,'入库'  ,'2009-1-19'  ,'原材料'  ,100  ,9.4017  ,940.17
union select   'A004'  ,'入库'  ,'2009-3-31'  ,'耗材'  ,5  ,5  ,25union select   'A045'  ,'出库'  ,'2008-7-15'  ,'原材料'  ,-1  ,10  ,-10
union select   'A045'  ,'出库'  ,'2008-10-14'  ,'原材料'  ,-1  ,10  ,-10
union select   'A045'  ,'出库'  ,'2008-11-5'  ,'原材料'  ,-4  ,10  ,-40
union select   'A045'  ,'出库'  ,'2009-2-9'  ,'原材料'  ,-1  ,10  ,-10
union select   'A045'  ,'入库'  ,'2008-6-5'  ,'原材料'  ,8  ,10  ,80select * from @t

Answer 1

這個是我以前寫的庫齡存儲過程,你參考一下,其實就是運用sum(case when) 語句了.Alter Procedure kuling 
@Date Datetime='2007-02-15' 
As 
Declare @Bdate Datetime 
Declare @Edate Datetime 
Begin 
Select  @Bdate=Dateadd(dd,-(Datepart(dd,@Date)-1),@Date) 
Select  @Edate=Dateadd(dd,-1,Dateadd(mm,1,Dateadd(dd,-(Datepart(dd,@Date)-1),@Date))) 
Select   Cast(Datepart(mm,@Date) as Char(2)) As '月份', 
            Sum(Case When  IoDate>= Dateadd(mm,-3,@Bdate) Then IOStockQnty Else 0 End  ) As  '3個月以內數量', 
            Sum(Case When ( IoDate>= Dateadd(mm,-6,@Bdate) and IoDate<Dateadd(mm,-3,@Bdate)) Then IOStockQnty Else 0 End  ) As '3-6個月數量', 
        Sum(Case When  IoDate< Dateadd(mm,-6,@Bdate)  Then IOStockQnty Else 0 End  ) As '6個月以上數量' 
   Into #數量 
From stk_historyIO 
Where  IOStockQnty>0 Select   Cast(Datepart(mm,@Date) as Char(2)) As '月份', 
            Sum(Case When  IoDate>= Dateadd(mm,-3,@Bdate) Then dbo.PGetStockPrice(PartNum,Revision,@Bdate)*IOStockQnty Else 0 End  ) As  '3個月以內金額', 
            Sum(Case When ( IoDate>= Dateadd(mm,-6,@Bdate) and IoDate<Dateadd(mm,-3,@Bdate)) Then dbo.PGetStockPrice(PartNum,Revision,@Bdate)*IOStockQnty Else 0 End  ) As '3-6個月金額', 
         Sum(Case When  IoDate< Dateadd(mm,-6,@Bdate)  Then dbo.PGetStockPrice(PartNum,Revision,@Bdate)*IOStockQnty Else 0 End  ) As '6個月以上金額' 
    Into #金額 
From stk_historyIO 
Where  IOStockQnty>0 Select     Cast(Datepart(mm,@Date) as Char(2)) As '月份', 
            Sum(Case When  t1.IoDate>= Dateadd(mm,-3,@Bdate) Then  dbo.PDL_FunCalcArea(t1.PartNum,t1.Revision,t2.LLPiece,1)*IOStockQnty  Else 0 End  ) As  '3個月以內面積', 
            Sum(Case When ( t1.IoDate>= Dateadd(mm,-6,@Bdate) and IoDate<Dateadd(mm,-3,@Bdate)) Then dbo.PDL_FunCalcArea(t1.PartNum,t1.Revision,t2.LLPiece,1)*IOStockQnty  Else 0 End  ) As '3-6個月面積', 
           Sum(Case When  t1.IoDate< Dateadd(mm,-6,@Bdate)  Then  dbo.PDL_FunCalcArea(t1.PartNum,t1.Revision,t2.LLPiece,1)*IOStockQnty  Else 0 End  ) As '6個月以上面積' 
Into #面積 
From stk_historyIO t1,ProdBasic t2 
Where t1.PartNum=t2.PartNum 
And t1.Revision=t2.Revision 
And t1.IOStockQnty>0 
   
Select * 
From #數量 t1,#金額 t2,#面積 t3 
Where t1.月份=t2.月份 and t1.月份=t3.月份 End 

Answer 2

是要这个吗?
--测试数据，我插入的数据记录比较多，大家也可以自定义些数据，不要那么多。 
create table tb( 
物料编码  varchar(20), 
出入库类别  varchar(20) , 
出入库日期  datetime, 
物资类别  varchar(20), 
数量  float, 
单价  money, 
总金额 money 
) 
insert tb 
select    'A004'  ,'出库'  ,'2008-8-5'  ,'耗材'  ,-40  ,5  ,-200 
union select  'A004'  ,'出库'  ,'2008-8-5'  ,'耗材'  ,-100  ,5  ,-500 
union select  'A004'  ,'出库'  ,'2008-9-25'  ,'耗材'  ,-5  ,5  ,-25 
union select  'A004'  ,'出库'  ,'2008-9-25'  ,'耗材'  ,-7  ,5  ,-35 
union select  'A004'  ,'出库'  ,'2008-10-24'  ,'耗材'  ,-10  ,5  ,-50 
union select  'A004'  ,'出库'  ,'2009-1-15'  ,'耗材'  ,-5  ,5  ,-25 
union select  'A004'  ,'出库'  ,'2009-2-11'  ,'耗材'  ,-4  ,5  ,-20 
union select  'A004'  ,'出库'  ,'2009-4-15'  ,'耗材'  ,-10  ,5  ,-50 
union select  'A004'  ,'入库'  ,'2008-6-5'  ,'耗材'  ,962  ,5  ,4810 
union select  'A004'  ,'入库'  ,'2009-1-19'  ,'原材料'  ,100  ,9.4017  ,940.17 
union select  'A004'  ,'入库'  ,'2009-3-31'  ,'耗材'  ,5  ,5  ,25 
union select  'A045'  ,'出库'  ,'2008-7-15'  ,'原材料'  ,-1  ,10  ,-10 
union select  'A045'  ,'出库'  ,'2008-10-14'  ,'原材料'  ,-1  ,10  ,-10 
union select  'A045'  ,'出库'  ,'2008-11-5'  ,'原材料'  ,-4  ,10  ,-40 
union select  'A045'  ,'出库'  ,'2009-2-9'  ,'原材料'  ,-1  ,10  ,-10 
union select  'A045'  ,'入库'  ,'2008-6-5'  ,'原材料'  ,8  ,10  ,80 
go
CREATE PROCEDURE 帐龄计算
@d datetime
AS
SET NOCOUNT ON;
select 物资类别,物料编码,datediff(dd,min(出入库日期),@d) as 账龄,
 sum(case when 出入库类别='入库' then 数量 else 数量*(-1) end) as 数量,
 sum(case when 出入库类别='入库' then 数量*单价 else 数量*(-1)*单价 end) as 总金额
from tb group by 物资类别,物料编码
GO
exec 帐龄计算 '2009-03-01'
go
drop procedure 帐龄计算
drop table tb
/*
物资类别                 物料编码                 账龄          数量                     总金额
-------------------- -------------------- ----------- ---------------------- ----------------------
耗材                   A004                 269         1148                   5740
原材料                  A004                 41          100                    940.17
原材料                  A045                 269         15                     150
*/

Answer 3

就是库龄，也称作账龄，叫法不同而已，qianjin036a 理解的是准确的，就是那么写，但是程序代码我还没有仔细测试，看看算法有无漏洞。