今天在做财务总监要求的一个报表,需要采集一个上月月末的结存。易飞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