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)