Index / Free SQL

HaloPSA Unbilled Time Report: Find Billable Work That Was Never Invoiced (free SQL)

The question every MSP owner asks eventually: are we actually billing for everything? This lists tickets closed in the last 90 days that have billable hours logged against them but were never invoiced, biggest first.

SELECT TOP 500
    f.Faultid       AS [Ticket],
    a.aareadesc     AS [Client],
    f.Symptom       AS [Summary],
    f.datecleared   AS [Closed],
    CAST(SUM(ISNULL(ac.ActionChargeHours,0)) AS DECIMAL(8,1)) AS [Billable Hrs]
FROM FAULTS f
JOIN AREA a       ON a.Aarea = f.Areaint
JOIN ACTIONS ac   ON ac.Faultid = f.Faultid
WHERE f.datecleared >= DATEADD(DAY,-90,GETDATE())
  AND f.InvoiceDate IS NULL
  AND ISNULL(f.FDeleted,0) = 0
GROUP BY f.Faultid, a.aareadesc, f.Symptom, f.datecleared
HAVING SUM(ISNULL(ac.ActionChargeHours,0)) > 0
ORDER BY [Billable Hrs] DESC

How to add it

1. In Halo go to Configuration → Reporting → Reports (or the Reports module) and click New.
2. Set Data Source to Custom SQL Query and paste the SQL.
3. Click Load Report to preview, then save it. You can feed it into a dashboard widget.

Tweaks

• Change -90 to -30 and run it before each billing run.
• It sums ActionChargeHours on the ticket's actions. Non-billable time is ignored on purpose.
• Contract (prepaid) time can show up here if your contracts invoice separately. Filter those clients out, or join CONTRACTHEADER to exclude them.