DataBrighten

    Invoices sample database for SQL Server

    1,000rows · 1 table · 6 questions with answers

    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.

    textnumberdateyes/no

    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.sql

    SSMS 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

    ColumnSQL Server type
    invoice_idNVARCHAR(20)
    clientNVARCHAR(30)
    issue_dateDATE
    due_dateDATE
    paid_dateDATE
    subtotalDECIMAL(9,2)
    tax_rateDECIMAL(8,4)
    taxDECIMAL(8,2)
    totalDECIMAL(10,2)
    statusNVARCHAR(20)
    days_lateINT

    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

    statusinvoicestotal
    Paid6392,429,763.23
    Paid late215799,346.67
    Overdue146506,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

    unpaidoutstanding
    146506,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_lateavg_days_late
    21522.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

    clientinvoicesinvoiced
    Granite Dental Group71289,367.35
    Maple Design Studio63286,534.26
    Cedar Design Studio66246,002.47
    Delta Logistics LLC59243,071.27
    Redwood Print Shop58237,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

    monthinvoiced
    2024-01297,105.81
    2024-02298,363.89
    2024-03292,532.10
    2024-04288,950.21
    2024-05309,437.91
    2024-06366,142.27
    2024-07313,354.37
    2024-08414,329.34
    2024-09304,277.59
    2024-10291,716.19
    2024-11309,157.08
    2024-12250,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.