Create Lists Cleanup Accountability

Clearinghouse Repository Sierra Repository
, , , ,
1

Create Lists Cleanup Accountability.sql (1.6 KB)

I noticed a bunch of review files in Create LIsts dating to 2024 and figured it was high time to send out some email to staff to take a good look at their review files and empty the one-offs they are unlikely to run again. I couldn’t see any way in the SDA to print the Create Lists grid, so this query was written instead.

The output is organized with workgroups in mind, in case the results are to be sent to one or more supervisors for cleanup assignments. We are no longer a consortium member, so consortia may want to add an additional column if the User Group and User Stat Group do not provide enough granular detail.

2

I just noticed that I made a potentially confusing choice in the CASE expression:

    CASE
        WHEN TRIM(COALESCE(cl.owner_iii_user_name, '')) != '' AND cl.owner_iii_user_name != u.name
            THEN u.name
        ELSE
            ''
    END AS "Last Run By",
...
INNER JOIN iii_user u
   ON u.name = cl.iii_user_name

The CASE expression logic would be more self-explanatory had I used cl.iii_user_name in place of u.name, as the CASE boils down to “if the Sierra login who last produced these review file results is not the owner, display that non-owner login name”.  The CASE could have been written to compare the values of two columns which are available in the same row of the same table, but I have complicated matters by referring to a value from a joined table.  One might argue that I am making a nitpicky point, since by virtue of the INNER JOIN u.name is guaranteed to equal cl.iii_user_name, but I consider it poor form.

I’m leaving this boo-boo in place for its instructional value, but I am correcting my local copy and encourage you to do likewise after reading this self-flagellating commentary.