SQLize Online / PHPize Online  /  SQLtest Online

-- CALL GraphKVAHBarDetails(79, '2022-05-12',3); truncate table pivot_tempGraphKVAHBarDetails; -- select * from pivot_tempGraphKVAHBarDetails; insert into pivot_tempGraphKVAHBarDetails (SELECT DeviceDateTime, DeviceID, DashboardName, CurrentVal FROM (select Distinct Protocols.IsGraph, TrnCommDeviceParameterValue.DeviceID,Protocols.DashboardName, TrnCommDeviceParameterValue.CurrentVal,TrnCommDeviceParameterValue.DeviceDateTime FROM TrnCommDeviceParameterValue LEFT JOIN DeviceProtocolsValuses on DeviceProtocolsValuses.DeviceProtocolValID=TrnCommDeviceParameterValue.DeviceProtocolValID LEFT JOIN Devices ON Devices.DeviceID=TrnCommDeviceParameterValue.DeviceID LEFT JOIN Protocols on Protocols.ProtocolID=TrnCommDeviceParameterValue.ProtocolID where TrnCommDeviceParameterValue.CurrentVal IS NOT NULL AND TrnCommDeviceParameterValue.IsArchieve=0 AND TrnCommDeviceParameterValue.CurrentVal not like 0.00 AND Protocols.IsGraph=1 and DAY(Cast(TrnCommDeviceParameterValue.DeviceDateTime as DATETIME)) = DAY(Cast("2022-05-12 " as DATETIME) ) and month(Cast(TrnCommDeviceParameterValue.DeviceDateTime as DATETIME)) = month(Cast("2022-05-12 " as DATETIME) ) and year(Cast(TrnCommDeviceParameterValue.DeviceDateTime as DATETIME)) = year(Cast("2022-05-12 " as DATETIME) ) and TrnCommDeviceParameterValue.deviceID=79 and DashboardName in ('TotalActiveEnergy','TotalReactiveEnergy') ) x ); -- ELSEIF (GraphType =2) -- THEN (SELECT DeviceDateTime,DeviceID,DashboardName,CurrentVal FROM (select Distinct TrnCommDeviceParameterValue.DeviceID,Protocols.DashboardName,TrnCommDeviceParameterValue.CurrentVal, TrnCommDeviceParameterValue.DeviceDateTime FROM TrnCommDeviceParameterValue LEFT JOIN DeviceProtocolsValuses on DeviceProtocolsValuses.DeviceProtocolValID=TrnCommDeviceParameterValue.DeviceProtocolValID LEFT JOIN Devices ON Devices.DeviceID=TrnCommDeviceParameterValue.DeviceID LEFT JOIN Protocols on Protocols.ProtocolID=TrnCommDeviceParameterValue.ProtocolID where TrnCommDeviceParameterValue.CurrentVal Is not null AND TrnCommDeviceParameterValue.IsArchieve=0 AND TrnCommDeviceParameterValue.CurrentVal not like 0.00 AND Protocols.IsGraph=1 and DAY(Cast(TrnCommDeviceParameterValue.DeviceDateTime as DATETIME)) = DAY(Cast("2022-05-12 " as DATETIME) ) -- and month(Cast(TrnCommDeviceParameterValue.DeviceDateTime as DATETIME)) = month(Cast("2022-05-11 " as DATETIME) ) AND Year(Cast(TrnCommDeviceParameterValue.DeviceDateTime as DATETIME)) = Year(Cast("2022-05-12 " as DATETIME) ) and TrnCommDeviceParameterValue.deviceID=79 and DashboardName in ('TotalActiveEnergy','TotalReactiveEnergy') ) x ); (SELECT DeviceDateTime,DeviceID,DashboardName,CurrentVal from (select Distinct TrnCommDeviceParameterValue.DeviceID,Protocols.DashboardName,TrnCommDeviceParameterValue.CurrentVal, TrnCommDeviceParameterValue.DeviceDateTime FROM TrnCommDeviceParameterValue LEFT JOIN DeviceProtocolsValuses on DeviceProtocolsValuses.DeviceProtocolValID=TrnCommDeviceParameterValue.DeviceProtocolValID LEFT JOIN Devices ON Devices.DeviceID=TrnCommDeviceParameterValue.DeviceID LEFT JOIN Protocols on Protocols.ProtocolID=TrnCommDeviceParameterValue.ProtocolID where TrnCommDeviceParameterValue.CurrentVal Is not null AND TrnCommDeviceParameterValue.IsArchieve=0 AND TrnCommDeviceParameterValue.CurrentVal not like 0.00 AND Protocols.IsGraph=1 AND Month(Cast(TrnCommDeviceParameterValue.DeviceDateTime as DATETIME))= Month(Cast("2022-05-05" as DATETIME) ) AND YEAR(Cast(TrnCommDeviceParameterValue.DeviceDateTime as DATETIME)) = YEAR(Cast("2022-05-05" as DATETIME) ) and TrnCommDeviceParameterValue.deviceID=79 and DashboardName in ('TotalActiveEnergy','TotalReactiveEnergy') ) x ); select * from pivot_tempGraphKVAHBarDetails; SET @sql = NULL; SELECT GROUP_CONCAT(DISTINCT CONCAT( 'SUM(case when field_key = ''', field_key, ''' then field_value end) ', field_key ) ) INTO @sql FROM pivot_tempGraphKVAHBarDetails where field_key in ('TotalActiveEnergy','TotalReactiveEnergy'); -- select @sql; Select DeviceDateTime,DeviceID, SUM(case when field_key = 'TotalActiveEnergy' then field_value end) TotalActiveEnergy, SUM(case when field_key = 'TotalReactiveEnergy' then field_value end) TotalReactiveEnergy FROM pivot_tempGraphKVAHBarDetails GROUP BY DeviceDateTime,DeviceID; /*SET @sql = CONCAT('SELECT DeviceDateTime,DeviceID ', @sql, ' FROM pivot_tempGraphKVAHBarDetails GROUP BY DeviceDateTime,DeviceID ');*/ -- select @sql; PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;