Creating Data Files via Database Queries (e.g., SQL)

select
    -- Start with the main IDs.
    c.CompanyID, i.InvoiceID, c.BillCountry,c.BillCompanyName, c.Name, c.Notes, c.AdminNotes, c.BeforeRenewal, c.NumberOfProjects,
    -- Calculate the "organization name" in the same manner which is used for
    -- outstanding invoice reminders to staff.
    isnull(c.BillCompanyName, c.Name) + (case
        when c.BillCompanyName is not null and c.Name is not null
            and c.BillCompanyName <> c.Name
        then ' (company name: ' + c.Name + ')'
        else ''
    end)  as Organization,
    -- These fields come straight from the Invoices table.
    i.Issued, i.Due, i.Currency, i.Total, i.Tax, i.Cancelled, i.OfficeId,
    -- "CreatedBy" is only populated if the invoice was created by a site
    -- administrator who was logged in as such at the time.
    isnull(i.CreatedBy, '') as CreatedBy,
    -- Turn the InvoiceLines for each Invoice into a comma-separated list
    -- which includes the quantity (fancy!)
    stuff((
        select ', ' + Description + ' (x' + convert(varchar(23), Quantity) + ')'
        from InvoiceLines
        where InvoiceID = i.InvoiceID
        order by Description
        for xml path('')
    ), 1, 2, '') as Lines,
    -- The earliest start date of an annual licence
    (
        select min(Start)
        from InvoiceLines
        where InvoiceID = i.InvoiceID
        and Finish is not null
    ) as StartDate,
    -- The latest end date of an annual licence
    (
        select max(Finish)
        from InvoiceLines
        where InvoiceID = i.InvoiceID
        and Finish is not null
    ) as EndDate,
    -- The number of Professional licences (ignoring server time licences)
    (
        select sum(Quantity)
        from InvoiceLines
        where InvoiceID = i.InvoiceID
        and Finish is not null and AllowEditingForTicks > 0 and Total > 0
    ) as NumberProfessionalLicenses,
    -- Total payments against this invoice, or zero if there are none.
    (
        select sum(Total)
        from InvoiceLines
        where InvoiceID = i.InvoiceID
        and Finish is not null
    ) as TotalAnnualLicenceValue,
    -- Total payments against this invoice, or zero if there are none.
    isnull((
        select sum(Amount)
        from Payments
        where InvoiceID = i.InvoiceID
    ), 0) as TotalPayments,
    -- Across the Company, work out what percentage of available edit-mode
    -- time has been used, as a percentage.
    isnull(convert(float, (
        -- Used edit mode "ticks" - there are 10 000 000 ticks per second.
        select sum(s.EditedForTicks)
        from Licences s
            join InvoiceLines n2 on s.InvoiceLineID = n2.InvoiceLineID
            join Invoices i2 on n2.InvoiceID = i2.InvoiceID
        where i2.CompanyID = i.CompanyID
            -- As Licences can be cancelled separately to Invoices, we need
            -- to check the "Cancelled" field here too.
            and s.Cancelled is null
    )), 0.0) / isnull(nullif(convert(float, (
        -- Available edit mode "ticks" - there are 10 000 000 ticks per second.
        select sum(AllowEditingForTicks)
        from InvoiceLines n3
            join Invoices i3 on n3.InvoiceID = i3.InvoiceID
        where i3.CompanyID = i.CompanyID
    )), 0.0), 1.0) * 100.0 as CompanyEditUsedPC
from Invoices i
    join Companies c on i.CompanyID = c.CompanyID
-- Don't include cancelled Invoices.
where i.Cancelled is null
order by Issued