Visit Count by Visit Date per Collection Protocol

We can create a query to get the count of visits for every visit date for each CP.

Add Visit >> Visit Date Operator Exists
Add the following expression as a ‘Calculated Filter’ : date_format(SpecimenCollectionGroup.collectionDate, "%month2%/%month_day%/%year4%") any

You can change the expression to show the dates in your desired date format, for more date formats refer: https://openspecimen.atlassian.net/wiki/spaces/CAT/pages/72024115/Calculated+Filter+Temporal+Queries#Temporal-functions

Reason for Step#2:

As ‘Visit Date’ is stored as timestamp in the database i.e. the value is stored as 2025-01-10 12:12:100, even though 2 visits would have same day, month, and year, there might be chance the hours, minutes and millisecond might be different, because of which you might see 2 rows of same date.

If required: add other conditions to the query.

Click on View Records --> Action --> columns.
Select fields --> ‘CP Short Title’, ‘Visit: Identifier or ‘Visit: Name’, ‘Date Format (Calculated Field)’

Optional: Rename the ‘Date Format’ to ‘Visit Date’.
Click on ‘Next’, select ‘Count’ for the ‘Visit: Identifier’ field.
Click on ‘Done’.