Plausible before birth checks

[MelaniePhilofsky]

First, thank you @katy-sadowski, @MaximMoinat and all who update, maintain and support the DQD! This is a very useful tool and we rely on it to ensure our data are of high quality.

One suggestion, the new plausible before birth checks require an even start before birth. However, in our source data some of our events start at birth. So, a baby has a birthdatetime of 10am on December 31, 2024, their visit_start_datime is also at 10am on December 31, 2024. The DQD requires birthdatetime < visit_start_datime. I think changing the check of birthdatetime < or = visit_start_datime would be more appropriate since visits (and possibly other clinical events) can start at birthdatetime.

[MaximMoinat]

That makes sense to me. The check is already implemented such that a record violates it if event_date < birth_date. So same date is allowed.

DataQualityDashboard/inst/sql/sql_server/field_plausible_after_birth.sql

Lines 45 to 46 in 98c825a

	CAST(cdmTable.@cdmFieldName AS DATE) < COALESCE(
	p.birth_datetime,

When looking at event datetime, things indeed go wrong. The event is cast to a date, but the birth_datetime is not and will still have a time component. This explains your findings; all datetime of events happening at same date as birth will violated the check.

Two possible solutions:

• Cast birth_datetime to date, allowing all events that are on the same day as birth (disregarding time of day)
• Add separate clause when dealing with datetime fields to take the birth time into account.

I would propose the first as it is simple to implement and consistent.

[katy-sadowski]

I concur with your suggestion @MaximMoinat - thanks! And thanks Melanie for the report :)

[katy-sadowski]

@rakeshnbabu any luck with your work on this post-hackathon / want to open a PR for your change? Thanks 😄

[rakeshnbabu]

So sorry, I’ve been fighting with line endings of all things... my diffs were flagging the entire file as being edited. I’m going to have a few hours this afternoon to finally sort it out on my mac at home. You should see a push later today!

…

-- Rocky

On Sat, Nov 9, 2024 at 9:57 AM Katy Sadowski ***@***.***> wrote: @rakeshnbabu <https://github.com/rakeshnbabu> any luck with your work on this post-hackathon / want to open a PR for your change? Thanks 😄 — Reply to this email directly, view it on GitHub <#561 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/ABINFHQO7FU5OBF7HPKP2LLZ7YPFBAVCNFSM6AAAAABLU3YGCSVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDINRWGI2DOOBXGE> . You are receiving this because you were mentioned.Message ID: ***@***.***>

[katy-sadowski]

OK fantastic, thank you so much! It's no real rush, just wanted to see if you were still planning to finish it :)