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