動的SQLに初挑戦

テーブル定義書がないので、自分で想像でデータベースの定義書を作り、テストデータも想像で作り。

だれがどの帳票を作るかも決まっておらず、こちとら、作業ができない状態なのです。

システム稼働まで3か月を切った今。

ギリギリで「さぁやって！！」と言われてもいいように、ひな形を作りまくってる。

このSQLもエラーや、変な出力してたので、15回作り直して、やっと満足のいく出力ができた。

1日1本ペースで作らないと、追いつかないのです。
そのペースでも追いつかないな、帳票200本あるし。

動的SQLは初めてだけれど、やってみると面白い。

DECLARE @loop_counter int
DECLARE @date_from varchar(max)
DECLARE @date_to   varchar(max)
DECLARE @date_diff int
DECLARE @date_now  varchar(max)
DECLARE @date_next varchar(max)
DECLARE @CAL_TABLE table(
     PlantCode varchar(5) NOT NULL
    ,LineCode varchar(2) NOT NULL
	,PartNumber varchar(20) NOT NULL
    ,CalDate datetime NOT NULL
)

set @loop_counter = 0
set @date_from = '2016/04/02'
set @date_to = '2016/05/03'

SELECT
    @date_diff =
	DATEDIFF(day,@date_from,@date_to) + 1

WHILE @loop_counter < @date_diff
    BEGIN
	    IF @loop_counter = 0
		    BEGIN
			    --INSERT INTO @CAL_TABLE VALUES ( @date_from )
				INSERT INTO @CAL_TABLE
				    SELECT DISTINCT
					     PlantCode
						,LineCode
						,PartNumber
						,@date_from
					FROM WORK_PERFORMANCE
				set @date_now  = @date_from
			END
		ELSE
		    BEGIN
		        SELECT @date_next = FORMAT(DATEADD(day,1,@date_now), 'yyyy/MM/dd')
			    --INSERT INTO @CAL_TABLE VALUES ( @date_next )
				INSERT INTO @CAL_TABLE
				    SELECT DISTINCT
					     PlantCode
						,LineCode
						,PartNumber
						,@date_next
					FROM WORK_PERFORMANCE
			    set @date_now = @date_next
		    END
	    
        set @loop_counter = @loop_counter + 1
    END

SELECT DISTINCT
	 CALTABLE.PlantCode AS PlantCode
	,CALTABLE.LineCode AS LineCode
	,CALTABLE.PartNumber AS PartNumber
	,CALTABLE.CalDate AS CalDate
	,FORMAT(CALTABLE.CalDate, 'MM') AS WorkMonth
	,FORMAT(CALTABLE.caldate, 'dd') AS WorkDate
	,CASE WHEN (DATENAME(weekday,CALTABLE.caldate) = '土曜日' OR DATENAME(weekday,CALTABLE.caldate) = '日曜日')
	 THEN '*' + FORMAT(CALTABLE.caldate, 'dd')
	 ELSE FORMAT(CALTABLE.caldate, 'dd') END AS YoubiAdd
    -- 予定数
    ,T2.NgrYotei AS NgrYotei
	-- 実績数
	,T3.NgrJisseki AS NgrJisseki
	-- 稼働率
	,CASE WHEN (T3.NgrJisseki IS NULL) OR (T3.NgrJisseki = 0) THEN 0 ELSE ROUND((CONVERT(FLOAT,T2.NgrYotei) / T3.NgrJisseki) * 100,2) END AS KadoRt
	-- 合格
	,CASE WHEN (T3.NgrJisseki - (CASE WHEN T4.NgrPeke IS NULL THEN 0 ELSE T4.NgrPeke END)) < 0 THEN 0
	 ELSE (T3.NgrJisseki - (CASE WHEN T4.NgrPeke IS NULL THEN 0 ELSE T4.NgrPeke END)) END AS OKJisskeki
	,CASE WHEN T4.NgrPeke IS NULL THEN 0 ELSE T4.NgrPeke END AS NgrPeke
	-- 合格数
	,CASE WHEN (T3.NgrJisseki IS NULL) OR (T3.NgrJisseki = 0) THEN 0 ELSE 
	    CASE WHEN (T3.NgrJisseki - (CASE WHEN T4.NgrPeke IS NULL THEN 0 ELSE T4.NgrPeke END)) < 0 THEN 0
		ELSE ROUND((CONVERT(FLOAT,(T3.NgrJisseki - (CASE WHEN T4.NgrPeke IS NULL THEN 0 ELSE T4.NgrPeke END))) / T3.NgrJisseki) * 100,2) END END AS Budomari

FROM
WORK_PERFORMANCE AS T1

-- 予定数
INNER JOIN (
SELECT
     PlantCode AS PlantCode
	,LineCode  AS LineCode
	,PartNumber AS PartNumber
	,Date
	,SUM(Qty) AS NgrYotei
FROM
MOLDING_PLAN T2
GROUP BY PlantCode, LineCode, PartNumber, Date
) T2 ON
    T1.PlantCode = T2.PlantCode
AND T1.LineCode = T2.LineCode
AND FORMAT(T1.WorkDate, 'yyyy/MM/dd') = FORMAT(T2.Date, 'yyyy/MM/dd')
AND T1.ProcessCode = '01'
AND T1.PartNumber = T2.PartNumber

-- 実績数
LEFT OUTER JOIN (
SELECT
     PlantCode AS PlantCode
	,LineCode  AS LineCode
	,PartNumber AS PartNumber
	,WorkDate AS WorkDate
	,SUM(Qty) AS NgrJisseki
FROM
WORK_PERFORMANCE T3
WHERE
ProcessCode = '01'
GROUP BY PlantCode, LineCode, PartNumber, WorkDate
) T3 ON
    T1.PlantCode = T3.PlantCode
AND T1.LineCode = T3.LineCode
AND FORMAT(T1.WorkDate, 'yyyy/MM/dd') = FORMAT(T3.WorkDate, 'yyyy/MM/dd')
AND T1.PartNumber = T3.PartNumber

-- 破損数
LEFT OUTER JOIN (
SELECT
     PlantCode AS PlantCode
	,LineCode  AS LineCode
	,PartNumber AS PartNumber
	,WorkDate AS WorkDate
	,SUM(Qty) AS NgrPeke
FROM
WORK_PERFORMANCE
WHERE
    ProcessCode = '01'
AND WorkPerformanceResultCode = '02'
GROUP BY PlantCode, LineCode, PartNumber, WorkDate
) T4 ON
    T1.PlantCode = T4.PlantCode
AND T1.LineCode = T4.LineCode
AND FORMAT(T1.WorkDate, 'yyyy/MM/dd') = FORMAT(T4.WorkDate, 'yyyy/MM/dd')
AND T1.PartNumber = T4.PartNumber

RIGHT OUTER JOIN @CAL_TABLE CALTABLE ON
    T1.PlantCode = CALTABLE.PlantCode
AND T1.LineCode = CALTABLE.LineCode
AND T1.PartNumber = CALTABLE.PartNumber
AND FORMAT(T1.WorkDate, 'yyyy/MM/dd') = FORMAT(CALTABLE.caldate, 'yyyy/MM/dd')

ORDER BY FORMAT(CALTABLE.CalDate, 'MM'),FORMAT(CALTABLE.CalDate, 'dd') ASC