Ok I have a stored procedure that is updated a table based on (dP.D_DATE >= DATEADD(day, DATEDIFF(day, 2, GETDATE()), 0)) so it pulls in data from 2 days prior. I tried adding a column for Hours on Line and Days on Line where Hours on Line adds 24 and Days on Line add 1. Is there a way in the stored procedure to say CASE if Source = DPD take the HOL from 3 days and add 24 to it and populate the same table with the value and the DOL +1 from the same table.
Example if DOL = 254 on 3/3/2020 add 1 and populate 3/4/2020 with 255. If HOL = 6892 on 3/3/2020 then add 24 and populate 3/4/2020 with 6916. I have tried everything and it is loading 1277 lines of duplicates for just one well? I have attached the Code.
INSERT INTO [dbo].[test]
([SOURCE]
,[WELL]
,[OPERATOR]
,[PROPNUM]
,[DATE]
,[TBG_PSI]
,[CSG_PSI]
,[DIFF_PSI]
,[LINE_PSI]
,[BB GAS]
,[CUM_GAS]
,[GAS_SPOT_MCFD]
,[NET_GAS_MCFD]
,[GAS_INJ_SPOT_MCFD]
,[BOPD]
,[CUM_OIL]
,[BWPD]
,[CUM_WTR]
,[PERCENT_LOAD]
,[HOL]
,[DOL]
,[GPI]
,[FUEL_GAS])
SELECT
'DPD'
,ac.LEASE
,ac.OPERATOR
,ac.PROPNUM
,dp.D_DATE
,dp.FTP
,dp.CP
,dp.DIFF
,dp.LINE
,dp.BUY_BACK
,dc.CUM_GAS AS [CUM_GAS]
,dp.GAS
,CASE WHEN dp.[BUY_BACK] IS NULL THEN dp.GAS ELSE dp.GAS - dp.BUY_BACK END
,dp.GAS_LIFT
,dp.OIL
,dc.CUM_OIL AS [CUM_OIL]
,dp.WATER
,dc.CUM_WATER AS [CUM_WTR]
,SUM(dc.CUM_WATER/wd.TTLR)*100 AS PERCENT_LOAD
,SUM(fb.HOL + 24) AS HOL
,SUM(fb.DOL + 1) AS DOL
,ac.GPI
,dp.FUEL_GAS
FROM [RESERVES].dbo.AC_PROPERTY ac
LEFT JOIN [RESERVES].dbo.AC_DAILY dp
ON ac.PROPNUM = dp.PROPNUM
LEFT JOIN [COMPLETIONS].dbo.FLOWBACK_COMPARISON fb
ON ac.PROPNUM = fb.PROPNUM
LEFT JOIN [COMPLETIONS].dbo.DAILY_CUMS dc
ON ac.PROPNUM = dc.PROPNUM
LEFT JOIN [COMPLETIONS].dbo.WELL_DATA wd
ON ac.PROPNUM = wd.PROPNUM
WHERE ac.ANALYST_FLAG2 ='FLOWBACK' AND ac.LEASE LIKE 'HAMILTON%' AND (dP.D_DATE >= DATEADD(day, DATEDIFF(day, 2, GETDATE()), 0))
GROUP BY ac.LEASE, ac.OPERATOR ,ac.PROPNUM, dp.D_DATE, dp.FTP, dp.CP, dp.DIFF, dp.LINE, dp.BUY_BACK, DP.GAS, dp.OIL, dp.WATER, dp.GAS_LIFT, fb.PERCENT_LOAD, ac.GPI, dp.FUEL_GAS, dc.CUM_GAS, dc.CUM_OIL, dc.CUM_WATER, fb.DOL, fb.HOL
ORDER BY ac.LEASE, ac.PROPNUM, dp.D_DATE
;
END