1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64
|
/* Transformation rate of quotations into orders */
/*Lines of Orders */
IF OBJECT_ID('tempdb..#tmp_SOL_Quot1') IS NOT NULL BEGIN DROP TABLE #tmp_SOL_Quot1 END
SELECT OOLINE."ORNO - Customer order number"
, OOLINE."PONR - Order line number"
, OOLINE."ITNO - Item number"
, OOLINE."ORST - Highest status - customer order"
, OOHEAD."CUNO - Customer"
, OOLINE."RGDT - Entry date"
, CONCAT(OOLINE."ORNO - Customer order number",OOLINE."PONR - Order line number") AS "Key CO"
INTO #tmp_SOL_Quot1
FROM M3JDTP600."V_OOLINE - TF: CO line file (OB)" OOLINE
INNER JOIN M3JDTP600."V_OOHEAD - TF: CO header file (OA)" OOHEAD
ON OOHEAD."ORNO - Customer order number" = OOLINE."ORNO - Customer order number"
WHERE OOLINE."ORST - Highest status - customer order" > '05'
AND OOLINE."ORST - Highest status - customer order" < 99
--AND OOLINE."RGDT - Entry date" >= 20180101
/* Lines of quotations*/
IF OBJECT_ID('tempdb..#tmp_SOL_Quot2') IS NOT NULL BEGIN DROP TABLE #tmp_SOL_Quot2 END
SELECT OOLINE."ORNO - Customer order number"
, OOLINE."PONR - Order line number"
, OOLINE."ITNO - Item number"
, OOLINE."ORST - Highest status - customer order"
, OOHEAD."CUNO - Customer"
, OOLINE."RGDT - Entry date"
, CONCAT(OOLINE."ORNO - Customer order number",OOLINE."PONR - Order line number") AS "Key Q"
INTO #tmp_SOL_Quot2
FROM M3JDTP600."V_OOLINE - TF: CO line file (OB)" OOLINE
INNER JOIN M3JDTP600."V_OOHEAD - TF: CO header file (OA)" OOHEAD
ON OOHEAD."ORNO - Customer order number" = OOLINE."ORNO - Customer order number"
WHERE OOLINE."ORST - Highest status - customer order" = '05'
--AND OOLINE."RGDT - Entry date" >= 20180101
/* Match between orders and quotations*/
IF OBJECT_ID('tempdb..#tmp_SOL_Quot') IS NOT NULL BEGIN DROP TABLE #tmp_SOL_Quot END
SELECT *
INTO #tmp_SOL_Quot
FROM (SELECT TTQ."ORNO CO"
, TTQ."PONR CO"
, TTQ."Entry date CO"
, TTQ."ORNO Q"
, TTQ."PONR Q"
, TTQ."Entry date Q"
, RANK() OVER (PARTITION BY "Entry date Q","Key Q" ORDER BY "Key Q","Entry date Q",TTQ."Entry date CO","Key CO" ASC) AS Ranking
, [Key CO]
FROM (SELECT *
FROM (SELECT #tmp_SOL_Quot1."ORNO - Customer order number" AS "ORNO CO"
, #tmp_SOL_Quot1."PONR - Order line number" AS "PONR CO"
, #tmp_SOL_Quot1."RGDT - Entry date" As "Entry date CO"
, #tmp_SOL_Quot2."ORNO - Customer order number" AS "ORNO Q"
, #tmp_SOL_Quot2."PONR - Order line number" AS "PONR Q"
, #tmp_SOL_Quot2."RGDT - Entry date" AS "Entry date Q"
, RANK() OVER (PARTITION BY #tmp_SOL_Quot1."RGDT - Entry date","Key CO" ORDER BY "Key CO",#tmp_SOL_Quot1."RGDT - Entry date", "Key Q" ASC) AS Rooky
, [Key CO]
, [Key Q]
FROM #tmp_SOL_Quot1
INNER JOIN #tmp_SOL_Quot2
ON #tmp_SOL_Quot1."ITNO - Item number" = #tmp_SOL_Quot2."ITNO - Item number"
AND #tmp_SOL_Quot1."CUNO - Customer" = #tmp_SOL_Quot2."CUNO - Customer"
AND #tmp_SOL_Quot1."RGDT - Entry date" > #tmp_SOL_Quot2."RGDT - Entry date") TTQ
WHERE TTQ."Rooky" = 1) TTQ) TTQ
WHERE "Ranking" = 1 |
Partager