[フライングしやすい]滞留倉庫資材予定受領
1274 ワード
最近、会社は私にアラートを書いて、毎週QC、PMCなどに送信するように要求しました.
需要:滞留倉庫品目の今後2ヶ月以内の予測受入レポート.
ストアド・プロシージャを書き込みます.
需要:滞留倉庫品目の今後2ヶ月以内の予測受入レポート.
ストアド・プロシージャを書き込みます.
-- =============================================
-- Author: <David Gong>
-- Create date: <2013-05-23>
-- Description: < >
-- =============================================
ALTER Proc [dbo].[UP_IdleStockPreNeed]
(
@0 as nvarchar(15)=null
)
as
begin
--- --------
declare @startdate as nvarchar(8),@enddate as nvarchar(8)
set @startdate=Convert(varchar(10),Getdate(),112)
set @enddate=Convert(varchar(10),dateadd(MONTH,2,Getdate()),112) ;
with ct as(
select a.Item,sum(a.Qty) as Qty from (
select TD004 as Item,sum(cast(TD008-TD009+TD024-TD025 as int)) as Qty from COPTD
where TD013>=@startdate and TD013<=@enddate
group by TD004
-- -------
UNION ALL
select TB003 as Item,sum(cast(TB004-TB005 as decimal(16,2))) AS Qty from MOCTA inner join MOCTB on TA001=TB001 AND TA002=TB002
where TA009>=@startdate and TA009<=@enddate
GROUP BY TB003) a
group by a.Item)
select b.*,ct.Qty as from (
select MC001 ,MB002,MB003,SUM(MC007) ,SUM(MC008)
from INVMC INNER JOIN INVMB on MC001=MB001
where MC002='04' AND MC007>0
GROUP BY MC001,MB002,MB003) b left join ct on ct.Item=b.
end