Hello, sorry but the code ctrl+K shortcut does not work for me do not know why. I tried.
I have a below code that i need to re-write without nested select statements. How do i do that ? i am fairly new to SQL.
Select distinct ji.[JobItemID] as 'PLP-ID'
,(Select MAX (crg.ChargePerUnit) FROM VARCharges as crg WITH (NOLOCK) where crg.VARInternalID= I.VARInternalID AND crg.ChargeTypeID = 8 ) AS 'WC_rate'
,(Select SUM (crg.Charge) FROM VARCharges as crg WITH (NOLOCK) where crg.VARInternalID= I.VARInternalID AND crg.ChargeTypeID = 8 ) AS 'WC_charge'
,(Select MAX (crg.ChargePerUnit) FROM VARCharges as crg WITH (NOLOCK) where crg.VARInternalID= I.VARInternalID AND crg.ChargeTypeID = 7 ) AS 'service_rate'
,(Select SUM (crg.Charge) FROM VARCharges as crg WITH (NOLOCK) where crg.VARInternalID= I.VARInternalID AND crg.ChargeTypeID = 7 ) AS 'service_charge'
,(Select SUM (crg.ChargeVolume) FROM VARCharges as crg WITH (NOLOCK) where crg.VARInternalID= I.VARInternalID AND crg.ChargeTypeID = 1 ) AS 'DTP_hour'
,(Select MAX (crg.ChargePerUnit) FROM VARCharges as crg WITH (NOLOCK) where crg.VARInternalID= I.VARInternalID AND crg.ChargeTypeID = 1 ) AS 'DTP_rate'
,(Select SUM (crg.Charge) FROM VARCharges as crg WITH (NOLOCK) where crg.VARInternalID= I.VARInternalID AND crg.ChargeTypeID = 1 ) AS 'DTP_charge'
,(Select SUM (crg.ChargeVolume) FROM VARCharges as crg WITH (NOLOCK) where crg.VARInternalID= I.VARInternalID AND crg.ChargeTypeID = 3 ) AS 'eng_hours'
,(Select MAX (crg.ChargePerUnit) FROM VARCharges as crg WITH (NOLOCK) where crg.VARInternalID= I.VARInternalID AND crg.ChargeTypeID = 3 ) AS 'eng_rate'
,(Select SUM (crg.Charge) FROM VARCharges as crg WITH (NOLOCK) where crg.VARInternalID= I.VARInternalID AND crg.ChargeTypeID = 3 ) AS 'eng_charge'
,(Select SUM (crg.Charge) FROM VARCharges as crg WITH (NOLOCK) where crg.VARInternalID= I.VARInternalID AND crg.ChargeTypeID = 5 ) AS 'PM_charge'
,(Select SUM (crg.Charge) FROM VARCharges as crg WITH (NOLOCK) where crg.VARInternalID= I.VARInternalID AND (crg.ChargeTypeID = 7 OR crg.ChargeTypeID = 8) ) AS 'total_service_charge'
,(Select SUM (crg.Charge) FROM VARCharges as crg WITH (NOLOCK) where crg.VARInternalID= I.VARInternalID AND (crg.ChargeTypeID = 1 OR crg.ChargeTypeID = 3 OR crg.ChargeTypeID = 5) ) AS 'total_non_service_charge'
from dbo.tbl_JobItem as JI WITH (NOLOCK)
JOIN VARInternals AS I WITH (NOLOCK) ON I.JobItemID = JI.jobItemID AND I.Archived = 0
join tbl_Language as L WITH (NOLOCK) on JI.[Target]=L.LanguageID
join tbl_job as J WITH (NOLOCK) on JI.JobID=J.JobID