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 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145
| ALTER PROCEDURE [theriaque].[GET_THE_GTIAM_ID]
@LISTESP VARCHAR(255),
@GRAVITE INT
AS
DECLARE @IT1SP_FIT_CODE_FK_PK INT
DECLARE @CDF_NOM VARCHAR(250)
DECLARE @SP_NOM VARCHAR(250)
DECLARE @SP_NOM2 VARCHAR(250)
DECLARE @IT1SP_SP_CODE_FK_PK INT
DECLARE @IT2SP_SP_CODE_FK_PK INT
DECLARE @FITNA_CDF_NAIT_CODE_FK_PK INT
DECLARE @idcombinaison VARCHAR(250)
DECLARE @row_count INT
create TABLE #tmp_gtiam1(
IDINTER INT,
VALIDE VARCHAR(250),
TERME_1 VARCHAR(250),
TERME_2 VARCHAR(250),
ID_T1 INT,
ID_T2 INT,
NIVEAU INT,
comb VARCHAR(250)
)
create TABLE #tmp_SP(
SP INT
)
insert into #tmp_SP(
[SP]) exec [theriaque].[Core_SP_SplitString] @LISTESP, ','
--select * from #tmp_SP;
DECLARE curseur1 CURSOR FOR
SELECT t1.IT1SP_FIT_CODE_FK_PK AS IDINTER ,
t5.CDF_NOM AS VALIDE,
t6.SP_NOM AS TERME_1,
t7.SP_NOM AS TERME_2,
t1.IT1SP_SP_CODE_FK_PK AS ID_T1,
t2.IT2SP_SP_CODE_FK_PK AS ID_T2,
t3.FITNA_CDF_NAIT_CODE_FK_PK AS NIVEAU ,
CASE WHEN t1.IT1SP_SP_CODE_FK_PK < t2.IT2SP_SP_CODE_FK_PK
THEN CONVERT(varchar(5),t1.IT1SP_FIT_CODE_FK_PK) + '-' + CONVERT(varchar(5),t1.IT1SP_SP_CODE_FK_PK) + '-' + CONVERT(varchar(5),t2.IT2SP_SP_CODE_FK_PK) + '-' + CONVERT(varchar(5),t3.FITNA_CDF_NAIT_CODE_FK_PK)
ELSE CONVERT(varchar(5),t1.IT1SP_FIT_CODE_FK_PK) + '-' + CONVERT(varchar(5),t2.IT2SP_SP_CODE_FK_PK) + '-' + CONVERT(varchar(5),t1.IT1SP_SP_CODE_FK_PK) + '-' + CONVERT(varchar(5),t3.FITNA_CDF_NAIT_CODE_FK_PK)
END AS combinaison
FROM theriaque.IT1SP_TERME1SPECIALITE t1,
theriaque.IT2SP_TERME2SPECIALITE t2,
theriaque.FITNA_INTERACTION_NATURE t3,
theriaque.FITVA_NIVEAU_VALIDATION t4,
theriaque.CDF_CODIF t5,
theriaque.SP_SPECIALITE t6,
theriaque.SP_SPECIALITE t7
WHERE t1.IT1SP_FIT_CODE_FK_PK = t2.IT2SP_FIT_CODE_FK_PK
AND t3.FITNA_FIT_CODE_FK_PK = t1.IT1SP_FIT_CODE_FK_PK
AND t4.FITVA_FIT_CODE_FK_PK = t1.IT1SP_FIT_CODE_FK_PK
AND t5.CDF_CODE_PK = t4.FITVA_CDF_VAIT_CODE_FK_PK
AND t6.SP_CODE_SQ_PK = t1.IT1SP_SP_CODE_FK_PK
AND t7.SP_CODE_SQ_PK = t2.IT2SP_SP_CODE_FK_PK
AND t4.FITVA_CDF_VAIT_CODE_FK_PK = 1
AND t5.CDF_NUMERO_PK = 'IV'
AND t1.IT1SP_SP_CODE_FK_PK <> t2.IT2SP_SP_CODE_FK_PK
AND t1.IT1SP_SP_CODE_FK_PK IN (select SP from #tmp_SP)
AND t2.IT2SP_SP_CODE_FK_PK IN (select SP from #tmp_SP)
AND t3.FITNA_CDF_NAIT_CODE_FK_PK <= @GRAVITE
AND EXISTS ( SELECT 1,5 /* IT1SP_FIT_CODE_FK_PK , IT1SP_SP_CODE_FK_PK*/
FROM theriaque.IT1SP_TERME1SPECIALITE
WHERE t2.IT2SP_SP_CODE_FK_PK = IT1SP_SP_CODE_FK_PK
AND t1.IT1SP_FIT_CODE_FK_PK = IT1SP_FIT_CODE_FK_PK )
UNION
SELECT t1.IT1SP_FIT_CODE_FK_PK AS IDINTER ,
t5.CDF_NOM AS VALIDE,
t6.SP_NOM AS TERME_1,
t7.SP_NOM AS TERME_2,
t1.IT1SP_SP_CODE_FK_PK AS ID_T1,
t2.IT2SP_SP_CODE_FK_PK AS ID_T2,
t3.FITNA_CDF_NAIT_CODE_FK_PK AS NIVEAU ,
CASE WHEN t1.IT1SP_SP_CODE_FK_PK < t2.IT2SP_SP_CODE_FK_PK
THEN CONVERT(varchar(5),t1.IT1SP_FIT_CODE_FK_PK) + '-' + CONVERT(varchar(5),t1.IT1SP_SP_CODE_FK_PK) + '-' + CONVERT(varchar(5),t2.IT2SP_SP_CODE_FK_PK) + '-' + CONVERT(varchar(5),t3.FITNA_CDF_NAIT_CODE_FK_PK)
ELSE CONVERT(varchar(5),t1.IT1SP_FIT_CODE_FK_PK) + '-' + CONVERT(varchar(5),t2.IT2SP_SP_CODE_FK_PK) + '-' + CONVERT(varchar(5),t1.IT1SP_SP_CODE_FK_PK) + '-' + CONVERT(varchar(5),t3.FITNA_CDF_NAIT_CODE_FK_PK)
END AS combinaison
FROM theriaque.IT1SP_TERME1SPECIALITE t1,
theriaque.IT2SP_TERME2SPECIALITE t2,
theriaque.FITNA_INTERACTION_NATURE t3,
theriaque.FITVA_NIVEAU_VALIDATION t4,
theriaque.CDF_CODIF t5,
theriaque.SP_SPECIALITE t6,
theriaque.SP_SPECIALITE t7
WHERE t1.IT1SP_FIT_CODE_FK_PK = t2.IT2SP_FIT_CODE_FK_PK
AND t3.FITNA_FIT_CODE_FK_PK = t1.IT1SP_FIT_CODE_FK_PK
AND t4.FITVA_FIT_CODE_FK_PK = t1.IT1SP_FIT_CODE_FK_PK
AND t5.CDF_CODE_PK = t4.FITVA_CDF_VAIT_CODE_FK_PK
AND t6.SP_CODE_SQ_PK = t1.IT1SP_SP_CODE_FK_PK
AND t7.SP_CODE_SQ_PK = t2.IT2SP_SP_CODE_FK_PK
AND t4.FITVA_CDF_VAIT_CODE_FK_PK = 1
AND t5.CDF_NUMERO_PK = 'IV'
AND t1.IT1SP_SP_CODE_FK_PK <> t2.IT2SP_SP_CODE_FK_PK
AND t1.IT1SP_SP_CODE_FK_PK IN (select SP from #tmp_SP)
AND t2.IT2SP_SP_CODE_FK_PK IN (select SP from #tmp_SP)
AND t3.FITNA_CDF_NAIT_CODE_FK_PK <= @GRAVITE
AND NOT EXISTS ( SELECT 1,5
FROM theriaque.IT1SP_TERME1SPECIALITE
WHERE t2.IT2SP_SP_CODE_FK_PK = IT1SP_SP_CODE_FK_PK
AND t1.IT1SP_FIT_CODE_FK_PK = IT1SP_FIT_CODE_FK_PK)
ORDER BY 1
OPEN curseur1
FETCH NEXT FROM curseur1
INTO @IT1SP_FIT_CODE_FK_PK ,
@CDF_NOM ,
@SP_NOM ,
@SP_NOM2 ,
@IT1SP_SP_CODE_FK_PK ,
@IT2SP_SP_CODE_FK_PK ,
@FITNA_CDF_NAIT_CODE_FK_PK ,
@idcombinaison
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @row_count = COUNT(*) FROM #tmp_gtiam1 WHERE comb = @idcombinaison
IF(@row_count=0)
BEGIN
INSERT #tmp_gtiam1 SELECT @IT1SP_FIT_CODE_FK_PK ,
@CDF_NOM ,
@SP_NOM ,
@SP_NOM2 ,
@IT1SP_SP_CODE_FK_PK ,
@IT2SP_SP_CODE_FK_PK ,
@FITNA_CDF_NAIT_CODE_FK_PK ,
@idcombinaison
END
FETCH NEXT FROM curseur1 INTO @IT1SP_FIT_CODE_FK_PK ,
@CDF_NOM ,
@SP_NOM ,
@SP_NOM2 ,
@IT1SP_SP_CODE_FK_PK ,
@IT2SP_SP_CODE_FK_PK ,
@FITNA_CDF_NAIT_CODE_FK_PK ,
@idcombinaison
END
CLOSE curseur1
select IDINTER, VALIDE, TERME_1, TERME_2, ID_T1, ID_T2, NIVEAU from #tmp_gtiam1
DROP TABLE #tmp_gtiam1
DROP TABLE #tmp_SP
DEALLOCATE curseur1' |
Partager