Hi arwa, in the first load of matable, if you are grouping by Date, you will retrieve one record per Date, so each row with [Maternité (Évènements)]=1 will have the same value for Date, mindate and maxdate
Also in the second table the Date>maxdate will return always false:
if(IsNull(mindate),2,if(Date>maxdate and Date<=date(mindate+365),1,0))
If mindate is not null, there will be a maxdate and it can't be lower than Date.
in the first load of matable,i am retrieving all the dates between mindate and maxdate. and if i omit date from field and from groupby ,i have one day for each person who have 1 for [Maternité (Évènements)]. but the if of the left join is not used.
if mindate is not null, then for any date between mindate and mindate+oneyear it takes 1