饅頭小舖

DECLARE @Strat_date date  = '2013/1/1',@End_date date ='2013/12/31';
	--事先定義只要2013年的資料，在2013年以前計算後當作期初數量
	WITH TEMP_START(mid,pqrt_START,ptotal_START) AS 
	(
		SELECT mid,SUM(pqrt) AS pqrt_START,SUM(ptotal) AS ptotal_START  FROM(
			SELECT mid,SUM(pqrt) as pqrt,SUM(ptotal) AS ptotal FROM 進貨
			WHERE date <@Strat_date
			GROUP BY mid
			UNION ALL
			SELECT mid,SUM(pqrt) as pqrt,SUM(ptotal) AS ptotal FROM 進貨退回
			WHERE date <@Strat_date
			GROUP BY mid
			UNION ALL
			SELECT mid,SUM(pqrt)*-1 as pqrt,SUM(ptotal) AS ptotal FROM 銷貨
			WHERE date <@Strat_date
			GROUP BY mid
			UNION ALL
			SELECT mid,SUM(pqrt)*-1 as pqrt,SUM(ptotal) AS ptotal FROM 銷貨退回
			WHERE date <@Strat_date
			GROUP BY mid
		) A
		GROUP BY mid
	),--計算期初數量，將進貨、進貨退回、銷貨、銷貨退回分別計算後，把資料表合併在一個CTE中
	TEMP_UNION AS
	(
		SELECT *,pqrt AS pqrt_IN,ptotal AS ptotal_IN,0 as pqrt_OUT,0 as ptotal_OUT FROM 進貨
		WHERE date >= @Strat_date AND date <=@End_date
		UNION ALL
		SELECT *,pqrt AS pqrt_IN,ptotal AS ptotal_IN,0 as pqrt_OUT,0 as ptotal_OUT FROM 進貨退回
		WHERE date >= @Strat_date AND date <=@End_date
		UNION ALL
		SELECT *,0 AS pqrt_IN,0 AS ptotal_IN,pqrt as pqrt_OUT,ptotal as ptotal_OUT FROM 銷貨
		WHERE date >= @Strat_date AND date <=@End_date
		UNION ALL
		SELECT *,0 AS pqrt_IN,0 AS ptotal_IN,pqrt as pqrt_OUT,ptotal as ptotal_OUT FROM 銷貨退回
		WHERE date >= @Strat_date AND date <=@End_date
	),--將2013年的進貨數量、進貨退回、銷貨量、銷貨退回合併在同一張資料表  (事先已將同一筆進貨單、進貨退回單、銷貨單、銷貨退回單依照產品代號加總)
	TEMP_ACC AS 
	(
		SELECT a.mid,a.date,a.code,SUM(b.pqrt_IN)
			-SUM(b.pqrt_OUT) AS total_ACC FROM TEMP_UNION a
		JOIN TEMP_UNION b
		ON a.mid = b.mid AND a.date >= b.date AND (a.date != b.date OR a.code <= b.code)
		GROUP BY a.mid,a.code,a.date
	)--計算累計數量
	/*
		選取產品代號、日期、紀錄單號以便作為群組使用(防止同一天有多筆不同紀錄單號的進出貨)
		先計算進貨的總計，與出貨的總計，然後將上一個CTE產生的進出貨數量相減取總和
		合計的條件是：產品代號相同的，將小於自己日期的加總在一起，而後面的條件是若一天內有多筆紀錄，依照紀錄單號的大小加總
	*/	
	SELECT TEMP_UNION.mid as [產品代號],TEMP_UNION.[date] as [日期],TEMP_UNION.code as [紀錄單號],
		TEMP_UNION.flag as [進出貨],a.pqrt_START as [期初數量],TEMP_UNION.pqrt_IN as [進貨數量],TEMP_UNION.pqrt_OUT as [出貨數量],
	a.pqrt_START + b.total_ACC as [累計數量]
	FROM  TEMP_UNION 
	LEFT OUTER JOIN TEMP_START a
	ON TEMP_UNION.mid = a.mid
	LEFT OUTER JOIN TEMP_ACC b
	ON a.mid = b.mid AND TEMP_UNION.date = b.date AND TEMP_UNION.code = b.code
	LEFT OUTER JOIN SL01
	ON TEMP_UNION.mid = SL01.mid
	WHERE a.mid IS NOT NULL
	ORDER BY TEMP_UNION.mid,TEMP_UNION.date