今天在做财务总监要求的一个报表,需要采集一个上月月末的结存。易飞ERP系统中有个月档：期初+本期发生数量。通常企业在本月的月初比如10号做上月的库存结转，也就意味着1-10号之前如果按照这个计算方法那么上月月末的库存是0；
上月末库存如何解决呢？如果能获取指定日期的结存，那就完美解决了。
CCWN:写了一个现成的SQL，包装成函数：

-- =============================================      
-- Author: <David Gong>      
-- Create date: <2015-11-12>      
-- Description: <指定日期结存 感谢：CCWEN>      
-- ============================================= 
Create function [dbo].[GetDateLineQty](
@Item AS VARCHAR(20),
@DateLine as nvarchar(10)
)returns decimal(18,4)
as 
begin

declare @qty as decimal(18,4)

select @qty=(A.QTY+B.LC004) FROM (
select LA001,LA009,sum(LA011*LA005) AS QTY from INVLA  WHERE [email protected] AND LA004<[email protected] and LA004>=LEFT(@DateLine,6)+'01'
GROUP BY LA001,LA009 ) A LEFT JOIN (
select LC001,LC003,LC004 from INVLC WHERE LC002 =LEFT(@DateLine,6)) B ON  A.LA001=B.LC001 AND A.LA009=B.LC003 left join INVMB
ON A.LA001=INVMB.MB001

return @qty

end

那么来测试下吧：

--===================================
--用途:前二十大销售产品
--作者:龚德辉
--日期:2015-11-12
--===================================
ALTER Proc [dbo].[UP_Top20Product]
as
begin
declare @yymm  as nvarchar(6)  --当年一月
declare @cyymm  as nvarchar(6) --当月
declare @lyymm  as nvarchar(6) --当月的上月
declare @cyymm1 as nvarchar(6) --后一月
declare @cyymm2 as nvarchar(6) --后二月
declare @m  as int --累计月份




set @yymm=datename(yyyy, getdate())+'01'
set @lyymm=convert(char(6),DATEADD(month,-1,getdate()),112)
set @cyymm=convert(char(6),getdate(),112)
set @cyymm1=convert(char(6),DATEADD(month,1,getdate()),112)
set @cyymm2=convert(char(6),DATEADD(month,2,getdate()),112);

set @m =DATEPART(MONTH,DATEADD(month,-1,getdate()));
declare @lmd as nvarchar(8)
set @lmd= convert(char(8),dateadd(dd,-day(getdate()),getdate()) ,112);


with cr
as
(
select 
Item
,sum(当月开票)   as 当月开票
,sum(后一月开票) as 后一月开票
,sum(后两月开票) as 后两月开票
from(
select Item,case when [Year]+[Month][email protected] then sum(Acount)  else 0 end as  当月开票,
            case when [Year]+[Month][email protected] then sum(Acount) else 0 end as 后一月开票,
            case when [Year]+[Month][email protected] then sum(Acount) else 0 end as 后两月开票 [email protected]
    from  UF_SalePlanning 
    where left(BillDate,6) in (@cyymm,@cyymm1,@cyymm2)
    group by Item,Year,Month
) as C
group by C.Item
),
 cr1
as
(
select TH004 ,SUM(TH008) AS Quantities,SUM(TH037) AS Amount 
from  COPTG INNER JOIN COPTH ON TG001=TH001 AND TG002=TH002 
where LEFT(TG003,6)[email protected]  AND TH020='Y'
group by TH004

)


select 
 MB002 as 产品
,A.TH004 as 品号
,A.Quantities 累计数量
,A.Amount 累计金额
,A.Quantities/@m as 平均数量
--,isnull(B.LB003,0) as 上月库存
,[dbo].[GetDateLineQty](A.TH004,@lmd) as 上月库存
,cr1.Quantities as 本月数量
,cr1.Amount as 本月金额
,当月开票
,后一月开票
,后两月开票 
from (
select top 20 TH004 ,SUM(TH008) AS Quantities,SUM(TH037) AS Amount 
from COPTG INNER JOIN COPTH ON TG001=TH001 AND TG002=TH002  
where LEFT(TG003,6)>[email protected]  and LEFT(TG003,6)<[email protected] AND TH020='Y'
group by TH004
order by SUM(TH037) desc
) A
    left join (select LB001,LB003 from INVLB where  [email protected]) as B on B.LB001=A.TH004
    left join INVMB on A.TH004=MB001
    left join cr on A.TH004=cr.Item
    left join cr1 on A.TH004=cr1.TH004
order by A.Amount desc

end