Reporter search: surveillance company family (parent + acquired brands)

Search an ecosystem rather than one invoice spelling. Supported families are axon, motorola, flock, soundthinking, magnet, securus, and viapath. For example, axon also searches TASER, Evidence.com, Fusus, Dedrone and VieVu; motorola also searches Vigilant, Avigilon and WatchGuard. Results remain leads, not a spending total: one procurement can recur at several stages and a parent company can sell non-surveillance products.

Custom SQL query (hide) 

WITH family_terms(family, term) AS (
  VALUES
    ('axon','axon'), ('axon','taser'), ('axon','evidence.com'),
    ('axon','fusus'), ('axon','dedrone'), ('axon','vievu'),
    ('motorola','motorola'), ('motorola','vigilant'),
    ('motorola','avigilon'), ('motorola','watchguard'),
    ('flock','flock'), ('flock','raven'), ('flock','condor'),
    ('flock','aerodome'),
    ('soundthinking','soundthinking'), ('soundthinking','shotspotter'),
    ('soundthinking','crimetracer'), ('soundthinking','coplink'),
    ('soundthinking','plateranger'),
    ('magnet','magnet forensics'), ('magnet','graykey'),
    ('magnet','grayshift'),
    ('securus','securus'), ('securus','aventiv'),
    ('viapath','viapath'), ('viapath','global tel'), ('viapath','gtl')
)
SELECT DISTINCT s.city, s.state_norm AS state, s.spend_date, s.vendor, s.product,
       s.amount, s.amount_type, s.dup_rank, s.geo_suspect, s.anomaly,
       s.quote, s.source_document
FROM surveillance_spending AS s
JOIN family_terms AS f
  ON lower(COALESCE(s.vendor,'') || ' ' || COALESCE(s.product,'') || ' ' ||
           COALESCE(s.quote,'')) LIKE '%' || f.term || '%'
WHERE f.family = lower(:company)
  AND (:state = '' OR s.state_norm = upper(:state))
  AND (:from_date = '' OR s.spend_date >= :from_date)
ORDER BY s.state_norm, s.city, s.spend_date, s.vendor

Query parameters

Edit SQL

0 results