Counts by Period

Optimizing counts by period using DBI

Attendees:

JP, KD, BC, LO

Tutorial: Count by Period w/ DBI

JP walked through the Count by Period w/ DBI tutorial to demonstrate some code he wrote to create counts by day, week, month, quarter or year using a DBI connection to avoid downloading the data until after aggreagion. This was intended to address some issues that Kelsey was having with the Biospecimen Report which has many tables that are aggregated by date, precluding her from using DBI easily.

What do we want to use this workshop for?

• Leila:

  • Use this to refactor code that is written, but need guidance to improve or use better practices

  • Debug problem code together

• Jake

  • Tackle big, recurring problems together

  • Write functions that can be reused

• Kelsey:

  • Refactoring code that has been re-used or passed repeatedly

  • Even working with ChatGPT

  • Using SQL to do transformations THEN pulling the data down

• Brittany:

  • SQL specific discussion.

  • We are all a little weaker in SQL.

• SQL:

  • Joins

  • Unions

  • Transformations

  • Group By (with counts)

  • Ungroup after counts (when is this necessary and why)

  • SQL Course on Coursera

Second Workshop:

• Code Snippets

Future Worshops:

• Joining Module 1 v1 and v2 Joining Module 2 v1 and v2