I'm working on a project and am trying to mesh together daily data (proddate / OIL / Gas / Water) and 15min data (logtime / field name / datavalue)
Pulling this data for 2 years back seems to break my python script (crashes the kernel). I'm trying to parse it down in SQL on the front end.
What I'm looking for is converting the 15min data (logtime) to an average daily number. The data being averaged would be (datavalue). Ideally this would leave me with a "proddate" and "logtime" that are both daily and a "datavalue" that is the average for the day.
SELECT w.XID, w.wellname, Fieldname, logtime, datavalue, proddate, OilProd_BBL as OIL, GasProd_MCF as Gas, WaterProd_BBL as Water
FROM X_DW.elx.DeviceReadings r
INNER JOIN X_VIEWS.dbo.Wells w
ON r.DeviceId = w.ScadaID
INNER JOIN [X_VIEWS].[dbo].[Production] p
ON w.XID = p.XID
WHERE logtime > GETDATE() - 5
AND w.XID = 'QW081588'
AND FieldName IN ('DischargePress_PSI', 'IntakePress_PSI','MotorTemp_F','Power_Amps','Frequency_HZ')
To get this to run I've been using:
WHERE logtime > GETDATE() - 5
to reduce the amount of rows... but like I said above I'd like to go back a couple of years.
I'm guessing there needs to be an "avg" somewhere in select with a group by somewhere.... the multiple inner joins with groupby is throwing me off I think.
Here is what the output logtime looks like:
2022-08-18 19:00:01.000
What this query returns right now
XID Wellname Fieldname Logtime Datavalue proddate OIL GAS WATER
QW081588 superwell Power_Amps 2022-08-18 19:00:01.000 50 8-18-22 100 200 300
QW081588 superwell Power_Amps 2022-08-18 19:15:01.000 45 8-18-22 100 200 300
QW081588 superwell Power_Amps 2022-08-18 19:30:01.000 60 8-18-22 100 200 300
QW081588 superwell Power_Amps 2022-08-18 19:45:01.000 55 8-18-22 100 200 300
QW081588 superwell MotorTemp_F 2022-08-18 19:00:01.000 300 8-18-22 100 200 300
QW081588 superwell MotorTemp_F 2022-08-18 19:15:01.000 290 8-18-22 100 200 300
QW081588 superwell MotorTemp_F 2022-08-18 19:30:01.000 250 8-18-22 100 200 300
QW081588 superwell MotorTemp_F 2022-08-18 19:45:01.000 301 8-18-22 100 200 300
This is what I'd want it to return:
XID Wellname Fieldname Logtime avg(Datavalue) proddate OIL GAS WATER
QW081588 superwell Power_Amps 8-18-22 52.5 8-18-22 100 200 300
QW081588 superwell MotorTemp_F 8-18-22 285.3 8-18-22 100 200 300
The frustrating part about this... is that I handled all the meshing and averaging in python... but when trying to go back very far it kills the kernel "The Kernel crashed while executing code"
In the Select clause, in place of logtime, use convert(date,logtime).
At the end of the query, add the line
group by w.XID, w.wellname, Fieldname, convert(date,logtime), proddate, OilProd_BBL, GasProd_MCF, WaterProd_BBL
And finally, in the Select clause in place of Datavalue, use avg(Datavalue)
I'm not sure how the Oil/water/gas figures in to all this, so you may have to make further adjustments if they're not all 100/200/300 all the time.