SQLite Forum

Help with a query
Login

Help with a query

(1) By anonymous on 2021-07-14 09:11:47 [link] [source]

Hi, I am ok with the simplest of queries but I am no expert with creating complex one, I am guessing
that the type of query I want is an easy one for those in the know. 

I am after a query that would get the sum(column value) from a table that records intervals based 
on subset (interval) of a number between 1 to 48 

i.e select aDate, sum(column) as v, sum(v / 2) as c 
	where interval between 15 and 22 order by aDate; 

the result I am after is something like 

aDate			 v		 c
---------------------------------------------
2020/01/01  	        234   	        117  
2020/01/02		187		93.5
2020/01/03		340		170

I have tried a couple of things but have only been able to get a single row for one day with the answer I am after

I do not have enough experience to work out what to do to get the result, any help would be appreciated, I will learn from the contributions.

Hope I have given enough info.

Cheers

(2) By anonymous on 2021-07-14 09:47:56 in reply to 1 [link] [source]

Group by aDate

List all fields which are no aggregates

(3) By mzm2021 on 2021-07-14 10:10:57 in reply to 1 [link] [source]

Giving more details about the input table will help us help you.

But your current description is very vague:

I am after a query that would get the sum(column value) from a table that records intervals based on subset (interval) of a number between 1 to 48

i.e select aDate, sum(column) as v, sum(v / 2) as c where interval between 15 and 22 order by aDate;

... and that explains also why you're probably not finding any answer: vague human words are not the best way to state your problem and translate it into SQL.

(4) By anonymous on 2021-07-14 10:33:00 in reply to 3 [link] [source]

As I said, I am not an expert so, vague human words is all I have but thank you for your input, I am a quick learner once pointed in the right direction.

I am assuming (sometimes a bad thing to do) that this is the type of table details you infer.

CREATE TABLE IF NOT EXISTS [Readings] (
  [id] INTEGER PRIMARY KEY, 
  [pollDate] datetime, 
  [pollTime] datetime, 
  [interval] INT, 
  [import] REAL DEFAULT 0, 
  [dateCreated] DATETIME, time_stamp VARCHAR);

If not, could you elaborate further!

One of the reasons why I cannot work out the answer is that I do not have the type of knowledge needed to describe the lack of knowledge I have to make it non vague.

Cheers

(9) By Ryan Smith (cuz) on 2021-07-14 14:15:35 in reply to 4 [link] [source]

As I said, I am not an expert so, vague human words is all I have but thank you for your input, I am a quick learner once pointed in the right direction.

Not a problem and quite understandable.

What will go a really long way is saying why you want half the sum? Do you not want the average? Half the sum is constantly proportional to the sum, it infers no value in and of itself.

Another good step is to show the table as in show some set of values, maybe make up your own table with 10 rows or so (or more if needed to make the point clear).... Then show from that table, what would be the desired output a correct query should produce.

10 brownie points if you can state the table and rows in actual SQL so any of us can copy paste it to our favourite SQLite/SQL editor and make the queries without having to translate all your human vagueness into data first. If it's easy for us to do, likely a lot more people will assist.

(11) By anonymous on 2021-07-14 20:38:58 in reply to 9 [link] [source]

Thank you Ryan, in the light of a new day, the sql from mzm2021 is something that I have done on more than one occasion but, I could only the path I had because I did not know through lack of knowledge where the thinking cap needed to be.

mzm has pointed me in the right direction as have you with this reply.

The division by 2 (applied to importCost) @ intervals peak times between 7am and 11pm (14 - 22) is roughly the same as the importCost @ off peak times.

What I am trying to do is compare the reduction in cost of the total 10 hour period of peak import power due to government change in regulation of peak and off peak times here in Australia.

We have gone from 16 hours of peak @ x rate to 6 hours of off peak a a very marginal increase in off peak price then consider a % discount of bill if paid on time

What I want to do with the final query is calculate all of this by a single day, week, month and year.

I a have made a change to mzm example highlighting what I need to do, will reply to mzm with other details. 

 
 
as for the table DDL

CREATE TABLE IF NOT EXISTS [Readings] (
  [id] INTEGER PRIMARY KEY, 
  [meterId] INT, 
  [pollDate] datetime, 
  [pollTime] datetime, 
  [interval] INT, 
  [importKwh] INT DEFAULT 0, 
  [importCost] FLOAT(0, 4) DEFAULT 0, 
  [importKwhRate] FLOAT(0, 4) DEFAULT 0, 
  [periodType] INT DEFAULT (-1), 
  [line1Part] INT DEFAULT 0, 
  [line2Part] INT DEFAULT 0, 
  [line3Part] INT DEFAULT 0, 
  [reversePart] INT DEFAULT 0, 
  [reverseIncome] FLOAT(0, 4) DEFAULT 0, 
  [reverseKwhRate] FLOAT(0, 4) DEFAULT 0, 
  [kWhsGenerated] INT DEFAULT 0, 
  [exportIntervalRate] FLOAT(0, 4) DEFAULT 0, 
  [assumedConsumptionBenefit] FLOAT(0, 4) DEFAULT 0, 
  [pfactor] VARCHAR(5), 
  [p1ave] INT DEFAULT 0, 
  [p2ave] INT DEFAULT 0, 
  [p3ave] int DEFAULT 0, 
  [toGrid] int DEFAULT 0, 
  [selfConsumed] int DEFAULT 0, 
  [dateCreated] DATETIME, time_stamp VARCHAR);

CREATE TRIGGER IF NOT EXISTS [meterReadingsdateCreated]
AFTER INSERT
ON [Readings]
BEGIN
      UPDATE readings SET dateCreated =datetime('now', 'localtime') WHERE id = new.id;
     END;

(13) By anonymous on 2021-07-14 21:04:00 in reply to 11 [link] [source]

Forgot to add some data

rowid	id	meterId	pollDate	pollTime	interval	importKwh	importCost	importKwhRate	periodType	line1Part	line2Part	line3Part	reversePart	reverseIncome	reverseKwhRate	kWhsGenerated	exportIntervalRate	assumedConsumptionBenefit	pfactor	p1ave	p2ave	p3ave	toGrid	selfConsumed	dateCreated	time_stamp
1	1	5	1899-12-30 00:00:00.000	2014-12-29 07:30:01.000	15	6	0.01962	0.327	2	6	0	27	27	0.18414	0.682	27	0.681999981403351	0.07521	L096	109	0	279	4	23	2014-12-29 08:28:52.000	201412290828
2	2	5	1899-12-30 00:00:00.000	2014-12-29 08:00:00.000	16	18	0.03204	0.178	1	18	0	41	41	0.27962	0.682	41	0.178	0.06052	L080	247	0	409	7	34	2014-12-29 08:58:51.000	201412290858
3	3	5	1899-12-30 00:00:00.000	2014-12-29 08:30:00.000	17	1	0.00178	0.178	1	1	0	64	64	0.43648	0.682	64	0.178	0.04806	C099	375	0	633	37	27	2014-12-29 09:28:50.000	201412290928
4	4	5	1899-12-30 00:00:00.000	2014-12-29 09:00:00.000	18	-1	-0.00178	0.178	1	-1	0	56	56	0.38192	0.682	56	0.178	0.03382	C097	366	0	562	37	19	2014-12-29 09:58:50.000	201412290958
5	5	5	1899-12-30 00:00:00.000	2014-12-29 09:30:00.000	19	1	0.00178	0.178	1	1	0	84	84	0.57288	0.682	84	0.178	0.04806	C099	574	0	840	57	27	2014-12-29 10:28:50.000	201412291028
6	6	5	1899-12-30 00:00:00.000	2014-12-29 10:00:00.000	20	3	0.00534	0.178	1	3	0	67	67	0.45694	0.682	67	0.178	0.08544	L024	217	0	662	19	48	2014-12-29 10:58:51.000	201412291058
7	7	5	1899-12-30 00:00:00.000	2014-12-29 10:30:00.000	21	2	0.00356	0.178	1	2	0	46	46	0.31372	0.682	46	0.178	0.05518	C090	166	0	461	15	31	2014-12-29 11:28:50.000	201412291128
8	8	5	1899-12-30 00:00:00.000	2014-12-29 11:00:00.000	22	1	0.00178	0.178	1	1	0	42	42	0.28644	0.682	42	0.178	0.03916	C098	205	0	420	20	22	2014-12-29 11:58:51.000	201412291158
9	9	5	1899-12-30 00:00:00.000	2014-12-29 11:30:00.000	23	4	0.00712	0.178	1	4	0	29	29	0.19778	0.682	29	0.178	0.03026	C091	169	0	287	12	17	2014-12-29 12:28:51.000	201412291228
10	10	5	1899-12-30 00:00:00.000	2014-12-29 12:00:00.000	24	0	0	0.178	1	0	0	80	80	0.5456	0.682	80	0.178	0.04272	L082	557	0	802	56	24	2014-12-29 12:58:51.000	201412291258
11	11	5	1899-12-30 00:00:00.000	2014-12-29 12:30:00.000	25	26	0.04628	0.178	1	26	0	20	20	0.1364	0.682	20	0.178	0.03382	C092	266	0	201	1	19	2014-12-29 13:28:50.000	201412291328
12	12	5	1899-12-30 00:00:00.000	2014-12-29 13:00:00.000	26	4	0.00712	0.178	1	4	0	28	28	0.19096	0.682	28	0.178	0.0267	C074	174	0	279	13	15	2014-12-29 13:58:50.000	201412291358
13	13	5	1899-12-30 00:00:00.000	2014-12-29 13:30:00.000	27	12	0.02136	0.178	1	12	0	10	10	0.0682	0.682	10	0.178	0.0178	L099	118	0	101	0	10	2014-12-29 14:28:51.000	201412291428
14	14	5	1899-12-30 00:00:00.000	2014-12-29 14:00:00.000	28	40	0.0712	0.178	1	40	0	18	18	0.12276	0.682	18	0.178	0.03026	C072	412	0	179	1	17	2014-12-29 14:58:50.000	201412291458
15	15	5	1899-12-30 00:00:00.000	2014-12-29 14:30:00.000	29	1	0.00178	0.178	1	1	0	117	117	0.79794	0.682	117	0.178	0.06052	 100	838	0	1164	83	34	2014-12-29 15:28:50.000	201412291528

(16) By Harald Hanche-Olsen (hanche) on 2021-07-15 10:46:21 in reply to 13 [link] [source]

Quick tip: Assuming you're doing this in the SQLite shell, if you run .mode insert, then subsequent SELECTs will produce INSERT statements that others can use to recreate the data. That will make it much easier for them to experiment.

(17) By anonymous on 2021-07-15 11:33:30 in reply to 16 [link] [source]

Thanks for that Harald, I don't / have not use(d) the shell, but can do it in SQLiteExpert.

This is a 48 interval set hope it's ok and not too long, you will note pollDate is shown as, for example, 1899-12-30 this is casued by type mapping comming from sqliteexpert export to sql, the date does show correctly though.

the date part of pollDate is is the same as the pollTime date part only diff is time is also included in pollTime, so should be easy to update that column with the date part of pollTime.

Cheers

DROP TABLE IF EXISTS [readings];
CREATE TABLE [readings]([meterId] INT, [pollDate] datetime, [pollTime] datetime, [interval] INT, [importKwh] INT, [importCost] FLOAT(0, 4), [importKwhRate] FLOAT(0, 4), [periodType] INT, [line1Part] INT, [line2Part] INT, [line3Part] INT, [reversePart] INT, [reverseIncome] FLOAT(0, 4), [reverseKwhRate] FLOAT(0, 4), [kWhsGenerated] INT, [exportIntervalRate] FLOAT(0, 4), [assumedConsumptionBenefit] FLOAT(0, 4), [pfactor] VARCHAR(5), [p1ave] INT, [p2ave] INT, [p3ave] int, [toGrid] int, [selfConsumed] int, [dateCreated] DATETIME, [time_stamp] VARCHAR);

INSERT INTO [readings]([meterId], [pollDate], [pollTime], [interval], [importKwh], [importCost], [importKwhRate], [periodType], [line1Part], [line2Part], [line3Part], [reversePart], [reverseIncome], [reverseKwhRate], [kWhsGenerated], [exportIntervalRate], [assumedConsumptionBenefit], [pfactor], [p1ave], [p2ave], [p3ave], [toGrid], [selfConsumed], [dateCreated], [time_stamp])
VALUES(5, '1899-12-30 00:00:00.000', '2015-01-05 06:00:00.000', 12, 16, 0.0224, 0.14, 2, 16, 0, 6, 6, 0.04092, 0.682, 6, 0.681999981403351, 0.0084, ' 100', 160, 0, 64, 0, 6, '2015-01-05 06:58:43.000', '201501050658');

INSERT INTO [readings]([meterId], [pollDate], [pollTime], [interval], [importKwh], [importCost], [importKwhRate], [periodType], [line1Part], [line2Part], [line3Part], [reversePart], [reverseIncome], [reverseKwhRate], [kWhsGenerated], [exportIntervalRate], [assumedConsumptionBenefit], [pfactor], [p1ave], [p2ave], [p3ave], [toGrid], [selfConsumed], [dateCreated], [time_stamp])
VALUES(5, '1899-12-30 00:00:00.000', '2015-01-05 06:30:00.000', 13, 53, 0.0742, 0.14, 2, 53, 0, 10, 10, 0.0682, 0.682, 10, 0.681999981403351, 0.014, 'C077', 523, 0, 93, 0, 10, '2015-01-05 07:28:43.000', '201501050728');

INSERT INTO [readings]([meterId], [pollDate], [pollTime], [interval], [importKwh], [importCost], [importKwhRate], [periodType], [line1Part], [line2Part], [line3Part], [reversePart], [reverseIncome], [reverseKwhRate], [kWhsGenerated], [exportIntervalRate], [assumedConsumptionBenefit], [pfactor], [p1ave], [p2ave], [p3ave], [toGrid], [selfConsumed], [dateCreated], [time_stamp])
VALUES(5, '1899-12-30 00:00:00.000', '2015-01-05 07:00:00.000', 14, 11, 0.0154, 0.14, 2, 11, 0, 12, 12, 0.08184, 0.682, 12, 0.681999981403351, 0.0168, 'C011', 98, 0, 121, 0, 12, '2015-01-05 07:58:43.000', '201501050758');

INSERT INTO [readings]([meterId], [pollDate], [pollTime], [interval], [importKwh], [importCost], [importKwhRate], [periodType], [line1Part], [line2Part], [line3Part], [reversePart], [reverseIncome], [reverseKwhRate], [kWhsGenerated], [exportIntervalRate], [assumedConsumptionBenefit], [pfactor], [p1ave], [p2ave], [p3ave], [toGrid], [selfConsumed], [dateCreated], [time_stamp])
VALUES(5, '1899-12-30 00:00:00.000', '2015-01-05 07:30:00.000', 15, 23, 0.07521, 0.327, 2, 23, 0, 21, 21, 0.14322, 0.682, 21, 0.681999981403351, 0.06867, 'L088', 227, 0, 207, 0, 21, '2015-01-05 08:28:43.000', '201501050828');

INSERT INTO [readings]([meterId], [pollDate], [pollTime], [interval], [importKwh], [importCost], [importKwhRate], [periodType], [line1Part], [line2Part], [line3Part], [reversePart], [reverseIncome], [reverseKwhRate], [kWhsGenerated], [exportIntervalRate], [assumedConsumptionBenefit], [pfactor], [p1ave], [p2ave], [p3ave], [toGrid], [selfConsumed], [dateCreated], [time_stamp])
VALUES(5, '1899-12-30 00:00:00.000', '2015-01-05 08:00:00.000', 16, 27, 0.04806, 0.178, 1, 27, 0, 34, 34, 0.23188, 0.682, 34, 0.178, 0.0534, 'C094', 311, 0, 344, 4, 30, '2015-01-05 08:58:43.000', '201501050858');

INSERT INTO [readings]([meterId], [pollDate], [pollTime], [interval], [importKwh], [importCost], [importKwhRate], [periodType], [line1Part], [line2Part], [line3Part], [reversePart], [reverseIncome], [reverseKwhRate], [kWhsGenerated], [exportIntervalRate], [assumedConsumptionBenefit], [pfactor], [p1ave], [p2ave], [p3ave], [toGrid], [selfConsumed], [dateCreated], [time_stamp])
VALUES(5, '1899-12-30 00:00:00.000', '2015-01-05 08:30:00.000', 17, 2, 0.00356, 0.178, 1, 2, 0, 56, 56, 0.38192, 0.682, 56, 0.178, 0.04628, 'C098', 310, 0, 552, 30, 26, '2015-01-05 09:28:43.000', '201501050928');

INSERT INTO [readings]([meterId], [pollDate], [pollTime], [interval], [importKwh], [importCost], [importKwhRate], [periodType], [line1Part], [line2Part], [line3Part], [reversePart], [reverseIncome], [reverseKwhRate], [kWhsGenerated], [exportIntervalRate], [assumedConsumptionBenefit], [pfactor], [p1ave], [p2ave], [p3ave], [toGrid], [selfConsumed], [dateCreated], [time_stamp])
VALUES(5, '1899-12-30 00:00:00.000', '2015-01-05 09:00:00.000', 18, -1, -0.00178, 0.178, 1, -1, 0, 73, 73, 0.49786, 0.682, 73, 0.178, 0.04094, ' 100', 498, 0, 733, 50, 23, '2015-01-05 09:58:43.000', '201501050958');

INSERT INTO [readings]([meterId], [pollDate], [pollTime], [interval], [importKwh], [importCost], [importKwhRate], [periodType], [line1Part], [line2Part], [line3Part], [reversePart], [reverseIncome], [reverseKwhRate], [kWhsGenerated], [exportIntervalRate], [assumedConsumptionBenefit], [pfactor], [p1ave], [p2ave], [p3ave], [toGrid], [selfConsumed], [dateCreated], [time_stamp])
VALUES(5, '1899-12-30 00:00:00.000', '2015-01-05 09:30:01.000', 19, 0, 0, 0.178, 1, 0, 0, 81, 81, 0.55242, 0.682, 81, 0.178, 0.04272, 'C098', 567, 0, 810, 57, 24, '2015-01-05 10:28:44.000', '201501051028');

INSERT INTO [readings]([meterId], [pollDate], [pollTime], [interval], [importKwh], [importCost], [importKwhRate], [periodType], [line1Part], [line2Part], [line3Part], [reversePart], [reverseIncome], [reverseKwhRate], [kWhsGenerated], [exportIntervalRate], [assumedConsumptionBenefit], [pfactor], [p1ave], [p2ave], [p3ave], [toGrid], [selfConsumed], [dateCreated], [time_stamp])
VALUES(5, '1899-12-30 00:00:00.000', '2015-01-05 10:00:00.000', 20, 1, 0.00178, 0.178, 1, 1, 0, 100, 100, 0.682, 0.682, 100, 0.178, 0.09256, 'L099', 484, 0, 1003, 48, 52, '2015-01-05 10:58:43.000', '201501051058');

INSERT INTO [readings]([meterId], [pollDate], [pollTime], [interval], [importKwh], [importCost], [importKwhRate], [periodType], [line1Part], [line2Part], [line3Part], [reversePart], [reverseIncome], [reverseKwhRate], [kWhsGenerated], [exportIntervalRate], [assumedConsumptionBenefit], [pfactor], [p1ave], [p2ave], [p3ave], [toGrid], [selfConsumed], [dateCreated], [time_stamp])
VALUES(5, '1899-12-30 00:00:00.000', '2015-01-05 10:30:00.000', 21, 0, 0, 0.178, 1, 0, 0, 147, 147, 1.00254, 0.682, 147, 0.178, 0.05874, ' 100', 1140, 0, 1459, 114, 33, '2015-01-05 11:28:43.000', '201501051128');

INSERT INTO [readings]([meterId], [pollDate], [pollTime], [interval], [importKwh], [importCost], [importKwhRate], [periodType], [line1Part], [line2Part], [line3Part], [reversePart], [reverseIncome], [reverseKwhRate], [kWhsGenerated], [exportIntervalRate], [assumedConsumptionBenefit], [pfactor], [p1ave], [p2ave], [p3ave], [toGrid], [selfConsumed], [dateCreated], [time_stamp])
VALUES(5, '1899-12-30 00:00:00.000', '2015-01-05 11:00:00.000', 22, -1, -0.00178, 0.178, 1, -1, 0, 149, 149, 1.01618, 0.682, 149, 0.178, 0.04628, ' 100', 1220, 0, 1485, 123, 26, '2015-01-05 11:58:43.000', '201501051158');

INSERT INTO [readings]([meterId], [pollDate], [pollTime], [interval], [importKwh], [importCost], [importKwhRate], [periodType], [line1Part], [line2Part], [line3Part], [reversePart], [reverseIncome], [reverseKwhRate], [kWhsGenerated], [exportIntervalRate], [assumedConsumptionBenefit], [pfactor], [p1ave], [p2ave], [p3ave], [toGrid], [selfConsumed], [dateCreated], [time_stamp])
VALUES(5, '1899-12-30 00:00:00.000', '2015-01-05 11:30:00.000', 23, 0, 0, 0.178, 1, 0, 0, 169, 169, 1.15258, 0.682, 169, 0.178, 0.04984, ' 100', 1411, 0, 1694, 141, 28, '2015-01-05 12:28:43.000', '201501051228');

INSERT INTO [readings]([meterId], [pollDate], [pollTime], [interval], [importKwh], [importCost], [importKwhRate], [periodType], [line1Part], [line2Part], [line3Part], [reversePart], [reverseIncome], [reverseKwhRate], [kWhsGenerated], [exportIntervalRate], [assumedConsumptionBenefit], [pfactor], [p1ave], [p2ave], [p3ave], [toGrid], [selfConsumed], [dateCreated], [time_stamp])
VALUES(5, '1899-12-30 00:00:00.000', '2015-01-05 12:00:00.000', 24, 1, 0.00178, 0.178, 1, 1, 0, 163, 163, 1.11166, 0.682, 163, 0.178, 0.06052, 'L099', 1291, 0, 1621, 129, 34, '2015-01-05 12:58:43.000', '201501051258');

INSERT INTO [readings]([meterId], [pollDate], [pollTime], [interval], [importKwh], [importCost], [importKwhRate], [periodType], [line1Part], [line2Part], [line3Part], [reversePart], [reverseIncome], [reverseKwhRate], [kWhsGenerated], [exportIntervalRate], [assumedConsumptionBenefit], [pfactor], [p1ave], [p2ave], [p3ave], [toGrid], [selfConsumed], [dateCreated], [time_stamp])
VALUES(5, '1899-12-30 00:00:00.000', '2015-01-05 12:30:00.000', 25, 0, 0, 0.178, 1, 0, 0, 153, 153, 1.04346, 0.682, 153, 0.178, 0.10502, 'L099', 933, 0, 1525, 94, 59, '2015-01-05 13:28:43.000', '201501051328');

INSERT INTO [readings]([meterId], [pollDate], [pollTime], [interval], [importKwh], [importCost], [importKwhRate], [periodType], [line1Part], [line2Part], [line3Part], [reversePart], [reverseIncome], [reverseKwhRate], [kWhsGenerated], [exportIntervalRate], [assumedConsumptionBenefit], [pfactor], [p1ave], [p2ave], [p3ave], [toGrid], [selfConsumed], [dateCreated], [time_stamp])
VALUES(5, '1899-12-30 00:00:00.000', '2015-01-05 13:00:00.000', 26, 0, 0, 0.178, 1, 0, 0, 183, 183, 1.24806, 0.682, 183, 0.178, 0.05162, ' 100', 1540, 0, 1827, 154, 29, '2015-01-05 13:58:43.000', '201501051358');

INSERT INTO [readings]([meterId], [pollDate], [pollTime], [interval], [importKwh], [importCost], [importKwhRate], [periodType], [line1Part], [line2Part], [line3Part], [reversePart], [reverseIncome], [reverseKwhRate], [kWhsGenerated], [exportIntervalRate], [assumedConsumptionBenefit], [pfactor], [p1ave], [p2ave], [p3ave], [toGrid], [selfConsumed], [dateCreated], [time_stamp])
VALUES(5, '1899-12-30 00:00:00.000', '2015-01-05 13:30:00.000', 27, 0, 0, 0.178, 1, 0, 0, 192, 192, 1.30944, 0.682, 192, 0.178, 0.06586, ' 100', 1551, 0, 1918, 155, 37, '2015-01-05 14:28:43.000', '201501051428');

INSERT INTO [readings]([meterId], [pollDate], [pollTime], [interval], [importKwh], [importCost], [importKwhRate], [periodType], [line1Part], [line2Part], [line3Part], [reversePart], [reverseIncome], [reverseKwhRate], [kWhsGenerated], [exportIntervalRate], [assumedConsumptionBenefit], [pfactor], [p1ave], [p2ave], [p3ave], [toGrid], [selfConsumed], [dateCreated], [time_stamp])
VALUES(5, '1899-12-30 00:00:00.000', '2015-01-05 14:00:00.000', 28, 0, 0, 0.178, 1, 0, 0, 169, 169, 1.15258, 0.682, 169, 0.178, 0.08722, ' 100', 1201, 0, 1690, 120, 49, '2015-01-05 14:58:43.000', '201501051458');

INSERT INTO [readings]([meterId], [pollDate], [pollTime], [interval], [importKwh], [importCost], [importKwhRate], [periodType], [line1Part], [line2Part], [line3Part], [reversePart], [reverseIncome], [reverseKwhRate], [kWhsGenerated], [exportIntervalRate], [assumedConsumptionBenefit], [pfactor], [p1ave], [p2ave], [p3ave], [toGrid], [selfConsumed], [dateCreated], [time_stamp])
VALUES(5, '1899-12-30 00:00:00.000', '2015-01-05 14:30:00.000', 29, 0, 0, 0.178, 1, 0, 0, 181, 181, 1.23442, 0.682, 181, 0.178, 0.07654, ' 100', 1398, 0, 1815, 138, 43, '2015-01-05 15:28:43.000', '201501051528');

INSERT INTO [readings]([meterId], [pollDate], [pollTime], [interval], [importKwh], [importCost], [importKwhRate], [periodType], [line1Part], [line2Part], [line3Part], [reversePart], [reverseIncome], [reverseKwhRate], [kWhsGenerated], [exportIntervalRate], [assumedConsumptionBenefit], [pfactor], [p1ave], [p2ave], [p3ave], [toGrid], [selfConsumed], [dateCreated], [time_stamp])
VALUES(5, '1899-12-30 00:00:00.000', '2015-01-05 15:00:00.000', 30, 0, 0, 0.178, 1, 0, 0, 178, 178, 1.21396, 0.682, 178, 0.178, 0.06764, ' 100', 1400, 0, 1776, 140, 38, '2015-01-05 15:58:44.000', '201501051558');

INSERT INTO [readings]([meterId], [pollDate], [pollTime], [interval], [importKwh], [importCost], [importKwhRate], [periodType], [line1Part], [line2Part], [line3Part], [reversePart], [reverseIncome], [reverseKwhRate], [kWhsGenerated], [exportIntervalRate], [assumedConsumptionBenefit], [pfactor], [p1ave], [p2ave], [p3ave], [toGrid], [selfConsumed], [dateCreated], [time_stamp])
VALUES(5, '1899-12-30 00:00:00.000', '2015-01-05 15:30:00.000', 31, -1, -0.00178, 0.178, 1, -1, 0, 151, 151, 1.02982, 0.682, 151, 0.178, 0.06408, ' 100', 1145, 0, 1515, 115, 36, '2015-01-05 16:28:43.000', '201501051628');

INSERT INTO [readings]([meterId], [pollDate], [pollTime], [interval], [importKwh], [importCost], [importKwhRate], [periodType], [line1Part], [line2Part], [line3Part], [reversePart], [reverseIncome], [reverseKwhRate], [kWhsGenerated], [exportIntervalRate], [assumedConsumptionBenefit], [pfactor], [p1ave], [p2ave], [p3ave], [toGrid], [selfConsumed], [dateCreated], [time_stamp])
VALUES(5, '1899-12-30 00:00:00.000', '2015-01-05 16:00:00.000', 32, 1, 0.00178, 0.178, 1, 1, 0, 134, 134, 0.91388, 0.682, 134, 0.178, 0.06942, ' 100', 947, 0, 1331, 95, 39, '2015-01-05 16:58:43.000', '201501051658');

INSERT INTO [readings]([meterId], [pollDate], [pollTime], [interval], [importKwh], [importCost], [importKwhRate], [periodType], [line1Part], [line2Part], [line3Part], [reversePart], [reverseIncome], [reverseKwhRate], [kWhsGenerated], [exportIntervalRate], [assumedConsumptionBenefit], [pfactor], [p1ave], [p2ave], [p3ave], [toGrid], [selfConsumed], [dateCreated], [time_stamp])
VALUES(5, '1899-12-30 00:00:00.000', '2015-01-05 16:30:00.000', 33, -1, -0.00178, 0.178, 1, -1, 0, 114, 114, 0.77748, 0.682, 114, 0.178, 0.05874, ' 100', 804, 0, 1136, 81, 33, '2015-01-05 17:28:43.000', '201501051728');

INSERT INTO [readings]([meterId], [pollDate], [pollTime], [interval], [importKwh], [importCost], [importKwhRate], [periodType], [line1Part], [line2Part], [line3Part], [reversePart], [reverseIncome], [reverseKwhRate], [kWhsGenerated], [exportIntervalRate], [assumedConsumptionBenefit], [pfactor], [p1ave], [p2ave], [p3ave], [toGrid], [selfConsumed], [dateCreated], [time_stamp])
VALUES(5, '1899-12-30 00:00:00.000', '2015-01-05 17:00:00.000', 34, 1, 0.00178, 0.178, 1, 1, 0, 136, 136, 0.92752, 0.682, 136, 0.178, 0.06052, ' 100', 1022, 0, 1362, 102, 34, '2015-01-05 17:58:43.000', '201501051758');

INSERT INTO [readings]([meterId], [pollDate], [pollTime], [interval], [importKwh], [importCost], [importKwhRate], [periodType], [line1Part], [line2Part], [line3Part], [reversePart], [reverseIncome], [reverseKwhRate], [kWhsGenerated], [exportIntervalRate], [assumedConsumptionBenefit], [pfactor], [p1ave], [p2ave], [p3ave], [toGrid], [selfConsumed], [dateCreated], [time_stamp])
VALUES(5, '1899-12-30 00:00:00.000', '2015-01-05 17:30:00.000', 35, 0, 0, 0.178, 1, 0, 0, 101, 101, 0.68882, 0.682, 101, 0.178, 0.06408, 'C098', 654, 0, 1009, 65, 36, '2015-01-05 18:28:43.000', '201501051828');

INSERT INTO [readings]([meterId], [pollDate], [pollTime], [interval], [importKwh], [importCost], [importKwhRate], [periodType], [line1Part], [line2Part], [line3Part], [reversePart], [reverseIncome], [reverseKwhRate], [kWhsGenerated], [exportIntervalRate], [assumedConsumptionBenefit], [pfactor], [p1ave], [p2ave], [p3ave], [toGrid], [selfConsumed], [dateCreated], [time_stamp])
VALUES(5, '1899-12-30 00:00:00.000', '2015-01-05 18:00:00.000', 36, 3, 0.00534, 0.178, 1, 3, 0, 77, 77, 0.52514, 0.682, 77, 0.178, 0.10858, 'L081', 193, 0, 764, 16, 61, '2015-01-05 18:58:43.000', '201501051858');

INSERT INTO [readings]([meterId], [pollDate], [pollTime], [interval], [importKwh], [importCost], [importKwhRate], [periodType], [line1Part], [line2Part], [line3Part], [reversePart], [reverseIncome], [reverseKwhRate], [kWhsGenerated], [exportIntervalRate], [assumedConsumptionBenefit], [pfactor], [p1ave], [p2ave], [p3ave], [toGrid], [selfConsumed], [dateCreated], [time_stamp])
VALUES(5, '1899-12-30 00:00:00.000', '2015-01-05 18:30:00.000', 37, 1, 0.00178, 0.178, 1, 1, 0, 53, 53, 0.36146, 0.682, 53, 0.178, 0.06408, 'C094', 174, 0, 533, 17, 36, '2015-01-05 19:28:43.000', '201501051928');

INSERT INTO [readings]([meterId], [pollDate], [pollTime], [interval], [importKwh], [importCost], [importKwhRate], [periodType], [line1Part], [line2Part], [line3Part], [reversePart], [reverseIncome], [reverseKwhRate], [kWhsGenerated], [exportIntervalRate], [assumedConsumptionBenefit], [pfactor], [p1ave], [p2ave], [p3ave], [toGrid], [selfConsumed], [dateCreated], [time_stamp])
VALUES(5, '1899-12-30 00:00:00.000', '2015-01-05 19:00:00.000', 38, 9, 0.01602, 0.178, 1, 9, 0, 24, 24, 0.16368, 0.682, 24, 0.178, 0.03916, 'C066', 118, 0, 237, 2, 22, '2015-01-05 19:58:43.000', '201501051958');

INSERT INTO [readings]([meterId], [pollDate], [pollTime], [interval], [importKwh], [importCost], [importKwhRate], [periodType], [line1Part], [line2Part], [line3Part], [reversePart], [reverseIncome], [reverseKwhRate], [kWhsGenerated], [exportIntervalRate], [assumedConsumptionBenefit], [pfactor], [p1ave], [p2ave], [p3ave], [toGrid], [selfConsumed], [dateCreated], [time_stamp])
VALUES(5, '1899-12-30 00:00:00.000', '2015-01-05 19:30:00.000', 39, 56, 0.09968, 0.178, 1, 56, 0, 5, 5, 0.0341, 0.682, 5, 0.178, 0.0089, ' 100', 553, 0, 52, 0, 5, '2015-01-05 20:28:43.000', '201501052028');

INSERT INTO [readings]([meterId], [pollDate], [pollTime], [interval], [importKwh], [importCost], [importKwhRate], [periodType], [line1Part], [line2Part], [line3Part], [reversePart], [reverseIncome], [reverseKwhRate], [kWhsGenerated], [exportIntervalRate], [assumedConsumptionBenefit], [pfactor], [p1ave], [p2ave], [p3ave], [toGrid], [selfConsumed], [dateCreated], [time_stamp])
VALUES(5, '1899-12-30 00:00:00.000', '2015-01-05 20:00:00.000', 40, 19, 0.03382, 0.178, 1, 19, 0, 0, 0, 0, 0.682, 0, 0.178, 0, 'C064', 186, 0, 1, 0, 0, '2015-01-05 20:58:43.000', '201501052058');

INSERT INTO [readings]([meterId], [pollDate], [pollTime], [interval], [importKwh], [importCost], [importKwhRate], [periodType], [line1Part], [line2Part], [line3Part], [reversePart], [reverseIncome], [reverseKwhRate], [kWhsGenerated], [exportIntervalRate], [assumedConsumptionBenefit], [pfactor], [p1ave], [p2ave], [p3ave], [toGrid], [selfConsumed], [dateCreated], [time_stamp])
VALUES(5, '1899-12-30 00:00:00.000', '2015-01-05 20:30:00.000', 41, 13, 0.02314, 0.178, 1, 13, 0, 0, 0, 0, 0.682, 0, 0.178, 0, 'C092', 138, 0, 0, 0, 0, '2015-01-05 21:28:42.000', '201501052128');

INSERT INTO [readings]([meterId], [pollDate], [pollTime], [interval], [importKwh], [importCost], [importKwhRate], [periodType], [line1Part], [line2Part], [line3Part], [reversePart], [reverseIncome], [reverseKwhRate], [kWhsGenerated], [exportIntervalRate], [assumedConsumptionBenefit], [pfactor], [p1ave], [p2ave], [p3ave], [toGrid], [selfConsumed], [dateCreated], [time_stamp])
VALUES(5, '1899-12-30 00:00:00.000', '2015-01-05 21:00:00.000', 42, 12, 0.02136, 0.178, 1, 12, 0, 0, 0, 0, 0.682, 0, 0.178, 0, 'C068', 113, 0, 0, 0, 0, '2015-01-05 21:58:43.000', '201501052158');

INSERT INTO [readings]([meterId], [pollDate], [pollTime], [interval], [importKwh], [importCost], [importKwhRate], [periodType], [line1Part], [line2Part], [line3Part], [reversePart], [reverseIncome], [reverseKwhRate], [kWhsGenerated], [exportIntervalRate], [assumedConsumptionBenefit], [pfactor], [p1ave], [p2ave], [p3ave], [toGrid], [selfConsumed], [dateCreated], [time_stamp])
VALUES(5, '1899-12-30 00:00:00.000', '2015-01-05 21:30:00.000', 43, 27, 0.04806, 0.178, 1, 27, 0, 0, 0, 0, 0.682, 0, 0.178, 0, 'L099', 272, 0, 0, 0, 0, '2015-01-05 22:28:43.000', '201501052228');

INSERT INTO [readings]([meterId], [pollDate], [pollTime], [interval], [importKwh], [importCost], [importKwhRate], [periodType], [line1Part], [line2Part], [line3Part], [reversePart], [reverseIncome], [reverseKwhRate], [kWhsGenerated], [exportIntervalRate], [assumedConsumptionBenefit], [pfactor], [p1ave], [p2ave], [p3ave], [toGrid], [selfConsumed], [dateCreated], [time_stamp])
VALUES(5, '1899-12-30 00:00:00.000', '2015-01-05 22:00:00.000', 44, 36, 0.06408, 0.178, 1, 36, 0, 0, 0, 0, 0.682, 0, 0.178, 0, 'C093', 358, 0, 0, 0, 0, '2015-01-05 22:58:43.000', '201501052258');

INSERT INTO [readings]([meterId], [pollDate], [pollTime], [interval], [importKwh], [importCost], [importKwhRate], [periodType], [line1Part], [line2Part], [line3Part], [reversePart], [reverseIncome], [reverseKwhRate], [kWhsGenerated], [exportIntervalRate], [assumedConsumptionBenefit], [pfactor], [p1ave], [p2ave], [p3ave], [toGrid], [selfConsumed], [dateCreated], [time_stamp])
VALUES(5, '1899-12-30 00:00:00.000', '2015-01-05 22:30:00.000', 45, 14, 0.02492, 0.178, 1, 14, 0, 0, 0, 0, 0.682, 0, 0.178, 0, 'C067', 138, 0, 0, 0, 0, '2015-01-05 23:28:43.000', '201501052328');

INSERT INTO [readings]([meterId], [pollDate], [pollTime], [interval], [importKwh], [importCost], [importKwhRate], [periodType], [line1Part], [line2Part], [line3Part], [reversePart], [reverseIncome], [reverseKwhRate], [kWhsGenerated], [exportIntervalRate], [assumedConsumptionBenefit], [pfactor], [p1ave], [p2ave], [p3ave], [toGrid], [selfConsumed], [dateCreated], [time_stamp])
VALUES(5, '1899-12-30 00:00:00.000', '2015-01-05 23:00:00.000', 46, 12, 0.0168, 0.14, 2, 12, 0, 0, 0, 0, 0.682, 0, 0.681999981403351, 0, 'C093', 124, 0, 0, 0, 0, '2015-01-05 23:58:42.000', '201501052358');

INSERT INTO [readings]([meterId], [pollDate], [pollTime], [interval], [importKwh], [importCost], [importKwhRate], [periodType], [line1Part], [line2Part], [line3Part], [reversePart], [reverseIncome], [reverseKwhRate], [kWhsGenerated], [exportIntervalRate], [assumedConsumptionBenefit], [pfactor], [p1ave], [p2ave], [p3ave], [toGrid], [selfConsumed], [dateCreated], [time_stamp])
VALUES(5, '1899-12-30 00:00:00.000', '2015-01-05 23:30:00.000', 47, 16, 0.0224, 0.14, 2, 16, 3, 0, 0, 0, 0.682, 0, 0.681999981403351, 0, 'C070', 121, 27, 0, 0, 0, '2015-01-06 00:28:42.000', '201501060028');

INSERT INTO [readings]([meterId], [pollDate], [pollTime], [interval], [importKwh], [importCost], [importKwhRate], [periodType], [line1Part], [line2Part], [line3Part], [reversePart], [reverseIncome], [reverseKwhRate], [kWhsGenerated], [exportIntervalRate], [assumedConsumptionBenefit], [pfactor], [p1ave], [p2ave], [p3ave], [toGrid], [selfConsumed], [dateCreated], [time_stamp])
VALUES(5, '1899-12-30 00:00:00.000', '2015-01-05 23:59:00.000', 48, 197, 0.350659996271133, 0.178000003099442, 2, 197, 173, 0, 0, 0, 0.682, 0, 0.681999981403351, 0, 'L099', 248, 1723, 0, 0, 0, '2015-01-06 00:58:43.000', '201501060058');

INSERT INTO [readings]([meterId], [pollDate], [pollTime], [interval], [importKwh], [importCost], [importKwhRate], [periodType], [line1Part], [line2Part], [line3Part], [reversePart], [reverseIncome], [reverseKwhRate], [kWhsGenerated], [exportIntervalRate], [assumedConsumptionBenefit], [pfactor], [p1ave], [p2ave], [p3ave], [toGrid], [selfConsumed], [dateCreated], [time_stamp])
VALUES(5, '1899-12-30 00:00:00.000', '2015-01-06 00:30:00.000', 1, 209, 0.2926, 0.14, 2, 209, 173, 0, 0, 0, 0.682, 0, 0.681999981403351, 0, 'C068', 353, 1731, 0, 0, 0, '2015-01-06 01:28:42.000', '201501060128');

INSERT INTO [readings]([meterId], [pollDate], [pollTime], [interval], [importKwh], [importCost], [importKwhRate], [periodType], [line1Part], [line2Part], [line3Part], [reversePart], [reverseIncome], [reverseKwhRate], [kWhsGenerated], [exportIntervalRate], [assumedConsumptionBenefit], [pfactor], [p1ave], [p2ave], [p3ave], [toGrid], [selfConsumed], [dateCreated], [time_stamp])
VALUES(5, '1899-12-30 00:00:00.000', '2015-01-06 01:00:00.000', 2, 186, 0.2604, 0.14, 2, 186, 171, 0, 0, 0, 0.682, 0, 0.681999981403351, 0, 'C094', 153, 1710, 0, 0, 0, '2015-01-06 01:58:42.000', '201501060158');

INSERT INTO [readings]([meterId], [pollDate], [pollTime], [interval], [importKwh], [importCost], [importKwhRate], [periodType], [line1Part], [line2Part], [line3Part], [reversePart], [reverseIncome], [reverseKwhRate], [kWhsGenerated], [exportIntervalRate], [assumedConsumptionBenefit], [pfactor], [p1ave], [p2ave], [p3ave], [toGrid], [selfConsumed], [dateCreated], [time_stamp])
VALUES(5, '1899-12-30 00:00:00.000', '2015-01-06 01:30:00.000', 3, 137, 0.1918, 0.14, 2, 137, 105, 0, 0, 0, 0.682, 0, 0.681999981403351, 0, 'L098', 313, 1049, 0, 0, 0, '2015-01-06 02:28:42.000', '201501060228');

INSERT INTO [readings]([meterId], [pollDate], [pollTime], [interval], [importKwh], [importCost], [importKwhRate], [periodType], [line1Part], [line2Part], [line3Part], [reversePart], [reverseIncome], [reverseKwhRate], [kWhsGenerated], [exportIntervalRate], [assumedConsumptionBenefit], [pfactor], [p1ave], [p2ave], [p3ave], [toGrid], [selfConsumed], [dateCreated], [time_stamp])
VALUES(5, '1899-12-30 00:00:00.000', '2015-01-06 02:00:00.000', 4, 43, 0.0602, 0.14, 2, 43, 0, 0, 0, 0, 0.682, 0, 0.681999981403351, 0, 'C093', 431, 0, 0, 0, 0, '2015-01-06 02:58:43.000', '201501060258');

INSERT INTO [readings]([meterId], [pollDate], [pollTime], [interval], [importKwh], [importCost], [importKwhRate], [periodType], [line1Part], [line2Part], [line3Part], [reversePart], [reverseIncome], [reverseKwhRate], [kWhsGenerated], [exportIntervalRate], [assumedConsumptionBenefit], [pfactor], [p1ave], [p2ave], [p3ave], [toGrid], [selfConsumed], [dateCreated], [time_stamp])
VALUES(5, '1899-12-30 00:00:00.000', '2015-01-06 02:30:00.000', 5, 14, 0.0196, 0.14, 2, 14, 0, 0, 0, 0, 0.682, 0, 0.681999981403351, 0, 'C068', 141, 0, 0, 0, 0, '2015-01-06 03:28:43.000', '201501060328');

INSERT INTO [readings]([meterId], [pollDate], [pollTime], [interval], [importKwh], [importCost], [importKwhRate], [periodType], [line1Part], [line2Part], [line3Part], [reversePart], [reverseIncome], [reverseKwhRate], [kWhsGenerated], [exportIntervalRate], [assumedConsumptionBenefit], [pfactor], [p1ave], [p2ave], [p3ave], [toGrid], [selfConsumed], [dateCreated], [time_stamp])
VALUES(5, '1899-12-30 00:00:00.000', '2015-01-06 03:00:00.000', 6, 13, 0.0182, 0.14, 2, 13, 0, 0, 0, 0, 0.682, 0, 0.681999981403351, 0, 'C093', 126, 0, 0, 0, 0, '2015-01-06 03:58:42.000', '201501060358');

INSERT INTO [readings]([meterId], [pollDate], [pollTime], [interval], [importKwh], [importCost], [importKwhRate], [periodType], [line1Part], [line2Part], [line3Part], [reversePart], [reverseIncome], [reverseKwhRate], [kWhsGenerated], [exportIntervalRate], [assumedConsumptionBenefit], [pfactor], [p1ave], [p2ave], [p3ave], [toGrid], [selfConsumed], [dateCreated], [time_stamp])
VALUES(5, '1899-12-30 00:00:00.000', '2015-01-06 03:30:00.000', 7, 12, 0.0168, 0.14, 2, 12, 0, 0, 0, 0, 0.682, 0, 0.681999981403351, 0, 'C068', 120, 0, 0, 0, 0, '2015-01-06 04:28:42.000', '201501060428');

INSERT INTO [readings]([meterId], [pollDate], [pollTime], [interval], [importKwh], [importCost], [importKwhRate], [periodType], [line1Part], [line2Part], [line3Part], [reversePart], [reverseIncome], [reverseKwhRate], [kWhsGenerated], [exportIntervalRate], [assumedConsumptionBenefit], [pfactor], [p1ave], [p2ave], [p3ave], [toGrid], [selfConsumed], [dateCreated], [time_stamp])
VALUES(5, '1899-12-30 00:00:00.000', '2015-01-06 04:00:00.000', 8, 14, 0.0196, 0.14, 2, 14, 0, 0, 0, 0, 0.682, 0, 0.681999981403351, 0, 'C093', 142, 0, 0, 0, 0, '2015-01-06 04:58:42.000', '201501060458');

INSERT INTO [readings]([meterId], [pollDate], [pollTime], [interval], [importKwh], [importCost], [importKwhRate], [periodType], [line1Part], [line2Part], [line3Part], [reversePart], [reverseIncome], [reverseKwhRate], [kWhsGenerated], [exportIntervalRate], [assumedConsumptionBenefit], [pfactor], [p1ave], [p2ave], [p3ave], [toGrid], [selfConsumed], [dateCreated], [time_stamp])
VALUES(5, '1899-12-30 00:00:00.000', '2015-01-06 04:30:00.000', 9, 10, 0.014, 0.14, 2, 10, 0, 0, 0, 0, 0.682, 0, 0.681999981403351, 0, 'C094', 103, 0, 0, 0, 0, '2015-01-06 05:28:42.000', '201501060528');

INSERT INTO [readings]([meterId], [pollDate], [pollTime], [interval], [importKwh], [importCost], [importKwhRate], [periodType], [line1Part], [line2Part], [line3Part], [reversePart], [reverseIncome], [reverseKwhRate], [kWhsGenerated], [exportIntervalRate], [assumedConsumptionBenefit], [pfactor], [p1ave], [p2ave], [p3ave], [toGrid], [selfConsumed], [dateCreated], [time_stamp])
VALUES(5, '1899-12-30 00:00:00.000', '2015-01-06 05:00:00.000', 10, 15, 0.021, 0.14, 2, 15, 0, 0, 0, 0, 0.682, 0, 0.681999981403351, 0, 'C065', 144, 0, 0, 0, 0, '2015-01-06 05:58:43.000', '201501060558');

INSERT INTO [readings]([meterId], [pollDate], [pollTime], [interval], [importKwh], [importCost], [importKwhRate], [periodType], [line1Part], [line2Part], [line3Part], [reversePart], [reverseIncome], [reverseKwhRate], [kWhsGenerated], [exportIntervalRate], [assumedConsumptionBenefit], [pfactor], [p1ave], [p2ave], [p3ave], [toGrid], [selfConsumed], [dateCreated], [time_stamp])
VALUES(5, '1899-12-30 00:00:00.000', '2015-01-06 05:30:00.000', 11, 11, 0.0154, 0.14, 2, 11, 0, 0, 0, 0, 0.682, 0, 0.681999981403351, 0, 'C080', 107, 0, 1, 0, 0, '2015-01-06 06:28:43.000', '201501060628');

(18) By Larry Brasfield (larrybr) on 2021-07-15 12:39:16 in reply to 17 [link] [source]

Please try to limit posts. Your 29 Kbytes is more than is necessary to adopt Harald's fine suggestion. If you do not see another you submit like that appear, it will be because it was rejected in moderation.

(19) By anonymous on 2021-07-15 21:04:54 in reply to 18 [link] [source]

Apologies Larry, was not aware of a size limit even though I was conscious, a courtesy, of keeping the size of content low, it made sense to me to have at least 48 entries to play with, I am sure there will be no further need to post anything like that again.

Cheers

(21) By Larry Brasfield (larrybr) on 2021-07-16 13:18:33 in reply to 19 [link] [source]

No big (or small) deal, or apology needed. If not for a sleep deficit, I would likely have prefaced that with, "I have not taken the time to figure out if that much data is necessary to demonstrate the issues, but ..."

My request was not directed solely to you, and I should have made that explicit. So I apologize for making size of one post a bigger issue than it is.

One related aspect of this "size" issue merits attention. When a dataset includes columns and rows not pertinent to the issue raised, that makes it harder for people who may be interested in understanding the posed problem, and maybe helping with it, because of the need for scrolling and sifting through a morass of wrapped text. And it's not just harder to comprehend. (This next point is not addressed to you specifically. If the shoe fits, wear it.)

When it appears that a poster cannot be bothered to conserve readers' time by weeding out surplusage, it is harder for potentially helpful responders to want to spend their time sifting it out. To me, and I expect this is true for others, a lot of surplusage implies either obliviousness or a calculation resembling "I am not going to bother taking a minute to reduce clutter for the sake of avoiding the waste of N readers' time and attention." Either way, it can be demotivating to somebody considering whether to take their time to help.

So I would encourage use of the SELECT and its WHERE clause to trim columns that do not matter for the problem at hand and to get a small enough subset of the rows that the problem is demonstrated without imposing needless scrolling/reading time expenditure. Saving some storage is a bonus.

(14) By Tim Streater (Clothears) on 2021-07-14 21:19:20 in reply to 11 [link] [source]

A number of the types you specify don't actually exist in SQLite, see https://www.sqlite.org/datatype3.html

(15) By anonymous on 2021-07-14 21:51:09 in reply to 14 [link] [source]

Thanks Tim, I am aware of that, the table and fields were created sometime ago using SQLiteExpert's ( http://www.sqliteexpert.com/ )field type mapping list.

At that time ( a long time ago, more than 7 years ) my knowledge was less than it is today, I did not concern myself with the finer details of sqlite but now am in the process of using the amalgamation and compiling it in my developer tools.

Cheers

(5) By anonymous on 2021-07-14 10:49:44 in reply to 3 [link] [source]

Just noticed something that may have added to the vagueness of the question first asked.

sum(v / 2) should actually be sum(value / 2)

My bad...

(6) By mzm2021 on 2021-07-14 10:59:55 in reply to 5 [link] [source]

I see no column named value in your table.

Which column do you want to use for computing the sum?

So far, and if my guess machine is correctly tuned, if you're after half the sum of column import for all rows which interval is between 1 and 48, the translation into SQL is:

SELECT sum(import)/2.0
FROM Readings
WHERE interval BETWEEN 1 AND 48

(7) By anonymous on 2021-07-14 11:20:14 in reply to 6 [link] [source]

Yes, the guessing machine is working (btw, sorry, it's very late where I am so very tired) the results I am after is all values between a given range of intervals  for every distinct pollDate 
which falls between a pollDate range.

There are other complicating factors to what I am after but would rather progress to those after getting the basic query out of the way.

Cheers

(10) By mzm2021 on 2021-07-14 16:06:52 in reply to 7 [link] [source]

Below is my previous query updated to group rows by pollDate and to apply the aggregate function sum() on each of these groups:

SELECT pollDate, sum(import)/2.0
FROM Readings
WHERE interval BETWEEN 1 AND 48
GROUP BY pollDate

(12) By anonymous on 2021-07-14 20:55:24 in reply to 10 [source]

Thanks for this, as I suspected, the basic query is a simple one which I have used before.

In my response to Ryan, I have explained in more detail what I am looking for, need to go this process due to my lack of knowledge so hope you can bear with me.

I have expanded on the example you have given. table DDL is in the response to Ryan.

SELECT pollDate, sum(importKwh)/2.0 as PeakImport, (sum(importCost) * sum(importKwh) / 2.0) as PeakCost, sum(importKwh) as OffPeak,

   -- need to get this info, don't know if it can be done in the 1st SELECT 
   -- don't now if it can be done here or needs to be done somewhere else, have tried with errors

          (SELECT (sum(importedKwh) * sum(importCost)) as OffPeakCost, 
          WHERE interval BETWEEN 22 AND 24 AND BETWEEN 1 AND 14 
          FROM Readings),

   -- continue as part of the 1st SELECT       
          sum(reversePart) as solar

FROM Readings WHERE interval BETWEEN 14 AND 22 GROUP BY pollDate

After this, I will probably need to alter any resulting sql to include additional parts that need to be done based on the expected deficiency in describing further what is needed to get the outcome I need.

Thanks Ryan,

(8) By Gunter Hick (gunter_hick) on 2021-07-14 12:26:41 in reply to 1 [link] [source]

Possibly what you are looking for is "GROUP BY aDate", which would return one row for each distinct aDate and do the computation within each group of records.

Also, try "sum(column)/2 as c" if you want half of the groups' sum; the value of v is only known at the end of the group and would be hard to determine during processing of rows.

(20) By anonymous on 2021-07-16 09:12:32 in reply to 8 [link] [source]

Hey folks, appreciate your efforts, they have pointed me in the right direction and have, in the light of a new day, found my solution to my own question hidden in the bowels of my own mind.

if this can be done any better, please feel free to comment, apart from an obvious changing of importKwh to the appropriate type without the need to do a cast.

SELECT pollDate, 
       sum(CAST(importKwh AS REAL) /100) as kwh,
       sum(CAST(importKwh AS REAL) /100) *  (SELECT importKwhRate  FROM Readings WHERE periodType = 2 LIMIT  1) as peakCost, 
        (SELECT importKwhRate  FROM Readings WHERE periodType = 2 LIMIT  1) as peakRate,         
       sum(CAST(importKwh AS REAL) /100) *  (SELECT importKwhRate FROM Readings WHERE periodType = 1 LIMIT  1) as offPeakcost,
        (SELECT importKwhRate  FROM Readings WHERE periodType = 1 LIMIT  1) as offPeakRate,                
       sum(CAST(importKwh AS REAL) /100) *  (SELECT importKwhRate  FROM Readings WHERE periodType = 2 LIMIT  1) - sum(CAST(importKwh AS REAL) /100) *  
        (SELECT importKwhRate  FROM Readings WHERE periodType = 1 LIMIT  1) as Diff 
FROM Readings 

WHERE (SELECT interval FROM Readings WHERE interval >= 14 and interval <= 22)  AND pollDate >= '2014//12/29' AND pollDate <= '2015/03/31'  GROUP BY polldate;


RESULT 

pollDate     kwh    peakCost	peakRate   offPeakcost	offPeakRate	Diff
----------------------------------------------------------------------------------------------
2014/12/29   0.659  2.15493    0.327	   1.17302	0.178		0.98191
2014/12/30   1.023  3.34521    0.327	   1.82094	0.178		1.52427
2015/01/01   0.922  3.01494    0.327	   1.64116	0.178		1.37378
2015/01/02   0.283  0.92541    0.327	   0.50374	0.178		0.42167

Cheers