'Is there a way that I can return multiple columns from a subquery rather than repeating the subquery for each column? It works but takes too long

As you can see I'm trying to add 4 columns by repeating the subquery, this works but takes too long to be useful. I tried to put all the joins at the end but then I only get the records that have values in [dbo.permit_terms] whereas I want all the records from [dbo.prd]. I hope this makes sense and will be very grateful for an answer.

   SELECT WLC.name AS WELLCOMPL
    , WLC.id_subsidiary AS SUB
    , PD.Date
    , PD.days
    , PD.oil
    , PD.water
    , PD.gas
    , PD.interest
    , PD.operator
    , PD.comments
    , (
    SELECT PT.net_working_int_oil
        FROM dbo.prd AS P (NOLOCK)
            left JOIN dbo.wellbore_compl AS W WITH (NOLOCK) ON (P.id_subsidiary = W.id_subsidiary) AND (P.id_wellborecompl = W.id) 
            left JOIN dbo.wellstring WITH (NOLOCK) ON W.id_wellstring = dbo.wellstring.id 
            left JOIN dbo.wellbore wb WITH (NOLOCK) ON dbo.wellstring.id_wellbore = wb.id 
            left JOIN dbo.wellbore_permit WP WITH (NOLOCK) ON wb.id = WP.id_wellbore
            left JOIN dbo.permit  WITH (NOLOCK) ON dbo.permit.id = WP.id_permit
            left JOIN dbo.permit_terms PT WITH (NOLOCK) ON dbo.permit.id = PT.id_permit 
        where (p.[date] >= pt.start_date and p.[date] <=pt.end_date)
    --      AND W.[name] = WLC.[name] 
            AND p.id = pd.id
            AND pd.id_subsidiary = w.id_subsidiary
            AND P.deletedAt Is Null 
            AND W.deletedAt Is Null
            AND dbo.wellstring.deletedAt Is Null
            AND dbo.permit.deletedAt Is Null
            AND wb.deletedAt Is Null
            AND PT.deletedAt Is Null
    )
     as permit_interest 
    , (
    SELECT PT.operator
        FROM dbo.prd AS P (NOLOCK)
            left JOIN dbo.wellbore_compl AS W WITH (NOLOCK) ON (P.id_subsidiary = W.id_subsidiary) AND (P.id_wellborecompl = W.id) 
            left JOIN dbo.wellstring WITH (NOLOCK) ON W.id_wellstring = dbo.wellstring.id 
            left JOIN dbo.wellbore wb WITH (NOLOCK) ON dbo.wellstring.id_wellbore = wb.id 
            left JOIN dbo.wellbore_permit WP WITH (NOLOCK) ON wb.id = WP.id_wellbore
            left JOIN dbo.permit  WITH (NOLOCK) ON dbo.permit.id = WP.id_permit
            left JOIN dbo.permit_terms PT WITH (NOLOCK) ON dbo.permit.id = PT.id_permit 
        where (p.[date] >= pt.start_date and p.[date] <=pt.end_date)
    --      AND W.[name] = WLC.[name] 
            AND p.id = pd.id
            AND pd.id_subsidiary = w.id_subsidiary
            AND P.deletedAt Is Null 
            AND W.deletedAt Is Null
            AND dbo.wellstring.deletedAt Is Null
            AND dbo.permit.deletedAt Is Null
            AND wb.deletedAt Is Null
            AND PT.deletedAt Is Null
    )
     as permit_operator
    , (
    SELECT pd.oil*PT.net_working_int_oil/100
        FROM dbo.prd AS P (NOLOCK)
            left JOIN dbo.wellbore_compl AS W WITH (NOLOCK) ON (P.id_subsidiary = W.id_subsidiary) AND (P.id_wellborecompl = W.id) 
            left JOIN dbo.wellstring WITH (NOLOCK) ON W.id_wellstring = dbo.wellstring.id 
            left JOIN dbo.wellbore wb WITH (NOLOCK) ON dbo.wellstring.id_wellbore = wb.id 
            left JOIN dbo.wellbore_permit WP WITH (NOLOCK) ON wb.id = WP.id_wellbore
            left JOIN dbo.permit  WITH (NOLOCK) ON dbo.permit.id = WP.id_permit
            left JOIN dbo.permit_terms PT WITH (NOLOCK) ON dbo.permit.id = PT.id_permit 
        where (p.[date] >= pt.start_date and p.[date] <=pt.end_date)
    --      AND W.[name] = WLC.[name] 
            AND p.id = pd.id
            AND pd.id_subsidiary = w.id_subsidiary
            AND P.deletedAt Is Null 
            AND W.deletedAt Is Null
            AND dbo.wellstring.deletedAt Is Null
            AND dbo.permit.deletedAt Is Null
            AND wb.deletedAt Is Null
            AND PT.deletedAt Is Null
    )
     as Oil_net
    , (
    SELECT pd.gas*PT.net_working_int_gas/100 
        FROM dbo.prd AS P (NOLOCK)
            left JOIN dbo.wellbore_compl AS W WITH (NOLOCK) ON (P.id_subsidiary = W.id_subsidiary) AND (P.id_wellborecompl = W.id) 
            left JOIN dbo.wellstring WITH (NOLOCK) ON W.id_wellstring = dbo.wellstring.id 
            left JOIN dbo.wellbore wb WITH (NOLOCK) ON dbo.wellstring.id_wellbore = wb.id 
            left JOIN dbo.wellbore_permit WP WITH (NOLOCK) ON wb.id = WP.id_wellbore
            left JOIN dbo.permit  WITH (NOLOCK) ON dbo.permit.id = WP.id_permit
            left JOIN dbo.permit_terms PT WITH (NOLOCK) ON dbo.permit.id = PT.id_permit 
        where (p.[date] >= pt.start_date and p.[date] <=pt.end_date)
    --      AND W.[name] = WLC.[name] 
            AND p.id = pd.id
            AND pd.id_subsidiary = w.id_subsidiary
            AND P.deletedAt Is Null 
            AND W.deletedAt Is Null
            AND dbo.wellstring.deletedAt Is Null
            AND dbo.permit.deletedAt Is Null
            AND wb.deletedAt Is Null
            AND PT.deletedAt Is Null
    )
    as Gas_net
    FROM (dbo.prd AS PD 
    INNER JOIN dbo.wellbore_compl AS WLC ON (PD.id_subsidiary = WLC.id_subsidiary) AND (PD.id_wellborecompl = WLC.id)) 
    WHERE 
     PD.deletedAt Is Null 
    AND WLC.deletedAt Is Null


Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source