How it's possible to rewrite this query with the INNER JOIN

 INNER JOIN [TD_DAT_DATe] WITH (NOLOCK)
        ON DAT_Date 
           BETWEEN [Date Début] AND [Date Fin]**

SELECT 
         SUBSTRING([Adhésion/Renouvellement],1,1) [Adhésion/Renouvellement],
         [Code Evénement],
         CAST(DAT_Periode '01' AS DATE) Période,
         COD_CodeProduit CodeProduit,
         COUNT(DISTINCT CAST(IdAdhesion AS VARCHAR) '-' CAST(DAT_Date AS VARCHAR)) [Exposition NB Jour]
         --INTO TF_COD_COntratDeclareGarantiePBIexpo
**FROM #ZZ3 WITH (NOLOCK)
    INNER JOIN [TD_DAT_DATe] WITH (NOLOCK)
        ON DAT_Date 
           BETWEEN [Date Début] AND [Date Fin]**
WHERE DAT_Periode >= '202201'  
GROUP BY 
         SUBSTRING([Adhésion/Renouvellement],1,1),
         [Code Evénement],
         CAST(DAT_Periode '01' AS DATE),
         COD_CodeProduit

I try many ways to rewrite the query.

CodePudding user response：

the problem is that in the execution plan it brings back more than 7 billion lines :(

enter image description here

CodePudding user response：

The join should be more like :

ON DAT_Date = DAT_Periode AND DAT_Date BETWEEN [Date Début] AND [Date Fin]