WITH root_path(fullpath, path_type, scan_id, path_depth) AS (SELECT sd.fullpath,
      sd.path_type,
      sd.scan_id,
      sd.path_depth
    FROM srs.current_fs_scandata_ad AS sd
    WHERE sd.path_type = 2 AND
      sd.fullpath_hash IN (srs.path_hash('\\ad.acme.com\Shares\Home')))
SELECT rp.fullpath AS root_path,
  sd.fullpath,
  sdd.file_count,
  sdd.directory_count,
  sd.size,
  srs.byte_string(sd.size) AS size_string,
  srs.ad_account_name(ad.domain, ad.name, sd.owner_id) AS owner,
  sdd.directory_quota,
  CASE WHEN sdd.directory_quota IS NULL THEN 'N/A'
    WHEN sdd.directory_quota = -1 THEN 'NONE'
    ELSE srs.byte_string(sdd.directory_quota) END AS quota_string,
  CASE WHEN sdd.directory_quota = -1 THEN NULL
    ELSE (sdd.directory_quota - sd.size) END AS remaining_quota,
  CASE WHEN sdd.directory_quota = -1 THEN '*UNLIMITED'
    ELSE srs.byte_string(sdd.directory_quota - sd.size)
  END AS remaining_quota_string,
  CASE WHEN sdd.directory_quota IS NULL THEN 0
    WHEN sdd.directory_quota = 0 THEN 0 WHEN sdd.directory_quota = -1 THEN 0
    ELSE (sd.size * 100) / sdd.directory_quota END AS quota_percent_used
FROM srs.scan_directory_data AS sdd
  INNER JOIN srs.scan_data AS sd ON sd.id = sdd.scan_data_id
  INNER JOIN srs.scans AS s ON s.id = sd.scan_id
  LEFT JOIN srs.ad_objects AS ad ON ad.sid = sd.owner_id
  INNER JOIN srs.scan_targets AS st ON st.id = s.scan_target_id
  INNER JOIN srs.identity_systems AS i ON i.id = st.identity_system_id
  INNER JOIN root_path AS rp ON rp.scan_id = sd.scan_id
WHERE sd.path_depth = rp.path_depth + 1 AND
  sdd.file_count > 0 AND
  sd.path_depth = 1 AND
  s.scan_type = 1 AND
  s.is_valid = 'true' AND
  i.type = 1