SQL to query Gas and Electricity usage over specific date range for the Dutch PrijsPlafond

[rrozema]

Here's another query I wrote to query the domoticz.db.

I switched to a new energy provider to make better use of the Dutch prijsplafond. My old contract ended January 27th 2023, so I wanted to see how much Gas and electricy was used between January 1st, 2023 and the January 27th, 2023, to verify if the old provider's bill was correct.

Here's how I found the exact values read from my P1 gas meter. First I needed to know the device ID for the device named "Gas". If your device has a different name, replace it in this query.

Code: Select all

-- How to find your Gas device's ID to use in below query:
select ID, Name from DeviceStatus where Name = 'Gas';

Now we query the Meter_Calendar table for the values read at the end of each day for this device. Replace the date values and the device ID with your particular values:

Code: Select all

-- List your Gas usage over the contract period.
-- Please replace the value after DeviceRowID = by the ID found for your "Gas" device.
-- Also replace the both date values by the start and end date of the period 
-- you want to query for. Start date must be the last day BEFORE the contract start!
-- In my example the period started January 1st 2023 (for calculating the 'prijs plafond'), 
-- so I entered 2022-12-31 for the start date.
select t2.date_start, t2.Counter_start as [Verbruik Meterstand start], t2.date_end, t2.Counter_end as [Verbruik Meterstand eind], (t2.Counter_end - t2.Counter_start)/1000.0 as [verbruik] from ( select max(case when t.nr = 1 then t.Date end) as Date_start, max(case when t.nr = 2 then t.Date end) as Date_end, max(case when t.nr = 1 then t.Counter end) as Counter_start, max(case when t.nr = 2 then t.Counter end) as Counter_end from ( select row_number() over (order by date) as nr, * from Meter_calendar where devicerowid = 6 and date in ( '2022-12-31', '2023-01-27') )t) t2;

We can do the same thing for electricity. However this query is a bit more complicated because 4 different values are stored in the MultiMeter_Calendar table: 'verbruik normaal tarief', 'levering normaal tarief', 'verbruik dal tarief' and 'levering dal tarief'. Here are the queries I built:

Code: Select all

-- How to find your Netstroom device's ID to use in below queries:
select ID, Name from DeviceStatus where Name = 'Netstroom';

Code: Select all

-- Electricity from January 1st 2023 until contract end.
-- Put your device ID found above after the DeviceRowID =.
-- and change the date values to your specific contract period. 
-- Please note the start date must indicate the last day before the contract start.
select t2.date_start, t2.Counter1_start as [Verbruik Daltarief Meterstand start], t2.Counter3_start as [Verbruik Normaaltarief Meterstand start], t2.Counter2_start as [Levering Daltarief Meterstand start], t2.Counter4_start as [Levering Normaaltarief Meterstand start], t2.date_end, t2.Counter1_end as [Verbruik Daltarief Meterstand eind], t2.Counter3_end as [Verbruik Normaaltarief Meterstand eind], t2.Counter2_end as [Levering Daltarief Meterstand eind], t2.Counter4_end as [Levering Normaaltarief Meterstand eind], (t2.Counter1_end - t2.Counter1_start)/1000.0 as [verbruik Daltarief], (t2.Counter2_end - t2.Counter2_start)/1000.0 as [levering Daltarief],(t2.Counter3_end - t2.Counter3_start)/1000.0 as [verbruik Normaaltarief], (t2.Counter4_end - t2.Counter4_start)/1000.0 as [levering Normaaltarief], ((t2.Counter3_end - t2.Counter3_start) - (t2.Counter4_end - t2.Counter4_start))/1000.0 as [Netto verbruik Normaaltarief], ((t2.Counter1_end - t2.Counter1_start) - (t2.Counter2_end - t2.Counter2_start))/1000.0 as [Netto verbruik Daltarief] from ( select max(case when t.nr = 1 then t.Date end) as Date_start, max(case when t.nr = 2 then t.Date end) as Date_end, max(case when t.nr = 1 then t.Counter1 end) as Counter1_start, max(case when t.nr = 2 then t.Counter1 end) as Counter1_end, max(case when t.nr = 1 then t.Counter2 end) as Counter2_start, max(case when t.nr = 2 then t.Counter2 end) as Counter2_end, max(case when t.nr = 1 then t.Counter3 end) as Counter3_start, max(case when t.nr = 2 then t.Counter3 end) as Counter3_end, max(case when t.nr = 1 then t.Counter4 end) as Counter4_start, max(case when t.nr = 2 then t.Counter4 end) as Counter4_end from ( select row_number() over (order by date) as nr, * from MultiMeter_calendar where devicerowid = 3 and date in ( '2022-12-31', '2023-01-27') )t) t2;

[gizmocuz]

Why don't you use the same JSON calls as the graphs use, or use the counter values from the report?

[rrozema]

Why don't you use the same JSON calls as the graphs use, or use the counter values from the report?

Please show me how. I know how to do queries, not how to use those json calls.

But also: the counter values from the report are not for a specific date range as far as I know. This query takes the exact counter values for the specified date range: in my case January 1st (the date the prijsplafond started) until January 27th (the date my contract ended). Using these counter values I get the exact values that were reported from my meter to the energy company and those values should match. The only discrepancy I could think of is that the time could be different when the energy company read my meter and when Domoticz read it.

[rrozema]

I also imported the prijsplafond values, as published on rijksoverheid.nl into the meter_calendar table for 2 dummy devices. I did this by creating the dummy devices, find their device ID and then running the following queries:

For the Gas prijsplafond values:

Code: Select all

insert into Meter_Calendar (DeviceRowID, Date, Value)
select 1983, t.Date, t.Gas * 1000.0
from (
select 	'2023-01-01' as [Date]	,	11.321629 as [Electra]	,	7.17361783 as [Gas]
union all select 	'2023-01-02'	,	11.151776	,	7.29365989
union all select 	'2023-01-03'	,	10.814767	,	7.50814813
union all select 	'2023-01-04'	,	10.933145	,	7.46678924
union all select 	'2023-01-05'	,	10.867808	,	7.40948305
union all select 	'2023-01-06'	,	10.874362	,	7.12583471
union all select 	'2023-01-07'	,	11.547017	,	7.06297627
union all select 	'2023-01-08'	,	11.609251	,	6.90513601
union all select 	'2023-01-09'	,	10.888195	,	6.93128915
union all select 	'2023-01-10'	,	10.611738	,	6.9051776
union all select 	'2023-01-11'	,	10.735336	,	6.94681715
union all select 	'2023-01-12'	,	10.606315	,	6.92379916
union all select 	'2023-01-13'	,	10.791567	,	6.69227866
union all select 	'2023-01-14'	,	11.522976	,	6.74976235
union all select 	'2023-01-15'	,	11.617777	,	6.72370085
union all select 	'2023-01-16'	,	10.779735	,	6.90239464
union all select 	'2023-01-17'	,	10.637142	,	6.96068977
union all select 	'2023-01-18'	,	10.592714	,	7.08851374
union all select 	'2023-01-19'	,	10.389801	,	7.00058675
union all select 	'2023-01-20'	,	10.798875	,	6.98597455
union all select 	'2023-01-21'	,	11.48632	,	7.12991528
union all select 	'2023-01-22'	,	11.563054	,	7.19546986
union all select 	'2023-01-23'	,	10.815985	,	7.43678302
union all select 	'2023-01-24'	,	10.70622	,	7.35814823
union all select 	'2023-01-25'	,	10.710802	,	7.49728597
union all select 	'2023-01-26'	,	10.4052	,	7.56062596
union all select 	'2023-01-27'	,	10.616436	,	7.31163871
union all select 	'2023-01-28'	,	11.463526	,	7.23298964
union all select 	'2023-01-29'	,	11.448301	,	7.10172329
union all select 	'2023-01-30'	,	10.702218	,	7.19504536
union all select 	'2023-01-31'	,	10.489358	,	7.37483185
union all select 	'2023-02-01'	,	10.519083	,	7.39434818
union all select 	'2023-02-02'	,	10.186772	,	7.16656154
union all select 	'2023-02-03'	,	10.316489	,	6.79139149
union all select 	'2023-02-04'	,	11.261048	,	6.76650941
union all select 	'2023-02-05'	,	11.521497	,	6.53328173
union all select 	'2023-02-06'	,	10.646596	,	6.69376133
union all select 	'2023-02-07'	,	10.330003	,	6.97538503
union all select 	'2023-02-08'	,	10.29848	,	7.14112236
union all select 	'2023-02-09'	,	10.120275	,	7.22550605
union all select 	'2023-02-10'	,	10.289374	,	7.18161464
union all select 	'2023-02-11'	,	11.059556	,	6.99223027
union all select 	'2023-02-12'	,	11.251913	,	6.8513672
union all select 	'2023-02-13'	,	10.304251	,	7.05662366
union all select 	'2023-02-14'	,	9.688175	,	7.02768284
union all select 	'2023-02-15'	,	9.761052	,	6.75782404
union all select 	'2023-02-16'	,	9.708185	,	6.56174147
union all select 	'2023-02-17'	,	9.723468	,	6.48236336
union all select 	'2023-02-18'	,	10.125843	,	6.51979424
union all select 	'2023-02-19'	,	10.088172	,	6.62183914
union all select 	'2023-02-20'	,	9.582209	,	6.62736338
union all select 	'2023-02-21'	,	8.822438	,	6.68609065
union all select 	'2023-02-22'	,	8.864227	,	6.69458268
union all select 	'2023-02-23'	,	9.131549	,	6.53537825
union all select 	'2023-02-24'	,	9.083699	,	6.25175401
union all select 	'2023-02-25'	,	9.615443	,	6.08763773
union all select 	'2023-02-26'	,	9.930615	,	5.98658881
union all select 	'2023-02-27'	,	9.216896	,	6.18420619
union all select 	'2023-02-28'	,	8.564686	,	6.21955242
union all select 	'2023-03-01'	,	8.708265	,	6.33414223
union all select 	'2023-03-02'	,	9.024655	,	6.3920358
union all select 	'2023-03-03'	,	9.018188	,	6.38074567
union all select 	'2023-03-04'	,	9.7324	,	6.1333713
union all select 	'2023-03-05'	,	10.161049	,	5.81858212
union all select 	'2023-03-06'	,	9.085961	,	5.87373527
union all select 	'2023-03-07'	,	8.573009	,	5.63116308
union all select 	'2023-03-08'	,	8.853497	,	5.49814379
union all select 	'2023-03-09'	,	8.859065	,	5.41436083
union all select 	'2023-03-10'	,	8.870694	,	5.35144112
union all select 	'2023-03-11'	,	9.608599	,	5.4200091
union all select 	'2023-03-12'	,	9.751627	,	5.14192746
union all select 	'2023-03-13'	,	8.779924	,	5.34588974
union all select 	'2023-03-14'	,	8.480499	,	5.30233717
union all select 	'2023-03-15'	,	8.699217	,	5.05722134
union all select 	'2023-03-16'	,	8.663344	,	4.85703858
union all select 	'2023-03-17'	,	8.598471	,	4.86955706
union all select 	'2023-03-18'	,	9.250565	,	4.9993197
union all select 	'2023-03-19'	,	9.211821	,	4.96335972
union all select 	'2023-03-20'	,	8.555725	,	5.06943626
union all select 	'2023-03-21'	,	8.33257	,	5.06784216
union all select 	'2023-03-22'	,	8.130034	,	4.96086198
union all select 	'2023-03-23'	,	8.214511	,	4.84258328
union all select 	'2023-03-24'	,	8.031173	,	4.53696997
union all select 	'2023-03-25'	,	8.647481	,	4.37508967
union all select 	'2023-03-26'	,	8.287736	,	4.55934398
union all select 	'2023-03-27'	,	7.73401	,	4.49977616
union all select 	'2023-03-28'	,	7.08122	,	4.55496799
union all select 	'2023-03-29'	,	7.418751	,	4.19289924
union all select 	'2023-03-30'	,	7.404657	,	3.69225967
union all select 	'2023-03-31'	,	7.592983	,	3.47473025
union all select 	'2023-04-01'	,	8.079777	,	3.37406227
union all select 	'2023-04-02'	,	8.137023	,	3.13616214
union all select 	'2023-04-03'	,	7.342858	,	3.42950266
union all select 	'2023-04-04'	,	7.053496	,	3.8634897
union all select 	'2023-04-05'	,	7.270503	,	4.17129634
union all select 	'2023-04-06'	,	7.100186	,	4.0048733
union all select 	'2023-04-07'	,	7.303998	,	3.5640263
union all select 	'2023-04-08'	,	8.019138	,	3.47990668
union all select 	'2023-04-09'	,	7.758457	,	3.10103989
union all select 	'2023-04-10'	,	7.375831	,	3.12204805
union all select 	'2023-04-11'	,	6.963016	,	3.29015648
union all select 	'2023-04-12'	,	6.94666	,	3.43146997
union all select 	'2023-04-13'	,	6.647699	,	3.68846788
union all select 	'2023-04-14'	,	6.722635	,	3.3293311
union all select 	'2023-04-15'	,	7.416199	,	3.06104812
union all select 	'2023-04-16'	,	7.035284	,	2.81264011
union all select 	'2023-04-17'	,	6.676003	,	2.9624469
union all select 	'2023-04-18'	,	6.303382	,	2.93833466
union all select 	'2023-04-19'	,	6.223864	,	2.89785233
union all select 	'2023-04-20'	,	6.255561	,	2.51750236
union all select 	'2023-04-21'	,	6.188339	,	2.172746
union all select 	'2023-04-22'	,	6.830834	,	2.00642815
union all select 	'2023-04-23'	,	6.592309	,	1.86792547
union all select 	'2023-04-24'	,	6.253589	,	1.83656459
union all select 	'2023-04-25'	,	5.979423	,	1.86647537
union all select 	'2023-04-26'	,	5.981221	,	1.95518306
union all select 	'2023-04-27'	,	6.702422	,	1.91831654
union all select 	'2023-04-28'	,	6.401808	,	2.09209433
union all select 	'2023-04-29'	,	6.810389	,	2.0167141
union all select 	'2023-04-30'	,	6.827731	,	1.81858578
union all select 	'2023-05-01'	,	6.166821	,	1.60206209
union all select 	'2023-05-02'	,	6.178508	,	1.61957664
union all select 	'2023-05-03'	,	6.143911	,	1.60384111
union all select 	'2023-05-04'	,	6.187933	,	1.69101241
union all select 	'2023-05-05'	,	6.03635	,	1.6423665
union all select 	'2023-05-06'	,	6.55603	,	1.57653253
union all select 	'2023-05-07'	,	6.468827	,	1.32181553
union all select 	'2023-05-08'	,	5.9044	,	1.35462656
union all select 	'2023-05-09'	,	5.740086	,	1.15338499
union all select 	'2023-05-10'	,	5.777525	,	1.12296589
union all select 	'2023-05-11'	,	5.736345	,	1.18614508
union all select 	'2023-05-12'	,	5.710448	,	1.26037852
union all select 	'2023-05-13'	,	6.294566	,	1.12140696
union all select 	'2023-05-14'	,	6.310052	,	1.21073387
union all select 	'2023-05-15'	,	5.791474	,	1.2450091
union all select 	'2023-05-16'	,	5.659698	,	1.1967596
union all select 	'2023-05-17'	,	5.555124	,	1.18517618
union all select 	'2023-05-18'	,	6.252197	,	1.02318482
union all select 	'2023-05-19'	,	5.74345	,	0.96128749
union all select 	'2023-05-20'	,	6.010424	,	0.8158073
union all select 	'2023-05-21'	,	6.210669	,	0.88714532
union all select 	'2023-05-22'	,	5.667035	,	0.90468481
union all select 	'2023-05-23'	,	5.615705	,	0.8695861
union all select 	'2023-05-24'	,	5.445388	,	0.85710858
union all select 	'2023-05-25'	,	5.442662	,	0.83091895
union all select 	'2023-05-26'	,	5.23247	,	0.84871555
union all select 	'2023-05-27'	,	5.494804	,	0.79069552
union all select 	'2023-05-28'	,	5.647953	,	0.81712562
union all select 	'2023-05-29'	,	5.558662	,	0.77404646
union all select 	'2023-05-30'	,	5.280407	,	0.71923118
union all select 	'2023-05-31'	,	5.31802	,	0.69844573
union all select 	'2023-06-01'	,	5.484219	,	0.67137725
union all select 	'2023-06-02'	,	5.4114	,	0.65331774
union all select 	'2023-06-03'	,	5.606483	,	0.65264522
union all select 	'2023-06-04'	,	5.504026	,	0.69033146
union all select 	'2023-06-05'	,	5.190855	,	0.68549868
union all select 	'2023-06-06'	,	5.09965	,	0.68327159
union all select 	'2023-06-07'	,	5.29888	,	0.67334834
union all select 	'2023-06-08'	,	5.243867	,	0.6671072
union all select 	'2023-06-09'	,	5.315439	,	0.68183083
union all select 	'2023-06-10'	,	5.692874	,	0.62803514
union all select 	'2023-06-11'	,	5.64195	,	0.64351399
union all select 	'2023-06-12'	,	5.178588	,	0.62778155
union all select 	'2023-06-13'	,	5.132014	,	0.62322211
union all select 	'2023-06-14'	,	5.33687	,	0.60647658
union all select 	'2023-06-15'	,	5.312017	,	0.59900252
union all select 	'2023-06-16'	,	5.316715	,	0.62671348
union all select 	'2023-06-17'	,	5.416562	,	0.56840167
union all select 	'2023-06-18'	,	5.570407	,	0.63051601
union all select 	'2023-06-19'	,	5.263152	,	0.60524975
union all select 	'2023-06-20'	,	5.109655	,	0.6038147
union all select 	'2023-06-21'	,	5.205181	,	0.5848005
union all select 	'2023-06-22'	,	5.316106	,	0.60646592
union all select 	'2023-06-23'	,	5.246912	,	0.63832346
union all select 	'2023-06-24'	,	5.461193	,	0.61693966
union all select 	'2023-06-25'	,	5.54683	,	0.62504214
union all select 	'2023-06-26'	,	5.214664	,	0.57042036
union all select 	'2023-06-27'	,	5.081525	,	0.56796326
union all select 	'2023-06-28'	,	5.078016	,	0.56537989
union all select 	'2023-06-29'	,	5.044724	,	0.57959834
union all select 	'2023-06-30'	,	4.979039	,	0.5622769
union all select 	'2023-07-01'	,	5.47317	,	0.55953127
union all select 	'2023-07-02'	,	5.538594	,	0.58038446
union all select 	'2023-07-03'	,	5.164639	,	0.5661326
union all select 	'2023-07-04'	,	5.032834	,	0.55614494
union all select 	'2023-07-05'	,	5.093415	,	0.54503117
union all select 	'2023-07-06'	,	4.982142	,	0.54999786
union all select 	'2023-07-07'	,	5.011577	,	0.54674671
union all select 	'2023-07-08'	,	5.448897	,	0.55009128
union all select 	'2023-07-09'	,	5.570842	,	0.59383794
union all select 	'2023-07-10'	,	5.207762	,	0.55603008
union all select 	'2023-07-11'	,	5.109191	,	0.56114256
union all select 	'2023-07-12'	,	5.084367	,	0.57363995
union all select 	'2023-07-13'	,	5.072796	,	0.56241896
union all select 	'2023-07-14'	,	5.032689	,	0.57818165
union all select 	'2023-07-15'	,	5.281741	,	0.54429919
union all select 	'2023-07-16'	,	5.399974	,	0.56796701
union all select 	'2023-07-17'	,	5.038344	,	0.54732128
union all select 	'2023-07-18'	,	5.097707	,	0.5481287
union all select 	'2023-07-19'	,	5.103014	,	0.54021942
union all select 	'2023-07-20'	,	5.219623	,	0.54267691
union all select 	'2023-07-21'	,	5.110496	,	0.53977651
union all select 	'2023-07-22'	,	5.461193	,	0.5384846
union all select 	'2023-07-23'	,	5.346353	,	0.56499448
union all select 	'2023-07-24'	,	5.002442	,	0.547195
union all select 	'2023-07-25'	,	5.04281	,	0.54254719
union all select 	'2023-07-26'	,	5.069432	,	0.54281489
union all select 	'2023-07-27'	,	5.106929	,	0.54540694
union all select 	'2023-07-28'	,	5.106378	,	0.5328153
union all select 	'2023-07-29'	,	5.467834	,	0.53998415
union all select 	'2023-07-30'	,	5.2345	,	0.56067566
union all select 	'2023-07-31'	,	5.060123	,	0.54111043
union all select 	'2023-08-01'	,	4.921909	,	0.53567102
union all select 	'2023-08-02'	,	4.963466	,	0.53514
union all select 	'2023-08-03'	,	4.914862	,	0.53514
union all select 	'2023-08-04'	,	5.144426	,	0.5253
union all select 	'2023-08-05'	,	5.631655	,	0.53374975
union all select 	'2023-08-06'	,	5.556835	,	0.54899698
union all select 	'2023-08-07'	,	5.541059	,	0.54093034
union all select 	'2023-08-08'	,	5.222088	,	0.5422708
union all select 	'2023-08-09'	,	5.34441	,	0.54683204
union all select 	'2023-08-10'	,	5.44475	,	0.54717354
union all select 	'2023-08-11'	,	5.549585	,	0.54480494
union all select 	'2023-08-12'	,	6.045949	,	0.54031513
union all select 	'2023-08-13'	,	6.098149	,	0.56252779
union all select 	'2023-08-14'	,	5.945986	,	0.55014626
union all select 	'2023-08-15'	,	5.629161	,	0.54058578
union all select 	'2023-08-16'	,	5.649983	,	0.53738905
union all select 	'2023-08-17'	,	5.856521	,	0.55425887
union all select 	'2023-08-18'	,	5.718307	,	0.54794138
union all select 	'2023-08-19'	,	5.928731	,	0.54873619
union all select 	'2023-08-20'	,	6.148522	,	0.56150334
union all select 	'2023-08-21'	,	5.805626	,	0.54844692
union all select 	'2023-08-22'	,	5.728486	,	0.55896661
union all select 	'2023-08-23'	,	5.762648	,	0.56550066
union all select 	'2023-08-24'	,	5.773088	,	0.5682563
union all select 	'2023-08-25'	,	5.849561	,	0.57024144
union all select 	'2023-08-26'	,	6.018544	,	0.56373832
union all select 	'2023-08-27'	,	6.443539	,	0.60008246
union all select 	'2023-08-28'	,	5.891756	,	0.58213879
union all select 	'2023-08-29'	,	5.898165	,	0.62706535
union all select 	'2023-08-30'	,	5.994822	,	0.62469466
union all select 	'2023-08-31'	,	5.90005	,	0.61423171
union all select 	'2023-09-01'	,	6.012773	,	0.58928904
union all select 	'2023-09-02'	,	6.251588	,	0.55941635
union all select 	'2023-09-03'	,	6.672581	,	0.63402464
union all select 	'2023-09-04'	,	6.026577	,	0.65885335
union all select 	'2023-09-05'	,	5.964198	,	0.65388412
union all select 	'2023-09-06'	,	6.189528	,	0.65097845
union all select 	'2023-09-07'	,	6.090348	,	0.65822159
union all select 	'2023-09-08'	,	6.062131	,	0.65170182
union all select 	'2023-09-09'	,	6.542835	,	0.61614016
union all select 	'2023-09-10'	,	6.75091	,	0.68267237
union all select 	'2023-09-11'	,	6.260375	,	0.67214809
union all select 	'2023-09-12'	,	6.254894	,	0.67865315
union all select 	'2023-09-13'	,	6.372083	,	0.74551014
union all select 	'2023-09-14'	,	6.197474	,	0.84495514
union all select 	'2023-09-15'	,	6.280965	,	0.8573852
union all select 	'2023-09-16'	,	6.633402	,	0.80558136
union all select 	'2023-09-17'	,	6.928622	,	0.89469236
union all select 	'2023-09-18'	,	6.566325	,	0.90856757
union all select 	'2023-09-19'	,	6.467754	,	0.9047651
union all select 	'2023-09-20'	,	6.480543	,	0.89446666
union all select 	'2023-09-21'	,	6.609709	,	0.83622768
union all select 	'2023-09-22'	,	6.773907	,	0.83826014
union all select 	'2023-09-23'	,	6.947704	,	0.81265447
union all select 	'2023-09-24'	,	7.66876	,	0.94133995
union all select 	'2023-09-25'	,	7.094647	,	0.95566073
union all select 	'2023-09-26'	,	6.921198	,	0.9663058
union all select 	'2023-09-27'	,	7.165233	,	0.96224534
union all select 	'2023-09-28'	,	7.453464	,	1.02139183
union all select 	'2023-09-29'	,	7.584921	,	1.11909116
union all select 	'2023-09-30'	,	8.006233	,	1.24319872
union all select 	'2023-10-01'	,	8.710585	,	1.57043478
union all select 	'2023-10-02'	,	7.898498	,	1.76777894
union all select 	'2023-10-03'	,	7.675604	,	1.81455061
union all select 	'2023-10-04'	,	7.971578	,	1.90907052
union all select 	'2023-10-05'	,	7.941447	,	1.99894022
union all select 	'2023-10-06'	,	8.099091	,	2.11086835
union all select 	'2023-10-07'	,	8.63011	,	2.07794884
union all select 	'2023-10-08'	,	8.975906	,	1.90463772
union all select 	'2023-10-09'	,	8.259316	,	2.04317806
union all select 	'2023-10-10'	,	7.985527	,	2.10946619
union all select 	'2023-10-11'	,	8.30473	,	2.21119639
union all select 	'2023-10-12'	,	8.107472	,	2.32430322
union all select 	'2023-10-13'	,	8.213815	,	2.31905846
union all select 	'2023-10-14'	,	8.712876	,	2.36815397
union all select 	'2023-10-15'	,	8.94331	,	2.29131431
union all select 	'2023-10-16'	,	8.423601	,	2.61299417
union all select 	'2023-10-17'	,	8.183307	,	2.75403059
union all select 	'2023-10-18'	,	8.450194	,	2.94665803
union all select 	'2023-10-19'	,	8.339704	,	2.89336418
union all select 	'2023-10-20'	,	8.52455	,	2.95666906
union all select 	'2023-10-21'	,	9.096604	,	3.07442792
union all select 	'2023-10-22'	,	9.086367	,	2.90511974
union all select 	'2023-10-23'	,	8.854802	,	3.05840303
union all select 	'2023-10-24'	,	8.468058	,	3.1672682
union all select 	'2023-10-25'	,	8.560278	,	3.18810383
union all select 	'2023-10-26'	,	8.584232	,	3.11528614
union all select 	'2023-10-27'	,	8.684978	,	3.2798946
union all select 	'2023-10-28'	,	9.453217	,	3.41381906
union all select 	'2023-10-29'	,	10.471784	,	3.48598024
union all select 	'2023-10-30'	,	9.402728	,	3.56958755
union all select 	'2023-10-31'	,	9.401597	,	3.62275514
union all select 	'2023-11-01'	,	9.646096	,	3.76247078
union all select 	'2023-11-02'	,	9.569797	,	3.79335062
union all select 	'2023-11-03'	,	9.587922	,	3.67880611
union all select 	'2023-11-04'	,	10.243293	,	3.63490626
union all select 	'2023-11-05'	,	10.320897	,	3.78758707
union all select 	'2023-11-06'	,	9.67933	,	3.97212916
union all select 	'2023-11-07'	,	9.629682	,	4.17706524
union all select 	'2023-11-08'	,	9.88349	,	4.24792609
union all select 	'2023-11-09'	,	9.789588	,	4.39175154
union all select 	'2023-11-10'	,	9.861653	,	4.41613518
union all select 	'2023-11-11'	,	10.469841	,	4.59637202
union all select 	'2023-11-12'	,	10.541239	,	4.63698242
union all select 	'2023-11-13'	,	10.243699	,	4.7195774
union all select 	'2023-11-14'	,	9.943868	,	4.78600768
union all select 	'2023-11-15'	,	9.968721	,	4.81466141
union all select 	'2023-11-16'	,	10.023995	,	4.96622921
union all select 	'2023-11-17'	,	10.036958	,	5.05569503
union all select 	'2023-11-18'	,	10.65808	,	5.23945098
union all select 	'2023-11-19'	,	10.694127	,	5.34327938
union all select 	'2023-11-20'	,	10.378491	,	5.68601556
union all select 	'2023-11-21'	,	10.118564	,	5.74850358
union all select 	'2023-11-22'	,	10.049747	,	5.61756916
union all select 	'2023-11-23'	,	10.191905	,	5.43609485
union all select 	'2023-11-24'	,	10.309007	,	5.55160963
union all select 	'2023-11-25'	,	11.059527	,	5.61753797
union all select 	'2023-11-26'	,	11.120137	,	5.5794874
union all select 	'2023-11-27'	,	10.704857	,	5.70469898
union all select 	'2023-11-28'	,	10.257068	,	5.77639088
union all select 	'2023-11-29'	,	10.483674	,	5.93504257
union all select 	'2023-11-30'	,	10.752272	,	6.04070029
union all select 	'2023-12-01'	,	11.036646	,	6.09285304
union all select 	'2023-12-02'	,	11.445082	,	6.15886988
union all select 	'2023-12-03'	,	11.656811	,	6.18658619
union all select 	'2023-12-04'	,	11.281957	,	6.38676769
union all select 	'2023-12-05'	,	10.826454	,	6.39470252
union all select 	'2023-12-06'	,	11.113902	,	6.37163657
union all select 	'2023-12-07'	,	11.253595	,	6.46708343
union all select 	'2023-12-08'	,	11.481912	,	6.57854221
union all select 	'2023-12-09'	,	11.785281	,	6.7170259
union all select 	'2023-12-10'	,	11.994342	,	6.71854288
union all select 	'2023-12-11'	,	11.304954	,	6.64992443
union all select 	'2023-12-12'	,	11.10178	,	6.51386155
union all select 	'2023-12-13'	,	11.210878	,	6.5689296
union all select 	'2023-12-14'	,	11.079653	,	6.72261404
union all select 	'2023-12-15'	,	11.247969	,	6.80619704
union all select 	'2023-12-16'	,	11.8697	,	6.7838974
union all select 	'2023-12-17'	,	12.150942	,	6.58253207
union all select 	'2023-12-18'	,	11.457755	,	6.49862754
union all select 	'2023-12-19'	,	11.439601	,	6.51519817
union all select 	'2023-12-20'	,	11.488553	,	6.80140727
union all select 	'2023-12-21'	,	11.511898	,	7.02736985
union all select 	'2023-12-22'	,	11.269458	,	6.77435087
union all select 	'2023-12-23'	,	11.667715	,	6.690633
union all select 	'2023-12-24'	,	12.176143	,	6.4199535
union all select 	'2023-12-25'	,	12.185655	,	6.63312469
union all select 	'2023-12-26'	,	12.185974	,	6.89685004
union all select 	'2023-12-27'	,	11.798592	,	7.14043776
union all select 	'2023-12-28'	,	11.880111	,	7.24106764
union all select 	'2023-12-29'	,	11.375627	,	7.19102346
union all select 	'2023-12-30'	,	11.427334	,	7.14543568
union all select 	'2023-12-31'	,	10.623773	,	6.88440509
) t;

And for the KWh prijsplafond values:

Code: Select all

insert into Meter_Calendar (DeviceRowID, Date, Value)
select 1984, t.Date, t.Electra * 1000.0
from (
select 	'2023-01-01' as [Date]	,	11.321629 as [Electra]	,	7.17361783 as [Gas]
union all select 	'2023-01-02'	,	11.151776	,	7.29365989
union all select 	'2023-01-03'	,	10.814767	,	7.50814813
union all select 	'2023-01-04'	,	10.933145	,	7.46678924
union all select 	'2023-01-05'	,	10.867808	,	7.40948305
union all select 	'2023-01-06'	,	10.874362	,	7.12583471
union all select 	'2023-01-07'	,	11.547017	,	7.06297627
union all select 	'2023-01-08'	,	11.609251	,	6.90513601
union all select 	'2023-01-09'	,	10.888195	,	6.93128915
union all select 	'2023-01-10'	,	10.611738	,	6.9051776
union all select 	'2023-01-11'	,	10.735336	,	6.94681715
union all select 	'2023-01-12'	,	10.606315	,	6.92379916
union all select 	'2023-01-13'	,	10.791567	,	6.69227866
union all select 	'2023-01-14'	,	11.522976	,	6.74976235
union all select 	'2023-01-15'	,	11.617777	,	6.72370085
union all select 	'2023-01-16'	,	10.779735	,	6.90239464
union all select 	'2023-01-17'	,	10.637142	,	6.96068977
union all select 	'2023-01-18'	,	10.592714	,	7.08851374
union all select 	'2023-01-19'	,	10.389801	,	7.00058675
union all select 	'2023-01-20'	,	10.798875	,	6.98597455
union all select 	'2023-01-21'	,	11.48632	,	7.12991528
union all select 	'2023-01-22'	,	11.563054	,	7.19546986
union all select 	'2023-01-23'	,	10.815985	,	7.43678302
union all select 	'2023-01-24'	,	10.70622	,	7.35814823
union all select 	'2023-01-25'	,	10.710802	,	7.49728597
union all select 	'2023-01-26'	,	10.4052	,	7.56062596
union all select 	'2023-01-27'	,	10.616436	,	7.31163871
union all select 	'2023-01-28'	,	11.463526	,	7.23298964
union all select 	'2023-01-29'	,	11.448301	,	7.10172329
union all select 	'2023-01-30'	,	10.702218	,	7.19504536
union all select 	'2023-01-31'	,	10.489358	,	7.37483185
union all select 	'2023-02-01'	,	10.519083	,	7.39434818
union all select 	'2023-02-02'	,	10.186772	,	7.16656154
union all select 	'2023-02-03'	,	10.316489	,	6.79139149
union all select 	'2023-02-04'	,	11.261048	,	6.76650941
union all select 	'2023-02-05'	,	11.521497	,	6.53328173
union all select 	'2023-02-06'	,	10.646596	,	6.69376133
union all select 	'2023-02-07'	,	10.330003	,	6.97538503
union all select 	'2023-02-08'	,	10.29848	,	7.14112236
union all select 	'2023-02-09'	,	10.120275	,	7.22550605
union all select 	'2023-02-10'	,	10.289374	,	7.18161464
union all select 	'2023-02-11'	,	11.059556	,	6.99223027
union all select 	'2023-02-12'	,	11.251913	,	6.8513672
union all select 	'2023-02-13'	,	10.304251	,	7.05662366
union all select 	'2023-02-14'	,	9.688175	,	7.02768284
union all select 	'2023-02-15'	,	9.761052	,	6.75782404
union all select 	'2023-02-16'	,	9.708185	,	6.56174147
union all select 	'2023-02-17'	,	9.723468	,	6.48236336
union all select 	'2023-02-18'	,	10.125843	,	6.51979424
union all select 	'2023-02-19'	,	10.088172	,	6.62183914
union all select 	'2023-02-20'	,	9.582209	,	6.62736338
union all select 	'2023-02-21'	,	8.822438	,	6.68609065
union all select 	'2023-02-22'	,	8.864227	,	6.69458268
union all select 	'2023-02-23'	,	9.131549	,	6.53537825
union all select 	'2023-02-24'	,	9.083699	,	6.25175401
union all select 	'2023-02-25'	,	9.615443	,	6.08763773
union all select 	'2023-02-26'	,	9.930615	,	5.98658881
union all select 	'2023-02-27'	,	9.216896	,	6.18420619
union all select 	'2023-02-28'	,	8.564686	,	6.21955242
union all select 	'2023-03-01'	,	8.708265	,	6.33414223
union all select 	'2023-03-02'	,	9.024655	,	6.3920358
union all select 	'2023-03-03'	,	9.018188	,	6.38074567
union all select 	'2023-03-04'	,	9.7324	,	6.1333713
union all select 	'2023-03-05'	,	10.161049	,	5.81858212
union all select 	'2023-03-06'	,	9.085961	,	5.87373527
union all select 	'2023-03-07'	,	8.573009	,	5.63116308
union all select 	'2023-03-08'	,	8.853497	,	5.49814379
union all select 	'2023-03-09'	,	8.859065	,	5.41436083
union all select 	'2023-03-10'	,	8.870694	,	5.35144112
union all select 	'2023-03-11'	,	9.608599	,	5.4200091
union all select 	'2023-03-12'	,	9.751627	,	5.14192746
union all select 	'2023-03-13'	,	8.779924	,	5.34588974
union all select 	'2023-03-14'	,	8.480499	,	5.30233717
union all select 	'2023-03-15'	,	8.699217	,	5.05722134
union all select 	'2023-03-16'	,	8.663344	,	4.85703858
union all select 	'2023-03-17'	,	8.598471	,	4.86955706
union all select 	'2023-03-18'	,	9.250565	,	4.9993197
union all select 	'2023-03-19'	,	9.211821	,	4.96335972
union all select 	'2023-03-20'	,	8.555725	,	5.06943626
union all select 	'2023-03-21'	,	8.33257	,	5.06784216
union all select 	'2023-03-22'	,	8.130034	,	4.96086198
union all select 	'2023-03-23'	,	8.214511	,	4.84258328
union all select 	'2023-03-24'	,	8.031173	,	4.53696997
union all select 	'2023-03-25'	,	8.647481	,	4.37508967
union all select 	'2023-03-26'	,	8.287736	,	4.55934398
union all select 	'2023-03-27'	,	7.73401	,	4.49977616
union all select 	'2023-03-28'	,	7.08122	,	4.55496799
union all select 	'2023-03-29'	,	7.418751	,	4.19289924
union all select 	'2023-03-30'	,	7.404657	,	3.69225967
union all select 	'2023-03-31'	,	7.592983	,	3.47473025
union all select 	'2023-04-01'	,	8.079777	,	3.37406227
union all select 	'2023-04-02'	,	8.137023	,	3.13616214
union all select 	'2023-04-03'	,	7.342858	,	3.42950266
union all select 	'2023-04-04'	,	7.053496	,	3.8634897
union all select 	'2023-04-05'	,	7.270503	,	4.17129634
union all select 	'2023-04-06'	,	7.100186	,	4.0048733
union all select 	'2023-04-07'	,	7.303998	,	3.5640263
union all select 	'2023-04-08'	,	8.019138	,	3.47990668
union all select 	'2023-04-09'	,	7.758457	,	3.10103989
union all select 	'2023-04-10'	,	7.375831	,	3.12204805
union all select 	'2023-04-11'	,	6.963016	,	3.29015648
union all select 	'2023-04-12'	,	6.94666	,	3.43146997
union all select 	'2023-04-13'	,	6.647699	,	3.68846788
union all select 	'2023-04-14'	,	6.722635	,	3.3293311
union all select 	'2023-04-15'	,	7.416199	,	3.06104812
union all select 	'2023-04-16'	,	7.035284	,	2.81264011
union all select 	'2023-04-17'	,	6.676003	,	2.9624469
union all select 	'2023-04-18'	,	6.303382	,	2.93833466
union all select 	'2023-04-19'	,	6.223864	,	2.89785233
union all select 	'2023-04-20'	,	6.255561	,	2.51750236
union all select 	'2023-04-21'	,	6.188339	,	2.172746
union all select 	'2023-04-22'	,	6.830834	,	2.00642815
union all select 	'2023-04-23'	,	6.592309	,	1.86792547
union all select 	'2023-04-24'	,	6.253589	,	1.83656459
union all select 	'2023-04-25'	,	5.979423	,	1.86647537
union all select 	'2023-04-26'	,	5.981221	,	1.95518306
union all select 	'2023-04-27'	,	6.702422	,	1.91831654
union all select 	'2023-04-28'	,	6.401808	,	2.09209433
union all select 	'2023-04-29'	,	6.810389	,	2.0167141
union all select 	'2023-04-30'	,	6.827731	,	1.81858578
union all select 	'2023-05-01'	,	6.166821	,	1.60206209
union all select 	'2023-05-02'	,	6.178508	,	1.61957664
union all select 	'2023-05-03'	,	6.143911	,	1.60384111
union all select 	'2023-05-04'	,	6.187933	,	1.69101241
union all select 	'2023-05-05'	,	6.03635	,	1.6423665
union all select 	'2023-05-06'	,	6.55603	,	1.57653253
union all select 	'2023-05-07'	,	6.468827	,	1.32181553
union all select 	'2023-05-08'	,	5.9044	,	1.35462656
union all select 	'2023-05-09'	,	5.740086	,	1.15338499
union all select 	'2023-05-10'	,	5.777525	,	1.12296589
union all select 	'2023-05-11'	,	5.736345	,	1.18614508
union all select 	'2023-05-12'	,	5.710448	,	1.26037852
union all select 	'2023-05-13'	,	6.294566	,	1.12140696
union all select 	'2023-05-14'	,	6.310052	,	1.21073387
union all select 	'2023-05-15'	,	5.791474	,	1.2450091
union all select 	'2023-05-16'	,	5.659698	,	1.1967596
union all select 	'2023-05-17'	,	5.555124	,	1.18517618
union all select 	'2023-05-18'	,	6.252197	,	1.02318482
union all select 	'2023-05-19'	,	5.74345	,	0.96128749
union all select 	'2023-05-20'	,	6.010424	,	0.8158073
union all select 	'2023-05-21'	,	6.210669	,	0.88714532
union all select 	'2023-05-22'	,	5.667035	,	0.90468481
union all select 	'2023-05-23'	,	5.615705	,	0.8695861
union all select 	'2023-05-24'	,	5.445388	,	0.85710858
union all select 	'2023-05-25'	,	5.442662	,	0.83091895
union all select 	'2023-05-26'	,	5.23247	,	0.84871555
union all select 	'2023-05-27'	,	5.494804	,	0.79069552
union all select 	'2023-05-28'	,	5.647953	,	0.81712562
union all select 	'2023-05-29'	,	5.558662	,	0.77404646
union all select 	'2023-05-30'	,	5.280407	,	0.71923118
union all select 	'2023-05-31'	,	5.31802	,	0.69844573
union all select 	'2023-06-01'	,	5.484219	,	0.67137725
union all select 	'2023-06-02'	,	5.4114	,	0.65331774
union all select 	'2023-06-03'	,	5.606483	,	0.65264522
union all select 	'2023-06-04'	,	5.504026	,	0.69033146
union all select 	'2023-06-05'	,	5.190855	,	0.68549868
union all select 	'2023-06-06'	,	5.09965	,	0.68327159
union all select 	'2023-06-07'	,	5.29888	,	0.67334834
union all select 	'2023-06-08'	,	5.243867	,	0.6671072
union all select 	'2023-06-09'	,	5.315439	,	0.68183083
union all select 	'2023-06-10'	,	5.692874	,	0.62803514
union all select 	'2023-06-11'	,	5.64195	,	0.64351399
union all select 	'2023-06-12'	,	5.178588	,	0.62778155
union all select 	'2023-06-13'	,	5.132014	,	0.62322211
union all select 	'2023-06-14'	,	5.33687	,	0.60647658
union all select 	'2023-06-15'	,	5.312017	,	0.59900252
union all select 	'2023-06-16'	,	5.316715	,	0.62671348
union all select 	'2023-06-17'	,	5.416562	,	0.56840167
union all select 	'2023-06-18'	,	5.570407	,	0.63051601
union all select 	'2023-06-19'	,	5.263152	,	0.60524975
union all select 	'2023-06-20'	,	5.109655	,	0.6038147
union all select 	'2023-06-21'	,	5.205181	,	0.5848005
union all select 	'2023-06-22'	,	5.316106	,	0.60646592
union all select 	'2023-06-23'	,	5.246912	,	0.63832346
union all select 	'2023-06-24'	,	5.461193	,	0.61693966
union all select 	'2023-06-25'	,	5.54683	,	0.62504214
union all select 	'2023-06-26'	,	5.214664	,	0.57042036
union all select 	'2023-06-27'	,	5.081525	,	0.56796326
union all select 	'2023-06-28'	,	5.078016	,	0.56537989
union all select 	'2023-06-29'	,	5.044724	,	0.57959834
union all select 	'2023-06-30'	,	4.979039	,	0.5622769
union all select 	'2023-07-01'	,	5.47317	,	0.55953127
union all select 	'2023-07-02'	,	5.538594	,	0.58038446
union all select 	'2023-07-03'	,	5.164639	,	0.5661326
union all select 	'2023-07-04'	,	5.032834	,	0.55614494
union all select 	'2023-07-05'	,	5.093415	,	0.54503117
union all select 	'2023-07-06'	,	4.982142	,	0.54999786
union all select 	'2023-07-07'	,	5.011577	,	0.54674671
union all select 	'2023-07-08'	,	5.448897	,	0.55009128
union all select 	'2023-07-09'	,	5.570842	,	0.59383794
union all select 	'2023-07-10'	,	5.207762	,	0.55603008
union all select 	'2023-07-11'	,	5.109191	,	0.56114256
union all select 	'2023-07-12'	,	5.084367	,	0.57363995
union all select 	'2023-07-13'	,	5.072796	,	0.56241896
union all select 	'2023-07-14'	,	5.032689	,	0.57818165
union all select 	'2023-07-15'	,	5.281741	,	0.54429919
union all select 	'2023-07-16'	,	5.399974	,	0.56796701
union all select 	'2023-07-17'	,	5.038344	,	0.54732128
union all select 	'2023-07-18'	,	5.097707	,	0.5481287
union all select 	'2023-07-19'	,	5.103014	,	0.54021942
union all select 	'2023-07-20'	,	5.219623	,	0.54267691
union all select 	'2023-07-21'	,	5.110496	,	0.53977651
union all select 	'2023-07-22'	,	5.461193	,	0.5384846
union all select 	'2023-07-23'	,	5.346353	,	0.56499448
union all select 	'2023-07-24'	,	5.002442	,	0.547195
union all select 	'2023-07-25'	,	5.04281	,	0.54254719
union all select 	'2023-07-26'	,	5.069432	,	0.54281489
union all select 	'2023-07-27'	,	5.106929	,	0.54540694
union all select 	'2023-07-28'	,	5.106378	,	0.5328153
union all select 	'2023-07-29'	,	5.467834	,	0.53998415
union all select 	'2023-07-30'	,	5.2345	,	0.56067566
union all select 	'2023-07-31'	,	5.060123	,	0.54111043
union all select 	'2023-08-01'	,	4.921909	,	0.53567102
union all select 	'2023-08-02'	,	4.963466	,	0.53514
union all select 	'2023-08-03'	,	4.914862	,	0.53514
union all select 	'2023-08-04'	,	5.144426	,	0.5253
union all select 	'2023-08-05'	,	5.631655	,	0.53374975
union all select 	'2023-08-06'	,	5.556835	,	0.54899698
union all select 	'2023-08-07'	,	5.541059	,	0.54093034
union all select 	'2023-08-08'	,	5.222088	,	0.5422708
union all select 	'2023-08-09'	,	5.34441	,	0.54683204
union all select 	'2023-08-10'	,	5.44475	,	0.54717354
union all select 	'2023-08-11'	,	5.549585	,	0.54480494
union all select 	'2023-08-12'	,	6.045949	,	0.54031513
union all select 	'2023-08-13'	,	6.098149	,	0.56252779
union all select 	'2023-08-14'	,	5.945986	,	0.55014626
union all select 	'2023-08-15'	,	5.629161	,	0.54058578
union all select 	'2023-08-16'	,	5.649983	,	0.53738905
union all select 	'2023-08-17'	,	5.856521	,	0.55425887
union all select 	'2023-08-18'	,	5.718307	,	0.54794138
union all select 	'2023-08-19'	,	5.928731	,	0.54873619
union all select 	'2023-08-20'	,	6.148522	,	0.56150334
union all select 	'2023-08-21'	,	5.805626	,	0.54844692
union all select 	'2023-08-22'	,	5.728486	,	0.55896661
union all select 	'2023-08-23'	,	5.762648	,	0.56550066
union all select 	'2023-08-24'	,	5.773088	,	0.5682563
union all select 	'2023-08-25'	,	5.849561	,	0.57024144
union all select 	'2023-08-26'	,	6.018544	,	0.56373832
union all select 	'2023-08-27'	,	6.443539	,	0.60008246
union all select 	'2023-08-28'	,	5.891756	,	0.58213879
union all select 	'2023-08-29'	,	5.898165	,	0.62706535
union all select 	'2023-08-30'	,	5.994822	,	0.62469466
union all select 	'2023-08-31'	,	5.90005	,	0.61423171
union all select 	'2023-09-01'	,	6.012773	,	0.58928904
union all select 	'2023-09-02'	,	6.251588	,	0.55941635
union all select 	'2023-09-03'	,	6.672581	,	0.63402464
union all select 	'2023-09-04'	,	6.026577	,	0.65885335
union all select 	'2023-09-05'	,	5.964198	,	0.65388412
union all select 	'2023-09-06'	,	6.189528	,	0.65097845
union all select 	'2023-09-07'	,	6.090348	,	0.65822159
union all select 	'2023-09-08'	,	6.062131	,	0.65170182
union all select 	'2023-09-09'	,	6.542835	,	0.61614016
union all select 	'2023-09-10'	,	6.75091	,	0.68267237
union all select 	'2023-09-11'	,	6.260375	,	0.67214809
union all select 	'2023-09-12'	,	6.254894	,	0.67865315
union all select 	'2023-09-13'	,	6.372083	,	0.74551014
union all select 	'2023-09-14'	,	6.197474	,	0.84495514
union all select 	'2023-09-15'	,	6.280965	,	0.8573852
union all select 	'2023-09-16'	,	6.633402	,	0.80558136
union all select 	'2023-09-17'	,	6.928622	,	0.89469236
union all select 	'2023-09-18'	,	6.566325	,	0.90856757
union all select 	'2023-09-19'	,	6.467754	,	0.9047651
union all select 	'2023-09-20'	,	6.480543	,	0.89446666
union all select 	'2023-09-21'	,	6.609709	,	0.83622768
union all select 	'2023-09-22'	,	6.773907	,	0.83826014
union all select 	'2023-09-23'	,	6.947704	,	0.81265447
union all select 	'2023-09-24'	,	7.66876	,	0.94133995
union all select 	'2023-09-25'	,	7.094647	,	0.95566073
union all select 	'2023-09-26'	,	6.921198	,	0.9663058
union all select 	'2023-09-27'	,	7.165233	,	0.96224534
union all select 	'2023-09-28'	,	7.453464	,	1.02139183
union all select 	'2023-09-29'	,	7.584921	,	1.11909116
union all select 	'2023-09-30'	,	8.006233	,	1.24319872
union all select 	'2023-10-01'	,	8.710585	,	1.57043478
union all select 	'2023-10-02'	,	7.898498	,	1.76777894
union all select 	'2023-10-03'	,	7.675604	,	1.81455061
union all select 	'2023-10-04'	,	7.971578	,	1.90907052
union all select 	'2023-10-05'	,	7.941447	,	1.99894022
union all select 	'2023-10-06'	,	8.099091	,	2.11086835
union all select 	'2023-10-07'	,	8.63011	,	2.07794884
union all select 	'2023-10-08'	,	8.975906	,	1.90463772
union all select 	'2023-10-09'	,	8.259316	,	2.04317806
union all select 	'2023-10-10'	,	7.985527	,	2.10946619
union all select 	'2023-10-11'	,	8.30473	,	2.21119639
union all select 	'2023-10-12'	,	8.107472	,	2.32430322
union all select 	'2023-10-13'	,	8.213815	,	2.31905846
union all select 	'2023-10-14'	,	8.712876	,	2.36815397
union all select 	'2023-10-15'	,	8.94331	,	2.29131431
union all select 	'2023-10-16'	,	8.423601	,	2.61299417
union all select 	'2023-10-17'	,	8.183307	,	2.75403059
union all select 	'2023-10-18'	,	8.450194	,	2.94665803
union all select 	'2023-10-19'	,	8.339704	,	2.89336418
union all select 	'2023-10-20'	,	8.52455	,	2.95666906
union all select 	'2023-10-21'	,	9.096604	,	3.07442792
union all select 	'2023-10-22'	,	9.086367	,	2.90511974
union all select 	'2023-10-23'	,	8.854802	,	3.05840303
union all select 	'2023-10-24'	,	8.468058	,	3.1672682
union all select 	'2023-10-25'	,	8.560278	,	3.18810383
union all select 	'2023-10-26'	,	8.584232	,	3.11528614
union all select 	'2023-10-27'	,	8.684978	,	3.2798946
union all select 	'2023-10-28'	,	9.453217	,	3.41381906
union all select 	'2023-10-29'	,	10.471784	,	3.48598024
union all select 	'2023-10-30'	,	9.402728	,	3.56958755
union all select 	'2023-10-31'	,	9.401597	,	3.62275514
union all select 	'2023-11-01'	,	9.646096	,	3.76247078
union all select 	'2023-11-02'	,	9.569797	,	3.79335062
union all select 	'2023-11-03'	,	9.587922	,	3.67880611
union all select 	'2023-11-04'	,	10.243293	,	3.63490626
union all select 	'2023-11-05'	,	10.320897	,	3.78758707
union all select 	'2023-11-06'	,	9.67933	,	3.97212916
union all select 	'2023-11-07'	,	9.629682	,	4.17706524
union all select 	'2023-11-08'	,	9.88349	,	4.24792609
union all select 	'2023-11-09'	,	9.789588	,	4.39175154
union all select 	'2023-11-10'	,	9.861653	,	4.41613518
union all select 	'2023-11-11'	,	10.469841	,	4.59637202
union all select 	'2023-11-12'	,	10.541239	,	4.63698242
union all select 	'2023-11-13'	,	10.243699	,	4.7195774
union all select 	'2023-11-14'	,	9.943868	,	4.78600768
union all select 	'2023-11-15'	,	9.968721	,	4.81466141
union all select 	'2023-11-16'	,	10.023995	,	4.96622921
union all select 	'2023-11-17'	,	10.036958	,	5.05569503
union all select 	'2023-11-18'	,	10.65808	,	5.23945098
union all select 	'2023-11-19'	,	10.694127	,	5.34327938
union all select 	'2023-11-20'	,	10.378491	,	5.68601556
union all select 	'2023-11-21'	,	10.118564	,	5.74850358
union all select 	'2023-11-22'	,	10.049747	,	5.61756916
union all select 	'2023-11-23'	,	10.191905	,	5.43609485
union all select 	'2023-11-24'	,	10.309007	,	5.55160963
union all select 	'2023-11-25'	,	11.059527	,	5.61753797
union all select 	'2023-11-26'	,	11.120137	,	5.5794874
union all select 	'2023-11-27'	,	10.704857	,	5.70469898
union all select 	'2023-11-28'	,	10.257068	,	5.77639088
union all select 	'2023-11-29'	,	10.483674	,	5.93504257
union all select 	'2023-11-30'	,	10.752272	,	6.04070029
union all select 	'2023-12-01'	,	11.036646	,	6.09285304
union all select 	'2023-12-02'	,	11.445082	,	6.15886988
union all select 	'2023-12-03'	,	11.656811	,	6.18658619
union all select 	'2023-12-04'	,	11.281957	,	6.38676769
union all select 	'2023-12-05'	,	10.826454	,	6.39470252
union all select 	'2023-12-06'	,	11.113902	,	6.37163657
union all select 	'2023-12-07'	,	11.253595	,	6.46708343
union all select 	'2023-12-08'	,	11.481912	,	6.57854221
union all select 	'2023-12-09'	,	11.785281	,	6.7170259
union all select 	'2023-12-10'	,	11.994342	,	6.71854288
union all select 	'2023-12-11'	,	11.304954	,	6.64992443
union all select 	'2023-12-12'	,	11.10178	,	6.51386155
union all select 	'2023-12-13'	,	11.210878	,	6.5689296
union all select 	'2023-12-14'	,	11.079653	,	6.72261404
union all select 	'2023-12-15'	,	11.247969	,	6.80619704
union all select 	'2023-12-16'	,	11.8697	,	6.7838974
union all select 	'2023-12-17'	,	12.150942	,	6.58253207
union all select 	'2023-12-18'	,	11.457755	,	6.49862754
union all select 	'2023-12-19'	,	11.439601	,	6.51519817
union all select 	'2023-12-20'	,	11.488553	,	6.80140727
union all select 	'2023-12-21'	,	11.511898	,	7.02736985
union all select 	'2023-12-22'	,	11.269458	,	6.77435087
union all select 	'2023-12-23'	,	11.667715	,	6.690633
union all select 	'2023-12-24'	,	12.176143	,	6.4199535
union all select 	'2023-12-25'	,	12.185655	,	6.63312469
union all select 	'2023-12-26'	,	12.185974	,	6.89685004
union all select 	'2023-12-27'	,	11.798592	,	7.14043776
union all select 	'2023-12-28'	,	11.880111	,	7.24106764
union all select 	'2023-12-29'	,	11.375627	,	7.19102346
union all select 	'2023-12-30'	,	11.427334	,	7.14543568
union all select 	'2023-12-31'	,	10.623773	,	6.88440509
) t;

This enables me to show the following reports. Don't know what exactly to do more with it yet, but they're nice graphs :

Prijsplafond Gas

prijsplafond Gas.JPG (101.88 KiB) Viewed 1614 times

Prijsplafond Electra

prijsplafond Electra.JPG (113.45 KiB) Viewed 1614 times

[gizmocuz]

Why don't you use the same JSON calls as the graphs use, or use the counter values from the report?

Please show me how. I know how to do queries, not how to use those json calls.

But also: the counter values from the report are not for a specific date range as far as I know. This query takes the exact counter values for the specified date range: in my case January 1st (the date the prijsplafond started) until January 27th (the date my contract ended). Using these counter values I get the exact values that were reported from my meter to the energy company and those values should match. The only discrepancy I could think of is that the time could be different when the energy company read my meter and when Domoticz read it.

Well you open the browsers developers console, go to the network tab, and you display a graph/report in domoticz.
Now you see the correct json calls

And yes, the report has the exact counter values

[rrozema]

Why don't you use the same JSON calls as the graphs use, or use the counter values from the report?

Please show me how. I know how to do queries, not how to use those json calls.

But also: the counter values from the report are not for a specific date range as far as I know. This query takes the exact counter values for the specified date range: in my case January 1st (the date the prijsplafond started) until January 27th (the date my contract ended). Using these counter values I get the exact values that were reported from my meter to the energy company and those values should match. The only discrepancy I could think of is that the time could be different when the energy company read my meter and when Domoticz read it.

Well you open the browsers developers console, go to the network tab, and you display a graph/report in domoticz.
Now you see the correct json calls

And yes, the report has the exact counter values

Did you try running the query?

Code: Select all

Date_start|Verbruik Daltarief Meterstand start|Verbruik Normaaltarief Meterstand start|Levering Daltarief Meterstand start|Levering Normaaltarief Meterstand start|Date_end|Verbruik Daltarief Meterstand eind|Verbruik Normaaltarief Meterstand eind|Levering Daltarief Meterstand eind|Levering Normaaltarief Meterstand eind|verbruik Daltarief|levering Daltarief|verbruik Normaaltarief|levering Normaaltarief|Netto verbruik Normaaltarief|Netto verbruik Daltarief
2022-12-31|34351936|31394968|2141251|4840995|2023-01-27|34525584|31582738|2142871|4849353|173.648|1.62|187.77|8.358|179.412|172.028

I included a lot more columns, mainly used to verify the outcome, but in the end what I needed are only the last 2 columns: my net electric usage for 'dal tarief' and for 'normaal tarief' over the specified period.

I think it's a lot easier to get those results from my query than to have to wade through those json calls and figure out how to call them for my purpose. Plus I still do not see how exactly to get the results using those calls for a specific range not spanning exactly a week or a month. Or do you mean to manually sum them from the numbers shown in the report page?

[rrozema]

I added the values for the prijsplafond itself into the queries, using the tables I imported earlier into deviceRowID 1984 (eletric) and 1983 (Gas):

For electric the query is:

Code: Select all

select t2.date_start, t2.Counter1_start as [Verbruik Daltarief Meterstand start], t2.Counter3_start as [Verbruik Normaaltarief Meterstand start], t2.Counter2_start as [Levering Daltarief Meterstand start], t2.Counter4_start as [Levering Normaaltarief Meterstand start], t2.date_end, t2.Counter1_end as [Verbruik Daltarief Meterstand eind], t2.Counter3_end as [Verbruik Normaaltarief Meterstand eind], t2.Counter2_end as [Levering Daltarief Meterstand eind], t2.Counter4_end as [Levering Normaaltarief Meterstand eind], (t2.Counter1_end - t2.Counter1_start)/1000.0 as [verbruik Daltarief], (t2.Counter2_end - t2.Counter2_start)/1000.0 as [levering Daltarief],(t2.Counter3_end - t2.Counter3_start)/1000.0 as [verbruik Normaaltarief], (t2.Counter4_end - t2.Counter4_start)/1000.0 as [levering Normaaltarief], ((t2.Counter3_end - t2.Counter3_start) - (t2.Counter4_end - t2.Counter4_start))/1000.0 as [Netto verbruik Normaaltarief], ((t2.Counter1_end - t2.Counter1_start) - (t2.Counter2_end - t2.Counter2_start))/1000.0 as [Netto verbruik Daltarief], (select sum(Value) from Meter_calendar pp where pp.Date between t2.date_start and t2.date_end and pp.devicerowid = 1984)/1000.0 as prijsplafond from ( select max(case when t.nr = 1 then t.Date end) as Date_start, max(case when t.nr = 2 then t.Date end) as Date_end, max(case when t.nr = 1 then t.Counter1 end) as Counter1_start, max(case when t.nr = 2 then t.Counter1 end) as Counter1_end, max(case when t.nr = 1 then t.Counter2 end) as Counter2_start, max(case when t.nr = 2 then t.Counter2 end) as Counter2_end, max(case when t.nr = 1 then t.Counter3 end) as Counter3_start, max(case when t.nr = 2 then t.Counter3 end) as Counter3_end, max(case when t.nr = 1 then t.Counter4 end) as Counter4_start, max(case when t.nr = 2 then t.Counter4 end) as Counter4_end from ( select row_number() over (order by mmc.date) as nr, mmc.* from MultiMeter_calendar mmc where mmc.devicerowid = 3 and mmc.date in ( '2022-12-31', '2023-01-27') )t ) t2;

Results in:

Code: Select all

Date_start|Verbruik Daltarief Meterstand start|Verbruik Normaaltarief Meterstand start|Levering Daltarief Meterstand start|Levering Normaaltarief Meterstand start|Date_end|Verbruik Daltarief Meterstand eind|Verbruik Normaaltarief Meterstand eind|Levering Daltarief Meterstand eind|Levering Normaaltarief Meterstand eind|verbruik Daltarief|levering Daltarief|verbruik Normaaltarief|levering Normaaltarief|Netto verbruik Normaaltarief|Netto verbruik Daltarief|prijsplafond
2022-12-31|34351936|31394968|2141251|4840995|2023-01-27|34525584|31582738|2142871|4849353|173.648|1.62|187.77|8.358|179.412|172.028|295.395943

i.e. I need to pay extra for the KWh I used this period: 179.412 + 172.028 = 351.440, which is 56.04 KWh over the prijsplafond of 295.395943. It is unclear to me how exactly the prijsplafond is applied to the dal and normaal tarief values, so I left those separate in the query.


And for Gas used:

Code: Select all

select t2.date_start, t2.Counter_start as [Verbruik Meterstand start], t2.date_end, t2.Counter_end as [Verbruik Meterstand eind], (t2.Counter_end - t2.Counter_start)/1000.0 as [verbruik], (select sum(Value) from Meter_calendar pp where pp.Date between t2.date_start and t2.date_end and pp.devicerowid = 1983)/1000.0 as prijsplafond from ( select max(case when t.nr = 1 then t.Date end) as Date_start, max(case when t.nr = 2 then t.Date end) as Date_end, max(case when t.nr = 1 then t.Counter end) as Counter_start, max(case when t.nr = 2 then t.Counter end) as Counter_end from ( select row_number() over (order by date) as nr, * from Meter_calendar where devicerowid = 6 and date in ( '2022-12-31', '2023-01-27') )t) t2;

For Gas I'm well under the prijsplafond in the period up until January 27th:

Code: Select all

Date_start|Verbruik Meterstand start|Date_end|Verbruik Meterstand eind|verbruik|prijsplafond
2022-12-31|5856651|2023-01-27|5962922|106.271|192.24649653

[hreintke]

I used the python script below to add the "prijsplafond gas" data as the actual usage of gas in a year before I started measuring (for me 2017)

Now I can see in the compare graph whether I use more or less than the prijsplafond.

Code: Select all

import csv
import datetime
import sqlite3

domdb = sqlite3.connect("Domoticz.db")
curdb = domdb.cursor()

currentdate = datetime.date(2017,1,1)
currentDeviceID = 4

gasPlafond = 120000

res = curdb.execute(f"SELECT Counter, Value from Meter_Calendar where (date = (SELECT Min(Date) from Meter_Calendar WHERE DeviceRowID = {currentDeviceID}) AND DeviceRowID = {currentDeviceID})")

for row in res:
    gasFirstCount = row[0]
    gasFirstValue = row[1]

print (f"gasFirstCount {gasFirstCount} gasFirstValue {gasFirstValue}")
currentsum = gasFirstCount - gasPlafond - gasFirstValue


with open('plafond.csv') as csv_file:
    csv_reader = csv.reader(csv_file, delimiter=';')
    line_count = 0
    for row in csv_reader:
        if line_count == 0:
            print(f'Column names are {", ".join(row)}')
            line_count += 1
        else:
            currentvalue = round(float(row[2])*1000)
            currentsum = currentsum + currentvalue
            curdb.execute(f"INSERT INTO Meter_Calendar VALUES ({currentDeviceID},{currentvalue},{currentsum},\'{currentdate}\')")
            currentdate = currentdate + datetime.timedelta(days=1)

            line_count += 1
    print(f'Processed {line_count} lines.')
    domdb.commit()
domdb.close()

[rrozema]

Cool,
I have some questions if you please?

- how do you execute the python script? Do you do that from within Domoticz or do you use some external program?
- i am not familiar with python, what exactly is the result of your script?

[hreintke]

The proces is completely outside domoticz, you need a python interpreter to run the script.

I am running domoticz on a raspberry, my dev machine is windows

I did it with the following steps

- Backup the database -> I have a Domoticz.db on my dev machine
- In the prijsplafond.csv, replace all "," by "." (I used notepad++ for that)
- in the top of the script :
set the correct domoticz.db path & name
set the correct prijsplafond.csv path & name
set the correct deviceID
set the year which you want to use for the data

- Run the script in a python interpreter
- Restore the database -> domoticz uses the updated db

Result is that in the "Compare Gas" graph, there is an additional year of data (the one you configured in the previous step)
The "usage" in that year (per day) is the value of the prijsplafond

[rrozema]

The proces is completely outside domoticz, you need a python interpreter to run the script.

I am running domoticz on a raspberry, my dev machine is windows

I did it with the following steps

- Backup the database -> I have a Domoticz.db on my dev machine
- In the prijsplafond.csv, replace all "," by "." (I used notepad++ for that)
- in the top of the script :
set the correct domoticz.db path & name
set the correct prijsplafond.csv path & name
set the correct deviceID
set the year which you want to use for the data

- Run the script in a python interpreter
- Restore the database -> domoticz uses the updated db

Result is that in the "Compare Gas" graph, there is an additional year of data (the one you configured in the previous step)
The "usage" in that year (per day) is the value of the prijsplafond

Ok, so the only thing you did different than what i did is to load the prijsplafond data into the actual gas device, replacing the 2023 date in it with an earlier year? Isn't running my query a lot simpler? I did it in my RPI, using the sqlite3 command. Similar like you, i could have edited the query's date value and use the gas devices I'd. All without having to write and execute a python script. This shows there are always different ways to skin a cat!