• From a query perspective, one should not get in the habit of simply moving a JOIN condition to the WHERE clause. It will only be a matter of time before you move one where it will actually cause a problem. I would suggest using the WITH clause to create CTE's that will use those conditions in a WHERE clause, and then the main join in your query is to the CTE. See the following formatted SQL:

    ;WITH TRN_DEFECT AS (

    SELECT *

    FROM CUSCONFIG.[TrnDefectDetailsUI]

    WHERE i_defect_status_id NOT IN (6, 8)

    ),

    TRN_SVC_TRACKING AS (

    SELECT *

    FROM [CUSCONFIG].[TrnServiceTrackingSystemUI]

    WHERE DWTENABLED IS NULL

    OR DWTENABLED = 0

    )

    SELECT defect.i_project_id AS projectid,

    NULL AS phaseid,

    MasVerificationRecordUI.detectbyprocessid AS processdisciplineid,

    effort.f_effort AS actual

    FROM TRN_DEFECT AS defect

    INNER JOIN CUSCONFIG.MasVerificationRecordUI AS MasVerificationRecordUI

    ON defect.i_verification_record_id = MasVerificationRecordUI.i_verification_record_id

    AND defect.i_project_id = MasVerificationRecordUI.i_project_id

    INNER JOIN SCHEMABFS.lean_package_projectdetails AS proj

    ON proj.projectid = defect.i_project_id

    INNER JOIN cusconfig.[TrnDefectEffortUI] effort

    ON effort.i_defect_detail_ref = defect.i_defect_detail_ref

    AND effort.d_entry_date <= proj.ENDDATE

    INNER JOIN TRN_SVC_TRACKING AS ServiceTracking

    ON defect.I_PROJECT_ID =ServiceTracking.PROJECT_ID

    INNER JOIN cusconfig.[pcprocesspackagetasksUI] AS task

    ON task.taskid = MasVerificationRecordUI.detectbytaskid

    INNER JOIN cusconfig.[PCPROCESSPACKAGEWORKFLOWSUI] AS workflow

    ON task.workflowid = workflow.workflowid

    INNER JOIN cusconfig.[PCPROCESSPACKAGEPHASESUI] AS phase

    ON workflow.phaseid = phase.phaseid

    This will at least segregate the conditions where they belong and give SQL Server a better opportunity to optimize the query. Also, formatting your queries this way makes it MUCH EASIER to read and understand. Just continuing each line at the beginning of a line makes it hard to see what's going on.

    Steve (aka sgmunson) 🙂 🙂 🙂
    Rent Servers for Income (picks and shovels strategy)