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
| SELECT *
FROM
(SELECT ID_CALENDAR,
IS_END_OF_MONTH,
CONTRACT,
MATRICULE,
CONTRACT_START_DATE,
CONTRACT_END_DATE,
CONTRACT_END_CALCULATE_DATE,
CASE WHEN CONTRACT_END_DATE < CONTRACT_END_CALCULATE_DATE THEN
CONTRACT_END_DATE
ELSE CONTRACT_END_CALCULATE_DATE
END AS FINAL_DATE
FROM
(SELECT ID_CALENDAR,
IS_END_OF_MONTH,
MATRICULE,
CONTRACT,
CHANGING_TYPE,
CONTRACT_START_DATE,
CASE WHEN CHANGING_TYPE = 'C' THEN
CONTRACT_END_DATE
ELSE CONVERT(int,COALESCE((SELECT TOP 1 CONTRACT_END_DATE
FROM DIM_CONTRACT D
WHERE CHANGING_TYPE = 'C'
AND CONTRACT_START_DATE <= ID_CALENDAR
AND MATRICULE ='109'
ORDER BY CONTRACT_START_DATE DESC
),CONVERT(VARCHAR,EOMONTH(GETDATE()),112))) END AS CONTRACT_END_DATE ,
CONTRACT_END_CALCULATE_DATE
FROM [V_DIM_CALENDAR] a
INNER JOIN
(SELECT MATRICULE,
CONTRACT,
CHANGING_TYPE,
CONTRACT_START_DATE,
CASE
WHEN CONTRACT_END_CALCULATE_DATE < CONTRACT_END_DATE THEN CONTRACT_END_CALCULATE_DATE
ELSE CONTRACT_END_DATE
END AS EFFECT_CONTRACT_END_DATE,
CONTRACT_END_DATE,
CONTRACT_END_CALCULATE_DATE
FROM
(SELECT MATRICULE, CONTRACT,
CHANGING_TYPE,
CONTRACT_START_DATE,
CONVERT(INT,COALESCE(CONTRACT_END_DATE,CONVERT(VARCHAR,EOMONTH(GETDATE()),112))) AS CONTRACT_END_DATE,
CONVERT(INT,CONVERT(VARCHAR ,COALESCE(
(SELECT TOP 1 DATEADD(DAY,-1,convert(date,str(CONTRACT_START_DATE)))
FROM DIM_CONTRACT A
WHERE A.MATRICULE = B.MATRICULE
AND A.CONTRACT_START_DATE > B.CONTRACT_START_DATE
ORDER BY CONTRACT_START_DATE), EOMONTH(GETDATE())),112)) AS CONTRACT_END_CALCULATE_DATE
FROM DIM_CONTRACT B) AS R) AS T ON a.ID_CALENDAR >= CONTRACT_START_DATE AND ID_CALENDAR <= EFFECT_CONTRACT_END_DATE) as M) AS P
WHERE ID_CALENDAR >= CONTRACT_START_DATE AND ID_CALENDAR <= FINAL_DATE AND MATRICULE = '109'
ORDER BY MATRICULE,ID_CALENDAR |
Partager