Agent activity

[Jegelewicz]

@dustymc I'd like to get some SQL that I could give a list of agent preferred names and get back for each of them:

agent remark
birth date
death date
ORCiD address
Wikidata address
earliest collection date
latest collection date
a comma separated list of all collections they are active in (in any capacity: collector, determiner, transaction)

Really not that much... :-)

Maybe this is a report? I'm really just tired of all the clicking through individual agents for comparison with potential duplicates for incoming collections.

[dustymc]

Maybe this is a report?

Yea, SQL isn't a great tool for that, want to mark up the agent activity report?

[dustymc]

From some meeting or something: summarize collector activity by collection and year (maybe even decade, need to see what's workable)

[dustymc]

More detail in next release.

Here's some SQL @Jegelewicz - performance is not good and it's not all of what you asked for, probably the only realistic comprehensive approach would be to APIify the detail page.

select
    agent.agent_id,
    agent.preferred_agent_name,
    agent.agent_remarks,
    status.status_summary,
    addrs.addrs,
    mindate.earliest_date,
    maxdate.latest_date,
    allcolns.gps
from
    agent
    left outer join (
        select 
            agent_id,
            string_agg(ss,' | ') as status_summary from (
            select agent_id,concat(agent_status.agent_status,': ',agent_status.status_date) as ss 
            from agent_status
            ) x group by agent_id
    ) as status on agent.agent_id=status.agent_id
    left outer join (
        select agent_id, string_agg(concat_ws(': ',address_type,address),' | ') as addrs from address
        where address_type in ('ORCID','Library of Congress','Wikidata')
         group by agent_id
    ) as addrs on agent.agent_id=addrs.agent_id
    inner join (
        select collector.agent_id,min(began_date) as earliest_date
        from
        collector
        inner join specimen_event on collector.collection_object_id=specimen_event.collection_object_id
        inner join collecting_event on specimen_event.collecting_event_id=collecting_event.collecting_event_id
        group by collector.agent_id
    ) as mindate on agent.agent_id=mindate.agent_id
    inner join (
        select collector.agent_id,max(ended_date) as latest_date
        from
        collector
        inner join specimen_event on collector.collection_object_id=specimen_event.collection_object_id
        inner join collecting_event on specimen_event.collecting_event_id=collecting_event.collecting_event_id
        group by collector.agent_id
    ) as maxdate on agent.agent_id=maxdate.agent_id
    inner join (
        select
        agent_id,
        string_agg(guid_prefix,' | ') as gps
        from (select collector.agent_id,
            guid_prefix
            from
        collector
        inner join cataloged_item on collector.collection_object_id=cataloged_item.collection_object_id
        inner join collection on cataloged_item.collection_id=collection.collection_id
        group by collector.agent_id,guid_prefix
        order by guid_prefix
        ) x group by agent_id
    ) allcolns on agent.agent_id=allcolns.agent_id
where agent.agent_id in (21247816);

[Jegelewicz]

@dustymc thanks! I hope you are feeling better! Can you help out Jessica with this? https://github.com/ArctosDB/data-migration/issues/1091#issuecomment-1076785641