invoicing/invoice-system-common/complete.sql.in
2024-09-13 14:42:56 +01:00

21 lines
503 B
MySQL

\pset footer off
SELECT
scheduled AS date,
ROUND(duration/3600.0, 2) AS hours,
regexp_replace(description, '^${CUSTOMER}: ', '', 'i') AS description
FROM billable
ORDER BY scheduled
;
SELECT
date_trunc('week', scheduled)::date AS week,
ROUND(SUM(duration)/3600.0, 2) AS hours_in_week
FROM billable
GROUP BY week
ORDER BY week
;
SELECT
ROUND(SUM(duration)/3600.0, 2) AS "hours total",
${RATE} AS "hourly rate, ${RATE_CURRENCY}",
ROUND(SUM(duration)/3600.0, 2) * ${RATE} AS "amount billed"
FROM billable