Skip to content

Implementations

Jump to bottom Edit New page
tj-wasteology edited this page Jun 3, 2025 · 46 revisions

Performance Report - Tables & Procedures

This document outlines a collection of SQL stored procedures used to update the underlying date for the Wasteology Performance Report. The procedures are organized into categories based on their functionality and the tables they interact with. Here is a Miro Board visualization.

Table of Contents

  1. Common Scenarios

  2. Reference Tables

    • portal.vw_wg_accounts
    • portal.wg_disposal_type
    • portal.wg_equipment
    • portal.wg_service_activity
    • portal.wg_month_closings

  3. Stored Procedures

    • Accounts and Services

      • [portal].[usp_stg_refresh_accounts]
      • [portal].[usp_stg_refresh_services]

    • Weight

      • Staging:

        • [portal].[usp_stg_append_dispatch_weight]
        • [portal].[usp_stg_append_scheduled_weight]
        • [portal].[usp_stg_append_rebate_weight]

      • Delete:

        • [portal].[usp_delete_rpt_weight_by_key]

      • Populate:

        • [portal].[usp_rpt_populate_weight]

      • Orchestrate:

        • [portal].[usp_execute_weight_refresh]

    • Billing & Rebate Charges

      • Staging:

        • [portal].[usp_stg_append_billing_charges]
        • [portal].[usp_stg_append_rebate_charges]

      • Delete:

        • [portal].[usp_delete_rpt_bill_charges_by_key]
        • [portal].[usp_delete_rpt_rebates_by_key]

      • Populate:

        • [portal].[usp_rpt_populate_bill_charges_details]
        • [portal].[usp_rpt_populate_rebate_charges_details]

      • Orchestrate:

        • [portal].[usp_execute_charges_refresh]

    • Execute Refresh

      • < More here >

  4. Performance Report

    • < More here >

Common Scenarios 👽

  • Get Wasteology location id for "KYGRA - 911 Grade Ln" (Answer: 3341)
SELECT *
  FROM portal.vw_wg_accounts
 WHERE location_name LIKE '%KYGRA%' ;
  • Get charge/details for "KYGRA - 911 Grade Ln"
SELECT *
  FROM portal.rpt_details_test
 WHERE location_id = 3341
ORDER BY service_date ;
  • Get weight for "KYGRA - 911 Grade Ln"
SELECT *
  FROM portal.rpt_weight_test
  WHERE location_id = 3341
ORDER BY serv_date_id ;
  • Join tables & aggregate for March 2025 for "KYGRA - 911 Grade Ln"
SELECT SUM(d.combined_amount) AS combined_total,
       d.spend_rebate,
       SUM(w.weight_in_tons) AS total_weight,
       w.material
  FROM portal.rpt_details_test d
  JOIN portal.rpt_weight_test  w ON w.weight_id = d.weight_id
  WHERE d.location_id = 3341
    AND d.service_date BETWEEN '2025-03-01' AND '2025-03-31'
  GROUP BY d.spend_rebate, w.material ;

Reference Tables 📄

portal.vw_wg_accounts

View:

   SELECT * FROM portal.vw_wg_accounts ;

Underlying Tables:

SELECT * FROM portal.wg_parent_accounts ;  --(PK: parent_account_id)
SELECT * FROM portal.wg_accounts ;         --(PK: account_id,  FK: parent_account_id)
SELECT * FROM portal.wg_account_locations ;--(PK: location_id, FK: account_id)

Definition:

SELECT p.parent_account_id          -- Autogenerated table integer, PK for portal.wg_parent_accounts
     , p.portal_load_phase          -- Historical column used to track when account was added to Performance Report. Defaulted to 'New'.
     , p.parent_display_name        -- Parent Account label.
     , p.parent_account_name        -- Parent Account Name. Defaulted to Account Name unless UPS hierarchy is required.
     , a.account_id                 -- Autogenerated table integer, PK for portal.wg_accounts
     , a.cietrade_account_id        -- Counterparty ID (CPID) from cieTrade.
     , a.profile_customer_id        -- 3P column. No longer in use.
     , a.dynamics_account_guid      -- Dynamics account guid. Not maintained.
     , a.account_name               -- Account Name. 
     , l.location_id                -- Autogenerated table integer, PK for portal.wg_account_locations
     , l.cietrade_address_id        -- AddressID from cieTrade. Only available in API at this time (5/29/25).
     , l.location_name              -- Location name should match on both tables (new_ct.addresses).
     , l.location_display_name      -- Location name label.
     , l.recycle_location_name      -- Location name from cieTrade Brokerage.
     , l.address
     , l.city
     , l.[state]
     , l.zip                        -- Padded with leading zeros.
     , l.location_udf1              -- User-defined fields from cieTrade
     , l.location_udf2
     , l.location_udf3
     , l.location_udf4
     , l.modified_date              -- Date record was last modified. This is pretty well maintained.
FROM portal.wg_account_locations l
JOIN portal.wg_accounts a ON a.account_id = l.account_id
JOIN portal.wg_parent_accounts p ON p.parent_account_id = a.parent_account_id

ERD:

    erDiagram wg_parent_accounts {
        int parent_account_id PK "Primary Key"
        string other_columns "..."
    }
    wg_accounts {
        int account_id PK "Primary Key"
        int parent_account_id FK "Foreign Key to wg_parent_accounts"
        string other_columns "..."
    }
    wg_account_locations {
        int location_id PK "Primary Key"
        int account_id FK "Foreign Key to wg_accounts"
        string other_columns "..."
    }

    wg_parent_accounts ||--o{ wg_accounts : "has"
    wg_accounts ||--o{ wg_account_locations : "has"
Loading

Purpose: Created to address inconsistent account setups in cieTrade and provide a normalized account structure for reporting.

  • within cieTrade:
    • A "counterparty" is an account.
    • Counterparty ID (CPID) is an account ID.
    • Counterparties (accounts) can have multiple locations.
    • Some key accounts (UPS, Goodwill, McKesson Health, etc.) have many accounts with multiple locations due to billing requirements.
    • This creates a situation where 1 customer, like UPS, is spread across many accounts which themselves may have multiple locations.
    • cieTrade does not have a "Parent Account", forcing users to individually select all Accounts that are UPS.
      • UPS alone exceeds 1,500 accounts.

This structure adds a "Parent Account" to roll up multiple cieTrade accounts into a single entity for reporting. This is represented in the portal.wg_parent_accounts table.

The cietrade_account_id (CPID) is stored in the portal.wg_accounts table. This represents the account level and schematically aligns with cieTrade. CPID is called account_id in the new_ct (cieTrade) Azure database schema.

Last, the portal.wg_account_locations table represents the individual location/address level. It also aligns schematically with cieTrade addresses. The table relies on location_id as a unique ID and location_name should match location_name in cieTrade.

‼️ Important ‼️️

  • The location_name in cieTrade lacks an associated integer AddressID in exports, requiring the use of a text field for matching.
  • AddressID from cieTrade does exist in portal.wg_account_locations in the cietrade_address_id column.
  • AddressID is only available via the cieTrade API at this time. It does not exist in any current cieTrade export.
  • You can join to new_ct.addresses to portal.vw_wg_accounts with the following: 
    SELECT v.location_id, a.location_name
  FROM portal.vw_wg_accounts v 
  JOIN new_ct.addresses      a ON a.account_id = v.cietrade_account_id AND a.location_name = v.location_name
  • This view is an essential requirement for the Performance Report & others at Wasteology.
  • Scripts for maintaining this structure are available in wasteology_account_hierarchy.

portal.wg_disposal_type

Purpose:

  • Contains information about materials and their disposal types (recycle or landfill).
  • Essential for Performance Report volume & emission calculations.
  • Includes carbon factors, volumetric conversion factors (e.g., pounds to cubic yards or gallons), EPA source names, and a start/end date range for factors.

portal.wg_equipment

Purpose:

  • Provides standardized/clean values for equipment types.
  • Includes equipment name, container type, container volume, container UOM, and line of business (industrial, commercial, medical, etc.).
  • Maintained to be exhaustive of all equipment types in cieTrade.

Constraints:

  • A unique constraint exists on equipment_name to ensure each equipment type is represented only once.

portal.wg_service_activity

Purpose:

  • Contains information about service descriptions and their properties.
  • Includes service_desc, a commonly mapped service description (service_desc_new), service_category, is_activity, and is_weight.

Constraints:

  • A unique constraint exists on service_desc to ensure each service description is represented only once (even if misspelled).

Historical Context:

  • This table has some redundancy with portal.wg_equipment due to historical data from Navusoft and the transition to new cieTrade (new_ct).
  • Navusoft bills listed the full equipment type as the "service description," leading to the redundancy.
  • This table was initially used for normalizing container types and volumes but has evolved over time.

‼️ Important ‼️️

  • The is_activity and is_weight fields are crucial for determining if a service is considered an "activity" and if it should have an associated weight. These fields have been reviewed and QA'd.
  • Other services are filtered based on is_weight = 1 and their weights are calculated using container volume and material conversion factors.

portal.wg_month_closings

View:

SELECT * FROM portal.vw_current_close_date ; --references portal.wg_month_closings

Purpose:

  • Returns the maximum month closing date grouped by parent_account_id
  • Tracks monthly closing dates for parent accounts.
  • The view is used to filter data in reports, ensuring that only data up to the latest closed month is displayed.
  • This prevents displaying data before billing has completed its monthly processes.

Update Schedule:

  • The table is updated monthly on the 20th by the portal.usp_insert_month_close stored procedure.

Stored Procedures 🔨

Accounts & Services 📱

[portal].[usp_stg_refresh_accounts]

Purpose: Refreshes the portal.stg_accounts table with data from new_ct.accounts and new_ct.addresses.

Functionality:

  • Deletes existing data from portal.stg_accounts.
  • Retrieves account information from new_ct.addresses and new_ct.accounts.
  • Transforms data:
    • Handles special cases for parent_account_name (e.g., "TForce Freight," "Goodwill Industries of Kentucky").
    • Formats zip codes to 5 digits (adds leading zeros if necessary).
  • Inserts the transformed data into portal.stg_accounts.
  • Filters for customer accounts (account_role LIKE '%CUST%') and non-primary addresses (is_primary_address != 'Y').
  • Includes transaction management and error handling.
  • Provides detailed logging of the process.

Source Tables:

  • new_ct.addresses
  • new_ct.accounts

Target Tables:

  • portal.stg_accounts

[portal].[usp_stg_refresh_services]

Purpose: Refreshes the portal.stg_services table with data from new_ct.services. Functionality:

  • Deletes existing data from portal.stg_services.
  • Retrieves service information from new_ct.services.
  • Transforms data:
    • Handles special cases for parent_account_name (e.g., "TForce Freight," "Goodwill Industries of Kentucky").
    • Sets date to 12/31/9999 if end_date = '01/01/1900'
    • Uses material_recovered if material is null.
    • String removal to retrieve frequency from schedule in integer form.
    • Formats zip codes to 5 digits (adds leading zeros if necessary).
  • Inserts the transformed data into portal.stg_services.
  • Includes transaction management and error handling.
  • Provides detailed logging of the process.

Source Tables:

  • new_ct.services

Target Tables:

  • portal.stg_services

Weight ♻️

[portal].[usp_stg_append_dispatch_weight]

Purpose: Appends weight data from completed dispatches to the portal.stg_weight table.

Functionality:

  • Retrieves dispatch information from new_ct.dispatches (job ID, service ID, location, date, material, equipment, weight, weight UOM).
  • Joins with new_ct.services and new_ct.billing_charges to get material and equipment information if not directly available in new_ct.dispatches.
  • Handles different weight units of measure (UOM) and converts them to tons.
  • Calculates emissions based on material type.
  • Inserts the transformed data into portal.stg_weight.
  • Parameters:
    • Required: @MIN_DATE, @MAX_DATE
    • Optional: @PARENT_ACCOUNT_ID
      • If NULL it will run for all, otherwise it will run for the specified account.
  • Includes transaction management and error handling.
  • Provides detailed logging of the process.

Source Tables:

  • new_ct.dispatches
  • new_ct.services
  • new_ct.billing_charges
  • portal.vw_wg_accounts
  • portal.wg_equipment
  • portal.wg_disposal_type

Target Tables:

  • portal.stg_weight

[portal].[usp_stg_append_scheduled_weight]

Purpose: Appends weight data for scheduled services to the portal.stg_weight table.

Functionality:

  • Retrieves scheduled service information from portal.stg_billing_charges (service ID, location, date, material, equipment, container type, container volume, quantity, frequency, schedule).
  • Calculates weight based on container volume, material type, frequency, and schedule factors.
  • Handles different container units of measure (UOM) and converts them to tons.
  • Calculates emissions based on material type and applies a default value for transportation emissions.
  • Calculates emissions based on material type.
  • Parameters:
    • Required: @MIN_DATE, @MAX_DATE
    • Optional: @PARENT_ACCOUNT_ID
      • If NULL it will run for all, otherwise it will run for the specified account.
  • Includes transaction management and error handling.
  • Provides detailed logging of the process.

Source Tables:

  • portal.stg_billing_charges
  • portal.wg_service_activity
  • portal.wg_disposal_type
  • portal.wg_equipment

Target Tables:

  • portal.stg_weight

[portal].[usp_stg_append_rebate_weight]

Purpose: Appends weight data for recycling rebates to the portal.stg_weight table.

Functionality:

  • Retrieves rebate information from portal.stg_rebate_charges (job number, service ID, location, date, material, equipment, container type, container volume, quantity, frequency, schedule, weight).
  • Joins with portal.vw_wg_accounts to handle both regular and recycle location names.
    • Location names within the Brokerage section of cieTrade have separate distinct names.
  • Calculates emissions based on material type.
  • Inserts the transformed data into portal.stg_weight.
  • Parameters:
    • Required: @MIN_DATE, @MAX_DATE
    • Optional: @PARENT_ACCOUNT_ID
      • If NULL it will run for all, otherwise it will run for the specified account.
  • Includes transaction management and error handling.
  • Provides detailed logging of the process.

‼️ Important ‼️️
This data exists in the Brokerage module of cieTrade. Customers may sell their recycled material which needs to reflect in the Performance Report. The tonnage is essentially all recycling with the $ amounts acting as a (negative) rebate value.

Source Tables:

  • portal.stg_rebate_charges
  • portal.vw_wg_accounts
  • portal.wg_disposal_type

Target Tables:

  • portal.stg_weight

[portal].[usp_rpt_populate_weight]

Purpose: Populates the portal.rpt_weight_test table with data from portal.stg_weight.

Functionality:

  • Inserts all records from portal.stg_weight into portal.rpt_weight_test.
  • Includes transaction management and error handling.
  • Provides detailed logging of the process.

Source Tables:

  • portal.stg_weight

Target Tables:

  • portal.rpt_weight_test

[portal].[usp_delete_rpt_weight_by_key]

Purpose: Deletes records from portal.rpt_weight_test based on weight_id.

Functionality:

  • Deletes records from portal.rpt_weight_test where the weight_id exists in portal.stg_weight.
  • Includes transaction management and error handling.
  • Provides detailed logging of the process.

Source Tables:

  • portal.stg_weight

Target Tables:

  • portal.rpt_weight_test

[portal].[usp_execute_weight_refresh]

Purpose: Orchestrates the weight data refresh process.

Functionality: Calls all the other weight procedures.

  • Truncates the portal.stg_weight table.
  • Executes the following stored procedures in order:
    1. [portal].[usp_stg_append_dispatch_weight]
    2. [portal].[usp_stg_append_scheduled_weight]
    3. [portal].[usp_stg_append_rebate_weight]
    4. [portal].[usp_delete_rpt_weight_by_key]
    5. [portal].[usp_rpt_populate_weight]
  • Passes the parameters (@MIN_DATE, @MAX_DATE, @PARENT_ACCOUNT_ID) to the first three procedures.
    • @PARENT_ACCOUNT_ID is optional.
  • Includes comprehensive error handling using TRY...CATCH.
  • Provides detailed logging of the process, including start/end times and durations of each step.

Source Tables:

  • See underlying stored procedures.

Target Tables:

  • portal.rpt_weight_test

Billing & Rebate Charges 💰

[portal].[usp_stg_append_billing_charges]

Purpose: Appends billing charge data to the portal.stg_billing_charges table.

Functionality:

  • Retrieves billing charge information from new_ct.billing_charges.
  • Transforms data:
    • Joins to portal.vw_wg_accounts to map to parent_account_name, account_name,location_name.
    • Handles special cases for service_type.
    • Use material_recovered if material is null.
    • Formats zip codes to 5 digits.
    • NULL handles and type conversions.
  • Uses a temporary table (#stg_temp) to stage the data.
  • Inserts the transformed data from #stg_temp into portal.stg_billing_charges.
  • Includes transaction management and error handling.
  • Provides detailed logging of the process.
  • Parameters:
    • Required: @MIN_DATE, @MAX_DATE
    • Optional: @PARENT_ACCOUNT_ID
      • If NULL it will run for all, otherwise it will run for the specified account.

Source Tables:

  • new_ct.billing_charges
  • portal.stg_services
  • portal.wg_equipment
  • portal.stg_accounts
  • portal.vw_wg_accounts
  • portal.wg_service_activity

Target Tables:

  • portal.stg_billing_charges

[portal].[usp_stg_append_rebate_charges]

Purpose: Appends rebate charge data to the portal.stg_rebate_charges table.

Functionality:

  • Retrieves rebate charge information from new_ct.brokerage.
  • Transforms data:
    • Specific logic concerning location name due to Brokerage module in cieTrade
    • NULL handles and type conversions.
    • Formats zip codes to 5 digits.
  • Uses a temporary table (#stg_temp) to stage the data.
  • Deletes existing data from portal.stg_rebate_charges.
  • Inserts the transformed data from #stg_temp into portal.stg_rebate_charges.
  • Includes transaction management and error handling.
  • Provides detailed logging of the process.
  • Parameters:
    • Required: @MIN_DATE, @MAX_DATE
    • Optional: @PARENT_ACCOUNT_ID
      • If NULL it will run for all, otherwise it will run for the specified account.

Source Tables:

  • new_ct.brokerage
  • portal.stg_services
  • portal.wg_equipment Target Tables:
  • portal.stg_rebate_charges

[portal].[usp_delete_rpt_bill_charges_by_key]

Purpose: Deletes records from portal.rpt_details_test based on item_id.

Functionality:

  • Deletes records from portal.rpt_details_test where the item_id exists in portal.stg_billing_charges
  • Includes transaction management and error handling.
  • Provides detailed logging of the process.

Source Tables:

  • portal.stg_billing_charges

Target Tables:

  • portal.rpt_details_test

[portal].[usp_delete_rpt_rebates_by_key]

Purpose: Deletes records from portal.rpt_details_test based on item_id.

Functionality:

  • Deletes records from portal.rpt_details_test where the item_id exists in portal.stg_rebate_charges.
  • Includes transaction management and error handling.
  • Provides detailed logging of the process.

Source Tables:

  • portal.stg_rebate_charges

Target Tables:

  • portal.rpt_details_test

[portal].[usp_rpt_populate_bill_charges_details]

Purpose: Populates the portal.rpt_details_test table with billing charge details.

Functionality:

  • Inserts billing charges into portal.rpt_details_test.
    • Rebates may exist in billing charges as well.
    • They are accounted for in this step.
  • Retrieves data from portal.stg_billing_charges.
  • Transforms data:
    • Creates a weight_id based on job_no or item_id and service_type_id.
      • 0 for non-rebate
    • Creates a schedule_display based on schedule and frequency.
    • Calculates a schedule_factor based on schedule.
    • Creates address fields.
  • Handles rebate charges differently, setting amount to 0 and rebate_amount to the absolute value of the original amount.
  • Includes transaction management and error handling.
  • Provides detailed logging of the process.

Source Tables:

  • portal.stg_billing_charges

Target Tables:

  • portal.rpt_details_test

[portal].[usp_rpt_populate_rebate_charges_details]

Purpose: Populates the portal.rpt_details_test table with rebate charge details from portal.stg_rebate_charges.

Functionality:

  • Inserts rebate billing charges into portal.rpt_details_test.
  • Retrieves data from portal.stg_rebate_charges.
  • Transforms data:
    • Creates a weight_id based on job_no or item_id and service_type_id.
      • 1 for rebate
    • Creates a schedule_display based on schedule and frequency.
    • Calculates a schedule_factor based on schedule.
    • Creates address fields.
  • Sets amount to 0 and uses rebate_amount for the rebate value.
  • Includes transaction management and error handling.
  • Provides detailed logging of the process.

Source Tables:

  • portal.stg_rebate_charges

Target Tables:

  • portal.rpt_details_test

[portal].[usp_execute_charges_refresh]

Purpose: Orchestrates the billing charges data refresh process.

Functionality: Calls all the other billing charges procedures.

  • Truncates the portal.stg_billing_charges table.
  • Truncates the portal.stg_rebate_charges table.
  • Executes the following stored procedures in order:
    1. [portal].[usp_stg_append_billing_charges]
    2. [portal].[usp_stg_append_rebate_charges]
    3. [portal].[usp_delete_rpt_bill_charges_by_key]
    4. [portal].[usp_delete_rpt_rebates_by_key]
    5. [portal].[usp_rpt_populate_bill_charges_details]
    6. [portal].[usp_rpt_populate_rebate_charges_details]
  • Passes (@BEGIN_DATE, @END_DATE, @PARENT_ACCOUNT_ID) parameters to their downstream partners within [portal].[usp_stg_append_billing_charges] and [portal].[usp_stg_append_rebate_charges].
    • @PARENT_ACCOUNT_ID is optional, if NULL it will run for all.
  • Includes comprehensive error handling using TRY...CATCH.
  • Provides detailed logging of the process, including start/end times and durations of each step.

Source Tables:

  • See underlying stored procedures.

Target Tables:

  • portal.rpt_details_test

Execute Refresh 🏃🏻

  • Execute refresh for all accounts from 3/1/2025 to 5/31/2025
DECLARE @START DATE = '2025-03-01' ;
DECLARE @END   DATE = '2025-05-31' ;

EXECUTE portal.usp_stg_refresh_accounts ;
EXECUTE portal.usp_stg_refresh_services ;
EXECUTE portal.usp_execute_charges_refresh @START, @END ;
EXECUTE portal.usp_execute_weight_refresh  @START, @END ;
  • Execute refresh for 1 parent account from 3/1/2025 to 5/31/2025
DECLARE @START DATE = '2025-03-01' ;
DECLARE @END   DATE = '2025-05-31' ;
DECLARE @PARENT INT = 1048; --Associated Bank

EXECUTE portal.usp_stg_refresh_accounts ;
EXECUTE portal.usp_stg_refresh_services ;
EXECUTE portal.usp_execute_charges_refresh @START, @END, @PARENT ;
EXECUTE portal.usp_execute_weight_refresh  @START, @END, @PARENT ;

General Notes 📎

  • Schema: All procedures are designed to operate within the portal schema.
  • Error Handling: All procedures include robust error handling using TRY...CATCH blocks and RAISERROR.
  • Logging: All procedures include detailed logging using PRINT statements.
  • Transactions: Transactions are used to ensure data consistency.
  • No Count: SET NOCOUNT ON; is used to prevent the (n row(s) affected) messages from being returned.
  • WITH EXEC AS CALLER: WITH EXEC AS CALLER is used to ensure that the procedure runs with the permissions of the caller.
  • Date Range: Many of the procedures accept a date range as parameters, allowing for flexibility in the data being processed.
  • Temporary Tables: Temporary tables are used to stage data before inserting it into the target tables.
  • UDFs: The dbo.udf_null_if_blank function is used to handle blank values.

This markdown file provides a comprehensive overview of the provided SQL stored procedures, their functionality, and their interactions with the database tables.

Add a custom footer

https://github.com/markdown/markdown.github.com.wiki.git

Clone this wiki locally