TUSMGP Trainings/Notes

All videos are linked here: https://tufts.box.com/s/2od1yie9x92gdlz5667dqe01h0oyewsk

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