22,209
社区成员
发帖
与我相关
我的任务
分享
DECLARE @StartDate DATETIME,@EndDate DATETIME
SELECT @StartDate='2015-01-01',@EndDate='2015-01-03'
;WITH CTE AS(
SELECT T2.商品编号
,SUM(T2.销售数量)OVER(PARTITION BY T2.商品编号)[销售数量]
,SUM(T2.来货数量)OVER(PARTITION BY T2.商品编号)[来货数量]
,T2.结存数量
,ROW_NUMBER()OVER(PARTITION BY T2.商品编号 ORDER BY T1.生成时间 DESC)RN
FROM #table1 T1
JOIN #table2 T2 ON T1.单号=T2.单号
WHERE T1.生成时间>=@StartDate AND T1.生成时间<=@EndDate
)
SELECT T1.商品编号,ISNULL(T5.结存数量,0) AS[期初数量],T1.销售数量,T1.来货数量,T1.结存数量
FROM CTE T1
OUTER APPLY(
SELECT TOP 1 T4.结存数量 FROM #table1 T3 JOIN #table2 T4 ON T3.单号=T4.单号
WHERE T1.RN=1 AND T1.商品编号=T4.商品编号 AND T3.生成时间<@StartDate
ORDER BY T3.生成时间 DESC
)T5
WHERE T1.RN=1
create table #table1 (单号 int,生成时间 datetime)
create table #table2 (单号 int ,商品编号 int ,销售数量 int,来货数量 int,结存数量 int)
insert into #table1(单号,生成时间)
values (10,'2015-01-01')
,(11,'2015-01-02'),
(12,'2015-01-03')
insert into #table2(单号,商品编号,销售数量,来货数量,结存数量)
values(10,1,0,10,10)
,(10,2,0,5,5)
,(11,1,0,1,11)
,(12,1,3,0,8)
,(12,2,1,0,4)
declare @stdate smalldatetime,@endate smalldatetime
select @stdate='2015-01-02',
@endate='2015-01-03'
;with sel as
( select a.生成时间,b.单号,商品编号,销售数量,来货数量,结存数量 from #Table1 a
join #table2 b on a.单号=b.单号
)
select a.商品编号, isnull(b.期初,0) as 期初,sum( 销售数量) 销售数量,sum(来货数量) 来货数量,isnull(b.期初,0)+sum(来货数量)- sum( 销售数量) as 结存数量
from sel a
outer apply(select top 1 结存数量 as 期初 from sel where 商品编号=a.商品编号
and 生成时间< @stdate order by 生成时间 desc ) b
where a.生成时间 between @stdate and @endate
group by a.商品编号,期初