Ok I think I have it working well now. Took a lot of pairing down of the sqbpro script but I got there in the end.
<?xml version="1.0" encoding="UTF-8"?><sqlb_project><db path="/projects/esb_smart_meter_stats/homeassistant/config/home-assistant_v2.db" readonly="0" foreign_keys="1" case_sensitive_like="0" temp_store="0" wal_autocheckpoint="1000" synchronous="2"/><attached/><window><main_tabs open="structure browser pragmas query" current="3"/></window><tab_structure><column_width id="0" width="406"/><column_width id="1" width="0"/><column_width id="2" width="100"/><column_width id="3" width="3448"/><column_width id="4" width="0"/><expanded_item id="0" parent="1"/><expanded_item id="1" parent="1"/><expanded_item id="2" parent="1"/><expanded_item id="3" parent="1"/></tab_structure><tab_browse><current_table name="4,15:mainstatistics_meta"/><default_encoding codec=""/><browse_table_settings><table schema="main" name="event_data" show_row_id="0" encoding="" plot_x_axis="" unlock_view_pk="_rowid_"><sort/><column_widths><column index="1" value="47"/><column index="2" value="76"/><column index="3" value="300"/></column_widths><filter_values/><conditional_formats/><row_id_formats/><display_formats/><hidden_columns/><plot_y_axes/><global_filter/></table><table schema="main" name="statistics_meta" show_row_id="0" encoding="" plot_x_axis="" unlock_view_pk="_rowid_"><sort/><column_widths><column index="1" value="23"/><column index="2" value="235"/><column index="3" value="53"/><column index="4" value="122"/><column index="5" value="61"/><column index="6" value="54"/><column index="7" value="39"/></column_widths><filter_values/><conditional_formats/><row_id_formats/><display_formats/><hidden_columns/><plot_y_axes/><global_filter/></table></browse_table_settings></tab_browse><tab_sql><sql name="Import Toon data">/*
Import Toon files
How to:
NT = normal tarriff
LT = Low rate tarriff
*/
/* Remove the temporary tables if they exist */
DROP TABLE IF EXISTS NT_ORIG_NEW;
DROP TABLE IF EXISTS LT_ORIG_NEW;
DROP TABLE IF EXISTS NT_PROD_NEW;
DROP TABLE IF EXISTS LT_PROD_NEW;
DROP TABLE IF EXISTS SOLAR_NEW;
DROP TABLE IF EXISTS GAS_NEW;
/* Create temp tables that can hold the difference between the measurements and create a new sum */
CREATE TABLE "NT_ORIG_NEW" (
"ts" INTEGER,
"value" FLOAT,
"diff" FLOAT,
"old_sum" FLOAT,
"new_sum" FLOAT
);
/* Insert the hourly records from Toon - max 5 years */
INSERT INTO NT_ORIG_NEW (ts, value)
SELECT field1, round(field2 / 1000.0, 3)
FROM elec_quantity_esb;
/* Remove any overlapping records from Toon which are already in Home Assistant */
DELETE FROM NT_ORIG_NEW
WHERE
ts >= (SELECT MIN(start_ts) FROM statistics WHERE metadata_id = 2); /* Change */
/* Insert the data from Home Assistant so that we can adjust the records with the new calculated sum */
INSERT INTO NT_ORIG_NEW (ts, value, old_sum)
SELECT start_ts, state, sum
FROM statistics
WHERE metadata_id = 2; /* Change */
/*
Calculate the difference from the previous record in the table
- For the Toon values calculate the diff from the previous record from the imported values (use value column / old_sum column is empty)
- For the Home Assistant values calculate the diff from the previous record from the existing sum column (use old_sum column / old_sum column is not empty)
*/
WITH CTE_DIFF_NT_ORIG_VALUE AS (
SELECT ts, round(value - (lag(value, 1, 0) OVER (ORDER BY ts)), 3) AS diff
FROM NT_ORIG_NEW
ORDER BY ts
)
UPDATE NT_ORIG_NEW
SET diff = CTE_DIFF_NT_ORIG_VALUE.diff
FROM CTE_DIFF_NT_ORIG_VALUE
WHERE
NT_ORIG_NEW.ts = CTE_DIFF_NT_ORIG_VALUE.ts AND
NT_ORIG_NEW.old_sum IS NULL;
WITH CTE_DIFF_NT_ORIG_SUM AS (
SELECT ts, old_sum - (lag(old_sum, 1, 0) OVER (ORDER BY ts)) AS diff
FROM NT_ORIG_NEW
ORDER BY ts
)
UPDATE NT_ORIG_NEW
SET diff = CTE_DIFF_NT_ORIG_SUM.diff
FROM CTE_DIFF_NT_ORIG_SUM
WHERE
NT_ORIG_NEW.ts = CTE_DIFF_NT_ORIG_SUM.ts AND
NT_ORIG_NEW.old_sum IS NOT NULL;
/* Cleanup possible wrong values:
- Remove the first record if no diff could be determined (Toon data)
- Diff is null => The point where Toon data goes over to Home Assistant data
- Diff < 0 => Probably new meter installed (measurement should be low)
- Diff > 1000 => Incorrect value
First handle the first two cases and then correct to 0 when incorrect value
*/
DELETE FROM NT_ORIG_NEW
WHERE
ROWID IN (
SELECT ROWID FROM NT_ORIG_NEW
WHERE
old_sum IS NULL
ORDER BY ts
LIMIT 1
);
UPDATE NT_ORIG_NEW
SET diff = round(old_sum, 3)
WHERE (diff IS NULL);
UPDATE NT_ORIG_NEW
SET diff = round(value, 3)
WHERE (diff < 0.0) AND (value < 10);
UPDATE NT_ORIG_NEW
SET diff = 0
WHERE (diff < 0.0) OR (diff > 1000.0);
/* Calculate the new sum
It is calculated by calculating the sum until the record that is currently processed
*/
WITH CTE_SUM_NT_ORIG AS (
SELECT ts, SUM(diff) OVER (ORDER BY ts) AS new_sum
FROM NT_ORIG_NEW
)
UPDATE NT_ORIG_NEW
SET new_sum = round(CTE_SUM_NT_ORIG.new_sum, 3)
FROM CTE_SUM_NT_ORIG
WHERE
NT_ORIG_NEW.ts = CTE_SUM_NT_ORIG.ts;
/* Copy the new information to the statistics table
id => primary key and automatically filled with ROWID
sum => calculated new_sum value
metadata_id => the fixed metadata id of this statistics (see top)
created_ts => set to the timestamp of the statistic
start_ts => timestamp of the statistic
The sum is updated in case the record is already in Home Assistant
"where true" is needed to remove parsing ambiguity
*/
INSERT INTO statistics (state, sum, metadata_id, created_ts, start_ts)
SELECT new_sum, new_sum, 2, ts, ts FROM NT_ORIG_NEW WHERE true /* Change */
ON CONFLICT DO UPDATE SET sum = excluded.sum;
/* Also update the short term statistics.
We calculate the delta with which the sum was changed and add that to the current measurements
*/
UPDATE statistics_short_term
SET sum = sum + (
SELECT (new_sum - sum) as correction_factor
FROM
statistics_short_term as SST, NT_ORIG_NEW AS SN
WHERE
SST.metadata_id = 2 AND /* Change */
SST.start_ts = SN.ts
ORDER BY state DESC
LIMIT 1
)
WHERE
metadata_id = 2; /* Change */
/* Remove the temporary tables */
DROP TABLE IF EXISTS NT_ORIG_NEW;
DROP TABLE IF EXISTS LT_ORIG_NEW;
DROP TABLE IF EXISTS NT_PROD_NEW;
DROP TABLE IF EXISTS LT_PROD_NEW;
DROP TABLE IF EXISTS SOLAR_NEW;
DROP TABLE IF EXISTS GAS_NEW;
DROP TABLE IF EXISTS elec_quantity_esb;</sql><current_tab id="0"/></tab_sql></sqlb_project>
