defmod LogDBRep_Statistic_previous_Quarter DbRep LogDB
attr LogDBRep_Statistic_previous_Quarter DbLogExclude .*
attr LogDBRep_Statistic_previous_Quarter allowDeletion 0
attr LogDBRep_Statistic_previous_Quarter comment Version 2023.04.28 17:00\
\
Achtung das automatische Formatieren hat einen Fehler!
attr LogDBRep_Statistic_previous_Quarter device WR_1_API
attr LogDBRep_Statistic_previous_Quarter reading SW_Statistic_%Year,Statistic_EnergyHomeBat_Year EXCLUDE=%Autarky%,%Rate%,%NoBat%
attr LogDBRep_Statistic_previous_Quarter room System
attr LogDBRep_Statistic_previous_Quarter sqlCmdHistoryLength 1
attr LogDBRep_Statistic_previous_Quarter sqlCmdVars SET @offset:=  (   CASE WHEN MONTH(CURRENT_DATE) IN (1,4,7,10) THEN @offset:=0          WHEN MONTH(CURRENT_DATE) IN (2,5,8,11) THEN @offset:=1       ELSE @offset:=2   END  );;
attr LogDBRep_Statistic_previous_Quarter suppressReading SqlResultRow_.*
attr LogDBRep_Statistic_previous_Quarter userExitFn splitReading .*:.*
attr LogDBRep_Statistic_previous_Quarter verbose 0

setstate LogDBRep_Statistic_previous_Quarter 2024-01-12 17:49:52 sqlCmd SELECT * FROM (SELECT TIMESTAMP,IF(READING='_Year',CONCAT('Q',CAST(MONTH(TIMESTAMP)/3 AS DECIMAL(1))),CONCAT('Q',CAST(MONTH(TIMESTAMP)/3 AS DECIMAL(1)),'_',REPLACE(READING,'_Year',''))) AS READING,VALUE FROM (SELECT DATE_FORMAT(CURRENT_DATE - INTERVAL @offset MONTH,'%Y-%m-01 23:59:00') - INTERVAL 1 DAY AS TIMESTAMP,'_Year' AS READING,'previous' AS VALUE UNION ALL SELECT * FROM (SELECT DATE_FORMAT(end.TIMESTAMP,'%Y-%m-%d 23:59:00') AS TIMESTAMP,end.READING,(end.VALUE-begin.VALUE) AS VALUE FROM (SELECT h.TIMESTAMP,h.READING,cast(h.VALUE/1000 AS decimal(6)) AS VALUE FROM history h INNER JOIN (SELECT max(TIMESTAMP) AS TIMESTAMP,READING FROM history WHERE §device§ AND §reading§ AND TIMESTAMP >= DATE_FORMAT(CURRENT_DATE - INTERVAL 1+@offset MONTH,'%Y/%m/28') AND TIMESTAMP < DATE_FORMAT(CURRENT_DATE - INTERVAL @offset MONTH,'%Y/%m/01') GROUP BY READING) x1 ON h.TIMESTAMP = x1.TIMESTAMP AND h.READING = x1.READING AND h.VALUE != 0) end INNER JOIN (SELECT h.TIMESTAMP,h.READING,if(MONTH(x1.TIMESTAMP)=12,0,cast(h.VALUE/1000 AS decimal(6))) AS VALUE FROM history h INNER JOIN (SELECT max(TIMESTAMP) AS TIMESTAMP,READING FROM history WHERE §device§ AND §reading§ AND TIMESTAMP >= DATE_FORMAT(CURRENT_DATE - INTERVAL 4+@offset MONTH,'%Y/%m/28') AND TIMESTAMP < DATE_FORMAT(CURRENT_DATE - INTERVAL 3+@offset MONTH,'%Y/%m/01') GROUP BY READING) x1 ON h.TIMESTAMP = x1.TIMESTAMP AND h.READING = x1.READING AND h.VALUE != 0) begin ON end.READING = begin.READING AND MONTH(end.TIMESTAMP) IN (3,6,9,12)) QX) QA) UA UNION ALL SELECT TIMESTAMP,IF(READING='_Year',CONCAT('Q',CAST(MONTH(TIMESTAMP)/3 AS DECIMAL(1))),CONCAT('Q',CAST(MONTH(TIMESTAMP)/3 AS DECIMAL(1)),'_',REPLACE(READING,'_Year',''))) AS READING,VALUE FROM (SELECT DATE_FORMAT(CURRENT_DATE - INTERVAL 3+@offset MONTH,'%Y-%m-01 23:59:00') - INTERVAL 1 DAY AS TIMESTAMP,'_Year' AS READING,null AS VALUE UNION ALL SELECT * FROM (SELECT DATE_FORMAT(end.TIMESTAMP,'%Y-%m-%d 23:59:00') AS TIMESTAMP,end.READING,(end.VALUE-begin.VALUE) AS VALUE FROM (SELECT h.TIMESTAMP,h.READING,cast(h.VALUE/1000 AS decimal(6)) AS VALUE FROM history h INNER JOIN (SELECT max(TIMESTAMP) AS TIMESTAMP,READING FROM history WHERE §device§ AND §reading§ AND TIMESTAMP >= DATE_FORMAT(CURRENT_DATE - INTERVAL 4+@offset MONTH,'%Y/%m/28') AND TIMESTAMP < DATE_FORMAT(CURRENT_DATE - INTERVAL 3+@offset MONTH,'%Y/%m/01') GROUP BY READING) x1 ON h.TIMESTAMP = x1.TIMESTAMP AND h.READING = x1.READING AND h.VALUE != 0) end INNER JOIN (SELECT h.TIMESTAMP,h.READING,if(MONTH(x1.TIMESTAMP)=12,0,cast(h.VALUE/1000 AS decimal(6))) AS VALUE FROM history h INNER JOIN (SELECT max(TIMESTAMP) AS TIMESTAMP,READING FROM history WHERE §device§ AND §reading§ AND TIMESTAMP >= DATE_FORMAT(CURRENT_DATE - INTERVAL 7+@offset MONTH,'%Y/%m/28') AND TIMESTAMP < DATE_FORMAT(CURRENT_DATE - INTERVAL 6+@offset MONTH,'%Y/%m/01') GROUP BY READING) x1 ON h.TIMESTAMP = x1.TIMESTAMP AND h.READING = x1.READING AND h.VALUE != 0) begin ON end.READING = begin.READING AND MONTH(end.TIMESTAMP) IN (3,6,9,12)) QX) QB UNION ALL SELECT TIMESTAMP,IF(READING='_Year',CONCAT('Q',CAST(MONTH(TIMESTAMP)/3 AS DECIMAL(1))),CONCAT('Q',CAST(MONTH(TIMESTAMP)/3 AS DECIMAL(1)),'_',REPLACE(READING,'_Year',''))) AS READING,VALUE FROM (SELECT DATE_FORMAT(CURRENT_DATE - INTERVAL 6+@offset MONTH,'%Y-%m-01 23:59:00') - INTERVAL 1 DAY AS TIMESTAMP,'_Year' AS READING,null AS VALUE UNION ALL SELECT * FROM (SELECT DATE_FORMAT(end.TIMESTAMP,'%Y-%m-%d 23:59:00') AS TIMESTAMP,end.READING,(end.VALUE-begin.VALUE) AS VALUE FROM (SELECT h.TIMESTAMP,h.READING,cast(h.VALUE/1000 AS decimal(6)) AS VALUE FROM history h INNER JOIN (SELECT max(TIMESTAMP) AS TIMESTAMP,READING FROM history WHERE §device§ AND §reading§ AND TIMESTAMP >= DATE_FORMAT(CURRENT_DATE - INTERVAL 7+@offset MONTH,'%Y/%m/28') AND TIMESTAMP < DATE_FORMAT(CURRENT_DATE - INTERVAL 6+@offset MONTH,'%Y/%m/01') GROUP BY READING) x1 ON h.TIMESTAMP = x1.TIMESTAMP AND h.READING = x1.READING AND h.VALUE != 0) end INNER JOIN (SELECT h.TIMESTAMP,h.READING,if(MONTH(x1.TIMESTAMP)=12,0,cast(h.VALUE/1000 AS decimal(6))) AS VALUE FROM history h INNER JOIN (SELECT max(TIMESTAMP) AS TIMESTAMP,READING FROM history WHERE §device§ AND §reading§ AND TIMESTAMP >= DATE_FORMAT(CURRENT_DATE - INTERVAL 10+@offset MONTH,'%Y/%m/28') AND TIMESTAMP < DATE_FORMAT(CURRENT_DATE - INTERVAL 9+@offset MONTH,'%Y/%m/01') GROUP BY READING) x1 ON h.TIMESTAMP = x1.TIMESTAMP AND h.READING = x1.READING AND h.VALUE != 0) begin ON end.READING = begin.READING AND MONTH(end.TIMESTAMP) IN (3,6,9,12)) QX) QC UNION ALL SELECT TIMESTAMP,IF(READING='_Year',CONCAT('Q',CAST(MONTH(TIMESTAMP)/3 AS DECIMAL(1))),CONCAT('Q',CAST(MONTH(TIMESTAMP)/3 AS DECIMAL(1)),'_',REPLACE(READING,'_Year',''))) AS READING,VALUE FROM (SELECT DATE_FORMAT(CURRENT_DATE - INTERVAL 9+@offset MONTH,'%Y-%m-01 23:59:00') - INTERVAL 1 DAY AS TIMESTAMP,'_Year' AS READING,null AS VALUE UNION ALL SELECT * FROM (SELECT DATE_FORMAT(end.TIMESTAMP,'%Y-%m-%d 23:59:00') AS TIMESTAMP,end.READING,(end.VALUE-begin.VALUE) AS VALUE FROM (SELECT h.TIMESTAMP,h.READING,cast(h.VALUE/1000 AS decimal(6)) AS VALUE FROM history h INNER JOIN (SELECT max(TIMESTAMP) AS TIMESTAMP,READING FROM history WHERE §device§ AND §reading§ AND TIMESTAMP >= DATE_FORMAT(CURRENT_DATE - INTERVAL 10+@offset MONTH,'%Y/%m/28') AND TIMESTAMP < DATE_FORMAT(CURRENT_DATE - INTERVAL 9+@offset MONTH,'%Y/%m/01') GROUP BY READING) x1 ON h.TIMESTAMP = x1.TIMESTAMP AND h.READING = x1.READING AND h.VALUE != 0) end INNER JOIN (SELECT h.TIMESTAMP,h.READING,if(MONTH(x1.TIMESTAMP)=12,0,cast(h.VALUE/1000 AS decimal(6))) AS VALUE FROM history h INNER JOIN (SELECT max(TIMESTAMP) AS TIMESTAMP,READING FROM history WHERE §device§ AND §reading§ AND TIMESTAMP >= DATE_FORMAT(CURRENT_DATE - INTERVAL 13+@offset MONTH,'%Y/%m/28') AND TIMESTAMP < DATE_FORMAT(CURRENT_DATE - INTERVAL 12+@offset MONTH,'%Y/%m/01') GROUP BY READING) x1 ON h.TIMESTAMP = x1.TIMESTAMP AND h.READING = x1.READING AND h.VALUE != 0) begin ON end.READING = begin.READING AND MONTH(end.TIMESTAMP) IN (3,6,9,12)) QX) QD;;