Re-write this query without using nested Select statements

Other SQL Server Topics
1

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

2 
Select distinct ji.[JobItemID] as 'PLP-ID'
,C.WC_rate
,C.WC_charge
,C.service_rate
,C.service_charge
,C.DTP_hour
,C.DTP_rate
,C.DTP_charge
,C.eng_hours
,C.eng_rate
,C.eng_charge
,C.PM_charge
,C.total_service_charge
,C.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
left outer join (
    Select 
        VARInternalID,
        MAX(CASE WHEN ChargeTypeID = 8 THEN ChargePerUnit END) AS WC_rate,
        SUM(CASE WHEN ChargeTypeID = 8 THEN Charge END) AS WC_charge,
        MAX(CASE WHEN ChargeTypeID = 7 THEN ChargePerUnit END) AS service_rate,
        SUM(CASE WHEN ChargeTypeID = 7 THEN Charge END) AS service_charge,
        SUM(CASE WHEN ChargeTypeID = 1 THEN ChargePerUnit END) AS DTP_rate,
        SUM(CASE WHEN ChargeTypeID = 1 THEN Charge END) AS DTP_charge,
        MAX(CASE WHEN ChargeTypeID = 3 THEN ChargeVolume END) AS DTP_hour,
        SUM(CASE WHEN ChargeTypeID = 3 THEN ChargePerUnit END) AS eng_rate,
        SUM(CASE WHEN ChargeTypeID = 3 THEN Charge END) AS eng_charge,
        MAX(CASE WHEN ChargeTypeID = 1 THEN ChargeVolume END) AS eng_hours,
        SUM(CASE WHEN ChargeTypeID = 5 THEN Charge END) AS PM_charge,
        SUM(CASE WHEN ChargeTypeID IN (7, 8) THEN Charge END) AS total_service_charge,
        SUM(CASE WHEN ChargeTypeID IN (1, 3, 5) THEN Charge END) AS total_non_service_charge
    From VARCharges
    Group By VARInternalID
) AS C ON C.VARInternalID = I.VARInternalID
1 Like
3

Query should run noticeably faster too.

1 Like