select Timeofday as 'Time of day', sum(KWatts) as 'KWatts'
From (
select convert(datetime,datename(DAY,DATEADD([second], timecollected, '1/1/1970')) + ' ' + datename(MONTH,DATEADD([second], timecollected, '1/1/1970')) + ' ' + datename(YEAR,DATEADD([second], timecollected, '1/1/1970')) + ' ' + datename(HOUR,DATEADD([second], timecollected, '1/1/1970')) + ':00:00') as 'Timeofday', device as 'Device',
convert(DECIMAL(15,2), (avg(snmpdata.avgval)/1000)) as 'KWatts', count(avgval) as 'Samples' from snmpdata
left outer join snmpmonitors on snmpmonitors.id = snmpdata.id 
left outer join devices on devices.ipaddress = snmpmonitors.device
left outer join devicegroups on devicegroups.id = devices.devicegroup
where timecollected between <VIEWSQLFROMTIME> and <VIEWSQLTOTIME>
and (snmpmonitors.OID='/redfish/v1/PowerEquipment/RackPDUs/1/Sensors/PDUPower' and snmpmonitors.WQL = 'Reading')
<DEVICEGROUP>
group by convert(datetime,datename(DAY,DATEADD([second], timecollected, '1/1/1970')) + ' ' + datename(MONTH,DATEADD([second], timecollected, '1/1/1970')) + ' ' + datename(YEAR,DATEADD([second], timecollected, '1/1/1970')) + ' ' + datename(HOUR,DATEADD([second], timecollected, '1/1/1970')) + ':00:00'), device) as abc group by Timeofday order by Timeofday asc	


"select distinct CAST(Timeofday as DATE) as 'Day', SUM(SumKWatt) as 'KWatt'
                           from(
                            select Timeofday as 'Timeofday', sum(KWatts) as 'SumKWatt'
                            From(
                            select convert(datetime, datename(DAY, DATEADD([second], timecollected, '1/1/1970')) + ' ' + datename(MONTH, DATEADD([second], timecollected, '1/1/1970')) + ' ' + datename(YEAR, DATEADD([second], timecollected, '1/1/1970')) + ' ' + datename(HOUR, DATEADD([second], timecollected, '1/1/1970')) + ':00:00') as 'Timeofday', device as 'Device',
                            convert(DECIMAL(15, 2), (avg(snmpdata.avgval) / 1000)) as 'KWatts', count(avgval) as 'Samples' from snmpdata
                            left outer join snmpmonitors on snmpmonitors.id = snmpdata.id
                            left outer join devices on devices.ipaddress = snmpmonitors.device
                            left outer join devicegroups on devicegroups.id = devices.devicegroup
                            where timecollected between <VIEWSQLFROMTIME> and <VIEWSQLTOTIME>                            
                            and (snmpmonitors.OID='/redfish/v1/PowerEquipment/RackPDUs/1/Sensors/PDUPower' and snmpmonitors.WQL = 'Reading') 
                            <DEVICEGROUP>
                            group by convert(datetime, datename(DAY, DATEADD([second], timecollected, '1/1/1970')) + ' ' + datename(MONTH, DATEADD([second], timecollected, '1/1/1970')) + ' ' + datename(YEAR, DATEADD([second], timecollected, '1/1/1970')) + ' ' + datename(HOUR, DATEADD([second], timecollected, '1/1/1970')) + ':00:00'), device) as abc group by Timeofday) as totalday group by CAST(Timeofday as DATE)";