Howto Query the Reporting Database for Resolved author affiliations

This query can be used to extract author and publication data from Elements to associate each Publication Record Person Organisation (PRPO) with each Claimed User on the Publication in order to classify Users as affiliated or not to an institution. Note the resolved author link cannot be 100% accurate. See this article about resolved authors for more detail. Please use as a guide.

SELECT  pr.[Publication ID]
        ,pr.[Data Source Proprietary ID] "Uni_Pub_ID"
        ,pr.[ID] "Publication Record ID"
        ,pur.[Type] "Relationship Type"
        ,u.[ID] "Claimed User ID"
        ,u.[First Name]
        ,u.[Last Name]
        ,CASE WHEN prp.[Resolved User ID] IS NULL THEN 0 ELSE 1 END AS [Is Resolved]
        ,prpa.[Organisation]
FROM    -- Start with claimed relationships
        [Publication User Relationship] pur
        -- Join to User data
        JOIN [User] u
            ON  u.ID = pur.[User ID]
        -- Join to Publication Record data (note that we join directly to the Publication
        -- Record table instead of the Publication table)
        JOIN [Publication Record] pr
            ON  pur.[Publication ID] = pr.[Publication ID]
         -- Where possible, match the claimed relationship to a resolved person in the
         -- record data. Using a left join ensures that claimed relationships without
         -- resolved person data are not excluded.
         LEFT JOIN [Publication Record Person] prp
             ON  prp.[Publication Record ID] = pr.[ID]
                 AND prp.[Resolved User ID] = pur.[User ID]
         -- Where possible, match the resolved person to a address data. Using a left
         -- join ensures that resolved persons without address data are not excluded.
         LEFT JOIN [Publication Record Person Address] prpa 
             ON  prpa.[Publication Record Person ID] = prp.[ID]
-- limit to the publication records we are interested in.
WHERE   pr.[Data Source] = 'Uni Data Source'
        AND pr.[Data Source Proprietary ID] in ('1000002050', '1000002052', '1000002055')
;