Skip to content

Instantly share code, notes, and snippets.

@ryancole

ryancole/foo.sql

Last active December 11, 2015 20:59
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save ryancole/4659388 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save ryancole/4659388 to your computer and use it in GitHub Desktop.
Download ZIP
get a count of items for each user, accounting for duplicates and nulls / empties
Raw
foo.sql
SELECT v.custodian_id, COUNT(DISTINCT NVL(LOWER(TRIM(v.serial_numberev)), v.evidence_id)) AS esi_count
FROM evidence_to_custodian e
LEFT OUTER JOIN evidence_main v ON v.evidence_id = e.evidence_id
WHERE v.purged != 1 AND LOWER(v.source) = 'ac' AND v.inventory_class = 1 AND LOWER(v.source_mediaev) = 'hdd' AND e.custodian_id IN (:custodians)
GROUP BY v.custodian_id
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment