SELECT typeDiagnostic = CASE
WHEN ((TSEC_CNS_COD=1) OR (TSEC_CNS_COD=3)) THEN 'Lesionnel'
WHEN ((TSEC_CNS_COD!=1) AND (TSEC_CNS_COD!=3)) THEN 'Medical'
ELSE 'Non renseigné'
END
, TOBC_OBC_TXT
, TOBC_SUM_NUM
, CONVERT(smalldatetime,CONVERT(varchar,TOBC_OBC_DATE,103)) as date
,TOBC_UFO_COD
, flag =
CASE
WHEN ((TOBC_SUM_NUM, TOBC_UFO_COD) IN (SELECT TMVS.TMVS_UFO_COD, TSUM.TSUM_SUM_NUM
FROM TMVS
LEFT JOIN TSUM ON TMVS.TMVS_VEN_NUM = TSUM.TSUM_VEN_NUM
where TMVS.TMVS_SUF_NUM IN
(SELECT MAX(TMVS_SUF_NUM)
FROM TMVS T
WHERE T.TMVS_VEN_NUM=TMVS.TMVS_VEN_NUM
GROUP BY TMVS.TMVS_SUF_NUM)))THEN '1'
ELSE '0'
END
FROM TOBC
LEFT JOIN TSUM ON TOBC_SUM_NUM=TSUM_SUM_NUM
LEFT JOIN TINU ON TINU_INU_COD = TOBC_INU_COD
LEFT JOIN TSEC ON TSUM_SUM_NUM=TSEC_SUM_NUM
WHERE TOBC_UFO_COD!=1 AND TINU_INU_TYP IN ('C','R','E','P','I') AND TOBC_DIA_PRI= 1
ORDER BY date, TOBC_SUM_NUM
Partager