I recently wrote some reports to generate a report showing how much cost is associated with running computers. It shows how much a machine costs while over a given period of time as well as showing how much cost was saved by not having it powered on 24 hours a day. Average hours per day is also a metric I included to expose high OPEX computers.
last week
SELECT M.NAME as 'Machine', FORMAT(SUM(MDU.HOURS), 0) as 'Hours On',FORMAT((COUNT(MDU.ID)*24) - SUM(MDU.HOURS),0) as 'Hours Off', FORMAT(SUM(MDU.HOURS) / COUNT(MDU.ID),0) as 'Avg Hours Per Day', CONCAT('$',FORMAT(SUM(MDU.HOURS) * .07 * .31, 2)) as 'Energy Cost Spent',
CONCAT('$', FORMAT(((COUNT(MDU.ID)*24) - SUM(MDU.HOURS)) *.07 * .31, 2)) as 'Energy Cost Saved'
FROM MACHINE M, MACHINE_DAILY_UPTIME MDU
WHERE MDU.MACHINE_ID = M.ID
AND MDU.DAY >= DATE_SUB(NOW(), INTERVAL 1 WEEK )
AND M.MANUAL_ENTRY = 0
GROUP BY M.NAME
last 2 weeks
SELECT M.NAME as 'Machine', FORMAT(SUM(MDU.HOURS), 0) as 'Hours On',FORMAT((COUNT(MDU.ID)*24) - SUM(MDU.HOURS),0) as 'Hours Off', FORMAT(SUM(MDU.HOURS) / COUNT(MDU.ID),0) as 'Avg Hours Per Day', CONCAT('$',FORMAT(SUM(MDU.HOURS) * .07 * .31, 2)) as 'Energy Cost Spent',
CONCAT('$', FORMAT(((COUNT(MDU.ID)*24) - SUM(MDU.HOURS)) *.07 * .31, 2)) as 'Energy Cost Saved'
FROM MACHINE M, MACHINE_DAILY_UPTIME MDU
WHERE MDU.MACHINE_ID = M.ID
AND MDU.DAY >= DATE_SUB(NOW(), INTERVAL 2 WEEK )
AND M.MANUAL_ENTRY = 0
GROUP BY M.NAME
last 3 weeks
SELECT M.NAME as 'Machine', FORMAT(SUM(MDU.HOURS), 0) as 'Hours On',FORMAT((COUNT(MDU.ID)*24) - SUM(MDU.HOURS),0) as 'Hours Off', FORMAT(SUM(MDU.HOURS) / COUNT(MDU.ID),0) as 'Avg Hours Per Day', CONCAT('$',FORMAT(SUM(MDU.HOURS) * .07 * .31, 2)) as 'Energy Cost Spent',
CONCAT('$', FORMAT(((COUNT(MDU.ID)*24) - SUM(MDU.HOURS)) *.07 * .31, 2)) as 'Energy Cost Saved'
FROM MACHINE M, MACHINE_DAILY_UPTIME MDU
WHERE MDU.MACHINE_ID = M.ID
AND MDU.DAY >= DATE_SUB(NOW(), INTERVAL 3 WEEK )
AND M.MANUAL_ENTRY = 0
GROUP BY M.NAME
last month
SELECT M.NAME as 'Machine', FORMAT(SUM(MDU.HOURS), 0) as 'Hours On',FORMAT((COUNT(MDU.ID)*24) - SUM(MDU.HOURS),0) as 'Hours Off', FORMAT(SUM(MDU.HOURS) / COUNT(MDU.ID),0) as 'Avg Hours Per Day', CONCAT('$',FORMAT(SUM(MDU.HOURS) * .07 * .31, 2)) as 'Energy Cost Spent',
CONCAT('$', FORMAT(((COUNT(MDU.ID)*24) - SUM(MDU.HOURS)) *.07 * .31, 2)) as 'Energy Cost Saved'
FROM MACHINE M, MACHINE_DAILY_UPTIME MDU
WHERE MDU.MACHINE_ID = M.ID
AND MDU.DAY >= DATE_SUB(NOW(), INTERVAL 1 MONTH )
AND M.MANUAL_ENTRY = 0
GROUP BY M.NAME
last 3 months (qtr)
SELECT M.NAME as 'Machine', FORMAT(SUM(MDU.HOURS), 0) as 'Hours On',FORMAT((COUNT(MDU.ID)*24) - SUM(MDU.HOURS),0) as 'Hours Off', FORMAT(SUM(MDU.HOURS) / COUNT(MDU.ID),0) as 'Avg Hours Per Day', CONCAT('$',FORMAT(SUM(MDU.HOURS) * .07 * .31, 2)) as 'Energy Cost Spent',
CONCAT('$', FORMAT(((COUNT(MDU.ID)*24) - SUM(MDU.HOURS)) *.07 * .31, 2)) as 'Energy Cost Saved'
FROM MACHINE M, MACHINE_DAILY_UPTIME MDU
WHERE MDU.MACHINE_ID = M.ID
AND MDU.DAY >= DATE_SUB(NOW(), INTERVAL 3 MONTH )
AND M.MANUAL_ENTRY = 0
GROUP BY M.NAME
last 6 months
SELECT M.NAME as 'Machine', FORMAT(SUM(MDU.HOURS), 0) as 'Hours On',FORMAT((COUNT(MDU.ID)*24) - SUM(MDU.HOURS),0) as 'Hours Off', FORMAT(SUM(MDU.HOURS) / COUNT(MDU.ID),0) as 'Avg Hours Per Day', CONCAT('$',FORMAT(SUM(MDU.HOURS) * .07 * .31, 2)) as 'Energy Cost Spent',
CONCAT('$', FORMAT(((COUNT(MDU.ID)*24) - SUM(MDU.HOURS)) *.07 * .31, 2)) as 'Energy Cost Saved'
FROM MACHINE M, MACHINE_DAILY_UPTIME MDU
WHERE MDU.MACHINE_ID = M.ID
AND MDU.DAY >= DATE_SUB(NOW(), INTERVAL 6 MONTH )
AND M.MANUAL_ENTRY = 0
GROUP BY M.NAME
last year
SELECT M.NAME as 'Machine', FORMAT(SUM(MDU.HOURS), 0) as 'Hours On',FORMAT((COUNT(MDU.ID)*24) - SUM(MDU.HOURS),0) as 'Hours Off', FORMAT(SUM(MDU.HOURS) / COUNT(MDU.ID),0) as 'Avg Hours Per Day', CONCAT('$',FORMAT(SUM(MDU.HOURS) * .07 * .31, 2)) as 'Energy Cost Spent',
CONCAT('$', FORMAT(((COUNT(MDU.ID)*24) - SUM(MDU.HOURS)) *.07 * .31, 2)) as 'Energy Cost Saved'
FROM MACHINE M, MACHINE_DAILY_UPTIME MDU
WHERE MDU.MACHINE_ID = M.ID
AND MDU.DAY >= DATE_SUB(NOW(), INTERVAL 1 YEAR )
AND M.MANUAL_ENTRY = 0
GROUP BY M.NAME
life of the machine
SELECT M.NAME as 'Machine', FORMAT(SUM(MDU.HOURS), 0) as 'Hours On',FORMAT((COUNT(MDU.ID)*24) - SUM(MDU.HOURS),0) as 'Hours Off', FORMAT(SUM(MDU.HOURS) / COUNT(MDU.ID),0) as 'Avg Hours Per Day', CONCAT('$',FORMAT(SUM(MDU.HOURS) * .07 * .31, 2)) as 'Energy Cost Spent',
CONCAT('$', FORMAT(((COUNT(MDU.ID)*24) - SUM(MDU.HOURS)) *.07 * .31, 2)) as 'Energy Cost Saved'
FROM MACHINE M, MACHINE_DAILY_UPTIME MDU
WHERE MDU.MACHINE_ID = M.ID
AND M.MANUAL_ENTRY = 0
GROUP BY M.NAME
Comments