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
|
CREATE VIEW [dbo].[V_DEVPART_ALL_DPT1]
AS
WITH Descendants (ID, LVL, NUM)
AS
(
-- Ancre
SELECT A.DPT_ID, 0 AS LVL, CAST(A.DPT_ORD AS VARCHAR(32)) AS NUM
FROM T_DEVPART_DPT A
WHERE A.DPT_PID IS NULL
UNION ALL
-- Partie Recursive
SELECT B.DPT_ID, P.LVL+1 AS LVL, CAST(P.NUM + '-' + CAST(B.DPT_ORD AS VARCHAR(4)) AS VARCHAR(32))
FROM T_DEVPART_DPT B
INNER JOIN Descendants P ON B.DPT_PID = P.ID
)
--Utlisation de la cte
SELECT
A.DPT_ID,
T.LVL AS DPT_LVL,
T.NUM AS DPT_NUM,
A.DEV_ID,
A.DPT_PID,
A.DPT_ORD,
A.DPT_STYPE,
A.DPT_FAMILLE,
A.DPT_SFAMILLE,
A.DPT_REF,
A.DPT_LIBELLE,
A.DPT_QTE,
A.DPT_UNITP,
B.UPU_QTE,
A.DPT_PUA1,
A.DPT_PUA2,
A.DPT_PUVHF,
A.DPT_PUV1,
A.DPT_PUVF,
A.DPT_MT_AJUST,
A.DPT_NB_HRS_MO,
A.DPT_MT_MO,
A.DPT_PUA1 * A.DPT_QTE * (1 / B.UPU_QTE) AS DPT_PTA1,
A.DPT_PUA2 * A.DPT_QTE * (1 / B.UPU_QTE) AS DPT_PTA2,
A.DPT_PUVHF * A.DPT_QTE * (1 / B.UPU_QTE) AS DPT_PTVHF,
A.DPT_PUV1 * A.DPT_QTE * (1 / B.UPU_QTE) AS DPT_PTV1,
A.DPT_PUVF * A.DPT_QTE * (1 / B.UPU_QTE) AS DPT_PTVF,
A.DPT_TEXTCOM,
A.DPT_REF_DEEE,
A.DPT_CODE_DEEE,
A.DPT_UNIT_DEEE,
A.DPT_MT_DEEE
FROM
T_DEVPART_DPT AS A INNER JOIN Descendants AS T ON (A.DPT_ID = T.ID)
LEFT OUTER JOIN T_UNITPU_UPU AS B ON (A.DPT_UNITP = B.UPU_CODE); |
Partager