Skip to main content

Exploration Queries

Use these SQL queries to explore and understand the account hierarchy patterns in your database.

Hierarchy Summary by Parent​

Shows account and location counts for each parent, helping identify 1:1:N vs 1:N:N patterns.

Hierarchy Summary
Identify relationship patterns for all parents
SELECT
  p.parent_account_id,
  p.parent_account_name,
  COUNT(DISTINCT a.account_id)  AS account_count,
  COUNT(DISTINCT l.location_id) AS location_count,
  CASE
      WHEN COUNT(DISTINCT a.account_id) = 1 THEN '1:1:N (Standard)'
      WHEN COUNT(DISTINCT a.account_id) > 1 THEN '1:N:N (Complex)'
      ELSE 'No accounts'
  END AS hierarchy_pattern
FROM portal.wg_parent_accounts p
LEFT JOIN portal.wg_accounts a
  ON a.parent_account_id = p.parent_account_id
LEFT JOIN portal.wg_account_locations l
  ON l.account_id = a.account_id
GROUP BY p.parent_account_id, p.parent_account_name
ORDER BY account_count DESC, location_count DESC;

Find Complex (1:N:N) Relationships​

Find all parents with multiple accounts (UPS/McKesson pattern).

Find 1:N:N Hierarchies
Parents with multiple CieTrade accounts

Find Standard (1:1:N) Relationships​

Find parents with single accounts but multiple locations.

Find 1:1:N Hierarchies
Parents with single account and multiple locations

UPS Hierarchy Detail​

View the complete UPS hierarchy structure.

UPS Hierarchy
All UPS accounts and their location counts

McKesson Hierarchy Detail​

View the complete McKesson hierarchy structure.

McKesson Hierarchy
All McKesson accounts and their location counts

Top Parents by Location Count​

Find the largest customers by number of service locations.

Top 20 Parents
Largest customers by location count

Full Hierarchy for Specific Parent​

View complete hierarchy details for any parent. Change @PARENT_ID to the desired parent_account_id.

Full Hierarchy View
Complete hierarchy details for a specific parent

Find Parent by Name​

Search for a parent account by name. Change @SEARCH to your search term.

Search Parent by Name
Find parent account by name pattern
DECLARE @SEARCH VARCHAR(100) = 'Goodwill';  -- Change to search term

SELECT
  p.parent_account_id,
  p.parent_account_name,
  COUNT(DISTINCT a.account_id) AS accounts,
  COUNT(l.location_id) AS locations
FROM portal.wg_parent_accounts p
LEFT JOIN portal.wg_accounts a
  ON a.parent_account_id = p.parent_account_id
LEFT JOIN portal.wg_account_locations l
  ON l.account_id = a.account_id
WHERE p.parent_account_name LIKE '%' + @SEARCH + '%'
GROUP BY p.parent_account_id, p.parent_account_name;

Accounts Without Locations​

Find accounts that have no locations (data quality check).

Accounts with No Locations
Potential data quality issue - accounts without any locations
SELECT
  p.parent_account_name,
  a.account_id,
  a.account_name,
  a.cietrade_account_id AS cpid
FROM portal.wg_accounts a
JOIN portal.wg_parent_accounts p
  ON p.parent_account_id = a.parent_account_id
LEFT JOIN portal.wg_account_locations l
  ON l.account_id = a.account_id
WHERE l.location_id IS NULL
ORDER BY p.parent_account_name;

Compare Portal to CieTrade​

Check for locations that exist in CieTrade but are missing from the portal hierarchy.

Missing Locations
Locations in CieTrade but not in portal (sync candidates)