This is my query
with cte as (
SELECT distinct STJH.[META_CODE_ORIGINE] AS Salle_TMD_JOUR_HISTORIQUE_ID
,STJH.[STJH_DATE_ACTION]
,STJH.[STJH_TYPE_ACTION]
,STJH.[ST_ID]
,STJH.[STJ_JOUR]
,STJH.[STJ_HEURE_DEBUT]
,STJH.[STJ_HEURE_FIN]
,STH.[META_CODE_ORIGINE] AS SALLE_TMD_HISTORIQUE_ID
,STH.[ST_DATE_DEBUT]
,STH.[ST_DATE_FIN]
,STH.[STH_DATE_ACTION]
,MAX (sth.STH_DATE_ACTION) over (partition by sth.st_id) as max_date_action,
MIN(sth.STH_DATE_ACTION) over (partition by sth.st_id) as Min_date_action ,
max (STJH_DATE_ACTION) over (PARTITION by stjh.st_id) as max_stjh_date_action
FROM [D_TMD_JOUR_HISTORIQUE] STJH
left JOIN [D_TMD_HISTORIQUE] STH ON STJH.[ST_ID] = STH.[ST_ID]
WHERE STJH.[STJH_TYPE_ACTION] <> 3
AND STJH.ST_ID = 86 and STJH.ST_ID = 86
and STJ_JOUR = 1
)
-----
select distinct
cte.max_stjh_date_action,
cte.STJH_DATE_ACTION,
cte.STJH_TYPE_ACTION,
cte.STH_DATE_ACTION,
cte.SALLE_TMD_HISTORIQUE_ID,
cte.max_date_action,
cte.Salle_TMD_JOUR_HISTORIQUE_ID,
cte.ST_DATE_DEBUT, cte.ST_DATE_FIN ,
cte.ST_ID ,
cte.STJ_JOUR,
cte.STJ_HEURE_DEBUT ,
cte.STJ_HEURE_FIN
from cte
where
max_date_action < cte.STJH_DATE_ACTION
and max_date_action> STH_DATE_ACTION
order BY cte.STJ_JOUR asc ;
This is my results ( sorry, I could not put a large array there is a lot of data.) :
I want to show only one row whose value is salle_tmd_historique_id = 144. I’am aware that the problem comes from the column [STH_DATE_ACTION]. My target is to show all columns and show Max value of [STJH_DATE_ACTION]. I tried: Where Max (STJH_DATE_ACTION) > STH_DATE_ACTION to avoid SALLE_TMD_HISTORIQUE_ID = 113. But still got 2 rows. That’s why I need your help. Thank You