Sunlike Stock
很多朋友玩了sunlike之後都會有些小聰明
但是不知道SUNLIKE的設計邏輯小聰明便無法發揮
如同空有小宇宙卻無法燃燒的窘境
所以今天先來談談庫存盤點表 讓大家對sunlike有基本的小概念
首先庫存是
"上個月"的"月盤存庫存"+"本月份的所有單據"
不囉唆下述就來談談自定義該怎麼寫

--先規範一些日期的變數

--月份的變數(前月份)
declare @A varchar(5)
SET @A=MONTH(getdate())-1

--年份的變數(本年度)
declare @B varchar(5)
SET @B=YEAR(getdate())

--月份的變數(本月份)
declare @C varchar(5)
SET @C=MONTH(getdate())

--月份的變數(前月份)
declare @D varchar(5)
SET @D=MONTH(getdate())-1

--年份的變數(前年度)
declare @E varchar(5)
SET @E=YEAR(getdate())

--遇到1月份自動改變年月 不然會錯誤
IF @A='0'
BEGIN
SET @D='12'
SET @E=YEAR(getdate())-1
END

;with
--期末庫存表上月份
AA(WH,PRD_NO,QTY,DD,ZZ)
AS(
select wh,prd_no,(qty-isnull(qty_ln,0)+isnull(qty_br,0)),getdate(),'STOK' from sprd
where yy=@E and MM=@D and (qty-isnull(qty_ln,0)+isnull(qty_br,0))>0
),

--成品繳庫單當月份1
MM(WH,PRD_NO,QTY,DD,ZZ)
AS(
select a.wh,a.prd_no,a.QTY,a.mm_dd,'MM'
from
tf_mm0 as a inner join mf_mm0 as b
on a.mm_no=b.mm_no
where
b.chk_man is not null and year(a.mm_dd)=@B and month(a.mm_dd)=@C
),

--進貨單銷退單當月份2
PCSB(WH,PRD_NO,QTY,DD,ZZ)
AS(
select a.wh,a.prd_no,a.QTY,a.PS_dd,a.PS_ID
from
tf_PSS as a inner join mf_PSS as b
on a.PS_no=b.PS_no
where
(a.ps_id='PC' or a.ps_id='SB') and b.chk_man is not null and year(a.PS_dd)=@B and month(a.PS_dd)=@C
),

--進退單銷貨單當月份3
PBSA(WH,PRD_NO,QTY,DD,ZZ)
AS(
select a.wh,a.prd_no,-1*(a.QTY),a.PS_dd,a.PS_ID
from
tf_PSS as a inner join mf_PSS as b
on a.PS_no=b.PS_no
where
(a.ps_id='PB' or a.ps_id='SA') and b.chk_man is not null and year(a.PS_dd)=@B and month(a.PS_dd)=@C
),

--生托領補單當月份4
ML(WH,PRD_NO,QTY,DD,ZZ)
AS(
select a.wh,a.prd_no,-1*(a.QTY),a.ML_dd,a.MLID
from
tf_ML as a inner join mf_ML as b
on a.ML_no=b.ML_no
where
(a.mlid='ML' or a.MLID='M3' or a.MLID='M4' or a.MLID='M6')
and b.chk_man is not null and year(a.ml_dd)=@B and month(a.ml_dd)=@C
),

--生託退料單當月份5
MJ(WH,PRD_NO,QTY,DD,ZZ)
AS(
select a.wh,a.prd_no,a.QTY,a.ML_dd,a.MLID
from
tf_ML as a inner join mf_ML as b
on a.ML_no=b.ML_no
where
(a.mlid='M2' or a.MLID='M5')
and b.chk_man is not null and year(a.ml_dd)=@B and month(a.ml_dd)=@C
),

--調整非領退單當月份6
IJ(WH,PRD_NO,QTY,DD,ZZ)
AS(
select a.wh,a.prd_no,a.QTY,a.IJ_dd,a.IJ_ID
from
TF_IJ as a inner join mf_IJ as b
on a.IJ_no=b.IJ_no
where b.chk_man is not null and year(a.IJ_dd)=@B and month(a.IJ_dd)=@C
),

--借出入單當月份7
LBBN(WH,PRD_NO,QTY,DD,ZZ)
AS(
select a.wh,a.prd_no,a.QTY,a.BL_dd,a.BL_ID
from
TF_BLN as a inner join mf_BLN as b on a.BL_no=b.BL_no
where (A.BL_ID='LB' or A.BL_ID='BN') and b.chk_man is not null and year(a.bl_dd)=@B and month(a.bl_dd)=@C
),

--借出入單當月份8
LNBB(WH,PRD_NO,QTY,DD,ZZ)
AS(
select a.wh,a.prd_no,-1*(a.QTY),a.BL_dd,a.BL_ID
from
TF_BLN as a inner join mf_BLN as b on a.BL_no=b.BL_no
where (A.BL_ID='LN' or A.BL_ID='BB') and b.chk_man is not null and year(a.bl_dd)=@B and month(a.bl_dd)=@C
),

--切割單當月份9
MDA(WH,PRD_NO,QTY,DD,ZZ)
AS(
select wh_prd,mrp_no,-1*(QTY),mb_dd,'MD'
from
mf_mb
where chk_man is not null and year(mb_dd)=@B and month(mb_dd)=@C
),

--切割單當月份10
MDE(WH,PRD_NO,QTY,DD,ZZ)
AS(
select a.wh,a.prd_no,a.QTY,a.mb_dd,'MD'
from
TF_mb as a inner join mf_mb as b on a.mb_no=b.mb_no
where b.chk_man is not null and year(a.mb_dd)=@B and month(a.mb_dd)=@C
),

--原料調撥單當月份11
MCA(WH,PRD_NO,QTY,DD,ZZ)
AS(
select a.wh2,a.prd_no,a.QTY,a.mc_dd,'MC'
from
TF_mc as a inner join mf_mc as b on a.mc_no=b.mc_no
where b.chk_man is not null and year(a.mc_dd)=@B and month(a.mc_dd)=@C
),

--原料調撥單當月份12
MCE(WH,PRD_NO,QTY,DD,ZZ)
AS(
select a.wh1,a.prd_no,-1*(a.QTY),a.mc_dd,'MC'
from
TF_mc as a inner join mf_mc as b on a.mc_no=b.mc_no
where b.chk_man is not null and year(a.mc_dd)=@B and month(a.mc_dd)=@C
),

--庫存調撥單當月份13
ICA(WH,PRD_NO,QTY,DD,ZZ)
AS(
select a.wh2,a.prd_no,a.QTY,a.Ic_dd,'IC'
from
TF_Ic as a inner join mf_Ic as b on a.Ic_no=b.Ic_no
where b.chk_man is not null and year(a.Ic_dd)=@B and month(a.Ic_dd)=@C
),

--庫存調撥單當月份14
ICE(WH,PRD_NO,QTY,DD,ZZ)
AS(
select a.wh1,a.prd_no,-1*(a.QTY),a.Ic_dd,'IC'
from
TF_Ic as a inner join mf_Ic as b on a.Ic_no=b.Ic_no
where b.chk_man is not null and year(a.Ic_dd)=@B and month(a.Ic_dd)=@C
),

--資料加總
TL(WH,PRD_NO,QTY,DD,ZZ)
as(
select * from AA
union all
select * from MM
union all
select * from PCSB
union all
select * from PBSA
union all
select * from ML
union all
select * from MJ
union all
select * from IJ
union all
select * from LBBN
union all
select * from LNBB
union all
select * from MDA
union all
select * from MDE
union all
select * from MCA
union all
select * from MCE
union all
select * from ICA
union all
select * from ICE
),

--庫存盤點表
SK(WH,PRD_NO,QTY)
as(
select wh,prd_no,sum(qty)
from tl
group by wh,prd_no
HAVING SUM(qty) > 0
)

select * from sk

有問題歡迎發問