Sample SQL Report for Demographic Archive Table

This guide will demonstrate how to create a sample SQL Report to retrieve demographic details for consumers whose debt records have been archived.

Note

This sample report does not include all the data available in the debt.archive table.

Creating a SQL Report from Demographic Archive Table

This query selects some of the demographic information from the demographic_archive table and orders the results by last_name and first_name.

1. Go to Help → Layout → SQL Views

   1. Download the file

   2. Locate the debt.archive table and fields that can be included in the report

2. Go to Reports → SQL Designer, select New

   1. Label: Demographic Archive Report

   2. Identifier: DEMOGRAPHIC_ARCHIVE_REPORT

   3. Description: This report retrieves demographic details for consumers whose debt records have been archived

   4. SQL Section: Enter the SQL query (see sample below)

3. Click Test to view the report

4. Click Save when satisfied with the report

Sample SQL Query

SELECT 
    demographic_archive.demographic_archive_id,
    demographic_archive.demographic_id,
    demographic_archive.first_name, 
    demographic_archive.middle_name, 
    demographic_archive.last_name,
    demographic_archive.dob,
    demographic_archive.address1,
    demographic_archive.address2,
    demographic_archive.city,
    demographic_archive.state,
    demographic_archive.zip,
    demographic_archive.phone_home,
    demographic_archive.phone_work,
    demographic_archive.phone_cell,
    demographic_archive.email,
    demographic_archive.debt_id,
    demographic_archive.client_id,
    demographic_archive.create_date,
    demographic_archive.demographic_type
FROM 
    demographic_archive
ORDER BY 
    demographic_archive.last_name, 
    demographic_archive.first_name;

Explanation of the SQL Query

• SELECT Clause

  • The SELECT clause specifies the columns to be retrieved from the demographic_archive table. Each column listed here represents a piece of data that you want to include in the report.

• FROM Clause

  • The FROM clause specifies the table from which to retrieve the data. In this case, it is the demographic_archive table.

• ORDER BY Clause

  • The ORDER BY clause specifies the columns by which to sort the results. In this query, the results are sorted first by last_name and then by first_name in ascending order. This means the records will be ordered alphabetically by last name and, within each last name, alphabetically by first name.

Sample Report Output

Below is an example of the report generated by the above SQL query: