TUSMGP Trainings/Notes
- Elizabeth Storrs
All videos are linked here: FLETCHER
08/07/2024 Timestamps
8/13/2024 Summary and Timestamps
*TO DO: put mentioned queries into a single ‘training’ folder and label as FGI
Here is the link to the query we were working on at the end, comparing the recommended decision of the first reviewer to the recommended decision of the second reviewer:
https://tusmgp.admissions.tufts.edu/manage/query/build?id=e4057c99-6c17-4924-9eeb-5d949c7c1bb6
Also, watching back the part where the case statement was giving us an error, I now see that there was just a typo in the formula "(case @Count when >= 3 then 'Y' else 'N' end)" when that should have been "(case when @Count >= 3 then 'Y' else 'N' end)"--here's the updated version: https://tusmgp.admissions.tufts.edu/manage/query/build?id=a71615f8-e7b0-443a-b4df-9db16d1f428b
I also mocked up a quick example query to illustrate the Decision NOT IN case: https://tusmgp.admissions.tufts.edu/manage/query/build?id=32dcc17d-100b-43fa-aaae-1f7356ce96c5 (filtering for NOT IN Withdraw returns only people who have any rank 1 confirmed decision that is not the withdraw decision, meaning people without any decision are excluded, so filtering for both NOT IN Withdraw and Not Exists a rank 1 confirmed decision will return 0 records)
We didn't have time to get to it, but continuing the theme of base joins vs. joins inside subqueries, I had mocked up two different queries for the use case where I want to return details about the first event someone attended(event date, event title, submission date, registration status etc), if that first event was in one of the "Programs" folders.
Here's the version using a base join: https://tusmgp.admissions.tufts.edu/manage/query/build?id=8ebd21ab-73e5-4a8c-8b13-5d13e063d99a
Here's the version using a subquery: https://tusmgp.admissions.tufts.edu/manage/query/build?id=9974ee78-483d-40c1-958a-e7c8756198aa
For next time, give some thought to which one it would be easier to update if you decided you actually wanted only people whose first event was in the DPT Programs folders and not the MPH Programs folders, and also see if you can explain why the inactive versions of the filter in the second query return different results:
On my list of items to revisit in future sessions:
-Finding potential twins (people where there exist other people with the same address, birthdate, and last name)
-NOT EXISTS/IN vs. EXISTS/NOT IN
8/21/2024
We touched on subquery joins vs. joins inside subqueries, took a detour into independent subqueries and system bases for lookup.activity, and then discussed various combinations of EXISTS, NOT EXISTS, IN, NOT IN, and NOT.
On that last front, Hannah, I think I misunderstood what you were asking at one point—you were correct that EXISTS a decision NOT IN Admit:
Is equivalent to EXISTS a decision IN all of the codes other than Admit:
Both of those would return applications that have at least one decision other than admit, but neither would exclude applications that also have an admit code. That was why we needed to add the second filter for NOT EXISTS code IN Admit.
9/11/2024 Reporting Demo
The reports we started on events and mailings can be found here:
https://tusmgp.admissions.tufts.edu/manage/report/render?id=271c0756-a2a2-4ecf-9c66-1b329699a06b
https://tusmgp.admissions.tufts.edu/manage/report/render?id=db5914b7-af9a-4cf0-ac4e-15b1340fd5c7
Once we've wrapped up the full rebuild of the other reports, I'll share those links as well.
The four legacy reports we identified for rebuilding have all been completed, along with the two demo reports for events and mailings that we mocked up for Alessa and Hannah on the call:
Demos:
Rebuilt:
[CJs Migration] 2023 MPH Application Funnel (legacy version here)
[CJs Migration] Fall 2023 - Tallies by Status - MBS (legacy version here)
[CJs Migration] PHPD 2023 - Tallies Report (MBS) (legacy version here)
[CJs Migration] Su 2023 DPT-PHX Application Funnel (legacy version here)