I have the following code. I am unable to share the actual databases at this time. I cannot use CTEs in my current environment. How can I rewrite this query without using CTEs? Is there a better of doing this?
WITH OIL_RF
AS
(
SELECT FldName,
[OOIP, 10^3bbl],
[RF, percent] AS 'Oil Recovery Factor %',
ResName
FROM [A].[dbo].[B]
WHERE ProjID NOT LIKE 'F%'
AND RtechDEsc = 'PRMS'
AND RclassDesc = '2P'
AND Product = 'O'
),
OIIP_SUM
AS
(
SELECT FldName,
SUM([OOIP, 10^3bbl]) AS 'Sum of OIIP per Field'
FROM [A].[dbo].[B]
WHERE ProjID NOT LIKE 'F%'
AND RtechDEsc = 'PRMS'
AND RclassDesc = '2P'
AND Product = 'O'
GROUP BY FldName),
Weights
AS
(
SELECT
OIL_RF.FldName, [OOIP, 10^3bbl],[Sum of OIIP per Field],ResName, [Oil Recovery Factor %],
case when [OOIP, 10^3bbl]=0 then NULL else [OOIP, 10^3bbl]/[Sum of OIIP per Field] end as 'Ratio of OIIP per Res and OIIP per Field (Weights)'
FROM OIL_RF LEFT JOIN OIIP_SUM ON OIL_RF.FldName= OIIP_SUM.FldName
),
RFbyWeights
AS
(
SELECT *,
[Oil Recovery Factor %]*[Ratio of OIIP per Res and OIIP per Field (Weights)] AS 'RF*Weight'
FROM Weights
)
SELECT FldName AS 'Field Name',
SUM([RF*Weight]) AS 'Weighted RF %'
FROM RFbyWeights
GROUP BY FldName