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;