Selecting internationalization preferences I have a query below using CTE. I am wondering if there are some approach other than this. So I am collecting some advise to refactor it. Or does the query look good even though I am using this IN (SELECT InvariantLanguageCultureCode FROM y) statement many times in the query?

DECLARE  @ReportingInstanceID INT

 SET @ReportingInstanceID=34
 ;WITH x AS (
        SELECT  [dbo].[tenant_Reporting_Instance].UserID
        FROM    [dbo].[tenant_Reporting_Instance]
        WHERE   tenant_Reporting_Instance.ReportingInstanceID = @ReportingInstanceID
        )
        ,y AS ( SELECT  [TRCCore].[dbo].[pr_Language].InvariantLanguageCultureCode
        FROM    [TRCCore].[dbo].[pr_Language]
                JOIN [dbo].[tenant_User_Setting] ON tenant_User_Setting.LanguageID = pr_Language.LanguageID
        WHERE   tenant_User_Setting.UserID IN (SELECT UserID FROM x))
        ,Default_User_Settings_CTE ( RoundingTypeInvariantCode, AgingDateValue, CurrencyLabel, JobZoneLinkTypeLabel, MatchPeriodLabel, PayFrequencyLabel, PayTypeLabel )
                  AS ( SELECT   r.RoundingTypeInvariantCode ,
                                a.AgingDateValue ,
                                cur.CurrencyLabel ,
                                jz.JobZoneLinkTypeLabel ,
                                m.MatchPeriodLabel ,
                                pf.PayFrequencyLabel ,
                                pt.PayTypeLabel
                       FROM     dbo.tenant_User_Setting s
                                JOIN TRCCore.dbo.pr_Rounding_Type r ON r.RoundingTypeID = s.RoundingTypeID
                                JOIN dbo.tenant_Aging_Date a ON a.AgingDateID = s.AgingDateID
                                JOIN TRCCore.dbo.translation_pr_Currency cur ON cur.CurrencyID = s.CurrencyID
                                                              AND cur.LanguageCultureCode IN (SELECT InvariantLanguageCultureCode FROM y)
                                JOIN dbo.translation_tenant_Job_Zone_Link_Type jz ON jz.JobZoneLinkTypeID = s.JobZoneLinkTypeID
                                                              AND jz.LanguageCultureCode IN (SELECT InvariantLanguageCultureCode FROM y)
                                JOIN dbo.tenant_Match_Period m ON m.MatchPeriodID = s.MatchPeriodID
                                JOIN dbo.translation_tenant_Pay_Frequency pf ON pf.PayFrequencyID = s.PayFrequencyID
                                                              AND pf.LanguageCultureCode IN (SELECT InvariantLanguageCultureCode FROM y)
                                JOIN dbo.translation_app_Pay_Type pt ON pt.PayTypeID = s.PayTypeID
                                                              AND pt.LanguageCultureCode IN (SELECT InvariantLanguageCultureCode FROM y)
                       WHERE    s.UserID IN (SELECT UserID FROM x)
                     )
        SELECT * FROM Default_User_Settings_CTE
Move the First and second Cte to temp table.

you don't have to refer  ( SELECT InvariantLanguageCultureCode FROM y) more than once in join condition instead use it in one column then join that column with other tables.

Try this.

DECLARE @ReportingInstanceID INT

SET @ReportingInstanceID=34;

SELECT [TRCCore].[dbo].[pr_Language].InvariantLanguageCultureCode
INTO   #temp_InvariantLanguageCultureCode
FROM   [TRCCore].[dbo].[pr_Language]
       JOIN [dbo].[tenant_User_Setting]
         ON tenant_User_Setting.LanguageID = pr_Language.LanguageID
WHERE  tenant_User_Setting.UserID IN (SELECT [dbo].[tenant_Reporting_Instance].UserID
                                      FROM   [dbo].[tenant_Reporting_Instance]
                                      WHERE  tenant_Reporting_Instance.ReportingInstanceID = @ReportingInstanceID)

SELECT [dbo].[tenant_Reporting_Instance].UserID
INTO   #temp_UserID
FROM   [dbo].[tenant_Reporting_Instance]
WHERE  tenant_Reporting_Instance.ReportingInstanceID = @ReportingInstanceID

SELECT r.RoundingTypeInvariantCode,
       a.AgingDateValue,
       cur.CurrencyLabel,
       jz.JobZoneLinkTypeLabel,
       m.MatchPeriodLabel,
       pf.PayFrequencyLabel,
       pt.PayTypeLabel
FROM   dbo.tenant_User_Setting s
       JOIN TRCCore.dbo.pr_Rounding_Type r
         ON r.RoundingTypeID = s.RoundingTypeID
       JOIN dbo.tenant_Aging_Date a
         ON a.AgingDateID = s.AgingDateID
       JOIN TRCCore.dbo.translation_pr_Currency cur
         ON cur.CurrencyID = s.CurrencyID
            AND cur.LanguageCultureCode IN (SELECT InvariantLanguageCultureCode
                                            FROM   #temp_InvariantLanguageCultureCode)
       JOIN dbo.translation_tenant_Job_Zone_Link_Type jz
         ON jz.JobZoneLinkTypeID = s.JobZoneLinkTypeID