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
(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 [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
(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.
(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.
(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
(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;
(12) By anonymous on 2021-07-14 20:55:24 in reply to 10 [link] [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,
(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
(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
(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
(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
(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.