Hi vijayarani

I took the liberty of changing the table aliases to a two character abreviation to make the code simple.

I reformatted the select statement. The condition of the table join should be in the ON clause.

Any other filtering, should be in the WHERE clause.

I think there are a total of three conditions that should be in the WHERE clause.

Since no ER Diagram is attached, DWTENABLED should be prefixed with the table alias for cleaner code.

Good luck

-- TSQL Code --

SELECT

d1.i_project_id AS projectid,

NULL AS phaseid,

m1.detectbyprocessid AS processdisciplineid

e1.f_effort AS actual

FROM

CUSCONFIG.[TrnDefectDetailsUI] AS d1

INNER JOIN CUSCONFIG.MasVerificationRecordUI m1

ON d1.i_verification_record_id = m1.i_verification_record_id

AND d1.i_project_id = m1.i_project_id

INNER JOIN SCHEMABFS.lean_package_projectdetails as p1

ON p1.projectid = d1.i_project_id

INNER JOIN cusconfig.[TrnDefectEffortUI] as e1

ON e1.i_defect_detail_ref = d1.i_defect_detail_ref

INNER JOIN [CUSCONFIG].[TrnServiceTrackingSystemUI] as s1

ON d1.I_PROJECT_ID = s1.PROJECT_ID

INNER JOIN cusconfig.[pcprocesspackagetasksUI] AS t1

ON t1.taskid = m1.detectbytaskid

INNER JOIN cusconfig.[PCPROCESSPACKAGEWORKFLOWSUI] AS w1

ON t1.workflowid = w1.workflowid

INNER JOIN cusconfig.[PCPROCESSPACKAGEPHASESUI] AS p1

ON w1.phaseid = h1.phaseid

WHERE

d1.i_defect_status_id NOT IN ( 6 , 8 ) AND

e1.d_entry_date <= p1.ENDDATE AND

isnull(DWTENABLED,0) = 0