special-dispatch-app/lib/controllers/statistics.js

const sequelize = require('../database/sequelize')

const get = async (req, res) => {
  const [results, metadata] = await sequelize.query(`
    SELECT
      loc.key,
      wd.date,
      SUM(svc.delivered) as delivered,
      SUM(svc.scanned) as scanned,
      SUM(svc.delivered) / (wd.regularHours + (wd.overtimeHours * 1.5)) as efficiency
    FROM workdays wd
    JOIN locations loc on wd.locationId = loc.id
    JOIN services svc on svc.workdayId = wd.id
    GROUP BY loc.key, wd.date      
  `)
  res.status(200).send(results)

}

module.exports = {
  get
}