อยากถามคนที่ทําโปรแกรม Ware House หรือ Inventory ครับเรื่อง Design Database ส่วน Master

Select 30 As Total   /*ยอดยกมา*/
UNION ALL
Select 30 + 5 As Total /* ยอดยกมา + ยอดเริ่มเคลื่อนไหว*/

SELECT 'HED' AS REP_GROUP, WHS_BAL.WAREHOUSE, WHS_BAL.PART_NO, 'ยอดยกมา' AS DOC_NO, '  ' AS SEQ, CAST('3/3/2014' AS TIMESTAMP) AS DOC_DATE, CAST('00' AS INTEGER) AS PROCESS_SEQ, 'AA' AS TRAN_CODE, 'AAA' AS TRAN_TYPE, MIN(WHS_STAT.BEGIN_BAL) AS BEGIN_BAL, MIN(WHS_STAT.BEG_AMT) AS BEG_AMT, SUM(HIS_STAT.REC_QTY) AS REC_QTY, SUM(HIS_STAT.ISS_QTY) AS ISS_QTY, SUM(HIS_STAT.ADJ_INC) AS ADJ_INC, SUM(HIS_STAT.ADJ_DEC) AS ADJ_DEC, 0.00 AS TRAN_QTY, 0.00 AS TRAN_QTYSORT, SUM(HIS_STAT.TRAN_AMT) AS TRAN_AMT, '0' AS POST_FLAG, MIN(WHS_BAL.ACCODE) AS ACCODE, MIN(AC_TAB.ACCOUNT_NO) AS ACCOUNT_NO, MIN(UM_TAB.UM) AS UM, MIN(UM_TAB.UM_DESC) AS UM_DESC, MIN(WHS_BAL.PART_DESC) AS PART_DESC, 0.00 AS UNIT_COST, (
		SELECT MIN(WHS_TAB.WHS_DESC)
		FROM WHS_TAB
		WHERE WHS_TAB.WAREHOUSE = WHS_BAL.WAREHOUSE
			AND WHS_TAB.STK_LOCATION = (
				SELECT MIN(WHB.STK_LOCATION)
				FROM WHS_TAB WHB
				WHERE WHB.WAREHOUSE = WHS_BAL.WAREHOUSE
				)
		) AS WHS_DESC, (
		SELECT MIN(TRAN_DESC)
		FROM PARTHIST
		WHERE 1 <> 1
		) AS TRAN_DESC, MIN(REP1.NOTE) AS NOTE, MIN(REP2.NOTE) AS NOTE2, CAST('0' AS NUMERIC(15, 2)) AS HDL_TRAN_QTY, CAST('0' AS NUMERIC(15, 2)) AS HDL_UNIT_COST
FROM WHS_BAL
LEFT JOIN WHS_STAT
	ON WHS_BAL.WAREHOUSE = WHS_STAT.WAREHOUSE
		AND WHS_BAL.PART_NO = WHS_STAT.PART_NO
		AND WHS_STAT.curYEAR = '57'
		AND WHS_STAT.PERIOD = '3'
LEFT JOIN HIS_STAT
	ON WHS_BAL.WAREHOUSE = HIS_STAT.WAREHOUSE
		AND WHS_BAL.PART_NO = HIS_STAT.PART_NO
		AND HIS_STAT.DOC_DATE < '3/1/2014'
LEFT JOIN AC_TAB
	ON WHS_BAL.WAREHOUSE = AC_TAB.WAREHOUSE
		AND WHS_BAL.ACCODE = AC_TAB.ACCODE
LEFT JOIN PART_STD
	ON WHS_BAL.PART_NO = PART_STD.PART_NO
LEFT JOIN UM_TAB
	ON PART_STD.UM = UM_TAB.UM
LEFT JOIN REP REP1
	ON REP1.ID = 'ปตท'
LEFT JOIN REP REP2
	ON REP2.ID = 'LAST_POST'
WHERE WHS_BAL.WAREHOUSE = 'WH_001'
	AND WHS_BAL.PART_NO BETWEEN 'DILDO'
		AND 'DILDO'
GROUP BY WHS_BAL.WAREHOUSE, WHS_BAL.PART_NO

UNION ALL

SELECT 'DET' AS REP_GROUP, PARTHIST.WAREHOUSE, PARTHIST.PART_NO, PARTHIST.DOC_NO, PARTHIST.SEQ, PARTHIST.DOC_DATE, PARTHIST.PROCESS_SEQ, PARTHIST.TRAN_CODE, PARTHIST.TRAN_TYPE, 0.00 AS BEGIN_BAL, 0.00 AS BEG_AMT, 0.00 AS REC_QTY, 0.00 AS ISS_QTY, 0.00 AS ADJ_INC, 0.00 AS ADJ_DEC, PARTHIST.TRAN_QTY, CASE 
		WHEN ABS(PARTHIST.TRAN_QTY) > 0
			THEN (PARTHIST.TRAN_QTY / (ABS(PARTHIST.TRAN_QTY)))
		ELSE 0
		END AS TRAN_QTYSORT, PARTHIST.TRAN_AMT, PARTHIST.POST_FLAG, WHS_BAL.ACCODE, AC_TAB.ACCOUNT_NO, UM_TAB.UM, UM_TAB.UM_DESC, WHS_BAL.PART_DESC, PARTHIST.UNIT_COST, (
		SELECT MIN(WHS_TAB.WHS_DESC)
		FROM WHS_TAB
		WHERE WHS_TAB.WAREHOUSE = WHS_BAL.WAREHOUSE
			AND WHS_TAB.STK_LOCATION = (
				SELECT MIN(WHB.STK_LOCATION)
				FROM WHS_TAB WHB
				WHERE WHB.WAREHOUSE = WHS_BAL.WAREHOUSE
				)
		) AS WHS_DESC, PARTHIST.TRAN_DESC, REP1.NOTE AS NOTE, REP2.NOTE AS NOTE2, CAST('0' AS NUMERIC(15, 2)) AS HDL_TRAN_QTY, CAST('0' AS NUMERIC(15, 2)) AS HDL_UNIT_COST
FROM WHS_BAL
LEFT JOIN PARTHIST
	ON WHS_BAL.WAREHOUSE = PARTHIST.WAREHOUSE
		AND WHS_BAL.PART_NO = PARTHIST.PART_NO
		AND PARTHIST.DOC_DATE BETWEEN '3/1/2014'
			AND '3/3/2014 23:59'
LEFT JOIN AC_TAB
	ON WHS_BAL.WAREHOUSE = AC_TAB.WAREHOUSE
		AND WHS_BAL.ACCODE = AC_TAB.ACCODE
LEFT JOIN PART_STD
	ON WHS_BAL.PART_NO = PART_STD.PART_NO
LEFT JOIN UM_TAB
	ON PART_STD.UM = UM_TAB.UM
LEFT JOIN REP REP1
	ON REP1.ID = 'ปตท'
LEFT JOIN REP REP2
	ON REP2.ID = 'LAST_POST'
WHERE WHS_BAL.WAREHOUSE = 'WH_001'
	AND WHS_BAL.PART_NO BETWEEN 'DILDO'
		AND 'DILDO'

UNION ALL

SELECT 'DET' AS REP_GROUP, PARTHIST.WAREHOUSE, PARTHIST.PART_NO, PARTHIST.DOC_NO, PARTHIST.SEQ, PARTHIST.DOC_DATE, PARTHIST.PROCESS_SEQ, PARTHIST.TRAN_CODE, PARTHIST.TRAN_TYPE, 0.00 AS BEGIN_BAL, 0.00 AS BEG_AMT, 0.00 AS REC_QTY, 0.00 AS ISS_QTY, 0.00 AS ADJ_INC, 0.00 AS ADJ_DEC, PARTHIST.TRAN_QTY, CASE 
		WHEN ABS(PARTHIST.TRAN_QTY) > 0
			THEN (PARTHIST.TRAN_QTY / (ABS(PARTHIST.TRAN_QTY)))
		ELSE 0
		END AS TRAN_QTYSORT, PARTHIST.TRAN_AMT, PARTHIST.POST_FLAG, WHS_BAL.ACCODE, AC_TAB.ACCOUNT_NO, UM_TAB.UM, UM_TAB.UM_DESC, WHS_BAL.PART_DESC, PARTHIST.UNIT_COST, (
		SELECT MIN(WHS_TAB.WHS_DESC)
		FROM WHS_TAB
		WHERE WHS_TAB.WAREHOUSE = WHS_BAL.WAREHOUSE
			AND WHS_TAB.STK_LOCATION = (
				SELECT MIN(WHB.STK_LOCATION)
				FROM WHS_TAB WHB
				WHERE WHB.WAREHOUSE = WHS_BAL.WAREHOUSE
				)
		) AS WHS_DESC, PARTHIST.TRAN_DESC, REP1.NOTE AS NOTE, REP2.NOTE AS NOTE2, CASE 
		WHEN (- HDL.TRAN_QTY) > 0
			THEN CAST((- HDL.TRAN_QTY) AS NUMERIC(15, 2))
		ELSE 0
		END AS HDL_TRAN_QTY, CASE 
		WHEN HDL.UNIT_COST > 0
			THEN CAST(HDL.UNIT_COST AS NUMERIC(15, 2))
		ELSE 0
		END AS HDL_UNIT_COST
FROM WHS_BAL
LEFT JOIN PARTHIST
	ON WHS_BAL.WAREHOUSE = PARTHIST.WAREHOUSE
		AND WHS_BAL.PART_NO = PARTHIST.PART_NO
		AND PARTHIST.DOC_DATE BETWEEN '3/1/2014'
			AND '3/3/2014 23:59'
LEFT JOIN HIS_DTL HDL
	ON HDL.WAREHOUSE = PARTHIST.WAREHOUSE
		AND HDL.DOC_DATE = PARTHIST.DOC_DATE
		AND HDL.DOC_NO = PARTHIST.DOC_NO
		AND HDL.SEQ = PARTHIST.SEQ
LEFT JOIN AC_TAB
	ON WHS_BAL.WAREHOUSE = AC_TAB.WAREHOUSE
		AND WHS_BAL.ACCODE = AC_TAB.ACCODE
LEFT JOIN PART_STD
	ON WHS_BAL.PART_NO = PART_STD.PART_NO
LEFT JOIN UM_TAB
	ON PART_STD.UM = UM_TAB.UM
LEFT JOIN REP REP1
	ON REP1.ID = 'ปตท'
LEFT JOIN REP REP2
	ON REP2.ID = 'LAST_POST'
WHERE WHS_BAL.WAREHOUSE = 'WH_001'
	AND HDL.UNIT_COST > 0
	AND (- HDL.TRAN_QTY) > 0
	AND WHS_BAL.PART_NO BETWEEN 'DILDO'
		AND 'DILDO'
ORDER BY DOC_DATE
 

IF OBJECT_ID('tempdb..#temp') IS NOT NULL
	DROP TABLE #temp