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
This should get you close
Select WELLCOMPL = WLC.name
, SUB = WLC.id_subsidiary
, PD.Date
, PD.days
, PD.oil
, PD.water
, PD.gas
, PD.interest
, PD.operator
, PD.comments
, permit_interest = tt.net_working_int_oil
, permit_operator = tt.operator
, Oil_net = PD.oil * tt.net_working_int_oil / 100
, Gas_net = PD.gas * tt.net_working_int_gas / 100
From dbo.prd PD With (NoLock)
Inner Join dbo.wellbore_compl WLC With (NoLock) On PD.id_subsidiary = WLC.id_subsidiary
And pd.id_wellborecompl = WLC.id
Cross Apply (Select PT.operator
, PT.net_working_int_oil
, PT.net_working_int_gas
From dbo.wellstring ws With (NoLock)
Inner Join dbo.wellbore wb With (NoLock) On wb.id = ws.id_wellbore
Inner Join dbo.wellbore_permit WP With (NoLock) On WP.id_wellbore = wb.id
Inner Join dbo.permit p With (NoLock) On p.id = WP.id_permit
Inner Join dbo.permit_terms PT With (NoLock) On PT.id_permit = p.id
Where ws.id = WLC.id_wellstring -- this ties us back to the outer query
And PD.[date] >= pt.Start_Date -- this ties back to date on outer PD table
And pd.[date] <= pt.end_date -- this ties back to date on outer PD table
And ws.deletedAt Is Null
And wb.deletedAt Is Null
And p.deletedAt Is Null
And PT.deletedAt Is Null
) tt
Where PD.deletedAt Is Null
And WLC.deletedAt Is Null;
As already stated - remove NOLOCK hints unless you are sure that you understand the risks associated with using it.
Now you can add any additional columns needed from the permit (PT) table and define your calculations based off those values. This assumes you will always have related values and are only interested in showing those that actually have values.
If you need to show all, even if there is no wellstring, wellbore, permit, etc... then change the cross apply to an outer apply and you will get NULL values for all of your calculations.