DECLARE @start DATE;
DECLARE @end DATE;
SET @start = '20170307';
SET @end = '20170313';
WITH CTEid
AS ( SELECT JoinFromHdrPKey
FROM [dataKLL].[dbo].[KLL Extender UDF Values with Data]
WHERE UDF_Date_Data >= @start
AND UDF_Date_Data < @end
),
CTEraw
AS ( SELECT D.division ,
SHPMH.shpm_ref ,
t.JoinFromHdrPKey ,
t.UDF_FIELD ,
t.UDF_Char_Data ,
t.UDF_Date_Data ,
t.UDF_Numeric_Data
FROM [dataKLL].[dbo].[KLL Extender UDF Values with Data] t
INNER JOIN CTEid r ON t.JoinFromHdrPKey = r.JoinFromHdrPKey
INNER JOIN ZZMSHPMH SHPMH ON t.JoinFromHdrPKey = SHPMH.pkey
LEFT OUTER JOIN ZZCORDRD D ON D.SHP_SEQ = SHPMH.SHIPMENT_NUM
)
SELECT Division ,
shpm_ref ,
JoinFromHdrPKey ,
MAX(CASE WHEN UDF_FIELD = 'FOB1VENDOR' THEN UDF_Char_Data
END) AS FOB1VENDOR ,
MAX(CASE WHEN UDF_FIELD = 'FOB1DATE' THEN UDF_Date_Data
END) AS FOB1DATE ,
MAX(CASE WHEN UDF_FIELD = 'FOB1AMT' THEN UDF_Numeric_Data
END) AS FOB1AMT ,
MAX(CASE WHEN UDF_FIELD = 'FOB2VENDOR' THEN UDF_Char_Data
END) AS FOB2VENDOR ,
MAX(CASE WHEN UDF_FIELD = 'FOB2DATE' THEN UDF_Date_Data
END) AS FOB2DATE ,
MAX(CASE WHEN UDF_FIELD = 'FOB2AMT' THEN UDF_Numeric_Data
END) AS FOB2AMT ,
MAX(CASE WHEN UDF_FIELD = 'MISC1VENDOR' THEN UDF_Char_Data
END) AS MISC1VENDOR ,
MAX(CASE WHEN UDF_FIELD = 'MISC1DATE' THEN UDF_Date_Data
END) AS MISC1DATE ,
MAX(CASE WHEN UDF_FIELD = 'MISC1AMT' THEN UDF_Numeric_Data
END) AS MISC1AMT ,
MAX(CASE WHEN UDF_FIELD = 'MISC2VENDOR' THEN UDF_Char_Data
END) AS MISC2VENDOR ,
MAX(CASE WHEN UDF_FIELD = 'MISC2DATE' THEN UDF_Date_Data
END) AS MISC2DATE ,
MAX(CASE WHEN UDF_FIELD = 'MISC2AMT' THEN UDF_Numeric_Data
END) AS MISC2AMT ,
MAX(CASE WHEN UDF_FIELD = 'OCN1VENDOR' THEN UDF_Char_Data
END) AS OCN1VENDOR ,
MAX(CASE WHEN UDF_FIELD = 'OCNC1DATE' THEN UDF_Date_Data
END) AS OCN1DATE ,
MAX(CASE WHEN UDF_FIELD = 'OCN1AMT' THEN UDF_Numeric_Data
END) AS OCN1AMT ,
MAX(CASE WHEN UDF_FIELD = 'OCN2VENDOR' THEN UDF_Char_Data
END) AS OCN2VENDOR ,
MAX(CASE WHEN UDF_FIELD = 'OCN2DATE' THEN UDF_Date_Data
END) AS OCN2DATE ,
MAX(CASE WHEN UDF_FIELD = 'OCN2AMT' THEN UDF_Numeric_Data
END) AS OCN2AMT ,
MAX(CASE WHEN UDF_FIELD = 'DUTY1VENDOR' THEN UDF_Char_Data
END) AS DUTY1VENDOR ,
MAX(CASE WHEN UDF_FIELD = 'DUTY1DATE' THEN UDF_Date_Data
END) AS DUTY1DATE ,
MAX(CASE WHEN UDF_FIELD = 'DUTY1AMT' THEN UDF_Numeric_Data
END) AS DUTY1AMT ,
MAX(CASE WHEN UDF_FIELD = 'DUTY2VENDOR' THEN UDF_Char_Data
END) AS DUTY2VENDOR ,
MAX(CASE WHEN UDF_FIELD = 'DUTY2DATE' THEN UDF_Date_Data
END) AS DUTY2DATE ,
MAX(CASE WHEN UDF_FIELD = 'DUTY2AMT' THEN UDF_Numeric_Data
END) AS DUTY2AMT ,
MAX(CASE WHEN UDF_FIELD = 'INLAND1VENDOR' THEN UDF_Char_Data
END) AS INLAND1VENDOR ,
MAX(CASE WHEN UDF_FIELD = 'INLAND1DATE' THEN UDF_Date_Data
END) AS INLAND1DATE ,
MAX(CASE WHEN UDF_FIELD = 'INLAND1AMT' THEN UDF_Numeric_Data
END) AS INLAND1AMT ,
MAX(CASE WHEN UDF_FIELD = 'INLAND2VENDOR' THEN UDF_Char_Data
END) AS INLAND2VENDOR ,
MAX(CASE WHEN UDF_FIELD = 'INLAND2DATE' THEN UDF_Date_Data
END) AS INLAND2DATE ,
MAX(CASE WHEN UDF_FIELD = 'INLAND2AMT' THEN UDF_Numeric_Data
END) AS INLAND2AMT
FROM CTEraw d
GROUP BY Division ,
JoinFromHdrPKey ,
shpm_ref
ORDER BY Division ,
JoinFromHdrPKey ,
shpm_ref;
Open in new window