Invoices sample database for SQL Server
A ready-to-run SQL Server script with invoices with due dates, payment dates, tax, status and days late. Tested on SQL Server 2022 16.0.4325.4. Updated 19 November 2026.
Invoices database for SQL Server
Each row is one invoice issued in 2024 to a fictional client: issue and due dates, paid date, subtotal, tax rate and amount, total, status as of 31 January 2025 and days late.
This page has it as a SQL Server script: one table and 1,000 rows, with column types chosen for SQL Server, primary keys. The script drops the table if it exists, creates it and inserts the rows in one transaction, so you can run it again to reset the data. It is the same fictional data, seed 42, as on the invoices dataset page, where it is also available as CSV and Excel.
We loaded this exact file into SQL Server 2022 16.0.4325.4 and ran every question below on it; the answers shown are that output. The same questions, written for SQLite, PostgreSQL, MySQL, SQL Server and DuckDB, return the same results on all five.
Load it
sqlcmd
sqlcmd -S localhost -Q "CREATE DATABASE practice"
sqlcmd -S localhost -d practice -i invoices-sqlserver.sqlSSMS or Azure Data Studio
Create a database, open the .sql file in a new query window connected to it, and press Execute.
Tables and columns
invoices 1,000 rows
| Column | SQL Server type |
|---|---|
| invoice_id | NVARCHAR(20) |
| client | NVARCHAR(30) |
| issue_date | DATE |
| due_date | DATE |
| paid_date | DATE |
| subtotal | DECIMAL(9,2) |
| tax_rate | DECIMAL(8,4) |
| tax | DECIMAL(8,2) |
| total | DECIMAL(10,2) |
| status | NVARCHAR(20) |
| days_late | INT |
Practice questions
answers from SQL Server 2022 16.0.4325.4
How many invoices are in each status, and what is their total value?
SQL
SELECT status, COUNT(*) AS invoices, ROUND(SUM(total), 2) AS total
FROM invoices
GROUP BY status
ORDER BY invoices DESC, status;Result
| status | invoices | total |
|---|---|---|
| Paid | 639 | 2,429,763.23 |
| Paid late | 215 | 799,346.67 |
| Overdue | 146 | 506,974.73 |
How many invoices have no paid date yet, and how much is outstanding?
SQL
SELECT COUNT(*) AS unpaid, ROUND(SUM(total), 2) AS outstanding
FROM invoices
WHERE paid_date IS NULL;Result
| unpaid | outstanding |
|---|---|
| 146 | 506,974.73 |
On average, how many days late are invoices that were paid after the due date? (computed from the dates)
SQL
SELECT COUNT(*) AS paid_late, ROUND(AVG(CAST(DATEDIFF(day, due_date, paid_date) AS DECIMAL(18,4))), 2) AS avg_days_late
FROM invoices
WHERE paid_date > due_date;DATEDIFF(day, earlier, later) returns the number of days between two dates.
Result
| paid_late | avg_days_late |
|---|---|
| 215 | 22.50 |
Which 5 clients were invoiced the most?
SQL
SELECT TOP 5 client, COUNT(*) AS invoices, ROUND(SUM(total), 2) AS invoiced
FROM invoices
GROUP BY client
ORDER BY invoiced DESC, client;SELECT TOP n ... ORDER BY returns the first n rows; SQL Server has no LIMIT.
Result
| client | invoices | invoiced |
|---|---|---|
| Granite Dental Group | 71 | 289,367.35 |
| Maple Design Studio | 63 | 286,534.26 |
| Cedar Design Studio | 66 | 246,002.47 |
| Delta Logistics LLC | 59 | 243,071.27 |
| Redwood Print Shop | 58 | 237,442.06 |
What was the total invoiced per month of issue?
SQL
SELECT CONVERT(char(7), issue_date, 126) AS month, ROUND(SUM(total), 2) AS invoiced
FROM invoices
GROUP BY CONVERT(char(7), issue_date, 126)
ORDER BY month;CONVERT(char(7), date, 126) keeps the first 7 characters of the ISO date (yyyy-mm). It is faster than FORMAT() and short enough to sort a window frame on.
Result
| month | invoiced |
|---|---|
| 2024-01 | 297,105.81 |
| 2024-02 | 298,363.89 |
| 2024-03 | 292,532.10 |
| 2024-04 | 288,950.21 |
| 2024-05 | 309,437.91 |
| 2024-06 | 366,142.27 |
| 2024-07 | 313,354.37 |
| 2024-08 | 414,329.34 |
| 2024-09 | 304,277.59 |
| 2024-10 | 291,716.19 |
| 2024-11 | 309,157.08 |
| 2024-12 | 250,717.87 |
Check the data: how many rows have a total that differs from subtotal + tax by more than one cent?
SQL
SELECT COUNT(*) AS mismatches
FROM invoices
WHERE ABS(total - (subtotal + tax)) > 0.01;Result
| mismatches |
|---|
| 0 |
SQL Server differences used here
- DATEDIFF(day, earlier, later) returns the number of days between two dates.
- SELECT TOP n ... ORDER BY returns the first n rows; SQL Server has no LIMIT.
- CONVERT(char(7), date, 126) keeps the first 7 characters of the ISO date (yyyy-mm). It is faster than FORMAT() and short enough to sort a window frame on.