Weekly fee calculation I have the following query.  Its execution took 15 min and it's too much slow.  Is there a way to optimize it?

SELECT
 Id,Fees,WeekOfMonth,CONVERT(NVARCHAR(MAX), StartDate, 103) AS StartDate,CONVERT(NVARCHAR(MAX), EndDate, 103) AS EndDate,'Temp Fees                      ' AS FeesName 
  ,@MonthName AS [MonthName]
  INTO ##TempFeesMonthly
  from
  (
SELECT DISTINCT 
1 as Id,sum(((CNTI_THPAYE *(CNTI_THFACT / CNTI_THPAYE)) *
(CASE WHEN CNTI_DURHEBDO IS NULL THEN 1 ELSE CNTI_DURHEBDO/5 END)*@NumberOfDays)) AS Fees,WeekOfMonth,StartDate,EndDate
FROM SCHHAYS.dbo.WTVTAT TAT
      LEFT JOIN
    SCHHAYS.dbo.WTTIEINT INT 
        ON (
            TAT.TIE_ID = INT.TIE_ID
        ) 
        AND (
            TAT.VTAT_IORDRE = INT.TIEI_ORDRE
        )     
    LEFT JOIN
    SCHHAYS.dbo.PYCONTRAT CC 
        ON TAT.PER_ID = CC.PER_ID 
        AND TAT.CNT_ID = CC.CNT_ID     
    LEFT JOIN
    SCHHAYS.dbo.CMTIERS T 
        ON TAT.TIE_ID = T.TIE_ID    
    LEFT JOIN
    SCHHAYS.dbo.WTMISS M 
        ON CC.PER_ID = M.PER_ID 
        AND CC.CNT_ID = M.CNT_ID     
    LEFT JOIN
    ##WTCNTIWeek COT1 
        ON M.PER_ID = COT1.PER_ID 
        AND M.CNT_ID = COT1.CNT_ID    
    INNER JOIN
    SCHHAYS.dbo.WTPRH AS PRH 
        ON M.PER_ID = PRH.PER_ID 
        AND M.CNT_ID = PRH.CNT_ID 
        AND M.TIE_ID = PRH.TIE_ID     
    INNER JOIN
    ##tempStartEndWeekDates AS Tsed 
        ON     PRH_DTEDEBSEM&gt;=Tsed.StartDate 
        AND PRH_DTEFINSEM&lt;=Tsed.EndDate     
    LEFT JOIN
    SCHHAYS.dbo.WTSCCT C 
        ON CC.RGPCNT_ID = C.RGPCNT_ID 
        AND CC.PER_ID = C.PER_ID 
        AND CC.CNT_ID = C.CNT_ID   
    INNER JOIN
    ##TempHaysStaffWeek HF 
        ON C.VAPO_CODE = HF.onetouch  COLLATE Latin1_General_CI_AS
group by
WeekOfMonth,StartDate,EndDate)t

--CREATE INDEX IDX_TempFeesMonthly ON ##TempFeesMonthly(WeekOfMonth)
--Calcul Temp Margin 
UNION ALL
--INSERT INTO ##TempFeesMonthly(Id,Fees,WeekOfMonth,StartDate,EndDate,FeesName,[MonthName])
SELECT
 2,  sum(Fees) AS Fees  ,WeekOfMonth,CONVERT(NVARCHAR(MAX), StartDate, 103) AS StartDate,CONVERT(NVARCHAR(MAX), EndDate, 103) AS EndDate,'Temp Margin ' AS FeesName 
  ,@MonthName AS [MonthName]
  from
  (
 SELECT DISTINCT 
sum((CASE WHEN CNTI_DURHEBDO IS NULL THEN 1 ELSE CNTI_DURHEBDO/5 END)*@NumberOfDays)-(CNTI_THPAYE *(CASE WHEN CNTI_DURHEBDO IS NULL THEN 1 ELSE CNTI_DURHEBDO/5 END)*1.453*1.21*@NumberOfDays) AS Fees,WeekOfMonth,StartDate,EndDate
FROM SCHHAYS.dbo.WTVTAT TAT
      LEFT JOIN
    SCHHAYS.dbo.WTTIEINT INT 
        ON (
            TAT.TIE_ID = INT.TIE_ID
        ) 
        AND (
            TAT.VTAT_IORDRE = INT.TIEI_ORDRE
        )     
    LEFT JOIN
    SCHHAYS.dbo.PYCONTRAT CC 
        ON TAT.PER_ID = CC.PER_ID 
        AND TAT.CNT_ID = CC.CNT_ID     
    LEFT JOIN
    SCHHAYS.dbo.CMTIERS T 
        ON TAT.TIE_ID = T.TIE_ID    
    LEFT JOIN
    SCHHAYS.dbo.WTMISS M 
        ON CC.PER_ID = M.PER_ID 
        AND CC.CNT_ID = M.CNT_ID     
    LEFT JOIN
    ##WTCNTIWeek COT1 
        ON M.PER_ID = COT1.PER_ID 
        AND M.CNT_ID = COT1.CNT_ID    
    INNER JOIN
    SCHHAYS.dbo.WTPRH AS PRH 
        ON M.PER_ID = PRH.PER_ID 
        AND M.CNT_ID = PRH.CNT_ID 
        AND M.TIE_ID = PRH.TIE_ID     
    INNER JOIN
    ##tempStartEndWeekDates AS Tsed 
        ON     PRH_DTEDEBSEM&gt;=Tsed.StartDate 
        AND PRH_DTEFINSEM&lt;=Tsed.EndDate     
    LEFT JOIN
    SCHHAYS.dbo.WTSCCT C 
        ON CC.RGPCNT_ID = C.RGPCNT_ID 
        AND CC.PER_ID = C.PER_ID 
        AND CC.CNT_ID = C.CNT_ID   
    INNER JOIN
    ##TempHaysStaffWeek HF 
        ON C.VAPO_CODE = HF.onetouch  COLLATE Latin1_General_CI_AS
group by
WeekOfMonth,StartDate,EndDate,CNTI_THPAYE,CNTI_DURHEBDO)t
GROUP BY WeekOfMonth,StartDate,EndDate
Indentation is your best friend when nesting SQL Queries
Be consistent with your casing on keywords, SCREAMCASE is the standard for keywords in SQL
Be consistent with your Alias syntax, sometimes it was written table t and others it was written table AS t
Use newlines in your select statements, especially when they are complex
INT is not a good table alias, change it
I have no clue what any of these tables are, and I can't tell from the aliases either.
Naming, 

I recognized like 4 field names

MonthName
WeekOfMonth
StartDate
EndDate

Some Temp Tables
and some variables that aren't assigned anywhere in the code you have given us.

Spacing in your equations




I took your code and I cleaned up the formatting so that it might be easier for others to read it and provide you a better review, here is the cleaned up code using standard spacing, casing, and other standards that I could implement.

SELECT
    Id
    ,Fees
    ,WeekOfMonth
    ,CONVERT(NVARCHAR(MAX), StartDate, 103) AS StartDate
    ,CONVERT(NVARCHAR(MAX), EndDate, 103) AS EndDate
    ,'Temp Fees                      ' AS FeesName 
    ,@MonthName AS [MonthName]
INTO ##TempFeesMonthly
FROM
(
    SELECT DISTINCT 
        1 AS Id
        ,sum((CNTI_THPAYE * (CNTI_THFACT / CNTI_THPAYE)) * (CASE WHEN CNTI_DURHEBDO IS NULL THEN 1 ELSE CNTI_DURHEBDO / 5 END) * @NumberOfDays) AS Fees
        ,WeekOfMonth
        ,StartDate
        ,EndDate
    FROM SCHHAYS.dbo.WTVTAT AS TAT
        LEFT JOIN SCHHAYS.dbo.WTTIEINT [INT] 
            ON TAT.TIE_ID = INT.TIE_ID
            AND TAT.VTAT_IORDRE = INT.TIEI_ORDRE
        LEFT JOIN SCHHAYS.dbo.PYCONTRAT CC 
            ON TAT.PER_ID = CC.PER_ID 
            AND TAT.CNT_ID = CC.CNT_ID     
        LEFT JOIN SCHHAYS.dbo.CMTIERS T 
            ON TAT.TIE_ID = T.TIE_ID    
        LEFT JOIN SCHHAYS.dbo.WTMISS M 
            ON CC.PER_ID = M.PER_ID 
            AND CC.CNT_ID = M.CNT_ID     
        LEFT JOIN ##WTCNTIWeek COT1 
            ON M.PER_ID = COT1.PER_ID 
            AND M.CNT_ID = COT1.CNT_ID    
        INNER JOIN SCHHAYS.dbo.WTPRH AS PRH 
            ON M.PER_ID = PRH.PER_ID 
            AND M.CNT_ID = PRH.CNT_ID 
            AND M.TIE_ID = PRH.TIE_ID     
        INNER JOIN ##tempStartEndWeekDates AS Tsed 
            ON PRH_DTEDEBSEM &gt;= Tsed.StartDate 
            AND PRH_DTEFINSEM &lt;= Tsed.EndDate     
        LEFT JOIN SCHHAYS.dbo.WTSCCT C 
            ON CC.RGPCNT_ID = C.RGPCNT_ID 
            AND CC.PER_ID = C.PER_ID 
            AND CC.CNT_ID = C.CNT_ID   
        INNER JOIN ##TempHaysStaffWeek HF 
            ON C.VAPO_CODE = HF.onetouch  COLLATE Latin1_General_CI_AS
    GROUP BY WeekOfMonth, StartDate, EndDate
) AS t

UNION ALL

SELECT
    2
    , SUM(Fees) AS Fees
    , WeekOfMonth
    , CONVERT(NVARCHAR(MAX), StartDate, 103) AS StartDate
    , CONVERT(NVARCHAR(MAX), EndDate, 103) AS EndDate
    , 'Temp Margin ' AS FeesName 
    , @MonthName AS [MonthName]
FROM
(
    SELECT DISTINCT 
        SUM((CASE WHEN CNTI_DURHEBDO IS NULL THEN 1 ELSE CNTI_DURHEBDO / 5 END) * @NumberOfDays) - (CNTI_THPAYE * (CASE WHEN CNTI_DURHEBDO IS NULL THEN 1 ELSE CNTI_DURHEBDO / 5 END) * 1.453 * 1.21 * @NumberOfDays) AS Fees
        , WeekOfMonth
        , StartDate
        , EndDate
    FROM SCHHAYS.dbo.WTVTAT TAT
        LEFT JOIN SCHHAYS.dbo.WTTIEINT AS [INT] 
            ON TAT.TIE_ID = INT.TIE_ID
        AND TAT.VTAT_IORDRE = INT.TIEI_ORDRE
        LEFT JOIN SCHHAYS.dbo.PYCONTRAT AS CC 
            ON TAT.PER_ID = CC.PER_ID 
            AND TAT.CNT_ID = CC.CNT_ID     
        LEFT JOIN SCHHAYS.dbo.CMTIERS AS T 
            ON TAT.TIE_ID = T.TIE_ID    
        LEFT JOIN SCHHAYS.dbo.WTMISS AS M 
            ON CC.PER_ID = M.PER_ID 
            AND CC.CNT_ID = M.CNT_ID     
        LEFT JOIN ##WTCNTIWeek AS COT1 
            ON M.PER_ID = COT1.PER_ID 
            AND M.CNT_ID = COT1.CNT_ID    
        INNER JOIN SCHHAYS.dbo.WTPRH AS PRH 
            ON M.PER_ID = PRH.PER_ID 
            AND M.CNT_ID = PRH.CNT_ID 
            AND M.TIE_ID = PRH.TIE_ID     
        INNER JOIN ##tempStartEndWeekDates AS Tsed 
            ON PRH_DTEDEBSEM &gt;= Tsed.StartDate 
            AND PRH_DTEFINSEM &lt;= Tsed.EndDate     
        LEFT JOIN SCHHAYS.dbo.WTSCCT AS C 
            ON CC.RGPCNT_ID = C.RGPCNT_ID 
            AND CC.PER_ID = C.PER_ID 
            AND CC.CNT_ID = C.CNT_ID   
        INNER JOIN ##TempHaysStaffWeek AS HF 
            ON C.VAPO_CODE = HF.onetouch  COLLATE Latin1_General_CI_AS
    GROUP BY WeekOfMonth, StartDate, EndDate, CNTI_THPAYE, CNTI_DURHEBDO
) AS t
GROUP BY WeekOfMonth, StartDate, EndDate




The only thing that I can suggest that might speed things up is a complete guess because you haven't provided the full code or table schemas

In both queries on either side of the UNION ALL  you join almost every table to another table on 2-3 fields, if this isn't necessary you are forcing the query to perform a lot of stuff that it doesn't need to



Another thought, you are calling COLLATE on both Queries as well, that probably doesn't need to done at all on this level, your SQL Server most likely already has Latin1_General_CI_AS as a default collation on the entire server, so calling it here is wasting processing power, twice.