Last updated on 2026-05-14 02:52:38 CEST.
| Flavor | Version | Tinstall | Tcheck | Ttotal | Status | Flags |
|---|---|---|---|---|---|---|
| r-devel-linux-x86_64-debian-clang | 2.8.7 | 9.07 | 409.44 | 418.51 | OK | |
| r-devel-linux-x86_64-debian-gcc | 2.8.7 | 6.55 | 265.03 | 271.58 | ERROR | |
| r-devel-linux-x86_64-fedora-clang | 2.8.7 | 16.00 | 649.30 | 665.30 | OK | |
| r-devel-linux-x86_64-fedora-gcc | 2.8.7 | 15.00 | 682.04 | 697.04 | OK | |
| r-devel-windows-x86_64 | 2.8.7 | 11.00 | 412.00 | 423.00 | OK | |
| r-patched-linux-x86_64 | 2.8.7 | 7.56 | 390.93 | 398.49 | OK | |
| r-release-linux-x86_64 | 2.8.7 | 8.81 | 394.57 | 403.38 | OK | |
| r-release-macos-arm64 | 2.8.7 | 2.00 | 121.00 | 123.00 | OK | |
| r-release-macos-x86_64 | 2.8.7 | 7.00 | 495.00 | 502.00 | OK | |
| r-release-windows-x86_64 | 2.8.7 | 13.00 | 412.00 | 425.00 | OK | |
| r-oldrel-macos-arm64 | 2.8.7 | 2.00 | 123.00 | 125.00 | OK | |
| r-oldrel-macos-x86_64 | 2.8.7 | 6.00 | 363.00 | 369.00 | OK | |
| r-oldrel-windows-x86_64 | 2.8.7 | 14.00 | 574.00 | 588.00 | OK |
Version: 2.8.7
Check: tests
Result: ERROR
Running ‘testthat.R’ [209s/263s]
Running the tests in ‘tests/testthat.R’ failed.
Complete output:
> library(testthat)
> library(DataQualityDashboard)
Loading required package: DatabaseConnector
>
> test_check("DataQualityDashboard")
Consider adding `DATABASECONNECTOR_JAR_FOLDER='/tmp/Rtmp9gDanm/jdbcDriversb7752d2a2d98'` to /home/hornik/.Renviron and restarting R.
trying URL 'https://jdbc.postgresql.org/download/postgresql-42.7.3.jar'
Content type 'application/java-archive' length 1089312 bytes (1.0 MB)
==================================================
downloaded 1.0 MB
DatabaseConnector postgresql JDBC driver downloaded to '/tmp/Rtmp9gDanm/jdbcDriversb7752d2a2d98'.
Consider adding `DATABASECONNECTOR_JAR_FOLDER='/tmp/Rtmp9gDanm/jdbcDriversb7752d2a2d98'` to /home/hornik/.Renviron and restarting R.
trying URL 'https://ohdsi.github.io/DatabaseConnectorJars/sqlServerV9.2.0.zip'
Content type 'application/x-zip-compressed' length 1262814 bytes (1.2 MB)
==================================================
downloaded 1.2 MB
DatabaseConnector sql server JDBC driver downloaded to '/tmp/Rtmp9gDanm/jdbcDriversb7752d2a2d98'.
Consider adding `DATABASECONNECTOR_JAR_FOLDER='/tmp/Rtmp9gDanm/jdbcDriversb7752d2a2d98'` to /home/hornik/.Renviron and restarting R.
trying URL 'https://ohdsi.github.io/DatabaseConnectorJars/oracleV19.8.zip'
Content type 'application/x-zip-compressed' length 4101479 bytes (3.9 MB)
==================================================
downloaded 3.9 MB
DatabaseConnector oracle JDBC driver downloaded to '/tmp/Rtmp9gDanm/jdbcDriversb7752d2a2d98'.
Consider adding `DATABASECONNECTOR_JAR_FOLDER='/tmp/Rtmp9gDanm/jdbcDriversb7752d2a2d98'` to /home/hornik/.Renviron and restarting R.
trying URL 'https://s3.amazonaws.com/redshift-downloads/drivers/jdbc/2.1.0.20/redshift-jdbc42-2.1.0.20.zip'
Content type 'application/zip' length 8434563 bytes (8.0 MB)
==================================================
downloaded 8.0 MB
DatabaseConnector redshift JDBC driver downloaded to '/tmp/Rtmp9gDanm/jdbcDriversb7752d2a2d98'.
attempting to download GiBleed
trying URL 'https://raw.githubusercontent.com/OHDSI/EunomiaDatasets/main/datasets/GiBleed/GiBleed_5.3.zip'
Content type 'application/zip' length 6861852 bytes (6.5 MB)
==================================================
downloaded 6.5 MB
attempting to extract and load: /tmp/Rtmp9gDanm/GiBleed_5.3.zip to: /tmp/Rtmp9gDanm/GiBleed_5.3.sqlite
Connecting using SQLite driver
Executing SQL took 0.013 secs
Connecting using SQLite driver
Currently in a tryCatch or withCallingHandlers block, so unable to add global calling handlers. ParallelLogger will not capture R messages, errors, and warnings, only explicit calls to ParallelLogger. (This message will not be shown again this R session)
-- Column specification --------------------------------------------------------
cols(
checkLevel = col_character(),
checkName = col_character(),
checkDescription = col_character(),
kahnContext = col_character(),
kahnCategory = col_character(),
kahnSubcategory = col_character(),
sqlFile = col_character(),
evaluationFilter = col_character(),
severity = col_character()
)
CDM Tables skipped: COST, CONCEPT, VOCABULARY, CONCEPT_ANCESTOR, CONCEPT_RELATIONSHIP, CONCEPT_CLASS, CONCEPT_SYNONYM, RELATIONSHIP, DOMAIN
Processing check description: cdmTable
Processing check description: cdmField
Processing check description: measureValueCompleteness
Writing results to file: /tmp/Rtmp9gDanm/dqd_b7757626365d/synthea-20260513143807.json
Execution Complete
Connecting using SQLite driver
Executing SQL took 0.095 secs
Executing SQL took 0.0151 secs
Connecting using SQLite driver
-- Column specification --------------------------------------------------------
cols(
checkLevel = col_character(),
checkName = col_character(),
checkDescription = col_character(),
kahnContext = col_character(),
kahnCategory = col_character(),
kahnSubcategory = col_character(),
sqlFile = col_character(),
evaluationFilter = col_character(),
severity = col_character()
)
CDM Tables skipped: COST, CONCEPT, VOCABULARY, CONCEPT_ANCESTOR, CONCEPT_RELATIONSHIP, CONCEPT_CLASS, CONCEPT_SYNONYM, RELATIONSHIP, DOMAIN
Processing check description: cdmTable
Processing check description: measureConditionEraCompleteness
Processing check description: cdmField
Processing check description: measureValueCompleteness
Writing results to file: /tmp/Rtmp9gDanm/dqd_b77530004ed3/synthea-20260513143835.json
Execution Complete
Executing SQL took 0.0496 secs
Executing SQL took 0.00659 secs
Connecting using SQLite driver
Executing SQL took 0.0247 secs
Executing SQL took 0.00464 secs
Connecting using SQLite driver
-- Column specification --------------------------------------------------------
cols(
checkLevel = col_character(),
checkName = col_character(),
checkDescription = col_character(),
kahnContext = col_character(),
kahnCategory = col_character(),
kahnSubcategory = col_character(),
sqlFile = col_character(),
evaluationFilter = col_character(),
severity = col_character()
)
CDM Tables skipped: COST, CONCEPT, VOCABULARY, CONCEPT_ANCESTOR, CONCEPT_RELATIONSHIP, CONCEPT_CLASS, CONCEPT_SYNONYM, RELATIONSHIP, DOMAIN
Processing check description: cdmTable
Processing check description: measureConditionEraCompleteness
Processing check description: cdmField
Processing check description: measureValueCompleteness
Writing results to file: /tmp/Rtmp9gDanm/dqd_b7755fd0f33e/synthea-20260513143900.json
Execution Complete
Executing SQL took 0.05 secs
Executing SQL took 0.01 secs
Connecting using SQLite driver
Executing SQL took 0.00629 secs
Executing SQL took 0.0042 secs
Connecting using SQLite driver
-- Column specification --------------------------------------------------------
cols(
checkLevel = col_character(),
checkName = col_character(),
checkDescription = col_character(),
kahnContext = col_character(),
kahnCategory = col_character(),
kahnSubcategory = col_character(),
sqlFile = col_character(),
evaluationFilter = col_character(),
severity = col_character()
)
CDM Tables skipped: COST, CONCEPT, VOCABULARY, CONCEPT_ANCESTOR, CONCEPT_RELATIONSHIP, CONCEPT_CLASS, CONCEPT_SYNONYM, RELATIONSHIP, DOMAIN
Processing check description: cdmTable
Processing check description: measurePersonCompleteness
Processing check description: cdmField
Processing check description: measureValueCompleteness
Writing results to file: /tmp/Rtmp9gDanm/dqd_b77535c90d26/synthea-20260513143923.json
Execution Complete
Executing SQL took 0.00485 secs
Executing SQL took 0.00344 secs
Connecting using SQLite driver
-- Column specification --------------------------------------------------------
cols(
checkLevel = col_character(),
checkName = col_character(),
checkDescription = col_character(),
kahnContext = col_character(),
kahnCategory = col_character(),
kahnSubcategory = col_character(),
sqlFile = col_character(),
evaluationFilter = col_character(),
severity = col_character()
)
CDM Tables skipped: CONCEPT, VOCABULARY, CONCEPT_ANCESTOR, CONCEPT_RELATIONSHIP, CONCEPT_CLASS, CONCEPT_SYNONYM, RELATIONSHIP, DOMAIN
Processing check description: measurePersonCompleteness
Writing results to file: /tmp/Rtmp9gDanm/dqd_b7754101d400/foo.json
Execution Complete
Writing results to file: /tmp/Rtmp9gDanm/dqd_b7754101d400/snake.json
Writing results to file: /tmp/Rtmp9gDanm/dqd_b7754101d400/snake_camel.json
Connecting using SQLite driver
-- Column specification --------------------------------------------------------
cols(
checkLevel = col_character(),
checkName = col_character(),
checkDescription = col_character(),
kahnContext = col_character(),
kahnCategory = col_character(),
kahnSubcategory = col_character(),
sqlFile = col_character(),
evaluationFilter = col_character(),
severity = col_character()
)
CDM Tables skipped: CONCEPT, VOCABULARY, CONCEPT_ANCESTOR, CONCEPT_RELATIONSHIP, CONCEPT_CLASS, CONCEPT_SYNONYM, RELATIONSHIP, DOMAIN
Processing check description: measurePersonCompleteness
Writing results to file: /tmp/Rtmp9gDanm/dqd_b77547514ae2/synthea-20260513143925.json
Execution Complete
Connecting using SQLite driver
-- Column specification --------------------------------------------------------
cols(
checkLevel = col_character(),
checkName = col_character(),
checkDescription = col_character(),
kahnContext = col_character(),
kahnCategory = col_character(),
kahnSubcategory = col_character(),
sqlFile = col_character(),
evaluationFilter = col_character(),
severity = col_character()
)
CDM Tables skipped: CONCEPT, VOCABULARY, CONCEPT_ANCESTOR, CONCEPT_RELATIONSHIP, CONCEPT_CLASS, CONCEPT_SYNONYM, RELATIONSHIP, DOMAIN
Processing check description: cdmTable
Processing check description: measurePersonCompleteness
Processing check description: measureConditionEraCompleteness
Processing check description: measureObservationPeriodOverlap
Writing results to file: /tmp/Rtmp9gDanm/dqd_b7754f88f7c/synthea-20260513143928.json
Execution Complete
Connecting using SQLite driver
-- Column specification --------------------------------------------------------
cols(
checkLevel = col_character(),
checkName = col_character(),
checkDescription = col_character(),
kahnContext = col_character(),
kahnCategory = col_character(),
kahnSubcategory = col_character(),
sqlFile = col_character(),
evaluationFilter = col_character(),
severity = col_character()
)
CDM Tables skipped: CONCEPT, VOCABULARY, CONCEPT_ANCESTOR, CONCEPT_RELATIONSHIP, CONCEPT_CLASS, CONCEPT_SYNONYM, RELATIONSHIP, DOMAIN
Processing check description: cdmField
Processing check description: isRequired
Processing check description: cdmDatatype
Processing check description: isPrimaryKey
Processing check description: isForeignKey
Processing check description: fkDomain
Processing check description: fkClass
Processing check description: isStandardValidConcept
Processing check description: measureValueCompleteness
Processing check description: standardConceptRecordCompleteness
Processing check description: sourceConceptRecordCompleteness
Processing check description: sourceValueCompleteness
Processing check description: plausibleValueLow
Processing check description: plausibleValueHigh
Processing check description: plausibleTemporalAfter
Processing check description: plausibleDuringLife
Processing check description: withinVisitDates
Processing check description: plausibleAfterBirth
Processing check description: plausibleBeforeDeath
Processing check description: plausibleStartBeforeEnd
Writing results to file: /tmp/Rtmp9gDanm/dqd_b77572926ff8/synthea-20260513144045.json
Execution Complete
Connecting using SQLite driver
-- Column specification --------------------------------------------------------
cols(
checkLevel = col_character(),
checkName = col_character(),
checkDescription = col_character(),
kahnContext = col_character(),
kahnCategory = col_character(),
kahnSubcategory = col_character(),
sqlFile = col_character(),
evaluationFilter = col_character(),
severity = col_character()
)
CDM Tables skipped: CONCEPT, VOCABULARY, CONCEPT_ANCESTOR, CONCEPT_RELATIONSHIP, CONCEPT_CLASS, CONCEPT_SYNONYM, RELATIONSHIP, DOMAIN
Processing check description: plausibleGender
[Level: CONCEPT] [Check: plausibleGender] [CDM Table: CONDITION_OCCURRENCE] [CDM Field: CONDITION_CONCEPT_ID] Error executing SQL:
near ",": syntax error
An error report has been created at /tmp/Rtmp9gDanm/dqd_b775312d9e93/errors/CONCEPT_plausibleGender_CONDITION_OCCURRENCE_CONDITION_CONCEPT_ID.txt
Processing check description: plausibleUnitConceptIds
Writing results to file: /tmp/Rtmp9gDanm/dqd_b775312d9e93/synthea-20260513144046.json
Execution Complete
Connecting using SQLite driver
-- Column specification --------------------------------------------------------
cols(
checkLevel = col_character(),
checkName = col_character(),
checkDescription = col_character(),
kahnContext = col_character(),
kahnCategory = col_character(),
kahnSubcategory = col_character(),
sqlFile = col_character(),
evaluationFilter = col_character(),
severity = col_character()
)
CDM Tables skipped: CONCEPT, VOCABULARY, CONCEPT_ANCESTOR, CONCEPT_RELATIONSHIP, CONCEPT_CLASS, CONCEPT_SYNONYM, RELATIONSHIP, DOMAIN
Processing check description: measureObservationPeriodOverlap
Writing results to file: /tmp/Rtmp9gDanm/dqd_b7757860cd34/synthea-20260513144046.json
Execution Complete
Connecting using SQLite driver
|
| | 0%
|
|======================================================================| 100%
Executing SQL took 0.00478 secs
Connecting using SQLite driver
-- Column specification --------------------------------------------------------
cols(
checkLevel = col_character(),
checkName = col_character(),
checkDescription = col_character(),
kahnContext = col_character(),
kahnCategory = col_character(),
kahnSubcategory = col_character(),
sqlFile = col_character(),
evaluationFilter = col_character(),
severity = col_character()
)
CDM Tables skipped: CONCEPT, VOCABULARY, CONCEPT_ANCESTOR, CONCEPT_RELATIONSHIP, CONCEPT_CLASS, CONCEPT_SYNONYM, RELATIONSHIP, DOMAIN
Processing check description: measureObservationPeriodOverlap
Writing results to file: /tmp/Rtmp9gDanm/dqd_b7757860cd34/synthea-20260513144046.json
Execution Complete
|
| | 0%
|
|======================================================================| 100%
Executing SQL took 0.00426 secs
|
| | 0%
|
|======================================================================| 100%
Executing SQL took 0.0045 secs
Connecting using SQLite driver
-- Column specification --------------------------------------------------------
cols(
checkLevel = col_character(),
checkName = col_character(),
checkDescription = col_character(),
kahnContext = col_character(),
kahnCategory = col_character(),
kahnSubcategory = col_character(),
sqlFile = col_character(),
evaluationFilter = col_character(),
severity = col_character()
)
CDM Tables skipped: CONCEPT, VOCABULARY, CONCEPT_ANCESTOR, CONCEPT_RELATIONSHIP, CONCEPT_CLASS, CONCEPT_SYNONYM, RELATIONSHIP, DOMAIN
Processing check description: measureObservationPeriodOverlap
Writing results to file: /tmp/Rtmp9gDanm/dqd_b7757860cd34/synthea-20260513144046.json
Execution Complete
|
| | 0%
|
|======================================================================| 100%
Executing SQL took 0.0047 secs
Connecting using SQLite driver
|
| | 0%
|
|======================================================================| 100%
Executing SQL took 0.0043 secs
Connecting using SQLite driver
-- Column specification --------------------------------------------------------
cols(
checkLevel = col_character(),
checkName = col_character(),
checkDescription = col_character(),
kahnContext = col_character(),
kahnCategory = col_character(),
kahnSubcategory = col_character(),
sqlFile = col_character(),
evaluationFilter = col_character(),
severity = col_character()
)
CDM Tables skipped: CONCEPT, VOCABULARY, CONCEPT_ANCESTOR, CONCEPT_RELATIONSHIP, CONCEPT_CLASS, CONCEPT_SYNONYM, RELATIONSHIP, DOMAIN
Processing check description: measurePersonCompleteness
Writing results to file: /tmp/Rtmp9gDanm/dqd_b775153c829a/synthea-20260513144047.json
Execution Complete
|
| | 0%
|
|======================================================================| 100%
Executing SQL took 0.00412 secs
[1] "Processing database type: oracle"
[1] "No connection details found for oracle, skipping..."
[1] "Processing database type: postgresql"
[1] "No connection details found for postgresql, skipping..."
[1] "Processing database type: sql server"
[1] "No connection details found for sql server, skipping..."
[1] "Processing database type: redshift"
[1] "No connection details found for redshift, skipping..."
[1] "Processing database type: iris"
[1] "No connection details found for iris, skipping..."
[1] "Processing database type: snowflake"
[1] "No connection details found for snowflake, skipping..."
[1] "Processing database type: spark"
[1] "No connection details found for spark, skipping..."
[1] "Processing database type: bigquery"
[1] "No connection details found for bigquery, skipping..."
Connecting using SQLite driver
-- Column specification --------------------------------------------------------
cols(
checkLevel = col_character(),
checkName = col_character(),
checkDescription = col_character(),
kahnContext = col_character(),
kahnCategory = col_character(),
kahnSubcategory = col_character(),
sqlFile = col_character(),
evaluationFilter = col_character(),
severity = col_character()
)
CDM Tables skipped: CONCEPT, VOCABULARY, CONCEPT_ANCESTOR, CONCEPT_RELATIONSHIP, CONCEPT_CLASS, CONCEPT_SYNONYM, RELATIONSHIP, DOMAIN
Processing check description: measurePersonCompleteness
Writing results to file: /tmp/Rtmp9gDanm/dqd_b775469f6793/synthea-20260513144049.json
Execution Complete
Connecting using SQLite driver
Writing results to table main.dqd_results
|
| | 0%
|
|=================================== | 50%
|
|======================================================================| 100%
Executing SQL took 0.0111 secs
Inserting data took 0.0215 secs
Finished writing table
Connecting using SQLite driver
|
| | 0%
|
|======================================================================| 100%
Executing SQL took 0.00471 secs
-- Column specification --------------------------------------------------------
cols(
checkLevel = col_character(),
checkName = col_character(),
checkDescription = col_character(),
kahnContext = col_character(),
kahnCategory = col_character(),
kahnSubcategory = col_character(),
sqlFile = col_character(),
evaluationFilter = col_character(),
severity = col_character()
)
CDM Tables skipped: CONCEPT, VOCABULARY, CONCEPT_ANCESTOR, CONCEPT_RELATIONSHIP, CONCEPT_CLASS, CONCEPT_SYNONYM, RELATIONSHIP, DOMAIN
Processing check description: measurePersonCompleteness
-- Column specification --------------------------------------------------------
cols(
checkLevel = col_character(),
checkName = col_character(),
checkDescription = col_character(),
kahnContext = col_character(),
kahnCategory = col_character(),
kahnSubcategory = col_character(),
sqlFile = col_character(),
evaluationFilter = col_character(),
severity = col_character()
)
CDM Tables skipped: CONCEPT, VOCABULARY, CONCEPT_ANCESTOR, CONCEPT_RELATIONSHIP, CONCEPT_CLASS, CONCEPT_SYNONYM, RELATIONSHIP, DOMAIN
Processing check description: measurePersonCompleteness
-- Column specification --------------------------------------------------------
cols(
checkLevel = col_character(),
checkName = col_character(),
checkDescription = col_character(),
kahnContext = col_character(),
kahnCategory = col_character(),
kahnSubcategory = col_character(),
sqlFile = col_character(),
evaluationFilter = col_character(),
severity = col_character()
)
CDM Tables skipped: CONCEPT, VOCABULARY, CONCEPT_ANCESTOR, CONCEPT_RELATIONSHIP, CONCEPT_CLASS, CONCEPT_SYNONYM, RELATIONSHIP, DOMAIN
Processing check description: measurePersonCompleteness
-- Column specification --------------------------------------------------------
cols(
checkLevel = col_character(),
checkName = col_character(),
checkDescription = col_character(),
kahnContext = col_character(),
kahnCategory = col_character(),
kahnSubcategory = col_character(),
sqlFile = col_character(),
evaluationFilter = col_character(),
severity = col_character()
)
CDM Tables skipped: CONCEPT, VOCABULARY, CONCEPT_ANCESTOR, CONCEPT_RELATIONSHIP, CONCEPT_CLASS, CONCEPT_SYNONYM, RELATIONSHIP, DOMAIN
Processing check description: measurePersonCompleteness
Connecting using SQLite driver
Executing SQL took 0.0093 secs
Executing SQL took 0.304 secs
|
| | 0%
|
|======================================================================| 100%
Executing SQL took 0.00438 secs
Connecting using SQLite driver
|
| | 0%
|
|======================================================================| 100%
Executing SQL took 0.00402 secs
Connecting using SQLite driver
-- Column specification --------------------------------------------------------
cols(
checkLevel = col_character(),
checkName = col_character(),
checkDescription = col_character(),
kahnContext = col_character(),
kahnCategory = col_character(),
kahnSubcategory = col_character(),
sqlFile = col_character(),
evaluationFilter = col_character(),
severity = col_character()
)
CDM Tables skipped: CONCEPT, VOCABULARY, CONCEPT_ANCESTOR, CONCEPT_RELATIONSHIP, CONCEPT_CLASS, CONCEPT_SYNONYM, RELATIONSHIP, DOMAIN
Processing check description: measurePersonCompleteness
Writing results to file: /tmp/Rtmp9gDanm/dqd_b7754e88c66d/synthea-20260513144053.json
Execution Complete
Connecting using SQLite driver
-- Column specification --------------------------------------------------------
cols(
checkLevel = col_character(),
checkName = col_character(),
checkDescription = col_character(),
kahnContext = col_character(),
kahnCategory = col_character(),
kahnSubcategory = col_character(),
sqlFile = col_character(),
evaluationFilter = col_character(),
severity = col_character()
)
CDM Tables skipped: CONCEPT, VOCABULARY, CONCEPT_ANCESTOR, CONCEPT_RELATIONSHIP, CONCEPT_CLASS, CONCEPT_SYNONYM, RELATIONSHIP, DOMAIN
Processing check description: measurePersonCompleteness
Writing results to file: /tmp/Rtmp9gDanm/dqd_b775383c556a/synthea-20260513144054.json
Execution Complete
Connecting using SQLite driver
-- Column specification --------------------------------------------------------
cols(
checkLevel = col_character(),
checkName = col_character(),
checkDescription = col_character(),
kahnContext = col_character(),
kahnCategory = col_character(),
kahnSubcategory = col_character(),
sqlFile = col_character(),
evaluationFilter = col_character(),
severity = col_character()
)
CDM Tables skipped: CONCEPT, VOCABULARY, CONCEPT_ANCESTOR, CONCEPT_RELATIONSHIP, CONCEPT_CLASS, CONCEPT_SYNONYM, RELATIONSHIP, DOMAIN
Processing check description: cdmTable
Processing check description: measureObservationPeriodOverlap
Processing check description: cdmField
Processing check description: isRequired
Processing check description: cdmDatatype
Processing check description: isPrimaryKey
Processing check description: isForeignKey
Writing results to file: /tmp/Rtmp9gDanm/dqd_b77537598b5e/synthea-20260513144130.json
Execution Complete
Saving _problems/test-executeDqChecks-712.R
-- Column specification --------------------------------------------------------
cols(
checkLevel = col_character(),
checkName = col_character(),
checkDescription = col_character(),
kahnContext = col_character(),
kahnCategory = col_character(),
kahnSubcategory = col_character(),
sqlFile = col_character(),
evaluationFilter = col_character(),
severity = col_character()
)
Connecting using SQLite driver
Executing SQL took 0.00464 secs
Executing SQL took 0.00431 secs
Connecting using SQLite driver
-- Column specification --------------------------------------------------------
cols(
checkLevel = col_character(),
checkName = col_character(),
checkDescription = col_character(),
kahnContext = col_character(),
kahnCategory = col_character(),
kahnSubcategory = col_character(),
sqlFile = col_character(),
evaluationFilter = col_character(),
severity = col_character()
)
CDM Tables skipped: COST, CONCEPT, VOCABULARY, CONCEPT_ANCESTOR, CONCEPT_RELATIONSHIP, CONCEPT_CLASS, CONCEPT_SYNONYM, RELATIONSHIP, DOMAIN
Processing check description: plausibleAfterBirth
Writing results to file: /tmp/Rtmp9gDanm/dqd_b775535c384a/synthea-20260513144136.json
Execution Complete
Available columns in CheckResults:
[1] "numViolatedRows" "pctViolatedRows" "numDenominatorRows"
[4] "executionTime" "queryText" "checkName"
[7] "checkLevel" "checkDescription" "cdmTableName"
[10] "cdmFieldName" "conceptId" "unitConceptId"
[13] "sqlFile" "category" "subcategory"
[16] "context" "warning" "error"
[19] "checkId" "failed" "passed"
[22] "isError" "notApplicable" "notApplicableReason"
[25] "thresholdValue" "notesValue"
All plausibleAfterBirth results:
numViolatedRows pctViolatedRows numDenominatorRows executionTime
1.1 0 0 65719 0.297296 secs
1.2 0 0 65719 0.372814 secs
1.3 0 0 65332 0.246727 secs
1.4 0 0 65332 0.273993 secs
1.5 0 0 56680 0.204728 secs
1.6 0 0 56680 0.345315 secs
1.7 0 0 0 0.007607 secs
1.8 0 0 0 0.005452 secs
1.9 0 0 0 0.005460 secs
1.10 0 0 0 0.005467 secs
1.11 0 0 0 0.005673 secs
1.12 0 0 0 0.005836 secs
1.13 0 0 0 0.005647 secs
1.14 0 0 0 0.014191 secs
1.15 0 0 52508 0.196127 secs
1.16 0 0 52508 0.179996 secs
1.17 0 0 67707 0.229766 secs
1.18 0 0 67707 0.308110 secs
1.19 0 0 67707 0.409444 secs
1.20 0 0 67707 0.351143 secs
1.21 0 0 61944 0.253345 secs
1.22 0 0 44053 0.167546 secs
1.23 0 0 44053 0.187648 secs
1.24 0 0 0 0.004553 secs
1.25 0 0 0 0.004681 secs
1.26 0 0 1477 0.010212 secs
1.27 0 0 1477 0.012929 secs
1.28 0 0 5343 0.016171 secs
1.29 0 0 5343 0.016610 secs
1.30 0 0 0 0.009822 secs
1.31 0 0 0 0.008680 secs
1.32 0 0 37409 0.184582 secs
1.33 0 0 37409 0.206449 secs
1.34 0 0 0 0.009885 secs
1.35 0 0 0 0.005597 secs
1.36 0 0 0 0.012587 secs
1.37 0 0 0 0.005390 secs
1.38 0 0 0 0.005678 secs
1.39 0 0 0 0.004983 secs
1.40 0 0 1 0.006515 secs
1.41 0 0 1 0.007083 secs
1.42 0 0 1 0.007306 secs
1.43 0 0 1 0.007472 secs
queryText
1.1 /*********\nPLAUSIBLE_AFTER_BIRTH\nChecks that all events happen after birth (PLAUSIBLE_AFTER_BIRTH == Yes)\nBirthdate is either birth_datetime or composed from year_of_birth, month_of_birth, day_of_birth (taking 1st month/1st day if missing).\nDenominator is number of events with a non-null date.\nParameters used in this template:\nschema = main\ncdmDatabaseSchema = main\ncdmTableName = CONDITION_ERA\ncdmFieldName = CONDITION_ERA_START_DATE\n**********/\nSELECT \n num_violated_rows, \n CASE \n WHEN denominator.num_rows = 0 THEN 0 \n ELSE 1.0*num_violated_rows/denominator.num_rows \n END AS pct_violated_rows, \n denominator.num_rows AS num_denominator_rows\nFROM\n(\n SELECT \n COUNT(violated_rows.violating_field) AS num_violated_rows\n FROM\n (\n /*violatedRowsBegin*/\n SELECT \n 'CONDITION_ERA.CONDITION_ERA_START_DATE' AS violating_field, \n cdmTable.*\n FROM main.CONDITION_ERA cdmTable\n JOIN main.person p \n ON cdmTable.person_id = p.person_id\n WHERE cdmTable.CONDITION_ERA_START_DATE IS NOT NULL AND \n CAST(STRFTIME('%s', SUBSTR(CAST(cdmTable.CONDITION_ERA_START_DATE AS TEXT), 1, 4) || '-' || SUBSTR(CAST(cdmTable.CONDITION_ERA_START_DATE AS TEXT), 5, 2) || '-' || SUBSTR(CAST(cdmTable.CONDITION_ERA_START_DATE AS TEXT), 7)) AS REAL) < COALESCE(\n CAST(STRFTIME('%s', SUBSTR(CAST(p.birth_datetime AS TEXT), 1, 4) || '-' || SUBSTR(CAST(p.birth_datetime AS TEXT), 5, 2) || '-' || SUBSTR(CAST(p.birth_datetime AS TEXT), 7)) AS REAL), \n CAST(STRFTIME('%s', SUBSTR(CAST(p.year_of_birth || COALESCE(\n SUBSTR(CAST('0' || CAST(p.month_of_birth AS TEXT) AS TEXT),-2),\n '01'\n ) || COALESCE(\n SUBSTR(CAST('0' || CAST(p.day_of_birth AS TEXT) AS TEXT),-2),\n '01'\n )\n AS TEXT), 1, 4) || '-' || SUBSTR(CAST(p.year_of_birth || COALESCE(\n SUBSTR(CAST('0' || CAST(p.month_of_birth AS TEXT) AS TEXT),-2),\n '01'\n ) || COALESCE(\n SUBSTR(CAST('0' || CAST(p.day_of_birth AS TEXT) AS TEXT),-2),\n '01'\n )\n AS TEXT), 5, 2) || '-' || SUBSTR(CAST(p.year_of_birth || COALESCE(\n SUBSTR(CAST('0' || CAST(p.month_of_birth AS TEXT) AS TEXT),-2),\n '01'\n ) || COALESCE(\n SUBSTR(CAST('0' || CAST(p.day_of_birth AS TEXT) AS TEXT),-2),\n '01'\n )\n AS TEXT), 7)) AS REAL)\n )\n /*violatedRowsEnd*/\n ) violated_rows\n) violated_row_count,\n(\n SELECT \n COUNT(*) AS num_rows\n FROM main.CONDITION_ERA cdmTable\n WHERE cdmTable.CONDITION_ERA_START_DATE IS NOT NULL\n) denominator\n;\n
1.2 /*********\nPLAUSIBLE_AFTER_BIRTH\nChecks that all events happen after birth (PLAUSIBLE_AFTER_BIRTH == Yes)\nBirthdate is either birth_datetime or composed from year_of_birth, month_of_birth, day_of_birth (taking 1st month/1st day if missing).\nDenominator is number of events with a non-null date.\nParameters used in this template:\nschema = main\ncdmDatabaseSchema = main\ncdmTableName = CONDITION_ERA\ncdmFieldName = CONDITION_ERA_END_DATE\n**********/\nSELECT \n num_violated_rows, \n CASE \n WHEN denominator.num_rows = 0 THEN 0 \n ELSE 1.0*num_violated_rows/denominator.num_rows \n END AS pct_violated_rows, \n denominator.num_rows AS num_denominator_rows\nFROM\n(\n SELECT \n COUNT(violated_rows.violating_field) AS num_violated_rows\n FROM\n (\n /*violatedRowsBegin*/\n SELECT \n 'CONDITION_ERA.CONDITION_ERA_END_DATE' AS violating_field, \n cdmTable.*\n FROM main.CONDITION_ERA cdmTable\n JOIN main.person p \n ON cdmTable.person_id = p.person_id\n WHERE cdmTable.CONDITION_ERA_END_DATE IS NOT NULL AND \n CAST(STRFTIME('%s', SUBSTR(CAST(cdmTable.CONDITION_ERA_END_DATE AS TEXT), 1, 4) || '-' || SUBSTR(CAST(cdmTable.CONDITION_ERA_END_DATE AS TEXT), 5, 2) || '-' || SUBSTR(CAST(cdmTable.CONDITION_ERA_END_DATE AS TEXT), 7)) AS REAL) < COALESCE(\n CAST(STRFTIME('%s', SUBSTR(CAST(p.birth_datetime AS TEXT), 1, 4) || '-' || SUBSTR(CAST(p.birth_datetime AS TEXT), 5, 2) || '-' || SUBSTR(CAST(p.birth_datetime AS TEXT), 7)) AS REAL), \n CAST(STRFTIME('%s', SUBSTR(CAST(p.year_of_birth || COALESCE(\n SUBSTR(CAST('0' || CAST(p.month_of_birth AS TEXT) AS TEXT),-2),\n '01'\n ) || COALESCE(\n SUBSTR(CAST('0' || CAST(p.day_of_birth AS TEXT) AS TEXT),-2),\n '01'\n )\n AS TEXT), 1, 4) || '-' || SUBSTR(CAST(p.year_of_birth || COALESCE(\n SUBSTR(CAST('0' || CAST(p.month_of_birth AS TEXT) AS TEXT),-2),\n '01'\n ) || COALESCE(\n SUBSTR(CAST('0' || CAST(p.day_of_birth AS TEXT) AS TEXT),-2),\n '01'\n )\n AS TEXT), 5, 2) || '-' || SUBSTR(CAST(p.year_of_birth || COALESCE(\n SUBSTR(CAST('0' || CAST(p.month_of_birth AS TEXT) AS TEXT),-2),\n '01'\n ) || COALESCE(\n SUBSTR(CAST('0' || CAST(p.day_of_birth AS TEXT) AS TEXT),-2),\n '01'\n )\n AS TEXT), 7)) AS REAL)\n )\n /*violatedRowsEnd*/\n ) violated_rows\n) violated_row_count,\n(\n SELECT \n COUNT(*) AS num_rows\n FROM main.CONDITION_ERA cdmTable\n WHERE cdmTable.CONDITION_ERA_END_DATE IS NOT NULL\n) denominator\n;\n
1.3 /*********\nPLAUSIBLE_AFTER_BIRTH\nChecks that all events happen after birth (PLAUSIBLE_AFTER_BIRTH == Yes)\nBirthdate is either birth_datetime or composed from year_of_birth, month_of_birth, day_of_birth (taking 1st month/1st day if missing).\nDenominator is number of events with a non-null date.\nParameters used in this template:\nschema = main\ncdmDatabaseSchema = main\ncdmTableName = CONDITION_OCCURRENCE\ncdmFieldName = CONDITION_START_DATE\n**********/\nSELECT \n num_violated_rows, \n CASE \n WHEN denominator.num_rows = 0 THEN 0 \n ELSE 1.0*num_violated_rows/denominator.num_rows \n END AS pct_violated_rows, \n denominator.num_rows AS num_denominator_rows\nFROM\n(\n SELECT \n COUNT(violated_rows.violating_field) AS num_violated_rows\n FROM\n (\n /*violatedRowsBegin*/\n SELECT \n 'CONDITION_OCCURRENCE.CONDITION_START_DATE' AS violating_field, \n cdmTable.*\n FROM main.CONDITION_OCCURRENCE cdmTable\n JOIN main.person p \n ON cdmTable.person_id = p.person_id\n WHERE cdmTable.CONDITION_START_DATE IS NOT NULL AND \n CAST(STRFTIME('%s', SUBSTR(CAST(cdmTable.CONDITION_START_DATE AS TEXT), 1, 4) || '-' || SUBSTR(CAST(cdmTable.CONDITION_START_DATE AS TEXT), 5, 2) || '-' || SUBSTR(CAST(cdmTable.CONDITION_START_DATE AS TEXT), 7)) AS REAL) < COALESCE(\n CAST(STRFTIME('%s', SUBSTR(CAST(p.birth_datetime AS TEXT), 1, 4) || '-' || SUBSTR(CAST(p.birth_datetime AS TEXT), 5, 2) || '-' || SUBSTR(CAST(p.birth_datetime AS TEXT), 7)) AS REAL), \n CAST(STRFTIME('%s', SUBSTR(CAST(p.year_of_birth || COALESCE(\n SUBSTR(CAST('0' || CAST(p.month_of_birth AS TEXT) AS TEXT),-2),\n '01'\n ) || COALESCE(\n SUBSTR(CAST('0' || CAST(p.day_of_birth AS TEXT) AS TEXT),-2),\n '01'\n )\n AS TEXT), 1, 4) || '-' || SUBSTR(CAST(p.year_of_birth || COALESCE(\n SUBSTR(CAST('0' || CAST(p.month_of_birth AS TEXT) AS TEXT),-2),\n '01'\n ) || COALESCE(\n SUBSTR(CAST('0' || CAST(p.day_of_birth AS TEXT) AS TEXT),-2),\n '01'\n )\n AS TEXT), 5, 2) || '-' || SUBSTR(CAST(p.year_of_birth || COALESCE(\n SUBSTR(CAST('0' || CAST(p.month_of_birth AS TEXT) AS TEXT),-2),\n '01'\n ) || COALESCE(\n SUBSTR(CAST('0' || CAST(p.day_of_birth AS TEXT) AS TEXT),-2),\n '01'\n )\n AS TEXT), 7)) AS REAL)\n )\n /*violatedRowsEnd*/\n ) violated_rows\n) violated_row_count,\n(\n SELECT \n COUNT(*) AS num_rows\n FROM main.CONDITION_OCCURRENCE cdmTable\n WHERE cdmTable.CONDITION_START_DATE IS NOT NULL\n) denominator\n;\n
1.4 /*********\nPLAUSIBLE_AFTER_BIRTH\nChecks that all events happen after birth (PLAUSIBLE_AFTER_BIRTH == Yes)\nBirthdate is either birth_datetime or composed from year_of_birth, month_of_birth, day_of_birth (taking 1st month/1st day if missing).\nDenominator is number of events with a non-null date.\nParameters used in this template:\nschema = main\ncdmDatabaseSchema = main\ncdmTableName = CONDITION_OCCURRENCE\ncdmFieldName = CONDITION_START_DATETIME\n**********/\nSELECT \n num_violated_rows, \n CASE \n WHEN denominator.num_rows = 0 THEN 0 \n ELSE 1.0*num_violated_rows/denominator.num_rows \n END AS pct_violated_rows, \n denominator.num_rows AS num_denominator_rows\nFROM\n(\n SELECT \n COUNT(violated_rows.violating_field) AS num_violated_rows\n FROM\n (\n /*violatedRowsBegin*/\n SELECT \n 'CONDITION_OCCURRENCE.CONDITION_START_DATETIME' AS violating_field, \n cdmTable.*\n FROM main.CONDITION_OCCURRENCE cdmTable\n JOIN main.person p \n ON cdmTable.person_id = p.person_id\n WHERE cdmTable.CONDITION_START_DATETIME IS NOT NULL AND \n CAST(STRFTIME('%s', SUBSTR(CAST(cdmTable.CONDITION_START_DATETIME AS TEXT), 1, 4) || '-' || SUBSTR(CAST(cdmTable.CONDITION_START_DATETIME AS TEXT), 5, 2) || '-' || SUBSTR(CAST(cdmTable.CONDITION_START_DATETIME AS TEXT), 7)) AS REAL) < COALESCE(\n CAST(STRFTIME('%s', SUBSTR(CAST(p.birth_datetime AS TEXT), 1, 4) || '-' || SUBSTR(CAST(p.birth_datetime AS TEXT), 5, 2) || '-' || SUBSTR(CAST(p.birth_datetime AS TEXT), 7)) AS REAL), \n CAST(STRFTIME('%s', SUBSTR(CAST(p.year_of_birth || COALESCE(\n SUBSTR(CAST('0' || CAST(p.month_of_birth AS TEXT) AS TEXT),-2),\n '01'\n ) || COALESCE(\n SUBSTR(CAST('0' || CAST(p.day_of_birth AS TEXT) AS TEXT),-2),\n '01'\n )\n AS TEXT), 1, 4) || '-' || SUBSTR(CAST(p.year_of_birth || COALESCE(\n SUBSTR(CAST('0' || CAST(p.month_of_birth AS TEXT) AS TEXT),-2),\n '01'\n ) || COALESCE(\n SUBSTR(CAST('0' || CAST(p.day_of_birth AS TEXT) AS TEXT),-2),\n '01'\n )\n AS TEXT), 5, 2) || '-' || SUBSTR(CAST(p.year_of_birth || COALESCE(\n SUBSTR(CAST('0' || CAST(p.month_of_birth AS TEXT) AS TEXT),-2),\n '01'\n ) || COALESCE(\n SUBSTR(CAST('0' || CAST(p.day_of_birth AS TEXT) AS TEXT),-2),\n '01'\n )\n AS TEXT), 7)) AS REAL)\n )\n /*violatedRowsEnd*/\n ) violated_rows\n) violated_row_count,\n(\n SELECT \n COUNT(*) AS num_rows\n FROM main.CONDITION_OCCURRENCE cdmTable\n WHERE cdmTable.CONDITION_START_DATETIME IS NOT NULL\n) denominator\n;\n
1.5 /*********\nPLAUSIBLE_AFTER_BIRTH\nChecks that all events happen after birth (PLAUSIBLE_AFTER_BIRTH == Yes)\nBirthdate is either birth_datetime or composed from year_of_birth, month_of_birth, day_of_birth (taking 1st month/1st day if missing).\nDenominator is number of events with a non-null date.\nParameters used in this template:\nschema = main\ncdmDatabaseSchema = main\ncdmTableName = CONDITION_OCCURRENCE\ncdmFieldName = CONDITION_END_DATE\n**********/\nSELECT \n num_violated_rows, \n CASE \n WHEN denominator.num_rows = 0 THEN 0 \n ELSE 1.0*num_violated_rows/denominator.num_rows \n END AS pct_violated_rows, \n denominator.num_rows AS num_denominator_rows\nFROM\n(\n SELECT \n COUNT(violated_rows.violating_field) AS num_violated_rows\n FROM\n (\n /*violatedRowsBegin*/\n SELECT \n 'CONDITION_OCCURRENCE.CONDITION_END_DATE' AS violating_field, \n cdmTable.*\n FROM main.CONDITION_OCCURRENCE cdmTable\n JOIN main.person p \n ON cdmTable.person_id = p.person_id\n WHERE cdmTable.CONDITION_END_DATE IS NOT NULL AND \n CAST(STRFTIME('%s', SUBSTR(CAST(cdmTable.CONDITION_END_DATE AS TEXT), 1, 4) || '-' || SUBSTR(CAST(cdmTable.CONDITION_END_DATE AS TEXT), 5, 2) || '-' || SUBSTR(CAST(cdmTable.CONDITION_END_DATE AS TEXT), 7)) AS REAL) < COALESCE(\n CAST(STRFTIME('%s', SUBSTR(CAST(p.birth_datetime AS TEXT), 1, 4) || '-' || SUBSTR(CAST(p.birth_datetime AS TEXT), 5, 2) || '-' || SUBSTR(CAST(p.birth_datetime AS TEXT), 7)) AS REAL), \n CAST(STRFTIME('%s', SUBSTR(CAST(p.year_of_birth || COALESCE(\n SUBSTR(CAST('0' || CAST(p.month_of_birth AS TEXT) AS TEXT),-2),\n '01'\n ) || COALESCE(\n SUBSTR(CAST('0' || CAST(p.day_of_birth AS TEXT) AS TEXT),-2),\n '01'\n )\n AS TEXT), 1, 4) || '-' || SUBSTR(CAST(p.year_of_birth || COALESCE(\n SUBSTR(CAST('0' || CAST(p.month_of_birth AS TEXT) AS TEXT),-2),\n '01'\n ) || COALESCE(\n SUBSTR(CAST('0' || CAST(p.day_of_birth AS TEXT) AS TEXT),-2),\n '01'\n )\n AS TEXT), 5, 2) || '-' || SUBSTR(CAST(p.year_of_birth || COALESCE(\n SUBSTR(CAST('0' || CAST(p.month_of_birth AS TEXT) AS TEXT),-2),\n '01'\n ) || COALESCE(\n SUBSTR(CAST('0' || CAST(p.day_of_birth AS TEXT) AS TEXT),-2),\n '01'\n )\n AS TEXT), 7)) AS REAL)\n )\n /*violatedRowsEnd*/\n ) violated_rows\n) violated_row_count,\n(\n SELECT \n COUNT(*) AS num_rows\n FROM main.CONDITION_OCCURRENCE cdmTable\n WHERE cdmTable.CONDITION_END_DATE IS NOT NULL\n) denominator\n;\n
1.6 /*********\nPLAUSIBLE_AFTER_BIRTH\nChecks that all events happen after birth (PLAUSIBLE_AFTER_BIRTH == Yes)\nBirthdate is either birth_datetime or composed from year_of_birth, month_of_birth, day_of_birth (taking 1st month/1st day if missing).\nDenominator is number of events with a non-null date.\nParameters used in this template:\nschema = main\ncdmDatabaseSchema = main\ncdmTableName = CONDITION_OCCURRENCE\ncdmFieldName = CONDITION_END_DATETIME\n**********/\nSELECT \n num_violated_rows, \n CASE \n WHEN denominator.num_rows = 0 THEN 0 \n ELSE 1.0*num_violated_rows/denominator.num_rows \n END AS pct_violated_rows, \n denominator.num_rows AS num_denominator_rows\nFROM\n(\n SELECT \n COUNT(violated_rows.violating_field) AS num_violated_rows\n FROM\n (\n /*violatedRowsBegin*/\n SELECT \n 'CONDITION_OCCURRENCE.CONDITION_END_DATETIME' AS violating_field, \n cdmTable.*\n FROM main.CONDITION_OCCURRENCE cdmTable\n JOIN main.person p \n ON cdmTable.person_id = p.person_id\n WHERE cdmTable.CONDITION_END_DATETIME IS NOT NULL AND \n CAST(STRFTIME('%s', SUBSTR(CAST(cdmTable.CONDITION_END_DATETIME AS TEXT), 1, 4) || '-' || SUBSTR(CAST(cdmTable.CONDITION_END_DATETIME AS TEXT), 5, 2) || '-' || SUBSTR(CAST(cdmTable.CONDITION_END_DATETIME AS TEXT), 7)) AS REAL) < COALESCE(\n CAST(STRFTIME('%s', SUBSTR(CAST(p.birth_datetime AS TEXT), 1, 4) || '-' || SUBSTR(CAST(p.birth_datetime AS TEXT), 5, 2) || '-' || SUBSTR(CAST(p.birth_datetime AS TEXT), 7)) AS REAL), \n CAST(STRFTIME('%s', SUBSTR(CAST(p.year_of_birth || COALESCE(\n SUBSTR(CAST('0' || CAST(p.month_of_birth AS TEXT) AS TEXT),-2),\n '01'\n ) || COALESCE(\n SUBSTR(CAST('0' || CAST(p.day_of_birth AS TEXT) AS TEXT),-2),\n '01'\n )\n AS TEXT), 1, 4) || '-' || SUBSTR(CAST(p.year_of_birth || COALESCE(\n SUBSTR(CAST('0' || CAST(p.month_of_birth AS TEXT) AS TEXT),-2),\n '01'\n ) || COALESCE(\n SUBSTR(CAST('0' || CAST(p.day_of_birth AS TEXT) AS TEXT),-2),\n '01'\n )\n AS TEXT), 5, 2) || '-' || SUBSTR(CAST(p.year_of_birth || COALESCE(\n SUBSTR(CAST('0' || CAST(p.month_of_birth AS TEXT) AS TEXT),-2),\n '01'\n ) || COALESCE(\n SUBSTR(CAST('0' || CAST(p.day_of_birth AS TEXT) AS TEXT),-2),\n '01'\n )\n AS TEXT), 7)) AS REAL)\n )\n /*violatedRowsEnd*/\n ) violated_rows\n) violated_row_count,\n(\n SELECT \n COUNT(*) AS num_rows\n FROM main.CONDITION_OCCURRENCE cdmTable\n WHERE cdmTable.CONDITION_END_DATETIME IS NOT NULL\n) denominator\n;\n
1.7 /*********\nPLAUSIBLE_AFTER_BIRTH\nChecks that all events happen after birth (PLAUSIBLE_AFTER_BIRTH == Yes)\nBirthdate is either birth_datetime or composed from year_of_birth, month_of_birth, day_of_birth (taking 1st month/1st day if missing).\nDenominator is number of events with a non-null date.\nParameters used in this template:\nschema = main\ncdmDatabaseSchema = main\ncdmTableName = DEATH\ncdmFieldName = DEATH_DATE\n**********/\nSELECT \n num_violated_rows, \n CASE \n WHEN denominator.num_rows = 0 THEN 0 \n ELSE 1.0*num_violated_rows/denominator.num_rows \n END AS pct_violated_rows, \n denominator.num_rows AS num_denominator_rows\nFROM\n(\n SELECT \n COUNT(violated_rows.violating_field) AS num_violated_rows\n FROM\n (\n /*violatedRowsBegin*/\n SELECT \n 'DEATH.DEATH_DATE' AS violating_field, \n cdmTable.*\n FROM main.DEATH cdmTable\n JOIN main.person p \n ON cdmTable.person_id = p.person_id\n WHERE cdmTable.DEATH_DATE IS NOT NULL AND \n CAST(STRFTIME('%s', SUBSTR(CAST(cdmTable.DEATH_DATE AS TEXT), 1, 4) || '-' || SUBSTR(CAST(cdmTable.DEATH_DATE AS TEXT), 5, 2) || '-' || SUBSTR(CAST(cdmTable.DEATH_DATE AS TEXT), 7)) AS REAL) < COALESCE(\n CAST(STRFTIME('%s', SUBSTR(CAST(p.birth_datetime AS TEXT), 1, 4) || '-' || SUBSTR(CAST(p.birth_datetime AS TEXT), 5, 2) || '-' || SUBSTR(CAST(p.birth_datetime AS TEXT), 7)) AS REAL), \n CAST(STRFTIME('%s', SUBSTR(CAST(p.year_of_birth || COALESCE(\n SUBSTR(CAST('0' || CAST(p.month_of_birth AS TEXT) AS TEXT),-2),\n '01'\n ) || COALESCE(\n SUBSTR(CAST('0' || CAST(p.day_of_birth AS TEXT) AS TEXT),-2),\n '01'\n )\n AS TEXT), 1, 4) || '-' || SUBSTR(CAST(p.year_of_birth || COALESCE(\n SUBSTR(CAST('0' || CAST(p.month_of_birth AS TEXT) AS TEXT),-2),\n '01'\n ) || COALESCE(\n SUBSTR(CAST('0' || CAST(p.day_of_birth AS TEXT) AS TEXT),-2),\n '01'\n )\n AS TEXT), 5, 2) || '-' || SUBSTR(CAST(p.year_of_birth || COALESCE(\n SUBSTR(CAST('0' || CAST(p.month_of_birth AS TEXT) AS TEXT),-2),\n '01'\n ) || COALESCE(\n SUBSTR(CAST('0' || CAST(p.day_of_birth AS TEXT) AS TEXT),-2),\n '01'\n )\n AS TEXT), 7)) AS REAL)\n )\n /*violatedRowsEnd*/\n ) violated_rows\n) violated_row_count,\n(\n SELECT \n COUNT(*) AS num_rows\n FROM main.DEATH cdmTable\n WHERE cdmTable.DEATH_DATE IS NOT NULL\n) denominator\n;\n
1.8 /*********\nPLAUSIBLE_AFTER_BIRTH\nChecks that all events happen after birth (PLAUSIBLE_AFTER_BIRTH == Yes)\nBirthdate is either birth_datetime or composed from year_of_birth, month_of_birth, day_of_birth (taking 1st month/1st day if missing).\nDenominator is number of events with a non-null date.\nParameters used in this template:\nschema = main\ncdmDatabaseSchema = main\ncdmTableName = DEATH\ncdmFieldName = DEATH_DATETIME\n**********/\nSELECT \n num_violated_rows, \n CASE \n WHEN denominator.num_rows = 0 THEN 0 \n ELSE 1.0*num_violated_rows/denominator.num_rows \n END AS pct_violated_rows, \n denominator.num_rows AS num_denominator_rows\nFROM\n(\n SELECT \n COUNT(violated_rows.violating_field) AS num_violated_rows\n FROM\n (\n /*violatedRowsBegin*/\n SELECT \n 'DEATH.DEATH_DATETIME' AS violating_field, \n cdmTable.*\n FROM main.DEATH cdmTable\n JOIN main.person p \n ON cdmTable.person_id = p.person_id\n WHERE cdmTable.DEATH_DATETIME IS NOT NULL AND \n CAST(STRFTIME('%s', SUBSTR(CAST(cdmTable.DEATH_DATETIME AS TEXT), 1, 4) || '-' || SUBSTR(CAST(cdmTable.DEATH_DATETIME AS TEXT), 5, 2) || '-' || SUBSTR(CAST(cdmTable.DEATH_DATETIME AS TEXT), 7)) AS REAL) < COALESCE(\n CAST(STRFTIME('%s', SUBSTR(CAST(p.birth_datetime AS TEXT), 1, 4) || '-' || SUBSTR(CAST(p.birth_datetime AS TEXT), 5, 2) || '-' || SUBSTR(CAST(p.birth_datetime AS TEXT), 7)) AS REAL), \n CAST(STRFTIME('%s', SUBSTR(CAST(p.year_of_birth || COALESCE(\n SUBSTR(CAST('0' || CAST(p.month_of_birth AS TEXT) AS TEXT),-2),\n '01'\n ) || COALESCE(\n SUBSTR(CAST('0' || CAST(p.day_of_birth AS TEXT) AS TEXT),-2),\n '01'\n )\n AS TEXT), 1, 4) || '-' || SUBSTR(CAST(p.year_of_birth || COALESCE(\n SUBSTR(CAST('0' || CAST(p.month_of_birth AS TEXT) AS TEXT),-2),\n '01'\n ) || COALESCE(\n SUBSTR(CAST('0' || CAST(p.day_of_birth AS TEXT) AS TEXT),-2),\n '01'\n )\n AS TEXT), 5, 2) || '-' || SUBSTR(CAST(p.year_of_birth || COALESCE(\n SUBSTR(CAST('0' || CAST(p.month_of_birth AS TEXT) AS TEXT),-2),\n '01'\n ) || COALESCE(\n SUBSTR(CAST('0' || CAST(p.day_of_birth AS TEXT) AS TEXT),-2),\n '01'\n )\n AS TEXT), 7)) AS REAL)\n )\n /*violatedRowsEnd*/\n ) violated_rows\n) violated_row_count,\n(\n SELECT \n COUNT(*) AS num_rows\n FROM main.DEATH cdmTable\n WHERE cdmTable.DEATH_DATETIME IS NOT NULL\n) denominator\n;\n
1.9 /*********\nPLAUSIBLE_AFTER_BIRTH\nChecks that all events happen after birth (PLAUSIBLE_AFTER_BIRTH == Yes)\nBirthdate is either birth_datetime or composed from year_of_birth, month_of_birth, day_of_birth (taking 1st month/1st day if missing).\nDenominator is number of events with a non-null date.\nParameters used in this template:\nschema = main\ncdmDatabaseSchema = main\ncdmTableName = DEVICE_EXPOSURE\ncdmFieldName = DEVICE_EXPOSURE_START_DATE\n**********/\nSELECT \n num_violated_rows, \n CASE \n WHEN denominator.num_rows = 0 THEN 0 \n ELSE 1.0*num_violated_rows/denominator.num_rows \n END AS pct_violated_rows, \n denominator.num_rows AS num_denominator_rows\nFROM\n(\n SELECT \n COUNT(violated_rows.violating_field) AS num_violated_rows\n FROM\n (\n /*violatedRowsBegin*/\n SELECT \n 'DEVICE_EXPOSURE.DEVICE_EXPOSURE_START_DATE' AS violating_field, \n cdmTable.*\n FROM main.DEVICE_EXPOSURE cdmTable\n JOIN main.person p \n ON cdmTable.person_id = p.person_id\n WHERE cdmTable.DEVICE_EXPOSURE_START_DATE IS NOT NULL AND \n CAST(STRFTIME('%s', SUBSTR(CAST(cdmTable.DEVICE_EXPOSURE_START_DATE AS TEXT), 1, 4) || '-' || SUBSTR(CAST(cdmTable.DEVICE_EXPOSURE_START_DATE AS TEXT), 5, 2) || '-' || SUBSTR(CAST(cdmTable.DEVICE_EXPOSURE_START_DATE AS TEXT), 7)) AS REAL) < COALESCE(\n CAST(STRFTIME('%s', SUBSTR(CAST(p.birth_datetime AS TEXT), 1, 4) || '-' || SUBSTR(CAST(p.birth_datetime AS TEXT), 5, 2) || '-' || SUBSTR(CAST(p.birth_datetime AS TEXT), 7)) AS REAL), \n CAST(STRFTIME('%s', SUBSTR(CAST(p.year_of_birth || COALESCE(\n SUBSTR(CAST('0' || CAST(p.month_of_birth AS TEXT) AS TEXT),-2),\n '01'\n ) || COALESCE(\n SUBSTR(CAST('0' || CAST(p.day_of_birth AS TEXT) AS TEXT),-2),\n '01'\n )\n AS TEXT), 1, 4) || '-' || SUBSTR(CAST(p.year_of_birth || COALESCE(\n SUBSTR(CAST('0' || CAST(p.month_of_birth AS TEXT) AS TEXT),-2),\n '01'\n ) || COALESCE(\n SUBSTR(CAST('0' || CAST(p.day_of_birth AS TEXT) AS TEXT),-2),\n '01'\n )\n AS TEXT), 5, 2) || '-' || SUBSTR(CAST(p.year_of_birth || COALESCE(\n SUBSTR(CAST('0' || CAST(p.month_of_birth AS TEXT) AS TEXT),-2),\n '01'\n ) || COALESCE(\n SUBSTR(CAST('0' || CAST(p.day_of_birth AS TEXT) AS TEXT),-2),\n '01'\n )\n AS TEXT), 7)) AS REAL)\n )\n /*violatedRowsEnd*/\n ) violated_rows\n) violated_row_count,\n(\n SELECT \n COUNT(*) AS num_rows\n FROM main.DEVICE_EXPOSURE cdmTable\n WHERE cdmTable.DEVICE_EXPOSURE_START_DATE IS NOT NULL\n) denominator\n;\n
1.10 /*********\nPLAUSIBLE_AFTER_BIRTH\nChecks that all events happen after birth (PLAUSIBLE_AFTER_BIRTH == Yes)\nBirthdate is either birth_datetime or composed from year_of_birth, month_of_birth, day_of_birth (taking 1st month/1st day if missing).\nDenominator is number of events with a non-null date.\nParameters used in this template:\nschema = main\ncdmDatabaseSchema = main\ncdmTableName = DEVICE_EXPOSURE\ncdmFieldName = DEVICE_EXPOSURE_START_DATETIME\n**********/\nSELECT \n num_violated_rows, \n CASE \n WHEN denominator.num_rows = 0 THEN 0 \n ELSE 1.0*num_violated_rows/denominator.num_rows \n END AS pct_violated_rows, \n denominator.num_rows AS num_denominator_rows\nFROM\n(\n SELECT \n COUNT(violated_rows.violating_field) AS num_violated_rows\n FROM\n (\n /*violatedRowsBegin*/\n SELECT \n 'DEVICE_EXPOSURE.DEVICE_EXPOSURE_START_DATETIME' AS violating_field, \n cdmTable.*\n FROM main.DEVICE_EXPOSURE cdmTable\n JOIN main.person p \n ON cdmTable.person_id = p.person_id\n WHERE cdmTable.DEVICE_EXPOSURE_START_DATETIME IS NOT NULL AND \n CAST(STRFTIME('%s', SUBSTR(CAST(cdmTable.DEVICE_EXPOSURE_START_DATETIME AS TEXT), 1, 4) || '-' || SUBSTR(CAST(cdmTable.DEVICE_EXPOSURE_START_DATETIME AS TEXT), 5, 2) || '-' || SUBSTR(CAST(cdmTable.DEVICE_EXPOSURE_START_DATETIME AS TEXT), 7)) AS REAL) < COALESCE(\n CAST(STRFTIME('%s', SUBSTR(CAST(p.birth_datetime AS TEXT), 1, 4) || '-' || SUBSTR(CAST(p.birth_datetime AS TEXT), 5, 2) || '-' || SUBSTR(CAST(p.birth_datetime AS TEXT), 7)) AS REAL), \n CAST(STRFTIME('%s', SUBSTR(CAST(p.year_of_birth || COALESCE(\n SUBSTR(CAST('0' || CAST(p.month_of_birth AS TEXT) AS TEXT),-2),\n '01'\n ) || COALESCE(\n SUBSTR(CAST('0' || CAST(p.day_of_birth AS TEXT) AS TEXT),-2),\n '01'\n )\n AS TEXT), 1, 4) || '-' || SUBSTR(CAST(p.year_of_birth || COALESCE(\n SUBSTR(CAST('0' || CAST(p.month_of_birth AS TEXT) AS TEXT),-2),\n '01'\n ) || COALESCE(\n SUBSTR(CAST('0' || CAST(p.day_of_birth AS TEXT) AS TEXT),-2),\n '01'\n )\n AS TEXT), 5, 2) || '-' || SUBSTR(CAST(p.year_of_birth || COALESCE(\n SUBSTR(CAST('0' || CAST(p.month_of_birth AS TEXT) AS TEXT),-2),\n '01'\n ) || COALESCE(\n SUBSTR(CAST('0' || CAST(p.day_of_birth AS TEXT) AS TEXT),-2),\n '01'\n )\n AS TEXT), 7)) AS REAL)\n )\n /*violatedRowsEnd*/\n ) violated_rows\n) violated_row_count,\n(\n SELECT \n COUNT(*) AS num_rows\n FROM main.DEVICE_EXPOSURE cdmTable\n WHERE cdmTable.DEVICE_EXPOSURE_START_DATETIME IS NOT NULL\n) denominator\n;\n
1.11 /*********\nPLAUSIBLE_AFTER_BIRTH\nChecks that all events happen after birth (PLAUSIBLE_AFTER_BIRTH == Yes)\nBirthdate is either birth_datetime or composed from year_of_birth, month_of_birth, day_of_birth (taking 1st month/1st day if missing).\nDenominator is number of events with a non-null date.\nParameters used in this template:\nschema = main\ncdmDatabaseSchema = main\ncdmTableName = DEVICE_EXPOSURE\ncdmFieldName = DEVICE_EXPOSURE_END_DATE\n**********/\nSELECT \n num_violated_rows, \n CASE \n WHEN denominator.num_rows = 0 THEN 0 \n ELSE 1.0*num_violated_rows/denominator.num_rows \n END AS pct_violated_rows, \n denominator.num_rows AS num_denominator_rows\nFROM\n(\n SELECT \n COUNT(violated_rows.violating_field) AS num_violated_rows\n FROM\n (\n /*violatedRowsBegin*/\n SELECT \n 'DEVICE_EXPOSURE.DEVICE_EXPOSURE_END_DATE' AS violating_field, \n cdmTable.*\n FROM main.DEVICE_EXPOSURE cdmTable\n JOIN main.person p \n ON cdmTable.person_id = p.person_id\n WHERE cdmTable.DEVICE_EXPOSURE_END_DATE IS NOT NULL AND \n CAST(STRFTIME('%s', SUBSTR(CAST(cdmTable.DEVICE_EXPOSURE_END_DATE AS TEXT), 1, 4) || '-' || SUBSTR(CAST(cdmTable.DEVICE_EXPOSURE_END_DATE AS TEXT), 5, 2) || '-' || SUBSTR(CAST(cdmTable.DEVICE_EXPOSURE_END_DATE AS TEXT), 7)) AS REAL) < COALESCE(\n CAST(STRFTIME('%s', SUBSTR(CAST(p.birth_datetime AS TEXT), 1, 4) || '-' || SUBSTR(CAST(p.birth_datetime AS TEXT), 5, 2) || '-' || SUBSTR(CAST(p.birth_datetime AS TEXT), 7)) AS REAL), \n CAST(STRFTIME('%s', SUBSTR(CAST(p.year_of_birth || COALESCE(\n SUBSTR(CAST('0' || CAST(p.month_of_birth AS TEXT) AS TEXT),-2),\n '01'\n ) || COALESCE(\n SUBSTR(CAST('0' || CAST(p.day_of_birth AS TEXT) AS TEXT),-2),\n '01'\n )\n AS TEXT), 1, 4) || '-' || SUBSTR(CAST(p.year_of_birth || COALESCE(\n SUBSTR(CAST('0' || CAST(p.month_of_birth AS TEXT) AS TEXT),-2),\n '01'\n ) || COALESCE(\n SUBSTR(CAST('0' || CAST(p.day_of_birth AS TEXT) AS TEXT),-2),\n '01'\n )\n AS TEXT), 5, 2) || '-' || SUBSTR(CAST(p.year_of_birth || COALESCE(\n SUBSTR(CAST('0' || CAST(p.month_of_birth AS TEXT) AS TEXT),-2),\n '01'\n ) || COALESCE(\n SUBSTR(CAST('0' || CAST(p.day_of_birth AS TEXT) AS TEXT),-2),\n '01'\n )\n AS TEXT), 7)) AS REAL)\n )\n /*violatedRowsEnd*/\n ) violated_rows\n) violated_row_count,\n(\n SELECT \n COUNT(*) AS num_rows\n FROM main.DEVICE_EXPOSURE cdmTable\n WHERE cdmTable.DEVICE_EXPOSURE_END_DATE IS NOT NULL\n) denominator\n;\n
1.12 /*********\nPLAUSIBLE_AFTER_BIRTH\nChecks that all events happen after birth (PLAUSIBLE_AFTER_BIRTH == Yes)\nBirthdate is either birth_datetime or composed from year_of_birth, month_of_birth, day_of_birth (taking 1st month/1st day if missing).\nDenominator is number of events with a non-null date.\nParameters used in this template:\nschema = main\ncdmDatabaseSchema = main\ncdmTableName = DEVICE_EXPOSURE\ncdmFieldName = DEVICE_EXPOSURE_END_DATETIME\n**********/\nSELECT \n num_violated_rows, \n CASE \n WHEN denominator.num_rows = 0 THEN 0 \n ELSE 1.0*num_violated_rows/denominator.num_rows \n END AS pct_violated_rows, \n denominator.num_rows AS num_denominator_rows\nFROM\n(\n SELECT \n COUNT(violated_rows.violating_field) AS num_violated_rows\n FROM\n (\n /*violatedRowsBegin*/\n SELECT \n 'DEVICE_EXPOSURE.DEVICE_EXPOSURE_END_DATETIME' AS violating_field, \n cdmTable.*\n FROM main.DEVICE_EXPOSURE cdmTable\n JOIN main.person p \n ON cdmTable.person_id = p.person_id\n WHERE cdmTable.DEVICE_EXPOSURE_END_DATETIME IS NOT NULL AND \n CAST(STRFTIME('%s', SUBSTR(CAST(cdmTable.DEVICE_EXPOSURE_END_DATETIME AS TEXT), 1, 4) || '-' || SUBSTR(CAST(cdmTable.DEVICE_EXPOSURE_END_DATETIME AS TEXT), 5, 2) || '-' || SUBSTR(CAST(cdmTable.DEVICE_EXPOSURE_END_DATETIME AS TEXT), 7)) AS REAL) < COALESCE(\n CAST(STRFTIME('%s', SUBSTR(CAST(p.birth_datetime AS TEXT), 1, 4) || '-' || SUBSTR(CAST(p.birth_datetime AS TEXT), 5, 2) || '-' || SUBSTR(CAST(p.birth_datetime AS TEXT), 7)) AS REAL), \n CAST(STRFTIME('%s', SUBSTR(CAST(p.year_of_birth || COALESCE(\n SUBSTR(CAST('0' || CAST(p.month_of_birth AS TEXT) AS TEXT),-2),\n '01'\n ) || COALESCE(\n SUBSTR(CAST('0' || CAST(p.day_of_birth AS TEXT) AS TEXT),-2),\n '01'\n )\n AS TEXT), 1, 4) || '-' || SUBSTR(CAST(p.year_of_birth || COALESCE(\n SUBSTR(CAST('0' || CAST(p.month_of_birth AS TEXT) AS TEXT),-2),\n '01'\n ) || COALESCE(\n SUBSTR(CAST('0' || CAST(p.day_of_birth AS TEXT) AS TEXT),-2),\n '01'\n )\n AS TEXT), 5, 2) || '-' || SUBSTR(CAST(p.year_of_birth || COALESCE(\n SUBSTR(CAST('0' || CAST(p.month_of_birth AS TEXT) AS TEXT),-2),\n '01'\n ) || COALESCE(\n SUBSTR(CAST('0' || CAST(p.day_of_birth AS TEXT) AS TEXT),-2),\n '01'\n )\n AS TEXT), 7)) AS REAL)\n )\n /*violatedRowsEnd*/\n ) violated_rows\n) violated_row_count,\n(\n SELECT \n COUNT(*) AS num_rows\n FROM main.DEVICE_EXPOSURE cdmTable\n WHERE cdmTable.DEVICE_EXPOSURE_END_DATETIME IS NOT NULL\n) denominator\n;\n
1.13 /*********\nPLAUSIBLE_AFTER_BIRTH\nChecks that all events happen after birth (PLAUSIBLE_AFTER_BIRTH == Yes)\nBirthdate is either birth_datetime or composed from year_of_birth, month_of_birth, day_of_birth (taking 1st month/1st day if missing).\nDenominator is number of events with a non-null date.\nParameters used in this template:\nschema = main\ncdmDatabaseSchema = main\ncdmTableName = DOSE_ERA\ncdmFieldName = DOSE_ERA_START_DATE\n**********/\nSELECT \n num_violated_rows, \n CASE \n WHEN denominator.num_rows = 0 THEN 0 \n ELSE 1.0*num_violated_rows/denominator.num_rows \n END AS pct_violated_rows, \n denominator.num_rows AS num_denominator_rows\nFROM\n(\n SELECT \n COUNT(violated_rows.violating_field) AS num_violated_rows\n FROM\n (\n /*violatedRowsBegin*/\n SELECT \n 'DOSE_ERA.DOSE_ERA_START_DATE' AS violating_field, \n cdmTable.*\n FROM main.DOSE_ERA cdmTable\n JOIN main.person p \n ON cdmTable.person_id = p.person_id\n WHERE cdmTable.DOSE_ERA_START_DATE IS NOT NULL AND \n CAST(STRFTIME('%s', SUBSTR(CAST(cdmTable.DOSE_ERA_START_DATE AS TEXT), 1, 4) || '-' || SUBSTR(CAST(cdmTable.DOSE_ERA_START_DATE AS TEXT), 5, 2) || '-' || SUBSTR(CAST(cdmTable.DOSE_ERA_START_DATE AS TEXT), 7)) AS REAL) < COALESCE(\n CAST(STRFTIME('%s', SUBSTR(CAST(p.birth_datetime AS TEXT), 1, 4) || '-' || SUBSTR(CAST(p.birth_datetime AS TEXT), 5, 2) || '-' || SUBSTR(CAST(p.birth_datetime AS TEXT), 7)) AS REAL), \n CAST(STRFTIME('%s', SUBSTR(CAST(p.year_of_birth || COALESCE(\n SUBSTR(CAST('0' || CAST(p.month_of_birth AS TEXT) AS TEXT),-2),\n '01'\n ) || COALESCE(\n SUBSTR(CAST('0' || CAST(p.day_of_birth AS TEXT) AS TEXT),-2),\n '01'\n )\n AS TEXT), 1, 4) || '-' || SUBSTR(CAST(p.year_of_birth || COALESCE(\n SUBSTR(CAST('0' || CAST(p.month_of_birth AS TEXT) AS TEXT),-2),\n '01'\n ) || COALESCE(\n SUBSTR(CAST('0' || CAST(p.day_of_birth AS TEXT) AS TEXT),-2),\n '01'\n )\n AS TEXT), 5, 2) || '-' || SUBSTR(CAST(p.year_of_birth || COALESCE(\n SUBSTR(CAST('0' || CAST(p.month_of_birth AS TEXT) AS TEXT),-2),\n '01'\n ) || COALESCE(\n SUBSTR(CAST('0' || CAST(p.day_of_birth AS TEXT) AS TEXT),-2),\n '01'\n )\n AS TEXT), 7)) AS REAL)\n )\n /*violatedRowsEnd*/\n ) violated_rows\n) violated_row_count,\n(\n SELECT \n COUNT(*) AS num_rows\n FROM main.DOSE_ERA cdmTable\n WHERE cdmTable.DOSE_ERA_START_DATE IS NOT NULL\n) denominator\n;\n
1.14 /*********\nPLAUSIBLE_AFTER_BIRTH\nChecks that all events happen after birth (PLAUSIBLE_AFTER_BIRTH == Yes)\nBirthdate is either birth_datetime or composed from year_of_birth, month_of_birth, day_of_birth (taking 1st month/1st day if missing).\nDenominator is number of events with a non-null date.\nParameters used in this template:\nschema = main\ncdmDatabaseSchema = main\ncdmTableName = DOSE_ERA\ncdmFieldName = DOSE_ERA_END_DATE\n**********/\nSELECT \n num_violated_rows, \n CASE \n WHEN denominator.num_rows = 0 THEN 0 \n ELSE 1.0*num_violated_rows/denominator.num_rows \n END AS pct_violated_rows, \n denominator.num_rows AS num_denominator_rows\nFROM\n(\n SELECT \n COUNT(violated_rows.violating_field) AS num_violated_rows\n FROM\n (\n /*violatedRowsBegin*/\n SELECT \n 'DOSE_ERA.DOSE_ERA_END_DATE' AS violating_field, \n cdmTable.*\n FROM main.DOSE_ERA cdmTable\n JOIN main.person p \n ON cdmTable.person_id = p.person_id\n WHERE cdmTable.DOSE_ERA_END_DATE IS NOT NULL AND \n CAST(STRFTIME('%s', SUBSTR(CAST(cdmTable.DOSE_ERA_END_DATE AS TEXT), 1, 4) || '-' || SUBSTR(CAST(cdmTable.DOSE_ERA_END_DATE AS TEXT), 5, 2) || '-' || SUBSTR(CAST(cdmTable.DOSE_ERA_END_DATE AS TEXT), 7)) AS REAL) < COALESCE(\n CAST(STRFTIME('%s', SUBSTR(CAST(p.birth_datetime AS TEXT), 1, 4) || '-' || SUBSTR(CAST(p.birth_datetime AS TEXT), 5, 2) || '-' || SUBSTR(CAST(p.birth_datetime AS TEXT), 7)) AS REAL), \n CAST(STRFTIME('%s', SUBSTR(CAST(p.year_of_birth || COALESCE(\n SUBSTR(CAST('0' || CAST(p.month_of_birth AS TEXT) AS TEXT),-2),\n '01'\n ) || COALESCE(\n SUBSTR(CAST('0' || CAST(p.day_of_birth AS TEXT) AS TEXT),-2),\n '01'\n )\n AS TEXT), 1, 4) || '-' || SUBSTR(CAST(p.year_of_birth || COALESCE(\n SUBSTR(CAST('0' || CAST(p.month_of_birth AS TEXT) AS TEXT),-2),\n '01'\n ) || COALESCE(\n SUBSTR(CAST('0' || CAST(p.day_of_birth AS TEXT) AS TEXT),-2),\n '01'\n )\n AS TEXT), 5, 2) || '-' || SUBSTR(CAST(p.year_of_birth || COALESCE(\n SUBSTR(CAST('0' || CAST(p.month_of_birth AS TEXT) AS TEXT),-2),\n '01'\n ) || COALESCE(\n SUBSTR(CAST('0' || CAST(p.day_of_birth AS TEXT) AS TEXT),-2),\n '01'\n )\n AS TEXT), 7)) AS REAL)\n )\n /*violatedRowsEnd*/\n ) violated_rows\n) violated_row_count,\n(\n SELECT \n COUNT(*) AS num_rows\n FROM main.DOSE_ERA cdmTable\n WHERE cdmTable.DOSE_ERA_END_DATE IS NOT NULL\n) denominator\n;\n
1.15 /*********\nPLAUSIBLE_AFTER_BIRTH\nChecks that all events happen after birth (PLAUSIBLE_AFTER_BIRTH == Yes)\nBirthdate is either birth_datetime or composed from year_of_birth, month_of_birth, day_of_birth (taking 1st month/1st day if missing).\nDenominator is number of events with a non-null date.\nParameters used in this template:\nschema = main\ncdmDatabaseSchema = main\ncdmTableName = DRUG_ERA\ncdmFieldName = DRUG_ERA_START_DATE\n**********/\nSELECT \n num_violated_rows, \n CASE \n WHEN denominator.num_rows = 0 THEN 0 \n ELSE 1.0*num_violated_rows/denominator.num_rows \n END AS pct_violated_rows, \n denominator.num_rows AS num_denominator_rows\nFROM\n(\n SELECT \n COUNT(violated_rows.violating_field) AS num_violated_rows\n FROM\n (\n /*violatedRowsBegin*/\n SELECT \n 'DRUG_ERA.DRUG_ERA_START_DATE' AS violating_field, \n cdmTable.*\n FROM main.DRUG_ERA cdmTable\n JOIN main.person p \n ON cdmTable.person_id = p.person_id\n WHERE cdmTable.DRUG_ERA_START_DATE IS NOT NULL AND \n CAST(STRFTIME('%s', SUBSTR(CAST(cdmTable.DRUG_ERA_START_DATE AS TEXT), 1, 4) || '-' || SUBSTR(CAST(cdmTable.DRUG_ERA_START_DATE AS TEXT), 5, 2) || '-' || SUBSTR(CAST(cdmTable.DRUG_ERA_START_DATE AS TEXT), 7)) AS REAL) < COALESCE(\n CAST(STRFTIME('%s', SUBSTR(CAST(p.birth_datetime AS TEXT), 1, 4) || '-' || SUBSTR(CAST(p.birth_datetime AS TEXT), 5, 2) || '-' || SUBSTR(CAST(p.birth_datetime AS TEXT), 7)) AS REAL), \n CAST(STRFTIME('%s', SUBSTR(CAST(p.year_of_birth || COALESCE(\n SUBSTR(CAST('0' || CAST(p.month_of_birth AS TEXT) AS TEXT),-2),\n '01'\n ) || COALESCE(\n SUBSTR(CAST('0' || CAST(p.day_of_birth AS TEXT) AS TEXT),-2),\n '01'\n )\n AS TEXT), 1, 4) || '-' || SUBSTR(CAST(p.year_of_birth || COALESCE(\n SUBSTR(CAST('0' || CAST(p.month_of_birth AS TEXT) AS TEXT),-2),\n '01'\n ) || COALESCE(\n SUBSTR(CAST('0' || CAST(p.day_of_birth AS TEXT) AS TEXT),-2),\n '01'\n )\n AS TEXT), 5, 2) || '-' || SUBSTR(CAST(p.year_of_birth || COALESCE(\n SUBSTR(CAST('0' || CAST(p.month_of_birth AS TEXT) AS TEXT),-2),\n '01'\n ) || COALESCE(\n SUBSTR(CAST('0' || CAST(p.day_of_birth AS TEXT) AS TEXT),-2),\n '01'\n )\n AS TEXT), 7)) AS REAL)\n )\n /*violatedRowsEnd*/\n ) violated_rows\n) violated_row_count,\n(\n SELECT \n COUNT(*) AS num_rows\n FROM main.DRUG_ERA cdmTable\n WHERE cdmTable.DRUG_ERA_START_DATE IS NOT NULL\n) denominator\n;\n
1.16 /*********\nPLAUSIBLE_AFTER_BIRTH\nChecks that all events happen after birth (PLAUSIBLE_AFTER_BIRTH == Yes)\nBirthdate is either birth_datetime or composed from year_of_birth, month_of_birth, day_of_birth (taking 1st month/1st day if missing).\nDenominator is number of events with a non-null date.\nParameters used in this template:\nschema = main\ncdmDatabaseSchema = main\ncdmTableName = DRUG_ERA\ncdmFieldName = DRUG_ERA_END_DATE\n**********/\nSELECT \n num_violated_rows, \n CASE \n WHEN denominator.num_rows = 0 THEN 0 \n ELSE 1.0*num_violated_rows/denominator.num_rows \n END AS pct_violated_rows, \n denominator.num_rows AS num_denominator_rows\nFROM\n(\n SELECT \n COUNT(violated_rows.violating_field) AS num_violated_rows\n FROM\n (\n /*violatedRowsBegin*/\n SELECT \n 'DRUG_ERA.DRUG_ERA_END_DATE' AS violating_field, \n cdmTable.*\n FROM main.DRUG_ERA cdmTable\n JOIN main.person p \n ON cdmTable.person_id = p.person_id\n WHERE cdmTable.DRUG_ERA_END_DATE IS NOT NULL AND \n CAST(STRFTIME('%s', SUBSTR(CAST(cdmTable.DRUG_ERA_END_DATE AS TEXT), 1, 4) || '-' || SUBSTR(CAST(cdmTable.DRUG_ERA_END_DATE AS TEXT), 5, 2) || '-' || SUBSTR(CAST(cdmTable.DRUG_ERA_END_DATE AS TEXT), 7)) AS REAL) < COALESCE(\n CAST(STRFTIME('%s', SUBSTR(CAST(p.birth_datetime AS TEXT), 1, 4) || '-' || SUBSTR(CAST(p.birth_datetime AS TEXT), 5, 2) || '-' || SUBSTR(CAST(p.birth_datetime AS TEXT), 7)) AS REAL), \n CAST(STRFTIME('%s', SUBSTR(CAST(p.year_of_birth || COALESCE(\n SUBSTR(CAST('0' || CAST(p.month_of_birth AS TEXT) AS TEXT),-2),\n '01'\n ) || COALESCE(\n SUBSTR(CAST('0' || CAST(p.day_of_birth AS TEXT) AS TEXT),-2),\n '01'\n )\n AS TEXT), 1, 4) || '-' || SUBSTR(CAST(p.year_of_birth || COALESCE(\n SUBSTR(CAST('0' || CAST(p.month_of_birth AS TEXT) AS TEXT),-2),\n '01'\n ) || COALESCE(\n SUBSTR(CAST('0' || CAST(p.day_of_birth AS TEXT) AS TEXT),-2),\n '01'\n )\n AS TEXT), 5, 2) || '-' || SUBSTR(CAST(p.year_of_birth || COALESCE(\n SUBSTR(CAST('0' || CAST(p.month_of_birth AS TEXT) AS TEXT),-2),\n '01'\n ) || COALESCE(\n SUBSTR(CAST('0' || CAST(p.day_of_birth AS TEXT) AS TEXT),-2),\n '01'\n )\n AS TEXT), 7)) AS REAL)\n )\n /*violatedRowsEnd*/\n ) violated_rows\n) violated_row_count,\n(\n SELECT \n COUNT(*) AS num_rows\n FROM main.DRUG_ERA cdmTable\n WHERE cdmTable.DRUG_ERA_END_DATE IS NOT NULL\n) denominator\n;\n
1.17 /*********\nPLAUSIBLE_AFTER_BIRTH\nChecks that all events happen after birth (PLAUSIBLE_AFTER_BIRTH == Yes)\nBirthdate is either birth_datetime or composed from year_of_birth, month_of_birth, day_of_birth (taking 1st month/1st day if missing).\nDenominator is number of events with a non-null date.\nParameters used in this template:\nschema = main\ncdmDatabaseSchema = main\ncdmTableName = DRUG_EXPOSURE\ncdmFieldName = DRUG_EXPOSURE_START_DATE\n**********/\nSELECT \n num_violated_rows, \n CASE \n WHEN denominator.num_rows = 0 THEN 0 \n ELSE 1.0*num_violated_rows/denominator.num_rows \n END AS pct_violated_rows, \n denominator.num_rows AS num_denominator_rows\nFROM\n(\n SELECT \n COUNT(violated_rows.violating_field) AS num_violated_rows\n FROM\n (\n /*violatedRowsBegin*/\n SELECT \n 'DRUG_EXPOSURE.DRUG_EXPOSURE_START_DATE' AS violating_field, \n cdmTable.*\n FROM main.DRUG_EXPOSURE cdmTable\n JOIN main.person p \n ON cdmTable.person_id = p.person_id\n WHERE cdmTable.DRUG_EXPOSURE_START_DATE IS NOT NULL AND \n CAST(STRFTIME('%s', SUBSTR(CAST(cdmTable.DRUG_EXPOSURE_START_DATE AS TEXT), 1, 4) || '-' || SUBSTR(CAST(cdmTable.DRUG_EXPOSURE_START_DATE AS TEXT), 5, 2) || '-' || SUBSTR(CAST(cdmTable.DRUG_EXPOSURE_START_DATE AS TEXT), 7)) AS REAL) < COALESCE(\n CAST(STRFTIME('%s', SUBSTR(CAST(p.birth_datetime AS TEXT), 1, 4) || '-' || SUBSTR(CAST(p.birth_datetime AS TEXT), 5, 2) || '-' || SUBSTR(CAST(p.birth_datetime AS TEXT), 7)) AS REAL), \n CAST(STRFTIME('%s', SUBSTR(CAST(p.year_of_birth || COALESCE(\n SUBSTR(CAST('0' || CAST(p.month_of_birth AS TEXT) AS TEXT),-2),\n '01'\n ) || COALESCE(\n SUBSTR(CAST('0' || CAST(p.day_of_birth AS TEXT) AS TEXT),-2),\n '01'\n )\n AS TEXT), 1, 4) || '-' || SUBSTR(CAST(p.year_of_birth || COALESCE(\n SUBSTR(CAST('0' || CAST(p.month_of_birth AS TEXT) AS TEXT),-2),\n '01'\n ) || COALESCE(\n SUBSTR(CAST('0' || CAST(p.day_of_birth AS TEXT) AS TEXT),-2),\n '01'\n )\n AS TEXT), 5, 2) || '-' || SUBSTR(CAST(p.year_of_birth || COALESCE(\n SUBSTR(CAST('0' || CAST(p.month_of_birth AS TEXT) AS TEXT),-2),\n '01'\n ) || COALESCE(\n SUBSTR(CAST('0' || CAST(p.day_of_birth AS TEXT) AS TEXT),-2),\n '01'\n )\n AS TEXT), 7)) AS REAL)\n )\n /*violatedRowsEnd*/\n ) violated_rows\n) violated_row_count,\n(\n SELECT \n COUNT(*) AS num_rows\n FROM main.DRUG_EXPOSURE cdmTable\n WHERE cdmTable.DRUG_EXPOSURE_START_DATE IS NOT NULL\n) denominator\n;\n
1.18 /*********\nPLAUSIBLE_AFTER_BIRTH\nChecks that all events happen after birth (PLAUSIBLE_AFTER_BIRTH == Yes)\nBirthdate is either birth_datetime or composed from year_of_birth, month_of_birth, day_of_birth (taking 1st month/1st day if missing).\nDenominator is number of events with a non-null date.\nParameters used in this template:\nschema = main\ncdmDatabaseSchema = main\ncdmTableName = DRUG_EXPOSURE\ncdmFieldName = DRUG_EXPOSURE_START_DATETIME\n**********/\nSELECT \n num_violated_rows, \n CASE \n WHEN denominator.num_rows = 0 THEN 0 \n ELSE 1.0*num_violated_rows/denominator.num_rows \n END AS pct_violated_rows, \n denominator.num_rows AS num_denominator_rows\nFROM\n(\n SELECT \n COUNT(violated_rows.violating_field) AS num_violated_rows\n FROM\n (\n /*violatedRowsBegin*/\n SELECT \n 'DRUG_EXPOSURE.DRUG_EXPOSURE_START_DATETIME' AS violating_field, \n cdmTable.*\n FROM main.DRUG_EXPOSURE cdmTable\n JOIN main.person p \n ON cdmTable.person_id = p.person_id\n WHERE cdmTable.DRUG_EXPOSURE_START_DATETIME IS NOT NULL AND \n CAST(STRFTIME('%s', SUBSTR(CAST(cdmTable.DRUG_EXPOSURE_START_DATETIME AS TEXT), 1, 4) || '-' || SUBSTR(CAST(cdmTable.DRUG_EXPOSURE_START_DATETIME AS TEXT), 5, 2) || '-' || SUBSTR(CAST(cdmTable.DRUG_EXPOSURE_START_DATETIME AS TEXT), 7)) AS REAL) < COALESCE(\n CAST(STRFTIME('%s', SUBSTR(CAST(p.birth_datetime AS TEXT), 1, 4) || '-' || SUBSTR(CAST(p.birth_datetime AS TEXT), 5, 2) || '-' || SUBSTR(CAST(p.birth_datetime AS TEXT), 7)) AS REAL), \n CAST(STRFTIME('%s', SUBSTR(CAST(p.year_of_birth || COALESCE(\n SUBSTR(CAST('0' || CAST(p.month_of_birth AS TEXT) AS TEXT),-2),\n '01'\n ) || COALESCE(\n SUBSTR(CAST('0' || CAST(p.day_of_birth AS TEXT) AS TEXT),-2),\n '01'\n )\n AS TEXT), 1, 4) || '-' || SUBSTR(CAST(p.year_of_birth || COALESCE(\n SUBSTR(CAST('0' || CAST(p.month_of_birth AS TEXT) AS TEXT),-2),\n '01'\n ) || COALESCE(\n SUBSTR(CAST('0' || CAST(p.day_of_birth AS TEXT) AS TEXT),-2),\n '01'\n )\n AS TEXT), 5, 2) || '-' || SUBSTR(CAST(p.year_of_birth || COALESCE(\n SUBSTR(CAST('0' || CAST(p.month_of_birth AS TEXT) AS TEXT),-2),\n '01'\n ) || COALESCE(\n SUBSTR(CAST('0' || CAST(p.day_of_birth AS TEXT) AS TEXT),-2),\n '01'\n )\n AS TEXT), 7)) AS REAL)\n )\n /*violatedRowsEnd*/\n ) violated_rows\n) violated_row_count,\n(\n SELECT \n COUNT(*) AS num_rows\n FROM main.DRUG_EXPOSURE cdmTable\n WHERE cdmTable.DRUG_EXPOSURE_START_DATETIME IS NOT NULL\n) denominator\n;\n
1.19 /*********\nPLAUSIBLE_AFTER_BIRTH\nChecks that all events happen after birth (PLAUSIBLE_AFTER_BIRTH == Yes)\nBirthdate is either birth_datetime or composed from year_of_birth, month_of_birth, day_of_birth (taking 1st month/1st day if missing).\nDenominator is number of events with a non-null date.\nParameters used in this template:\nschema = main\ncdmDatabaseSchema = main\ncdmTableName = DRUG_EXPOSURE\ncdmFieldName = DRUG_EXPOSURE_END_DATE\n**********/\nSELECT \n num_violated_rows, \n CASE \n WHEN denominator.num_rows = 0 THEN 0 \n ELSE 1.0*num_violated_rows/denominator.num_rows \n END AS pct_violated_rows, \n denominator.num_rows AS num_denominator_rows\nFROM\n(\n SELECT \n COUNT(violated_rows.violating_field) AS num_violated_rows\n FROM\n (\n /*violatedRowsBegin*/\n SELECT \n 'DRUG_EXPOSURE.DRUG_EXPOSURE_END_DATE' AS violating_field, \n cdmTable.*\n FROM main.DRUG_EXPOSURE cdmTable\n JOIN main.person p \n ON cdmTable.person_id = p.person_id\n WHERE cdmTable.DRUG_EXPOSURE_END_DATE IS NOT NULL AND \n CAST(STRFTIME('%s', SUBSTR(CAST(cdmTable.DRUG_EXPOSURE_END_DATE AS TEXT), 1, 4) || '-' || SUBSTR(CAST(cdmTable.DRUG_EXPOSURE_END_DATE AS TEXT), 5, 2) || '-' || SUBSTR(CAST(cdmTable.DRUG_EXPOSURE_END_DATE AS TEXT), 7)) AS REAL) < COALESCE(\n CAST(STRFTIME('%s', SUBSTR(CAST(p.birth_datetime AS TEXT), 1, 4) || '-' || SUBSTR(CAST(p.birth_datetime AS TEXT), 5, 2) || '-' || SUBSTR(CAST(p.birth_datetime AS TEXT), 7)) AS REAL), \n CAST(STRFTIME('%s', SUBSTR(CAST(p.year_of_birth || COALESCE(\n SUBSTR(CAST('0' || CAST(p.month_of_birth AS TEXT) AS TEXT),-2),\n '01'\n ) || COALESCE(\n SUBSTR(CAST('0' || CAST(p.day_of_birth AS TEXT) AS TEXT),-2),\n '01'\n )\n AS TEXT), 1, 4) || '-' || SUBSTR(CAST(p.year_of_birth || COALESCE(\n SUBSTR(CAST('0' || CAST(p.month_of_birth AS TEXT) AS TEXT),-2),\n '01'\n ) || COALESCE(\n SUBSTR(CAST('0' || CAST(p.day_of_birth AS TEXT) AS TEXT),-2),\n '01'\n )\n AS TEXT), 5, 2) || '-' || SUBSTR(CAST(p.year_of_birth || COALESCE(\n SUBSTR(CAST('0' || CAST(p.month_of_birth AS TEXT) AS TEXT),-2),\n '01'\n ) || COALESCE(\n SUBSTR(CAST('0' || CAST(p.day_of_birth AS TEXT) AS TEXT),-2),\n '01'\n )\n AS TEXT), 7)) AS REAL)\n )\n /*violatedRowsEnd*/\n ) violated_rows\n) violated_row_count,\n(\n SELECT \n COUNT(*) AS num_rows\n FROM main.DRUG_EXPOSURE cdmTable\n WHERE cdmTable.DRUG_EXPOSURE_END_DATE IS NOT NULL\n) denominator\n;\n
1.20 /*********\nPLAUSIBLE_AFTER_BIRTH\nChecks that all events happen after birth (PLAUSIBLE_AFTER_BIRTH == Yes)\nBirthdate is either birth_datetime or composed from year_of_birth, month_of_birth, day_of_birth (taking 1st month/1st day if missing).\nDenominator is number of events with a non-null date.\nParameters used in this template:\nschema = main\ncdmDatabaseSchema = main\ncdmTableName = DRUG_EXPOSURE\ncdmFieldName = DRUG_EXPOSURE_END_DATETIME\n**********/\nSELECT \n num_violated_rows, \n CASE \n WHEN denominator.num_rows = 0 THEN 0 \n ELSE 1.0*num_violated_rows/denominator.num_rows \n END AS pct_violated_rows, \n denominator.num_rows AS num_denominator_rows\nFROM\n(\n SELECT \n COUNT(violated_rows.violating_field) AS num_violated_rows\n FROM\n (\n /*violatedRowsBegin*/\n SELECT \n 'DRUG_EXPOSURE.DRUG_EXPOSURE_END_DATETIME' AS violating_field, \n cdmTable.*\n FROM main.DRUG_EXPOSURE cdmTable\n JOIN main.person p \n ON cdmTable.person_id = p.person_id\n WHERE cdmTable.DRUG_EXPOSURE_END_DATETIME IS NOT NULL AND \n CAST(STRFTIME('%s', SUBSTR(CAST(cdmTable.DRUG_EXPOSURE_END_DATETIME AS TEXT), 1, 4) || '-' || SUBSTR(CAST(cdmTable.DRUG_EXPOSURE_END_DATETIME AS TEXT), 5, 2) || '-' || SUBSTR(CAST(cdmTable.DRUG_EXPOSURE_END_DATETIME AS TEXT), 7)) AS REAL) < COALESCE(\n CAST(STRFTIME('%s', SUBSTR(CAST(p.birth_datetime AS TEXT), 1, 4) || '-' || SUBSTR(CAST(p.birth_datetime AS TEXT), 5, 2) || '-' || SUBSTR(CAST(p.birth_datetime AS TEXT), 7)) AS REAL), \n CAST(STRFTIME('%s', SUBSTR(CAST(p.year_of_birth || COALESCE(\n SUBSTR(CAST('0' || CAST(p.month_of_birth AS TEXT) AS TEXT),-2),\n '01'\n ) || COALESCE(\n SUBSTR(CAST('0' || CAST(p.day_of_birth AS TEXT) AS TEXT),-2),\n '01'\n )\n AS TEXT), 1, 4) || '-' || SUBSTR(CAST(p.year_of_birth || COALESCE(\n SUBSTR(CAST('0' || CAST(p.month_of_birth AS TEXT) AS TEXT),-2),\n '01'\n ) || COALESCE(\n SUBSTR(CAST('0' || CAST(p.day_of_birth AS TEXT) AS TEXT),-2),\n '01'\n )\n AS TEXT), 5, 2) || '-' || SUBSTR(CAST(p.year_of_birth || COALESCE(\n SUBSTR(CAST('0' || CAST(p.month_of_birth AS TEXT) AS TEXT),-2),\n '01'\n ) || COALESCE(\n SUBSTR(CAST('0' || CAST(p.day_of_birth AS TEXT) AS TEXT),-2),\n '01'\n )\n AS TEXT), 7)) AS REAL)\n )\n /*violatedRowsEnd*/\n ) violated_rows\n) violated_row_count,\n(\n SELECT \n COUNT(*) AS num_rows\n FROM main.DRUG_EXPOSURE cdmTable\n WHERE cdmTable.DRUG_EXPOSURE_END_DATETIME IS NOT NULL\n) denominator\n;\n
1.21 /*********\nPLAUSIBLE_AFTER_BIRTH\nChecks that all events happen after birth (PLAUSIBLE_AFTER_BIRTH == Yes)\nBirthdate is either birth_datetime or composed from year_of_birth, month_of_birth, day_of_birth (taking 1st month/1st day if missing).\nDenominator is number of events with a non-null date.\nParameters used in this template:\nschema = main\ncdmDatabaseSchema = main\ncdmTableName = DRUG_EXPOSURE\ncdmFieldName = VERBATIM_END_DATE\n**********/\nSELECT \n num_violated_rows, \n CASE \n WHEN denominator.num_rows = 0 THEN 0 \n ELSE 1.0*num_violated_rows/denominator.num_rows \n END AS pct_violated_rows, \n denominator.num_rows AS num_denominator_rows\nFROM\n(\n SELECT \n COUNT(violated_rows.violating_field) AS num_violated_rows\n FROM\n (\n /*violatedRowsBegin*/\n SELECT \n 'DRUG_EXPOSURE.VERBATIM_END_DATE' AS violating_field, \n cdmTable.*\n FROM main.DRUG_EXPOSURE cdmTable\n JOIN main.person p \n ON cdmTable.person_id = p.person_id\n WHERE cdmTable.VERBATIM_END_DATE IS NOT NULL AND \n CAST(STRFTIME('%s', SUBSTR(CAST(cdmTable.VERBATIM_END_DATE AS TEXT), 1, 4) || '-' || SUBSTR(CAST(cdmTable.VERBATIM_END_DATE AS TEXT), 5, 2) || '-' || SUBSTR(CAST(cdmTable.VERBATIM_END_DATE AS TEXT), 7)) AS REAL) < COALESCE(\n CAST(STRFTIME('%s', SUBSTR(CAST(p.birth_datetime AS TEXT), 1, 4) || '-' || SUBSTR(CAST(p.birth_datetime AS TEXT), 5, 2) || '-' || SUBSTR(CAST(p.birth_datetime AS TEXT), 7)) AS REAL), \n CAST(STRFTIME('%s', SUBSTR(CAST(p.year_of_birth || COALESCE(\n SUBSTR(CAST('0' || CAST(p.month_of_birth AS TEXT) AS TEXT),-2),\n '01'\n ) || COALESCE(\n SUBSTR(CAST('0' || CAST(p.day_of_birth AS TEXT) AS TEXT),-2),\n '01'\n )\n AS TEXT), 1, 4) || '-' || SUBSTR(CAST(p.year_of_birth || COALESCE(\n SUBSTR(CAST('0' || CAST(p.month_of_birth AS TEXT) AS TEXT),-2),\n '01'\n ) || COALESCE(\n SUBSTR(CAST('0' || CAST(p.day_of_birth AS TEXT) AS TEXT),-2),\n '01'\n )\n AS TEXT), 5, 2) || '-' || SUBSTR(CAST(p.year_of_birth || COALESCE(\n SUBSTR(CAST('0' || CAST(p.month_of_birth AS TEXT) AS TEXT),-2),\n '01'\n ) || COALESCE(\n SUBSTR(CAST('0' || CAST(p.day_of_birth AS TEXT) AS TEXT),-2),\n '01'\n )\n AS TEXT), 7)) AS REAL)\n )\n /*violatedRowsEnd*/\n ) violated_rows\n) violated_row_count,\n(\n SELECT \n COUNT(*) AS num_rows\n FROM main.DRUG_EXPOSURE cdmTable\n WHERE cdmTable.VERBATIM_END_DATE IS NOT NULL\n) denominator\n;\n
1.22 /*********\nPLAUSIBLE_AFTER_BIRTH\nChecks that all events happen after birth (PLAUSIBLE_AFTER_BIRTH == Yes)\nBirthdate is either birth_datetime or composed from year_of_birth, month_of_birth, day_of_birth (taking 1st month/1st day if missing).\nDenominator is number of events with a non-null date.\nParameters used in this template:\nschema = main\ncdmDatabaseSchema = main\ncdmTableName = MEASUREMENT\ncdmFieldName = MEASUREMENT_DATE\n**********/\nSELECT \n num_violated_rows, \n CASE \n WHEN denominator.num_rows = 0 THEN 0 \n ELSE 1.0*num_violated_rows/denominator.num_rows \n END AS pct_violated_rows, \n denominator.num_rows AS num_denominator_rows\nFROM\n(\n SELECT \n COUNT(violated_rows.violating_field) AS num_violated_rows\n FROM\n (\n /*violatedRowsBegin*/\n SELECT \n 'MEASUREMENT.MEASUREMENT_DATE' AS violating_field, \n cdmTable.*\n FROM main.MEASUREMENT cdmTable\n JOIN main.person p \n ON cdmTable.person_id = p.person_id\n WHERE cdmTable.MEASUREMENT_DATE IS NOT NULL AND \n CAST(STRFTIME('%s', SUBSTR(CAST(cdmTable.MEASUREMENT_DATE AS TEXT), 1, 4) || '-' || SUBSTR(CAST(cdmTable.MEASUREMENT_DATE AS TEXT), 5, 2) || '-' || SUBSTR(CAST(cdmTable.MEASUREMENT_DATE AS TEXT), 7)) AS REAL) < COALESCE(\n CAST(STRFTIME('%s', SUBSTR(CAST(p.birth_datetime AS TEXT), 1, 4) || '-' || SUBSTR(CAST(p.birth_datetime AS TEXT), 5, 2) || '-' || SUBSTR(CAST(p.birth_datetime AS TEXT), 7)) AS REAL), \n CAST(STRFTIME('%s', SUBSTR(CAST(p.year_of_birth || COALESCE(\n SUBSTR(CAST('0' || CAST(p.month_of_birth AS TEXT) AS TEXT),-2),\n '01'\n ) || COALESCE(\n SUBSTR(CAST('0' || CAST(p.day_of_birth AS TEXT) AS TEXT),-2),\n '01'\n )\n AS TEXT), 1, 4) || '-' || SUBSTR(CAST(p.year_of_birth || COALESCE(\n SUBSTR(CAST('0' || CAST(p.month_of_birth AS TEXT) AS TEXT),-2),\n '01'\n ) || COALESCE(\n SUBSTR(CAST('0' || CAST(p.day_of_birth AS TEXT) AS TEXT),-2),\n '01'\n )\n AS TEXT), 5, 2) || '-' || SUBSTR(CAST(p.year_of_birth || COALESCE(\n SUBSTR(CAST('0' || CAST(p.month_of_birth AS TEXT) AS TEXT),-2),\n '01'\n ) || COALESCE(\n SUBSTR(CAST('0' || CAST(p.day_of_birth AS TEXT) AS TEXT),-2),\n '01'\n )\n AS TEXT), 7)) AS REAL)\n )\n /*violatedRowsEnd*/\n ) violated_rows\n) violated_row_count,\n(\n SELECT \n COUNT(*) AS num_rows\n FROM main.MEASUREMENT cdmTable\n WHERE cdmTable.MEASUREMENT_DATE IS NOT NULL\n) denominator\n;\n
1.23 /*********\nPLAUSIBLE_AFTER_BIRTH\nChecks that all events happen after birth (PLAUSIBLE_AFTER_BIRTH == Yes)\nBirthdate is either birth_datetime or composed from year_of_birth, month_of_birth, day_of_birth (taking 1st month/1st day if missing).\nDenominator is number of events with a non-null date.\nParameters used in this template:\nschema = main\ncdmDatabaseSchema = main\ncdmTableName = MEASUREMENT\ncdmFieldName = MEASUREMENT_DATETIME\n**********/\nSELECT \n num_violated_rows, \n CASE \n WHEN denominator.num_rows = 0 THEN 0 \n ELSE 1.0*num_violated_rows/denominator.num_rows \n END AS pct_violated_rows, \n denominator.num_rows AS num_denominator_rows\nFROM\n(\n SELECT \n COUNT(violated_rows.violating_field) AS num_violated_rows\n FROM\n (\n /*violatedRowsBegin*/\n SELECT \n 'MEASUREMENT.MEASUREMENT_DATETIME' AS violating_field, \n cdmTable.*\n FROM main.MEASUREMENT cdmTable\n JOIN main.person p \n ON cdmTable.person_id = p.person_id\n WHERE cdmTable.MEASUREMENT_DATETIME IS NOT NULL AND \n CAST(STRFTIME('%s', SUBSTR(CAST(cdmTable.MEASUREMENT_DATETIME AS TEXT), 1, 4) || '-' || SUBSTR(CAST(cdmTable.MEASUREMENT_DATETIME AS TEXT), 5, 2) || '-' || SUBSTR(CAST(cdmTable.MEASUREMENT_DATETIME AS TEXT), 7)) AS REAL) < COALESCE(\n CAST(STRFTIME('%s', SUBSTR(CAST(p.birth_datetime AS TEXT), 1, 4) || '-' || SUBSTR(CAST(p.birth_datetime AS TEXT), 5, 2) || '-' || SUBSTR(CAST(p.birth_datetime AS TEXT), 7)) AS REAL), \n CAST(STRFTIME('%s', SUBSTR(CAST(p.year_of_birth || COALESCE(\n SUBSTR(CAST('0' || CAST(p.month_of_birth AS TEXT) AS TEXT),-2),\n '01'\n ) || COALESCE(\n SUBSTR(CAST('0' || CAST(p.day_of_birth AS TEXT) AS TEXT),-2),\n '01'\n )\n AS TEXT), 1, 4) || '-' || SUBSTR(CAST(p.year_of_birth || COALESCE(\n SUBSTR(CAST('0' || CAST(p.month_of_birth AS TEXT) AS TEXT),-2),\n '01'\n ) || COALESCE(\n SUBSTR(CAST('0' || CAST(p.day_of_birth AS TEXT) AS TEXT),-2),\n '01'\n )\n AS TEXT), 5, 2) || '-' || SUBSTR(CAST(p.year_of_birth || COALESCE(\n SUBSTR(CAST('0' || CAST(p.month_of_birth AS TEXT) AS TEXT),-2),\n '01'\n ) || COALESCE(\n SUBSTR(CAST('0' || CAST(p.day_of_birth AS TEXT) AS TEXT),-2),\n '01'\n )\n AS TEXT), 7)) AS REAL)\n )\n /*violatedRowsEnd*/\n ) violated_rows\n) violated_row_count,\n(\n SELECT \n COUNT(*) AS num_rows\n FROM main.MEASUREMENT cdmTable\n WHERE cdmTable.MEASUREMENT_DATETIME IS NOT NULL\n) denominator\n;\n
1.24 /*********\nPLAUSIBLE_AFTER_BIRTH\nChecks that all events happen after birth (PLAUSIBLE_AFTER_BIRTH == Yes)\nBirthdate is either birth_datetime or composed from year_of_birth, month_of_birth, day_of_birth (taking 1st month/1st day if missing).\nDenominator is number of events with a non-null date.\nParameters used in this template:\nschema = main\ncdmDatabaseSchema = main\ncdmTableName = NOTE\ncdmFieldName = NOTE_DATE\n**********/\nSELECT \n num_violated_rows, \n CASE \n WHEN denominator.num_rows = 0 THEN 0 \n ELSE 1.0*num_violated_rows/denominator.num_rows \n END AS pct_violated_rows, \n denominator.num_rows AS num_denominator_rows\nFROM\n(\n SELECT \n COUNT(violated_rows.violating_field) AS num_violated_rows\n FROM\n (\n /*violatedRowsBegin*/\n SELECT \n 'NOTE.NOTE_DATE' AS violating_field, \n cdmTable.*\n FROM main.NOTE cdmTable\n JOIN main.person p \n ON cdmTable.person_id = p.person_id\n WHERE cdmTable.NOTE_DATE IS NOT NULL AND \n CAST(STRFTIME('%s', SUBSTR(CAST(cdmTable.NOTE_DATE AS TEXT), 1, 4) || '-' || SUBSTR(CAST(cdmTable.NOTE_DATE AS TEXT), 5, 2) || '-' || SUBSTR(CAST(cdmTable.NOTE_DATE AS TEXT), 7)) AS REAL) < COALESCE(\n CAST(STRFTIME('%s', SUBSTR(CAST(p.birth_datetime AS TEXT), 1, 4) || '-' || SUBSTR(CAST(p.birth_datetime AS TEXT), 5, 2) || '-' || SUBSTR(CAST(p.birth_datetime AS TEXT), 7)) AS REAL), \n CAST(STRFTIME('%s', SUBSTR(CAST(p.year_of_birth || COALESCE(\n SUBSTR(CAST('0' || CAST(p.month_of_birth AS TEXT) AS TEXT),-2),\n '01'\n ) || COALESCE(\n SUBSTR(CAST('0' || CAST(p.day_of_birth AS TEXT) AS TEXT),-2),\n '01'\n )\n AS TEXT), 1, 4) || '-' || SUBSTR(CAST(p.year_of_birth || COALESCE(\n SUBSTR(CAST('0' || CAST(p.month_of_birth AS TEXT) AS TEXT),-2),\n '01'\n ) || COALESCE(\n SUBSTR(CAST('0' || CAST(p.day_of_birth AS TEXT) AS TEXT),-2),\n '01'\n )\n AS TEXT), 5, 2) || '-' || SUBSTR(CAST(p.year_of_birth || COALESCE(\n SUBSTR(CAST('0' || CAST(p.month_of_birth AS TEXT) AS TEXT),-2),\n '01'\n ) || COALESCE(\n SUBSTR(CAST('0' || CAST(p.day_of_birth AS TEXT) AS TEXT),-2),\n '01'\n )\n AS TEXT), 7)) AS REAL)\n )\n /*violatedRowsEnd*/\n ) violated_rows\n) violated_row_count,\n(\n SELECT \n COUNT(*) AS num_rows\n FROM main.NOTE cdmTable\n WHERE cdmTable.NOTE_DATE IS NOT NULL\n) denominator\n;\n
1.25 /*********\nPLAUSIBLE_AFTER_BIRTH\nChecks that all events happen after birth (PLAUSIBLE_AFTER_BIRTH == Yes)\nBirthdate is either birth_datetime or composed from year_of_birth, month_of_birth, day_of_birth (taking 1st month/1st day if missing).\nDenominator is number of events with a non-null date.\nParameters used in this template:\nschema = main\ncdmDatabaseSchema = main\ncdmTableName = NOTE\ncdmFieldName = NOTE_DATETIME\n**********/\nSELECT \n num_violated_rows, \n CASE \n WHEN denominator.num_rows = 0 THEN 0 \n ELSE 1.0*num_violated_rows/denominator.num_rows \n END AS pct_violated_rows, \n denominator.num_rows AS num_denominator_rows\nFROM\n(\n SELECT \n COUNT(violated_rows.violating_field) AS num_violated_rows\n FROM\n (\n /*violatedRowsBegin*/\n SELECT \n 'NOTE.NOTE_DATETIME' AS violating_field, \n cdmTable.*\n FROM main.NOTE cdmTable\n JOIN main.person p \n ON cdmTable.person_id = p.person_id\n WHERE cdmTable.NOTE_DATETIME IS NOT NULL AND \n CAST(STRFTIME('%s', SUBSTR(CAST(cdmTable.NOTE_DATETIME AS TEXT), 1, 4) || '-' || SUBSTR(CAST(cdmTable.NOTE_DATETIME AS TEXT), 5, 2) || '-' || SUBSTR(CAST(cdmTable.NOTE_DATETIME AS TEXT), 7)) AS REAL) < COALESCE(\n CAST(STRFTIME('%s', SUBSTR(CAST(p.birth_datetime AS TEXT), 1, 4) || '-' || SUBSTR(CAST(p.birth_datetime AS TEXT), 5, 2) || '-' || SUBSTR(CAST(p.birth_datetime AS TEXT), 7)) AS REAL), \n CAST(STRFTIME('%s', SUBSTR(CAST(p.year_of_birth || COALESCE(\n SUBSTR(CAST('0' || CAST(p.month_of_birth AS TEXT) AS TEXT),-2),\n '01'\n ) || COALESCE(\n SUBSTR(CAST('0' || CAST(p.day_of_birth AS TEXT) AS TEXT),-2),\n '01'\n )\n AS TEXT), 1, 4) || '-' || SUBSTR(CAST(p.year_of_birth || COALESCE(\n SUBSTR(CAST('0' || CAST(p.month_of_birth AS TEXT) AS TEXT),-2),\n '01'\n ) || COALESCE(\n SUBSTR(CAST('0' || CAST(p.day_of_birth AS TEXT) AS TEXT),-2),\n '01'\n )\n AS TEXT), 5, 2) || '-' || SUBSTR(CAST(p.year_of_birth || COALESCE(\n SUBSTR(CAST('0' || CAST(p.month_of_birth AS TEXT) AS TEXT),-2),\n '01'\n ) || COALESCE(\n SUBSTR(CAST('0' || CAST(p.day_of_birth AS TEXT) AS TEXT),-2),\n '01'\n )\n AS TEXT), 7)) AS REAL)\n )\n /*violatedRowsEnd*/\n ) violated_rows\n) violated_row_count,\n(\n SELECT \n COUNT(*) AS num_rows\n FROM main.NOTE cdmTable\n WHERE cdmTable.NOTE_DATETIME IS NOT NULL\n) denominator\n;\n
1.26 /*********\nPLAUSIBLE_AFTER_BIRTH\nChecks that all events happen after birth (PLAUSIBLE_AFTER_BIRTH == Yes)\nBirthdate is either birth_datetime or composed from year_of_birth, month_of_birth, day_of_birth (taking 1st month/1st day if missing).\nDenominator is number of events with a non-null date.\nParameters used in this template:\nschema = main\ncdmDatabaseSchema = main\ncdmTableName = OBSERVATION\ncdmFieldName = OBSERVATION_DATE\n**********/\nSELECT \n num_violated_rows, \n CASE \n WHEN denominator.num_rows = 0 THEN 0 \n ELSE 1.0*num_violated_rows/denominator.num_rows \n END AS pct_violated_rows, \n denominator.num_rows AS num_denominator_rows\nFROM\n(\n SELECT \n COUNT(violated_rows.violating_field) AS num_violated_rows\n FROM\n (\n /*violatedRowsBegin*/\n SELECT \n 'OBSERVATION.OBSERVATION_DATE' AS violating_field, \n cdmTable.*\n FROM main.OBSERVATION cdmTable\n JOIN main.person p \n ON cdmTable.person_id = p.person_id\n WHERE cdmTable.OBSERVATION_DATE IS NOT NULL AND \n CAST(STRFTIME('%s', SUBSTR(CAST(cdmTable.OBSERVATION_DATE AS TEXT), 1, 4) || '-' || SUBSTR(CAST(cdmTable.OBSERVATION_DATE AS TEXT), 5, 2) || '-' || SUBSTR(CAST(cdmTable.OBSERVATION_DATE AS TEXT), 7)) AS REAL) < COALESCE(\n CAST(STRFTIME('%s', SUBSTR(CAST(p.birth_datetime AS TEXT), 1, 4) || '-' || SUBSTR(CAST(p.birth_datetime AS TEXT), 5, 2) || '-' || SUBSTR(CAST(p.birth_datetime AS TEXT), 7)) AS REAL), \n CAST(STRFTIME('%s', SUBSTR(CAST(p.year_of_birth || COALESCE(\n SUBSTR(CAST('0' || CAST(p.month_of_birth AS TEXT) AS TEXT),-2),\n '01'\n ) || COALESCE(\n SUBSTR(CAST('0' || CAST(p.day_of_birth AS TEXT) AS TEXT),-2),\n '01'\n )\n AS TEXT), 1, 4) || '-' || SUBSTR(CAST(p.year_of_birth || COALESCE(\n SUBSTR(CAST('0' || CAST(p.month_of_birth AS TEXT) AS TEXT),-2),\n '01'\n ) || COALESCE(\n SUBSTR(CAST('0' || CAST(p.day_of_birth AS TEXT) AS TEXT),-2),\n '01'\n )\n AS TEXT), 5, 2) || '-' || SUBSTR(CAST(p.year_of_birth || COALESCE(\n SUBSTR(CAST('0' || CAST(p.month_of_birth AS TEXT) AS TEXT),-2),\n '01'\n ) || COALESCE(\n SUBSTR(CAST('0' || CAST(p.day_of_birth AS TEXT) AS TEXT),-2),\n '01'\n )\n AS TEXT), 7)) AS REAL)\n )\n /*violatedRowsEnd*/\n ) violated_rows\n) violated_row_count,\n(\n SELECT \n COUNT(*) AS num_rows\n FROM main.OBSERVATION cdmTable\n WHERE cdmTable.OBSERVATION_DATE IS NOT NULL\n) denominator\n;\n
1.27 /*********\nPLAUSIBLE_AFTER_BIRTH\nChecks that all events happen after birth (PLAUSIBLE_AFTER_BIRTH == Yes)\nBirthdate is either birth_datetime or composed from year_of_birth, month_of_birth, day_of_birth (taking 1st month/1st day if missing).\nDenominator is number of events with a non-null date.\nParameters used in this template:\nschema = main\ncdmDatabaseSchema = main\ncdmTableName = OBSERVATION\ncdmFieldName = OBSERVATION_DATETIME\n**********/\nSELECT \n num_violated_rows, \n CASE \n WHEN denominator.num_rows = 0 THEN 0 \n ELSE 1.0*num_violated_rows/denominator.num_rows \n END AS pct_violated_rows, \n denominator.num_rows AS num_denominator_rows\nFROM\n(\n SELECT \n COUNT(violated_rows.violating_field) AS num_violated_rows\n FROM\n (\n /*violatedRowsBegin*/\n SELECT \n 'OBSERVATION.OBSERVATION_DATETIME' AS violating_field, \n cdmTable.*\n FROM main.OBSERVATION cdmTable\n JOIN main.person p \n ON cdmTable.person_id = p.person_id\n WHERE cdmTable.OBSERVATION_DATETIME IS NOT NULL AND \n CAST(STRFTIME('%s', SUBSTR(CAST(cdmTable.OBSERVATION_DATETIME AS TEXT), 1, 4) || '-' || SUBSTR(CAST(cdmTable.OBSERVATION_DATETIME AS TEXT), 5, 2) || '-' || SUBSTR(CAST(cdmTable.OBSERVATION_DATETIME AS TEXT), 7)) AS REAL) < COALESCE(\n CAST(STRFTIME('%s', SUBSTR(CAST(p.birth_datetime AS TEXT), 1, 4) || '-' || SUBSTR(CAST(p.birth_datetime AS TEXT), 5, 2) || '-' || SUBSTR(CAST(p.birth_datetime AS TEXT), 7)) AS REAL), \n CAST(STRFTIME('%s', SUBSTR(CAST(p.year_of_birth || COALESCE(\n SUBSTR(CAST('0' || CAST(p.month_of_birth AS TEXT) AS TEXT),-2),\n '01'\n ) || COALESCE(\n SUBSTR(CAST('0' || CAST(p.day_of_birth AS TEXT) AS TEXT),-2),\n '01'\n )\n AS TEXT), 1, 4) || '-' || SUBSTR(CAST(p.year_of_birth || COALESCE(\n SUBSTR(CAST('0' || CAST(p.month_of_birth AS TEXT) AS TEXT),-2),\n '01'\n ) || COALESCE(\n SUBSTR(CAST('0' || CAST(p.day_of_birth AS TEXT) AS TEXT),-2),\n '01'\n )\n AS TEXT), 5, 2) || '-' || SUBSTR(CAST(p.year_of_birth || COALESCE(\n SUBSTR(CAST('0' || CAST(p.month_of_birth AS TEXT) AS TEXT),-2),\n '01'\n ) || COALESCE(\n SUBSTR(CAST('0' || CAST(p.day_of_birth AS TEXT) AS TEXT),-2),\n '01'\n )\n AS TEXT), 7)) AS REAL)\n )\n /*violatedRowsEnd*/\n ) violated_rows\n) violated_row_count,\n(\n SELECT \n COUNT(*) AS num_rows\n FROM main.OBSERVATION cdmTable\n WHERE cdmTable.OBSERVATION_DATETIME IS NOT NULL\n) denominator\n;\n
1.28 /*********\nPLAUSIBLE_AFTER_BIRTH\nChecks that all events happen after birth (PLAUSIBLE_AFTER_BIRTH == Yes)\nBirthdate is either birth_datetime or composed from year_of_birth, month_of_birth, day_of_birth (taking 1st month/1st day if missing).\nDenominator is number of events with a non-null date.\nParameters used in this template:\nschema = main\ncdmDatabaseSchema = main\ncdmTableName = OBSERVATION_PERIOD\ncdmFieldName = OBSERVATION_PERIOD_START_DATE\n**********/\nSELECT \n num_violated_rows, \n CASE \n WHEN denominator.num_rows = 0 THEN 0 \n ELSE 1.0*num_violated_rows/denominator.num_rows \n END AS pct_violated_rows, \n denominator.num_rows AS num_denominator_rows\nFROM\n(\n SELECT \n COUNT(violated_rows.violating_field) AS num_violated_rows\n FROM\n (\n /*violatedRowsBegin*/\n SELECT \n 'OBSERVATION_PERIOD.OBSERVATION_PERIOD_START_DATE' AS violating_field, \n cdmTable.*\n FROM main.OBSERVATION_PERIOD cdmTable\n JOIN main.person p \n ON cdmTable.person_id = p.person_id\n WHERE cdmTable.OBSERVATION_PERIOD_START_DATE IS NOT NULL AND \n CAST(STRFTIME('%s', SUBSTR(CAST(cdmTable.OBSERVATION_PERIOD_START_DATE AS TEXT), 1, 4) || '-' || SUBSTR(CAST(cdmTable.OBSERVATION_PERIOD_START_DATE AS TEXT), 5, 2) || '-' || SUBSTR(CAST(cdmTable.OBSERVATION_PERIOD_START_DATE AS TEXT), 7)) AS REAL) < COALESCE(\n CAST(STRFTIME('%s', SUBSTR(CAST(p.birth_datetime AS TEXT), 1, 4) || '-' || SUBSTR(CAST(p.birth_datetime AS TEXT), 5, 2) || '-' || SUBSTR(CAST(p.birth_datetime AS TEXT), 7)) AS REAL), \n CAST(STRFTIME('%s', SUBSTR(CAST(p.year_of_birth || COALESCE(\n SUBSTR(CAST('0' || CAST(p.month_of_birth AS TEXT) AS TEXT),-2),\n '01'\n ) || COALESCE(\n SUBSTR(CAST('0' || CAST(p.day_of_birth AS TEXT) AS TEXT),-2),\n '01'\n )\n AS TEXT), 1, 4) || '-' || SUBSTR(CAST(p.year_of_birth || COALESCE(\n SUBSTR(CAST('0' || CAST(p.month_of_birth AS TEXT) AS TEXT),-2),\n '01'\n ) || COALESCE(\n SUBSTR(CAST('0' || CAST(p.day_of_birth AS TEXT) AS TEXT),-2),\n '01'\n )\n AS TEXT), 5, 2) || '-' || SUBSTR(CAST(p.year_of_birth || COALESCE(\n SUBSTR(CAST('0' || CAST(p.month_of_birth AS TEXT) AS TEXT),-2),\n '01'\n ) || COALESCE(\n SUBSTR(CAST('0' || CAST(p.day_of_birth AS TEXT) AS TEXT),-2),\n '01'\n )\n AS TEXT), 7)) AS REAL)\n )\n /*violatedRowsEnd*/\n ) violated_rows\n) violated_row_count,\n(\n SELECT \n COUNT(*) AS num_rows\n FROM main.OBSERVATION_PERIOD cdmTable\n WHERE cdmTable.OBSERVATION_PERIOD_START_DATE IS NOT NULL\n) denominator\n;\n
1.29 /*********\nPLAUSIBLE_AFTER_BIRTH\nChecks that all events happen after birth (PLAUSIBLE_AFTER_BIRTH == Yes)\nBirthdate is either birth_datetime or composed from year_of_birth, month_of_birth, day_of_birth (taking 1st month/1st day if missing).\nDenominator is number of events with a non-null date.\nParameters used in this template:\nschema = main\ncdmDatabaseSchema = main\ncdmTableName = OBSERVATION_PERIOD\ncdmFieldName = OBSERVATION_PERIOD_END_DATE\n**********/\nSELECT \n num_violated_rows, \n CASE \n WHEN denominator.num_rows = 0 THEN 0 \n ELSE 1.0*num_violated_rows/denominator.num_rows \n END AS pct_violated_rows, \n denominator.num_rows AS num_denominator_rows\nFROM\n(\n SELECT \n COUNT(violated_rows.violating_field) AS num_violated_rows\n FROM\n (\n /*violatedRowsBegin*/\n SELECT \n 'OBSERVATION_PERIOD.OBSERVATION_PERIOD_END_DATE' AS violating_field, \n cdmTable.*\n FROM main.OBSERVATION_PERIOD cdmTable\n JOIN main.person p \n ON cdmTable.person_id = p.person_id\n WHERE cdmTable.OBSERVATION_PERIOD_END_DATE IS NOT NULL AND \n CAST(STRFTIME('%s', SUBSTR(CAST(cdmTable.OBSERVATION_PERIOD_END_DATE AS TEXT), 1, 4) || '-' || SUBSTR(CAST(cdmTable.OBSERVATION_PERIOD_END_DATE AS TEXT), 5, 2) || '-' || SUBSTR(CAST(cdmTable.OBSERVATION_PERIOD_END_DATE AS TEXT), 7)) AS REAL) < COALESCE(\n CAST(STRFTIME('%s', SUBSTR(CAST(p.birth_datetime AS TEXT), 1, 4) || '-' || SUBSTR(CAST(p.birth_datetime AS TEXT), 5, 2) || '-' || SUBSTR(CAST(p.birth_datetime AS TEXT), 7)) AS REAL), \n CAST(STRFTIME('%s', SUBSTR(CAST(p.year_of_birth || COALESCE(\n SUBSTR(CAST('0' || CAST(p.month_of_birth AS TEXT) AS TEXT),-2),\n '01'\n ) || COALESCE(\n SUBSTR(CAST('0' || CAST(p.day_of_birth AS TEXT) AS TEXT),-2),\n '01'\n )\n AS TEXT), 1, 4) || '-' || SUBSTR(CAST(p.year_of_birth || COALESCE(\n SUBSTR(CAST('0' || CAST(p.month_of_birth AS TEXT) AS TEXT),-2),\n '01'\n ) || COALESCE(\n SUBSTR(CAST('0' || CAST(p.day_of_birth AS TEXT) AS TEXT),-2),\n '01'\n )\n AS TEXT), 5, 2) || '-' || SUBSTR(CAST(p.year_of_birth || COALESCE(\n SUBSTR(CAST('0' || CAST(p.month_of_birth AS TEXT) AS TEXT),-2),\n '01'\n ) || COALESCE(\n SUBSTR(CAST('0' || CAST(p.day_of_birth AS TEXT) AS TEXT),-2),\n '01'\n )\n AS TEXT), 7)) AS REAL)\n )\n /*violatedRowsEnd*/\n ) violated_rows\n) violated_row_count,\n(\n SELECT \n COUNT(*) AS num_rows\n FROM main.OBSERVATION_PERIOD cdmTable\n WHERE cdmTable.OBSERVATION_PERIOD_END_DATE IS NOT NULL\n) denominator\n;\n
1.30 /*********\nPLAUSIBLE_AFTER_BIRTH\nChecks that all events happen after birth (PLAUSIBLE_AFTER_BIRTH == Yes)\nBirthdate is either birth_datetime or composed from year_of_birth, month_of_birth, day_of_birth (taking 1st month/1st day if missing).\nDenominator is number of events with a non-null date.\nParameters used in this template:\nschema = main\ncdmDatabaseSchema = main\ncdmTableName = PAYER_PLAN_PERIOD\ncdmFieldName = PAYER_PLAN_PERIOD_START_DATE\n**********/\nSELECT \n num_violated_rows, \n CASE \n WHEN denominator.num_rows = 0 THEN 0 \n ELSE 1.0*num_violated_rows/denominator.num_rows \n END AS pct_violated_rows, \n denominator.num_rows AS num_denominator_rows\nFROM\n(\n SELECT \n COUNT(violated_rows.violating_field) AS num_violated_rows\n FROM\n (\n /*violatedRowsBegin*/\n SELECT \n 'PAYER_PLAN_PERIOD.PAYER_PLAN_PERIOD_START_DATE' AS violating_field, \n cdmTable.*\n FROM main.PAYER_PLAN_PERIOD cdmTable\n JOIN main.person p \n ON cdmTable.person_id = p.person_id\n WHERE cdmTable.PAYER_PLAN_PERIOD_START_DATE IS NOT NULL AND \n CAST(STRFTIME('%s', SUBSTR(CAST(cdmTable.PAYER_PLAN_PERIOD_START_DATE AS TEXT), 1, 4) || '-' || SUBSTR(CAST(cdmTable.PAYER_PLAN_PERIOD_START_DATE AS TEXT), 5, 2) || '-' || SUBSTR(CAST(cdmTable.PAYER_PLAN_PERIOD_START_DATE AS TEXT), 7)) AS REAL) < COALESCE(\n CAST(STRFTIME('%s', SUBSTR(CAST(p.birth_datetime AS TEXT), 1, 4) || '-' || SUBSTR(CAST(p.birth_datetime AS TEXT), 5, 2) || '-' || SUBSTR(CAST(p.birth_datetime AS TEXT), 7)) AS REAL), \n CAST(STRFTIME('%s', SUBSTR(CAST(p.year_of_birth || COALESCE(\n SUBSTR(CAST('0' || CAST(p.month_of_birth AS TEXT) AS TEXT),-2),\n '01'\n ) || COALESCE(\n SUBSTR(CAST('0' || CAST(p.day_of_birth AS TEXT) AS TEXT),-2),\n '01'\n )\n AS TEXT), 1, 4) || '-' || SUBSTR(CAST(p.year_of_birth || COALESCE(\n SUBSTR(CAST('0' || CAST(p.month_of_birth AS TEXT) AS TEXT),-2),\n '01'\n ) || COALESCE(\n SUBSTR(CAST('0' || CAST(p.day_of_birth AS TEXT) AS TEXT),-2),\n '01'\n )\n AS TEXT), 5, 2) || '-' || SUBSTR(CAST(p.year_of_birth || COALESCE(\n SUBSTR(CAST('0' || CAST(p.month_of_birth AS TEXT) AS TEXT),-2),\n '01'\n ) || COALESCE(\n SUBSTR(CAST('0' || CAST(p.day_of_birth AS TEXT) AS TEXT),-2),\n '01'\n )\n AS TEXT), 7)) AS REAL)\n )\n /*violatedRowsEnd*/\n ) violated_rows\n) violated_row_count,\n(\n SELECT \n COUNT(*) AS num_rows\n FROM main.PAYER_PLAN_PERIOD cdmTable\n WHERE cdmTable.PAYER_PLAN_PERIOD_START_DATE IS NOT NULL\n) denominator\n;\n
1.31 /*********\nPLAUSIBLE_AFTER_BIRTH\nChecks that all events happen after birth (PLAUSIBLE_AFTER_BIRTH == Yes)\nBirthdate is either birth_datetime or composed from year_of_birth, month_of_birth, day_of_birth (taking 1st month/1st day if missing).\nDenominator is number of events with a non-null date.\nParameters used in this template:\nschema = main\ncdmDatabaseSchema = main\ncdmTableName = PAYER_PLAN_PERIOD\ncdmFieldName = PAYER_PLAN_PERIOD_END_DATE\n**********/\nSELECT \n num_violated_rows, \n CASE \n WHEN denominator.num_rows = 0 THEN 0 \n ELSE 1.0*num_violated_rows/denominator.num_rows \n END AS pct_violated_rows, \n denominator.num_rows AS num_denominator_rows\nFROM\n(\n SELECT \n COUNT(violated_rows.violating_field) AS num_violated_rows\n FROM\n (\n /*violatedRowsBegin*/\n SELECT \n 'PAYER_PLAN_PERIOD.PAYER_PLAN_PERIOD_END_DATE' AS violating_field, \n cdmTable.*\n FROM main.PAYER_PLAN_PERIOD cdmTable\n JOIN main.person p \n ON cdmTable.person_id = p.person_id\n WHERE cdmTable.PAYER_PLAN_PERIOD_END_DATE IS NOT NULL AND \n CAST(STRFTIME('%s', SUBSTR(CAST(cdmTable.PAYER_PLAN_PERIOD_END_DATE AS TEXT), 1, 4) || '-' || SUBSTR(CAST(cdmTable.PAYER_PLAN_PERIOD_END_DATE AS TEXT), 5, 2) || '-' || SUBSTR(CAST(cdmTable.PAYER_PLAN_PERIOD_END_DATE AS TEXT), 7)) AS REAL) < COALESCE(\n CAST(STRFTIME('%s', SUBSTR(CAST(p.birth_datetime AS TEXT), 1, 4) || '-' || SUBSTR(CAST(p.birth_datetime AS TEXT), 5, 2) || '-' || SUBSTR(CAST(p.birth_datetime AS TEXT), 7)) AS REAL), \n CAST(STRFTIME('%s', SUBSTR(CAST(p.year_of_birth || COALESCE(\n SUBSTR(CAST('0' || CAST(p.month_of_birth AS TEXT) AS TEXT),-2),\n '01'\n ) || COALESCE(\n SUBSTR(CAST('0' || CAST(p.day_of_birth AS TEXT) AS TEXT),-2),\n '01'\n )\n AS TEXT), 1, 4) || '-' || SUBSTR(CAST(p.year_of_birth || COALESCE(\n SUBSTR(CAST('0' || CAST(p.month_of_birth AS TEXT) AS TEXT),-2),\n '01'\n ) || COALESCE(\n SUBSTR(CAST('0' || CAST(p.day_of_birth AS TEXT) AS TEXT),-2),\n '01'\n )\n AS TEXT), 5, 2) || '-' || SUBSTR(CAST(p.year_of_birth || COALESCE(\n SUBSTR(CAST('0' || CAST(p.month_of_birth AS TEXT) AS TEXT),-2),\n '01'\n ) || COALESCE(\n SUBSTR(CAST('0' || CAST(p.day_of_birth AS TEXT) AS TEXT),-2),\n '01'\n )\n AS TEXT), 7)) AS REAL)\n )\n /*violatedRowsEnd*/\n ) violated_rows\n) violated_row_count,\n(\n SELECT \n COUNT(*) AS num_rows\n FROM main.PAYER_PLAN_PERIOD cdmTable\n WHERE cdmTable.PAYER_PLAN_PERIOD_END_DATE IS NOT NULL\n) denominator\n;\n
1.32 /*********\nPLAUSIBLE_AFTER_BIRTH\nChecks that all events happen after birth (PLAUSIBLE_AFTER_BIRTH == Yes)\nBirthdate is either birth_datetime or composed from year_of_birth, month_of_birth, day_of_birth (taking 1st month/1st day if missing).\nDenominator is number of events with a non-null date.\nParameters used in this template:\nschema = main\ncdmDatabaseSchema = main\ncdmTableName = PROCEDURE_OCCURRENCE\ncdmFieldName = PROCEDURE_DATE\n**********/\nSELECT \n num_violated_rows, \n CASE \n WHEN denominator.num_rows = 0 THEN 0 \n ELSE 1.0*num_violated_rows/denominator.num_rows \n END AS pct_violated_rows, \n denominator.num_rows AS num_denominator_rows\nFROM\n(\n SELECT \n COUNT(violated_rows.violating_field) AS num_violated_rows\n FROM\n (\n /*violatedRowsBegin*/\n SELECT \n 'PROCEDURE_OCCURRENCE.PROCEDURE_DATE' AS violating_field, \n cdmTable.*\n FROM main.PROCEDURE_OCCURRENCE cdmTable\n JOIN main.person p \n ON cdmTable.person_id = p.person_id\n WHERE cdmTable.PROCEDURE_DATE IS NOT NULL AND \n CAST(STRFTIME('%s', SUBSTR(CAST(cdmTable.PROCEDURE_DATE AS TEXT), 1, 4) || '-' || SUBSTR(CAST(cdmTable.PROCEDURE_DATE AS TEXT), 5, 2) || '-' || SUBSTR(CAST(cdmTable.PROCEDURE_DATE AS TEXT), 7)) AS REAL) < COALESCE(\n CAST(STRFTIME('%s', SUBSTR(CAST(p.birth_datetime AS TEXT), 1, 4) || '-' || SUBSTR(CAST(p.birth_datetime AS TEXT), 5, 2) || '-' || SUBSTR(CAST(p.birth_datetime AS TEXT), 7)) AS REAL), \n CAST(STRFTIME('%s', SUBSTR(CAST(p.year_of_birth || COALESCE(\n SUBSTR(CAST('0' || CAST(p.month_of_birth AS TEXT) AS TEXT),-2),\n '01'\n ) || COALESCE(\n SUBSTR(CAST('0' || CAST(p.day_of_birth AS TEXT) AS TEXT),-2),\n '01'\n )\n AS TEXT), 1, 4) || '-' || SUBSTR(CAST(p.year_of_birth || COALESCE(\n SUBSTR(CAST('0' || CAST(p.month_of_birth AS TEXT) AS TEXT),-2),\n '01'\n ) || COALESCE(\n SUBSTR(CAST('0' || CAST(p.day_of_birth AS TEXT) AS TEXT),-2),\n '01'\n )\n AS TEXT), 5, 2) || '-' || SUBSTR(CAST(p.year_of_birth || COALESCE(\n SUBSTR(CAST('0' || CAST(p.month_of_birth AS TEXT) AS TEXT),-2),\n '01'\n ) || COALESCE(\n SUBSTR(CAST('0' || CAST(p.day_of_birth AS TEXT) AS TEXT),-2),\n '01'\n )\n AS TEXT), 7)) AS REAL)\n )\n /*violatedRowsEnd*/\n ) violated_rows\n) violated_row_count,\n(\n SELECT \n COUNT(*) AS num_rows\n FROM main.PROCEDURE_OCCURRENCE cdmTable\n WHERE cdmTable.PROCEDURE_DATE IS NOT NULL\n) denominator\n;\n
1.33 /*********\nPLAUSIBLE_AFTER_BIRTH\nChecks that all events happen after birth (PLAUSIBLE_AFTER_BIRTH == Yes)\nBirthdate is either birth_datetime or composed from year_of_birth, month_of_birth, day_of_birth (taking 1st month/1st day if missing).\nDenominator is number of events with a non-null date.\nParameters used in this template:\nschema = main\ncdmDatabaseSchema = main\ncdmTableName = PROCEDURE_OCCURRENCE\ncdmFieldName = PROCEDURE_DATETIME\n**********/\nSELECT \n num_violated_rows, \n CASE \n WHEN denominator.num_rows = 0 THEN 0 \n ELSE 1.0*num_violated_rows/denominator.num_rows \n END AS pct_violated_rows, \n denominator.num_rows AS num_denominator_rows\nFROM\n(\n SELECT \n COUNT(violated_rows.violating_field) AS num_violated_rows\n FROM\n (\n /*violatedRowsBegin*/\n SELECT \n 'PROCEDURE_OCCURRENCE.PROCEDURE_DATETIME' AS violating_field, \n cdmTable.*\n FROM main.PROCEDURE_OCCURRENCE cdmTable\n JOIN main.person p \n ON cdmTable.person_id = p.person_id\n WHERE cdmTable.PROCEDURE_DATETIME IS NOT NULL AND \n CAST(STRFTIME('%s', SUBSTR(CAST(cdmTable.PROCEDURE_DATETIME AS TEXT), 1, 4) || '-' || SUBSTR(CAST(cdmTable.PROCEDURE_DATETIME AS TEXT), 5, 2) || '-' || SUBSTR(CAST(cdmTable.PROCEDURE_DATETIME AS TEXT), 7)) AS REAL) < COALESCE(\n CAST(STRFTIME('%s', SUBSTR(CAST(p.birth_datetime AS TEXT), 1, 4) || '-' || SUBSTR(CAST(p.birth_datetime AS TEXT), 5, 2) || '-' || SUBSTR(CAST(p.birth_datetime AS TEXT), 7)) AS REAL), \n CAST(STRFTIME('%s', SUBSTR(CAST(p.year_of_birth || COALESCE(\n SUBSTR(CAST('0' || CAST(p.month_of_birth AS TEXT) AS TEXT),-2),\n '01'\n ) || COALESCE(\n SUBSTR(CAST('0' || CAST(p.day_of_birth AS TEXT) AS TEXT),-2),\n '01'\n )\n AS TEXT), 1, 4) || '-' || SUBSTR(CAST(p.year_of_birth || COALESCE(\n SUBSTR(CAST('0' || CAST(p.month_of_birth AS TEXT) AS TEXT),-2),\n '01'\n ) || COALESCE(\n SUBSTR(CAST('0' || CAST(p.day_of_birth AS TEXT) AS TEXT),-2),\n '01'\n )\n AS TEXT), 5, 2) || '-' || SUBSTR(CAST(p.year_of_birth || COALESCE(\n SUBSTR(CAST('0' || CAST(p.month_of_birth AS TEXT) AS TEXT),-2),\n '01'\n ) || COALESCE(\n SUBSTR(CAST('0' || CAST(p.day_of_birth AS TEXT) AS TEXT),-2),\n '01'\n )\n AS TEXT), 7)) AS REAL)\n )\n /*violatedRowsEnd*/\n ) violated_rows\n) violated_row_count,\n(\n SELECT \n COUNT(*) AS num_rows\n FROM main.PROCEDURE_OCCURRENCE cdmTable\n WHERE cdmTable.PROCEDURE_DATETIME IS NOT NULL\n) denominator\n;\n
1.34 /*********\nPLAUSIBLE_AFTER_BIRTH\nChecks that all events happen after birth (PLAUSIBLE_AFTER_BIRTH == Yes)\nBirthdate is either birth_datetime or composed from year_of_birth, month_of_birth, day_of_birth (taking 1st month/1st day if missing).\nDenominator is number of events with a non-null date.\nParameters used in this template:\nschema = main\ncdmDatabaseSchema = main\ncdmTableName = SPECIMEN\ncdmFieldName = SPECIMEN_DATE\n**********/\nSELECT \n num_violated_rows, \n CASE \n WHEN denominator.num_rows = 0 THEN 0 \n ELSE 1.0*num_violated_rows/denominator.num_rows \n END AS pct_violated_rows, \n denominator.num_rows AS num_denominator_rows\nFROM\n(\n SELECT \n COUNT(violated_rows.violating_field) AS num_violated_rows\n FROM\n (\n /*violatedRowsBegin*/\n SELECT \n 'SPECIMEN.SPECIMEN_DATE' AS violating_field, \n cdmTable.*\n FROM main.SPECIMEN cdmTable\n JOIN main.person p \n ON cdmTable.person_id = p.person_id\n WHERE cdmTable.SPECIMEN_DATE IS NOT NULL AND \n CAST(STRFTIME('%s', SUBSTR(CAST(cdmTable.SPECIMEN_DATE AS TEXT), 1, 4) || '-' || SUBSTR(CAST(cdmTable.SPECIMEN_DATE AS TEXT), 5, 2) || '-' || SUBSTR(CAST(cdmTable.SPECIMEN_DATE AS TEXT), 7)) AS REAL) < COALESCE(\n CAST(STRFTIME('%s', SUBSTR(CAST(p.birth_datetime AS TEXT), 1, 4) || '-' || SUBSTR(CAST(p.birth_datetime AS TEXT), 5, 2) || '-' || SUBSTR(CAST(p.birth_datetime AS TEXT), 7)) AS REAL), \n CAST(STRFTIME('%s', SUBSTR(CAST(p.year_of_birth || COALESCE(\n SUBSTR(CAST('0' || CAST(p.month_of_birth AS TEXT) AS TEXT),-2),\n '01'\n ) || COALESCE(\n SUBSTR(CAST('0' || CAST(p.day_of_birth AS TEXT) AS TEXT),-2),\n '01'\n )\n AS TEXT), 1, 4) || '-' || SUBSTR(CAST(p.year_of_birth || COALESCE(\n SUBSTR(CAST('0' || CAST(p.month_of_birth AS TEXT) AS TEXT),-2),\n '01'\n ) || COALESCE(\n SUBSTR(CAST('0' || CAST(p.day_of_birth AS TEXT) AS TEXT),-2),\n '01'\n )\n AS TEXT), 5, 2) || '-' || SUBSTR(CAST(p.year_of_birth || COALESCE(\n SUBSTR(CAST('0' || CAST(p.month_of_birth AS TEXT) AS TEXT),-2),\n '01'\n ) || COALESCE(\n SUBSTR(CAST('0' || CAST(p.day_of_birth AS TEXT) AS TEXT),-2),\n '01'\n )\n AS TEXT), 7)) AS REAL)\n )\n /*violatedRowsEnd*/\n ) violated_rows\n) violated_row_count,\n(\n SELECT \n COUNT(*) AS num_rows\n FROM main.SPECIMEN cdmTable\n WHERE cdmTable.SPECIMEN_DATE IS NOT NULL\n) denominator\n;\n
1.35 /*********\nPLAUSIBLE_AFTER_BIRTH\nChecks that all events happen after birth (PLAUSIBLE_AFTER_BIRTH == Yes)\nBirthdate is either birth_datetime or composed from year_of_birth, month_of_birth, day_of_birth (taking 1st month/1st day if missing).\nDenominator is number of events with a non-null date.\nParameters used in this template:\nschema = main\ncdmDatabaseSchema = main\ncdmTableName = SPECIMEN\ncdmFieldName = SPECIMEN_DATETIME\n**********/\nSELECT \n num_violated_rows, \n CASE \n WHEN denominator.num_rows = 0 THEN 0 \n ELSE 1.0*num_violated_rows/denominator.num_rows \n END AS pct_violated_rows, \n denominator.num_rows AS num_denominator_rows\nFROM\n(\n SELECT \n COUNT(violated_rows.violating_field) AS num_violated_rows\n FROM\n (\n /*violatedRowsBegin*/\n SELECT \n 'SPECIMEN.SPECIMEN_DATETIME' AS violating_field, \n cdmTable.*\n FROM main.SPECIMEN cdmTable\n JOIN main.person p \n ON cdmTable.person_id = p.person_id\n WHERE cdmTable.SPECIMEN_DATETIME IS NOT NULL AND \n CAST(STRFTIME('%s', SUBSTR(CAST(cdmTable.SPECIMEN_DATETIME AS TEXT), 1, 4) || '-' || SUBSTR(CAST(cdmTable.SPECIMEN_DATETIME AS TEXT), 5, 2) || '-' || SUBSTR(CAST(cdmTable.SPECIMEN_DATETIME AS TEXT), 7)) AS REAL) < COALESCE(\n CAST(STRFTIME('%s', SUBSTR(CAST(p.birth_datetime AS TEXT), 1, 4) || '-' || SUBSTR(CAST(p.birth_datetime AS TEXT), 5, 2) || '-' || SUBSTR(CAST(p.birth_datetime AS TEXT), 7)) AS REAL), \n CAST(STRFTIME('%s', SUBSTR(CAST(p.year_of_birth || COALESCE(\n SUBSTR(CAST('0' || CAST(p.month_of_birth AS TEXT) AS TEXT),-2),\n '01'\n ) || COALESCE(\n SUBSTR(CAST('0' || CAST(p.day_of_birth AS TEXT) AS TEXT),-2),\n '01'\n )\n AS TEXT), 1, 4) || '-' || SUBSTR(CAST(p.year_of_birth || COALESCE(\n SUBSTR(CAST('0' || CAST(p.month_of_birth AS TEXT) AS TEXT),-2),\n '01'\n ) || COALESCE(\n SUBSTR(CAST('0' || CAST(p.day_of_birth AS TEXT) AS TEXT),-2),\n '01'\n )\n AS TEXT), 5, 2) || '-' || SUBSTR(CAST(p.year_of_birth || COALESCE(\n SUBSTR(CAST('0' || CAST(p.month_of_birth AS TEXT) AS TEXT),-2),\n '01'\n ) || COALESCE(\n SUBSTR(CAST('0' || CAST(p.day_of_birth AS TEXT) AS TEXT),-2),\n '01'\n )\n AS TEXT), 7)) AS REAL)\n )\n /*violatedRowsEnd*/\n ) violated_rows\n) violated_row_count,\n(\n SELECT \n COUNT(*) AS num_rows\n FROM main.SPECIMEN cdmTable\n WHERE cdmTable.SPECIMEN_DATETIME IS NOT NULL\n) denominator\n;\n
1.36 /*********\nPLAUSIBLE_AFTER_BIRTH\nChecks that all events happen after birth (PLAUSIBLE_AFTER_BIRTH == Yes)\nBirthdate is either birth_datetime or composed from year_of_birth, month_of_birth, day_of_birth (taking 1st month/1st day if missing).\nDenominator is number of events with a non-null date.\nParameters used in this template:\nschema = main\ncdmDatabaseSchema = main\ncdmTableName = VISIT_DETAIL\ncdmFieldName = VISIT_DETAIL_START_DATE\n**********/\nSELECT \n num_violated_rows, \n CASE \n WHEN denominator.num_rows = 0 THEN 0 \n ELSE 1.0*num_violated_rows/denominator.num_rows \n END AS pct_violated_rows, \n denominator.num_rows AS num_denominator_rows\nFROM\n(\n SELECT \n COUNT(violated_rows.violating_field) AS num_violated_rows\n FROM\n (\n /*violatedRowsBegin*/\n SELECT \n 'VISIT_DETAIL.VISIT_DETAIL_START_DATE' AS violating_field, \n cdmTable.*\n FROM main.VISIT_DETAIL cdmTable\n JOIN main.person p \n ON cdmTable.person_id = p.person_id\n WHERE cdmTable.VISIT_DETAIL_START_DATE IS NOT NULL AND \n CAST(STRFTIME('%s', SUBSTR(CAST(cdmTable.VISIT_DETAIL_START_DATE AS TEXT), 1, 4) || '-' || SUBSTR(CAST(cdmTable.VISIT_DETAIL_START_DATE AS TEXT), 5, 2) || '-' || SUBSTR(CAST(cdmTable.VISIT_DETAIL_START_DATE AS TEXT), 7)) AS REAL) < COALESCE(\n CAST(STRFTIME('%s', SUBSTR(CAST(p.birth_datetime AS TEXT), 1, 4) || '-' || SUBSTR(CAST(p.birth_datetime AS TEXT), 5, 2) || '-' || SUBSTR(CAST(p.birth_datetime AS TEXT), 7)) AS REAL), \n CAST(STRFTIME('%s', SUBSTR(CAST(p.year_of_birth || COALESCE(\n SUBSTR(CAST('0' || CAST(p.month_of_birth AS TEXT) AS TEXT),-2),\n '01'\n ) || COALESCE(\n SUBSTR(CAST('0' || CAST(p.day_of_birth AS TEXT) AS TEXT),-2),\n '01'\n )\n AS TEXT), 1, 4) || '-' || SUBSTR(CAST(p.year_of_birth || COALESCE(\n SUBSTR(CAST('0' || CAST(p.month_of_birth AS TEXT) AS TEXT),-2),\n '01'\n ) || COALESCE(\n SUBSTR(CAST('0' || CAST(p.day_of_birth AS TEXT) AS TEXT),-2),\n '01'\n )\n AS TEXT), 5, 2) || '-' || SUBSTR(CAST(p.year_of_birth || COALESCE(\n SUBSTR(CAST('0' || CAST(p.month_of_birth AS TEXT) AS TEXT),-2),\n '01'\n ) || COALESCE(\n SUBSTR(CAST('0' || CAST(p.day_of_birth AS TEXT) AS TEXT),-2),\n '01'\n )\n AS TEXT), 7)) AS REAL)\n )\n /*violatedRowsEnd*/\n ) violated_rows\n) violated_row_count,\n(\n SELECT \n COUNT(*) AS num_rows\n FROM main.VISIT_DETAIL cdmTable\n WHERE cdmTable.VISIT_DETAIL_START_DATE IS NOT NULL\n) denominator\n;\n
1.37 /*********\nPLAUSIBLE_AFTER_BIRTH\nChecks that all events happen after birth (PLAUSIBLE_AFTER_BIRTH == Yes)\nBirthdate is either birth_datetime or composed from year_of_birth, month_of_birth, day_of_birth (taking 1st month/1st day if missing).\nDenominator is number of events with a non-null date.\nParameters used in this template:\nschema = main\ncdmDatabaseSchema = main\ncdmTableName = VISIT_DETAIL\ncdmFieldName = VISIT_DETAIL_START_DATETIME\n**********/\nSELECT \n num_violated_rows, \n CASE \n WHEN denominator.num_rows = 0 THEN 0 \n ELSE 1.0*num_violated_rows/denominator.num_rows \n END AS pct_violated_rows, \n denominator.num_rows AS num_denominator_rows\nFROM\n(\n SELECT \n COUNT(violated_rows.violating_field) AS num_violated_rows\n FROM\n (\n /*violatedRowsBegin*/\n SELECT \n 'VISIT_DETAIL.VISIT_DETAIL_START_DATETIME' AS violating_field, \n cdmTable.*\n FROM main.VISIT_DETAIL cdmTable\n JOIN main.person p \n ON cdmTable.person_id = p.person_id\n WHERE cdmTable.VISIT_DETAIL_START_DATETIME IS NOT NULL AND \n CAST(STRFTIME('%s', SUBSTR(CAST(cdmTable.VISIT_DETAIL_START_DATETIME AS TEXT), 1, 4) || '-' || SUBSTR(CAST(cdmTable.VISIT_DETAIL_START_DATETIME AS TEXT), 5, 2) || '-' || SUBSTR(CAST(cdmTable.VISIT_DETAIL_START_DATETIME AS TEXT), 7)) AS REAL) < COALESCE(\n CAST(STRFTIME('%s', SUBSTR(CAST(p.birth_datetime AS TEXT), 1, 4) || '-' || SUBSTR(CAST(p.birth_datetime AS TEXT), 5, 2) || '-' || SUBSTR(CAST(p.birth_datetime AS TEXT), 7)) AS REAL), \n CAST(STRFTIME('%s', SUBSTR(CAST(p.year_of_birth || COALESCE(\n SUBSTR(CAST('0' || CAST(p.month_of_birth AS TEXT) AS TEXT),-2),\n '01'\n ) || COALESCE(\n SUBSTR(CAST('0' || CAST(p.day_of_birth AS TEXT) AS TEXT),-2),\n '01'\n )\n AS TEXT), 1, 4) || '-' || SUBSTR(CAST(p.year_of_birth || COALESCE(\n SUBSTR(CAST('0' || CAST(p.month_of_birth AS TEXT) AS TEXT),-2),\n '01'\n ) || COALESCE(\n SUBSTR(CAST('0' || CAST(p.day_of_birth AS TEXT) AS TEXT),-2),\n '01'\n )\n AS TEXT), 5, 2) || '-' || SUBSTR(CAST(p.year_of_birth || COALESCE(\n SUBSTR(CAST('0' || CAST(p.month_of_birth AS TEXT) AS TEXT),-2),\n '01'\n ) || COALESCE(\n SUBSTR(CAST('0' || CAST(p.day_of_birth AS TEXT) AS TEXT),-2),\n '01'\n )\n AS TEXT), 7)) AS REAL)\n )\n /*violatedRowsEnd*/\n ) violated_rows\n) violated_row_count,\n(\n SELECT \n COUNT(*) AS num_rows\n FROM main.VISIT_DETAIL cdmTable\n WHERE cdmTable.VISIT_DETAIL_START_DATETIME IS NOT NULL\n) denominator\n;\n
1.38 /*********\nPLAUSIBLE_AFTER_BIRTH\nChecks that all events happen after birth (PLAUSIBLE_AFTER_BIRTH == Yes)\nBirthdate is either birth_datetime or composed from year_of_birth, month_of_birth, day_of_birth (taking 1st month/1st day if missing).\nDenominator is number of events with a non-null date.\nParameters used in this template:\nschema = main\ncdmDatabaseSchema = main\ncdmTableName = VISIT_DETAIL\ncdmFieldName = VISIT_DETAIL_END_DATE\n**********/\nSELECT \n num_violated_rows, \n CASE \n WHEN denominator.num_rows = 0 THEN 0 \n ELSE 1.0*num_violated_rows/denominator.num_rows \n END AS pct_violated_rows, \n denominator.num_rows AS num_denominator_rows\nFROM\n(\n SELECT \n COUNT(violated_rows.violating_field) AS num_violated_rows\n FROM\n (\n /*violatedRowsBegin*/\n SELECT \n 'VISIT_DETAIL.VISIT_DETAIL_END_DATE' AS violating_field, \n cdmTable.*\n FROM main.VISIT_DETAIL cdmTable\n JOIN main.person p \n ON cdmTable.person_id = p.person_id\n WHERE cdmTable.VISIT_DETAIL_END_DATE IS NOT NULL AND \n CAST(STRFTIME('%s', SUBSTR(CAST(cdmTable.VISIT_DETAIL_END_DATE AS TEXT), 1, 4) || '-' || SUBSTR(CAST(cdmTable.VISIT_DETAIL_END_DATE AS TEXT), 5, 2) || '-' || SUBSTR(CAST(cdmTable.VISIT_DETAIL_END_DATE AS TEXT), 7)) AS REAL) < COALESCE(\n CAST(STRFTIME('%s', SUBSTR(CAST(p.birth_datetime AS TEXT), 1, 4) || '-' || SUBSTR(CAST(p.birth_datetime AS TEXT), 5, 2) || '-' || SUBSTR(CAST(p.birth_datetime AS TEXT), 7)) AS REAL), \n CAST(STRFTIME('%s', SUBSTR(CAST(p.year_of_birth || COALESCE(\n SUBSTR(CAST('0' || CAST(p.month_of_birth AS TEXT) AS TEXT),-2),\n '01'\n ) || COALESCE(\n SUBSTR(CAST('0' || CAST(p.day_of_birth AS TEXT) AS TEXT),-2),\n '01'\n )\n AS TEXT), 1, 4) || '-' || SUBSTR(CAST(p.year_of_birth || COALESCE(\n SUBSTR(CAST('0' || CAST(p.month_of_birth AS TEXT) AS TEXT),-2),\n '01'\n ) || COALESCE(\n SUBSTR(CAST('0' || CAST(p.day_of_birth AS TEXT) AS TEXT),-2),\n '01'\n )\n AS TEXT), 5, 2) || '-' || SUBSTR(CAST(p.year_of_birth || COALESCE(\n SUBSTR(CAST('0' || CAST(p.month_of_birth AS TEXT) AS TEXT),-2),\n '01'\n ) || COALESCE(\n SUBSTR(CAST('0' || CAST(p.day_of_birth AS TEXT) AS TEXT),-2),\n '01'\n )\n AS TEXT), 7)) AS REAL)\n )\n /*violatedRowsEnd*/\n ) violated_rows\n) violated_row_count,\n(\n SELECT \n COUNT(*) AS num_rows\n FROM main.VISIT_DETAIL cdmTable\n WHERE cdmTable.VISIT_DETAIL_END_DATE IS NOT NULL\n) denominator\n;\n
1.39 /*********\nPLAUSIBLE_AFTER_BIRTH\nChecks that all events happen after birth (PLAUSIBLE_AFTER_BIRTH == Yes)\nBirthdate is either birth_datetime or composed from year_of_birth, month_of_birth, day_of_birth (taking 1st month/1st day if missing).\nDenominator is number of events with a non-null date.\nParameters used in this template:\nschema = main\ncdmDatabaseSchema = main\ncdmTableName = VISIT_DETAIL\ncdmFieldName = VISIT_DETAIL_END_DATETIME\n**********/\nSELECT \n num_violated_rows, \n CASE \n WHEN denominator.num_rows = 0 THEN 0 \n ELSE 1.0*num_violated_rows/denominator.num_rows \n END AS pct_violated_rows, \n denominator.num_rows AS num_denominator_rows\nFROM\n(\n SELECT \n COUNT(violated_rows.violating_field) AS num_violated_rows\n FROM\n (\n /*violatedRowsBegin*/\n SELECT \n 'VISIT_DETAIL.VISIT_DETAIL_END_DATETIME' AS violating_field, \n cdmTable.*\n FROM main.VISIT_DETAIL cdmTable\n JOIN main.person p \n ON cdmTable.person_id = p.person_id\n WHERE cdmTable.VISIT_DETAIL_END_DATETIME IS NOT NULL AND \n CAST(STRFTIME('%s', SUBSTR(CAST(cdmTable.VISIT_DETAIL_END_DATETIME AS TEXT), 1, 4) || '-' || SUBSTR(CAST(cdmTable.VISIT_DETAIL_END_DATETIME AS TEXT), 5, 2) || '-' || SUBSTR(CAST(cdmTable.VISIT_DETAIL_END_DATETIME AS TEXT), 7)) AS REAL) < COALESCE(\n CAST(STRFTIME('%s', SUBSTR(CAST(p.birth_datetime AS TEXT), 1, 4) || '-' || SUBSTR(CAST(p.birth_datetime AS TEXT), 5, 2) || '-' || SUBSTR(CAST(p.birth_datetime AS TEXT), 7)) AS REAL), \n CAST(STRFTIME('%s', SUBSTR(CAST(p.year_of_birth || COALESCE(\n SUBSTR(CAST('0' || CAST(p.month_of_birth AS TEXT) AS TEXT),-2),\n '01'\n ) || COALESCE(\n SUBSTR(CAST('0' || CAST(p.day_of_birth AS TEXT) AS TEXT),-2),\n '01'\n )\n AS TEXT), 1, 4) || '-' || SUBSTR(CAST(p.year_of_birth || COALESCE(\n SUBSTR(CAST('0' || CAST(p.month_of_birth AS TEXT) AS TEXT),-2),\n '01'\n ) || COALESCE(\n SUBSTR(CAST('0' || CAST(p.day_of_birth AS TEXT) AS TEXT),-2),\n '01'\n )\n AS TEXT), 5, 2) || '-' || SUBSTR(CAST(p.year_of_birth || COALESCE(\n SUBSTR(CAST('0' || CAST(p.month_of_birth AS TEXT) AS TEXT),-2),\n '01'\n ) || COALESCE(\n SUBSTR(CAST('0' || CAST(p.day_of_birth AS TEXT) AS TEXT),-2),\n '01'\n )\n AS TEXT), 7)) AS REAL)\n )\n /*violatedRowsEnd*/\n ) violated_rows\n) violated_row_count,\n(\n SELECT \n COUNT(*) AS num_rows\n FROM main.VISIT_DETAIL cdmTable\n WHERE cdmTable.VISIT_DETAIL_END_DATETIME IS NOT NULL\n) denominator\n;\n
1.40 /*********\nPLAUSIBLE_AFTER_BIRTH\nChecks that all events happen after birth (PLAUSIBLE_AFTER_BIRTH == Yes)\nBirthdate is either birth_datetime or composed from year_of_birth, month_of_birth, day_of_birth (taking 1st month/1st day if missing).\nDenominator is number of events with a non-null date.\nParameters used in this template:\nschema = main\ncdmDatabaseSchema = main\ncdmTableName = VISIT_OCCURRENCE\ncdmFieldName = VISIT_START_DATE\n**********/\nSELECT \n num_violated_rows, \n CASE \n WHEN denominator.num_rows = 0 THEN 0 \n ELSE 1.0*num_violated_rows/denominator.num_rows \n END AS pct_violated_rows, \n denominator.num_rows AS num_denominator_rows\nFROM\n(\n SELECT \n COUNT(violated_rows.violating_field) AS num_violated_rows\n FROM\n (\n /*violatedRowsBegin*/\n SELECT \n 'VISIT_OCCURRENCE.VISIT_START_DATE' AS violating_field, \n cdmTable.*\n FROM main.VISIT_OCCURRENCE cdmTable\n JOIN main.person p \n ON cdmTable.person_id = p.person_id\n WHERE cdmTable.VISIT_START_DATE IS NOT NULL AND \n CAST(STRFTIME('%s', SUBSTR(CAST(cdmTable.VISIT_START_DATE AS TEXT), 1, 4) || '-' || SUBSTR(CAST(cdmTable.VISIT_START_DATE AS TEXT), 5, 2) || '-' || SUBSTR(CAST(cdmTable.VISIT_START_DATE AS TEXT), 7)) AS REAL) < COALESCE(\n CAST(STRFTIME('%s', SUBSTR(CAST(p.birth_datetime AS TEXT), 1, 4) || '-' || SUBSTR(CAST(p.birth_datetime AS TEXT), 5, 2) || '-' || SUBSTR(CAST(p.birth_datetime AS TEXT), 7)) AS REAL), \n CAST(STRFTIME('%s', SUBSTR(CAST(p.year_of_birth || COALESCE(\n SUBSTR(CAST('0' || CAST(p.month_of_birth AS TEXT) AS TEXT),-2),\n '01'\n ) || COALESCE(\n SUBSTR(CAST('0' || CAST(p.day_of_birth AS TEXT) AS TEXT),-2),\n '01'\n )\n AS TEXT), 1, 4) || '-' || SUBSTR(CAST(p.year_of_birth || COALESCE(\n SUBSTR(CAST('0' || CAST(p.month_of_birth AS TEXT) AS TEXT),-2),\n '01'\n ) || COALESCE(\n SUBSTR(CAST('0' || CAST(p.day_of_birth AS TEXT) AS TEXT),-2),\n '01'\n )\n AS TEXT), 5, 2) || '-' || SUBSTR(CAST(p.year_of_birth || COALESCE(\n SUBSTR(CAST('0' || CAST(p.month_of_birth AS TEXT) AS TEXT),-2),\n '01'\n ) || COALESCE(\n SUBSTR(CAST('0' || CAST(p.day_of_birth AS TEXT) AS TEXT),-2),\n '01'\n )\n AS TEXT), 7)) AS REAL)\n )\n /*violatedRowsEnd*/\n ) violated_rows\n) violated_row_count,\n(\n SELECT \n COUNT(*) AS num_rows\n FROM main.VISIT_OCCURRENCE cdmTable\n WHERE cdmTable.VISIT_START_DATE IS NOT NULL\n) denominator\n;\n
1.41 /*********\nPLAUSIBLE_AFTER_BIRTH\nChecks that all events happen after birth (PLAUSIBLE_AFTER_BIRTH == Yes)\nBirthdate is either birth_datetime or composed from year_of_birth, month_of_birth, day_of_birth (taking 1st month/1st day if missing).\nDenominator is number of events with a non-null date.\nParameters used in this template:\nschema = main\ncdmDatabaseSchema = main\ncdmTableName = VISIT_OCCURRENCE\ncdmFieldName = VISIT_START_DATETIME\n**********/\nSELECT \n num_violated_rows, \n CASE \n WHEN denominator.num_rows = 0 THEN 0 \n ELSE 1.0*num_violated_rows/denominator.num_rows \n END AS pct_violated_rows, \n denominator.num_rows AS num_denominator_rows\nFROM\n(\n SELECT \n COUNT(violated_rows.violating_field) AS num_violated_rows\n FROM\n (\n /*violatedRowsBegin*/\n SELECT \n 'VISIT_OCCURRENCE.VISIT_START_DATETIME' AS violating_field, \n cdmTable.*\n FROM main.VISIT_OCCURRENCE cdmTable\n JOIN main.person p \n ON cdmTable.person_id = p.person_id\n WHERE cdmTable.VISIT_START_DATETIME IS NOT NULL AND \n CAST(STRFTIME('%s', SUBSTR(CAST(cdmTable.VISIT_START_DATETIME AS TEXT), 1, 4) || '-' || SUBSTR(CAST(cdmTable.VISIT_START_DATETIME AS TEXT), 5, 2) || '-' || SUBSTR(CAST(cdmTable.VISIT_START_DATETIME AS TEXT), 7)) AS REAL) < COALESCE(\n CAST(STRFTIME('%s', SUBSTR(CAST(p.birth_datetime AS TEXT), 1, 4) || '-' || SUBSTR(CAST(p.birth_datetime AS TEXT), 5, 2) || '-' || SUBSTR(CAST(p.birth_datetime AS TEXT), 7)) AS REAL), \n CAST(STRFTIME('%s', SUBSTR(CAST(p.year_of_birth || COALESCE(\n SUBSTR(CAST('0' || CAST(p.month_of_birth AS TEXT) AS TEXT),-2),\n '01'\n ) || COALESCE(\n SUBSTR(CAST('0' || CAST(p.day_of_birth AS TEXT) AS TEXT),-2),\n '01'\n )\n AS TEXT), 1, 4) || '-' || SUBSTR(CAST(p.year_of_birth || COALESCE(\n SUBSTR(CAST('0' || CAST(p.month_of_birth AS TEXT) AS TEXT),-2),\n '01'\n ) || COALESCE(\n SUBSTR(CAST('0' || CAST(p.day_of_birth AS TEXT) AS TEXT),-2),\n '01'\n )\n AS TEXT), 5, 2) || '-' || SUBSTR(CAST(p.year_of_birth || COALESCE(\n SUBSTR(CAST('0' || CAST(p.month_of_birth AS TEXT) AS TEXT),-2),\n '01'\n ) || COALESCE(\n SUBSTR(CAST('0' || CAST(p.day_of_birth AS TEXT) AS TEXT),-2),\n '01'\n )\n AS TEXT), 7)) AS REAL)\n )\n /*violatedRowsEnd*/\n ) violated_rows\n) violated_row_count,\n(\n SELECT \n COUNT(*) AS num_rows\n FROM main.VISIT_OCCURRENCE cdmTable\n WHERE cdmTable.VISIT_START_DATETIME IS NOT NULL\n) denominator\n;\n
1.42 /*********\nPLAUSIBLE_AFTER_BIRTH\nChecks that all events happen after birth (PLAUSIBLE_AFTER_BIRTH == Yes)\nBirthdate is either birth_datetime or composed from year_of_birth, month_of_birth, day_of_birth (taking 1st month/1st day if missing).\nDenominator is number of events with a non-null date.\nParameters used in this template:\nschema = main\ncdmDatabaseSchema = main\ncdmTableName = VISIT_OCCURRENCE\ncdmFieldName = VISIT_END_DATE\n**********/\nSELECT \n num_violated_rows, \n CASE \n WHEN denominator.num_rows = 0 THEN 0 \n ELSE 1.0*num_violated_rows/denominator.num_rows \n END AS pct_violated_rows, \n denominator.num_rows AS num_denominator_rows\nFROM\n(\n SELECT \n COUNT(violated_rows.violating_field) AS num_violated_rows\n FROM\n (\n /*violatedRowsBegin*/\n SELECT \n 'VISIT_OCCURRENCE.VISIT_END_DATE' AS violating_field, \n cdmTable.*\n FROM main.VISIT_OCCURRENCE cdmTable\n JOIN main.person p \n ON cdmTable.person_id = p.person_id\n WHERE cdmTable.VISIT_END_DATE IS NOT NULL AND \n CAST(STRFTIME('%s', SUBSTR(CAST(cdmTable.VISIT_END_DATE AS TEXT), 1, 4) || '-' || SUBSTR(CAST(cdmTable.VISIT_END_DATE AS TEXT), 5, 2) || '-' || SUBSTR(CAST(cdmTable.VISIT_END_DATE AS TEXT), 7)) AS REAL) < COALESCE(\n CAST(STRFTIME('%s', SUBSTR(CAST(p.birth_datetime AS TEXT), 1, 4) || '-' || SUBSTR(CAST(p.birth_datetime AS TEXT), 5, 2) || '-' || SUBSTR(CAST(p.birth_datetime AS TEXT), 7)) AS REAL), \n CAST(STRFTIME('%s', SUBSTR(CAST(p.year_of_birth || COALESCE(\n SUBSTR(CAST('0' || CAST(p.month_of_birth AS TEXT) AS TEXT),-2),\n '01'\n ) || COALESCE(\n SUBSTR(CAST('0' || CAST(p.day_of_birth AS TEXT) AS TEXT),-2),\n '01'\n )\n AS TEXT), 1, 4) || '-' || SUBSTR(CAST(p.year_of_birth || COALESCE(\n SUBSTR(CAST('0' || CAST(p.month_of_birth AS TEXT) AS TEXT),-2),\n '01'\n ) || COALESCE(\n SUBSTR(CAST('0' || CAST(p.day_of_birth AS TEXT) AS TEXT),-2),\n '01'\n )\n AS TEXT), 5, 2) || '-' || SUBSTR(CAST(p.year_of_birth || COALESCE(\n SUBSTR(CAST('0' || CAST(p.month_of_birth AS TEXT) AS TEXT),-2),\n '01'\n ) || COALESCE(\n SUBSTR(CAST('0' || CAST(p.day_of_birth AS TEXT) AS TEXT),-2),\n '01'\n )\n AS TEXT), 7)) AS REAL)\n )\n /*violatedRowsEnd*/\n ) violated_rows\n) violated_row_count,\n(\n SELECT \n COUNT(*) AS num_rows\n FROM main.VISIT_OCCURRENCE cdmTable\n WHERE cdmTable.VISIT_END_DATE IS NOT NULL\n) denominator\n;\n
1.43 /*********\nPLAUSIBLE_AFTER_BIRTH\nChecks that all events happen after birth (PLAUSIBLE_AFTER_BIRTH == Yes)\nBirthdate is either birth_datetime or composed from year_of_birth, month_of_birth, day_of_birth (taking 1st month/1st day if missing).\nDenominator is number of events with a non-null date.\nParameters used in this template:\nschema = main\ncdmDatabaseSchema = main\ncdmTableName = VISIT_OCCURRENCE\ncdmFieldName = VISIT_END_DATETIME\n**********/\nSELECT \n num_violated_rows, \n CASE \n WHEN denominator.num_rows = 0 THEN 0 \n ELSE 1.0*num_violated_rows/denominator.num_rows \n END AS pct_violated_rows, \n denominator.num_rows AS num_denominator_rows\nFROM\n(\n SELECT \n COUNT(violated_rows.violating_field) AS num_violated_rows\n FROM\n (\n /*violatedRowsBegin*/\n SELECT \n 'VISIT_OCCURRENCE.VISIT_END_DATETIME' AS violating_field, \n cdmTable.*\n FROM main.VISIT_OCCURRENCE cdmTable\n JOIN main.person p \n ON cdmTable.person_id = p.person_id\n WHERE cdmTable.VISIT_END_DATETIME IS NOT NULL AND \n CAST(STRFTIME('%s', SUBSTR(CAST(cdmTable.VISIT_END_DATETIME AS TEXT), 1, 4) || '-' || SUBSTR(CAST(cdmTable.VISIT_END_DATETIME AS TEXT), 5, 2) || '-' || SUBSTR(CAST(cdmTable.VISIT_END_DATETIME AS TEXT), 7)) AS REAL) < COALESCE(\n CAST(STRFTIME('%s', SUBSTR(CAST(p.birth_datetime AS TEXT), 1, 4) || '-' || SUBSTR(CAST(p.birth_datetime AS TEXT), 5, 2) || '-' || SUBSTR(CAST(p.birth_datetime AS TEXT), 7)) AS REAL), \n CAST(STRFTIME('%s', SUBSTR(CAST(p.year_of_birth || COALESCE(\n SUBSTR(CAST('0' || CAST(p.month_of_birth AS TEXT) AS TEXT),-2),\n '01'\n ) || COALESCE(\n SUBSTR(CAST('0' || CAST(p.day_of_birth AS TEXT) AS TEXT),-2),\n '01'\n )\n AS TEXT), 1, 4) || '-' || SUBSTR(CAST(p.year_of_birth || COALESCE(\n SUBSTR(CAST('0' || CAST(p.month_of_birth AS TEXT) AS TEXT),-2),\n '01'\n ) || COALESCE(\n SUBSTR(CAST('0' || CAST(p.day_of_birth AS TEXT) AS TEXT),-2),\n '01'\n )\n AS TEXT), 5, 2) || '-' || SUBSTR(CAST(p.year_of_birth || COALESCE(\n SUBSTR(CAST('0' || CAST(p.month_of_birth AS TEXT) AS TEXT),-2),\n '01'\n ) || COALESCE(\n SUBSTR(CAST('0' || CAST(p.day_of_birth AS TEXT) AS TEXT),-2),\n '01'\n )\n AS TEXT), 7)) AS REAL)\n )\n /*violatedRowsEnd*/\n ) violated_rows\n) violated_row_count,\n(\n SELECT \n COUNT(*) AS num_rows\n FROM main.VISIT_OCCURRENCE cdmTable\n WHERE cdmTable.VISIT_END_DATETIME IS NOT NULL\n) denominator\n;\n
checkName checkLevel
1.1 plausibleAfterBirth FIELD
1.2 plausibleAfterBirth FIELD
1.3 plausibleAfterBirth FIELD
1.4 plausibleAfterBirth FIELD
1.5 plausibleAfterBirth FIELD
1.6 plausibleAfterBirth FIELD
1.7 plausibleAfterBirth FIELD
1.8 plausibleAfterBirth FIELD
1.9 plausibleAfterBirth FIELD
1.10 plausibleAfterBirth FIELD
1.11 plausibleAfterBirth FIELD
1.12 plausibleAfterBirth FIELD
1.13 plausibleAfterBirth FIELD
1.14 plausibleAfterBirth FIELD
1.15 plausibleAfterBirth FIELD
1.16 plausibleAfterBirth FIELD
1.17 plausibleAfterBirth FIELD
1.18 plausibleAfterBirth FIELD
1.19 plausibleAfterBirth FIELD
1.20 plausibleAfterBirth FIELD
1.21 plausibleAfterBirth FIELD
1.22 plausibleAfterBirth FIELD
1.23 plausibleAfterBirth FIELD
1.24 plausibleAfterBirth FIELD
1.25 plausibleAfterBirth FIELD
1.26 plausibleAfterBirth FIELD
1.27 plausibleAfterBirth FIELD
1.28 plausibleAfterBirth FIELD
1.29 plausibleAfterBirth FIELD
1.30 plausibleAfterBirth FIELD
1.31 plausibleAfterBirth FIELD
1.32 plausibleAfterBirth FIELD
1.33 plausibleAfterBirth FIELD
1.34 plausibleAfterBirth FIELD
1.35 plausibleAfterBirth FIELD
1.36 plausibleAfterBirth FIELD
1.37 plausibleAfterBirth FIELD
1.38 plausibleAfterBirth FIELD
1.39 plausibleAfterBirth FIELD
1.40 plausibleAfterBirth FIELD
1.41 plausibleAfterBirth FIELD
1.42 plausibleAfterBirth FIELD
1.43 plausibleAfterBirth FIELD
checkDescription
1.1 The number and percent of records with a date value in the CONDITION_ERA_START_DATE field of the CONDITION_ERA table that occurs prior to birth.
1.2 The number and percent of records with a date value in the CONDITION_ERA_END_DATE field of the CONDITION_ERA table that occurs prior to birth.
1.3 The number and percent of records with a date value in the CONDITION_START_DATE field of the CONDITION_OCCURRENCE table that occurs prior to birth.
1.4 The number and percent of records with a date value in the CONDITION_START_DATETIME field of the CONDITION_OCCURRENCE table that occurs prior to birth.
1.5 The number and percent of records with a date value in the CONDITION_END_DATE field of the CONDITION_OCCURRENCE table that occurs prior to birth.
1.6 The number and percent of records with a date value in the CONDITION_END_DATETIME field of the CONDITION_OCCURRENCE table that occurs prior to birth.
1.7 The number and percent of records with a date value in the DEATH_DATE field of the DEATH table that occurs prior to birth.
1.8 The number and percent of records with a date value in the DEATH_DATETIME field of the DEATH table that occurs prior to birth.
1.9 The number and percent of records with a date value in the DEVICE_EXPOSURE_START_DATE field of the DEVICE_EXPOSURE table that occurs prior to birth.
1.10 The number and percent of records with a date value in the DEVICE_EXPOSURE_START_DATETIME field of the DEVICE_EXPOSURE table that occurs prior to birth.
1.11 The number and percent of records with a date value in the DEVICE_EXPOSURE_END_DATE field of the DEVICE_EXPOSURE table that occurs prior to birth.
1.12 The number and percent of records with a date value in the DEVICE_EXPOSURE_END_DATETIME field of the DEVICE_EXPOSURE table that occurs prior to birth.
1.13 The number and percent of records with a date value in the DOSE_ERA_START_DATE field of the DOSE_ERA table that occurs prior to birth.
1.14 The number and percent of records with a date value in the DOSE_ERA_END_DATE field of the DOSE_ERA table that occurs prior to birth.
1.15 The number and percent of records with a date value in the DRUG_ERA_START_DATE field of the DRUG_ERA table that occurs prior to birth.
1.16 The number and percent of records with a date value in the DRUG_ERA_END_DATE field of the DRUG_ERA table that occurs prior to birth.
1.17 The number and percent of records with a date value in the DRUG_EXPOSURE_START_DATE field of the DRUG_EXPOSURE table that occurs prior to birth.
1.18 The number and percent of records with a date value in the DRUG_EXPOSURE_START_DATETIME field of the DRUG_EXPOSURE table that occurs prior to birth.
1.19 The number and percent of records with a date value in the DRUG_EXPOSURE_END_DATE field of the DRUG_EXPOSURE table that occurs prior to birth.
1.20 The number and percent of records with a date value in the DRUG_EXPOSURE_END_DATETIME field of the DRUG_EXPOSURE table that occurs prior to birth.
1.21 The number and percent of records with a date value in the VERBATIM_END_DATE field of the DRUG_EXPOSURE table that occurs prior to birth.
1.22 The number and percent of records with a date value in the MEASUREMENT_DATE field of the MEASUREMENT table that occurs prior to birth.
1.23 The number and percent of records with a date value in the MEASUREMENT_DATETIME field of the MEASUREMENT table that occurs prior to birth.
1.24 The number and percent of records with a date value in the NOTE_DATE field of the NOTE table that occurs prior to birth.
1.25 The number and percent of records with a date value in the NOTE_DATETIME field of the NOTE table that occurs prior to birth.
1.26 The number and percent of records with a date value in the OBSERVATION_DATE field of the OBSERVATION table that occurs prior to birth.
1.27 The number and percent of records with a date value in the OBSERVATION_DATETIME field of the OBSERVATION table that occurs prior to birth.
1.28 The number and percent of records with a date value in the OBSERVATION_PERIOD_START_DATE field of the OBSERVATION_PERIOD table that occurs prior to birth.
1.29 The number and percent of records with a date value in the OBSERVATION_PERIOD_END_DATE field of the OBSERVATION_PERIOD table that occurs prior to birth.
1.30 The number and percent of records with a date value in the PAYER_PLAN_PERIOD_START_DATE field of the PAYER_PLAN_PERIOD table that occurs prior to birth.
1.31 The number and percent of records with a date value in the PAYER_PLAN_PERIOD_END_DATE field of the PAYER_PLAN_PERIOD table that occurs prior to birth.
1.32 The number and percent of records with a date value in the PROCEDURE_DATE field of the PROCEDURE_OCCURRENCE table that occurs prior to birth.
1.33 The number and percent of records with a date value in the PROCEDURE_DATETIME field of the PROCEDURE_OCCURRENCE table that occurs prior to birth.
1.34 The number and percent of records with a date value in the SPECIMEN_DATE field of the SPECIMEN table that occurs prior to birth.
1.35 The number and percent of records with a date value in the SPECIMEN_DATETIME field of the SPECIMEN table that occurs prior to birth.
1.36 The number and percent of records with a date value in the VISIT_DETAIL_START_DATE field of the VISIT_DETAIL table that occurs prior to birth.
1.37 The number and percent of records with a date value in the VISIT_DETAIL_START_DATETIME field of the VISIT_DETAIL table that occurs prior to birth.
1.38 The number and percent of records with a date value in the VISIT_DETAIL_END_DATE field of the VISIT_DETAIL table that occurs prior to birth.
1.39 The number and percent of records with a date value in the VISIT_DETAIL_END_DATETIME field of the VISIT_DETAIL table that occurs prior to birth.
1.40 The number and percent of records with a date value in the VISIT_START_DATE field of the VISIT_OCCURRENCE table that occurs prior to birth.
1.41 The number and percent of records with a date value in the VISIT_START_DATETIME field of the VISIT_OCCURRENCE table that occurs prior to birth.
1.42 The number and percent of records with a date value in the VISIT_END_DATE field of the VISIT_OCCURRENCE table that occurs prior to birth.
1.43 The number and percent of records with a date value in the VISIT_END_DATETIME field of the VISIT_OCCURRENCE table that occurs prior to birth.
cdmTableName cdmFieldName conceptId
1.1 CONDITION_ERA CONDITION_ERA_START_DATE <NA>
1.2 CONDITION_ERA CONDITION_ERA_END_DATE <NA>
1.3 CONDITION_OCCURRENCE CONDITION_START_DATE <NA>
1.4 CONDITION_OCCURRENCE CONDITION_START_DATETIME <NA>
1.5 CONDITION_OCCURRENCE CONDITION_END_DATE <NA>
1.6 CONDITION_OCCURRENCE CONDITION_END_DATETIME <NA>
1.7 DEATH DEATH_DATE <NA>
1.8 DEATH DEATH_DATETIME <NA>
1.9 DEVICE_EXPOSURE DEVICE_EXPOSURE_START_DATE <NA>
1.10 DEVICE_EXPOSURE DEVICE_EXPOSURE_START_DATETIME <NA>
1.11 DEVICE_EXPOSURE DEVICE_EXPOSURE_END_DATE <NA>
1.12 DEVICE_EXPOSURE DEVICE_EXPOSURE_END_DATETIME <NA>
1.13 DOSE_ERA DOSE_ERA_START_DATE <NA>
1.14 DOSE_ERA DOSE_ERA_END_DATE <NA>
1.15 DRUG_ERA DRUG_ERA_START_DATE <NA>
1.16 DRUG_ERA DRUG_ERA_END_DATE <NA>
1.17 DRUG_EXPOSURE DRUG_EXPOSURE_START_DATE <NA>
1.18 DRUG_EXPOSURE DRUG_EXPOSURE_START_DATETIME <NA>
1.19 DRUG_EXPOSURE DRUG_EXPOSURE_END_DATE <NA>
1.20 DRUG_EXPOSURE DRUG_EXPOSURE_END_DATETIME <NA>
1.21 DRUG_EXPOSURE VERBATIM_END_DATE <NA>
1.22 MEASUREMENT MEASUREMENT_DATE <NA>
1.23 MEASUREMENT MEASUREMENT_DATETIME <NA>
1.24 NOTE NOTE_DATE <NA>
1.25 NOTE NOTE_DATETIME <NA>
1.26 OBSERVATION OBSERVATION_DATE <NA>
1.27 OBSERVATION OBSERVATION_DATETIME <NA>
1.28 OBSERVATION_PERIOD OBSERVATION_PERIOD_START_DATE <NA>
1.29 OBSERVATION_PERIOD OBSERVATION_PERIOD_END_DATE <NA>
1.30 PAYER_PLAN_PERIOD PAYER_PLAN_PERIOD_START_DATE <NA>
1.31 PAYER_PLAN_PERIOD PAYER_PLAN_PERIOD_END_DATE <NA>
1.32 PROCEDURE_OCCURRENCE PROCEDURE_DATE <NA>
1.33 PROCEDURE_OCCURRENCE PROCEDURE_DATETIME <NA>
1.34 SPECIMEN SPECIMEN_DATE <NA>
1.35 SPECIMEN SPECIMEN_DATETIME <NA>
1.36 VISIT_DETAIL VISIT_DETAIL_START_DATE <NA>
1.37 VISIT_DETAIL VISIT_DETAIL_START_DATETIME <NA>
1.38 VISIT_DETAIL VISIT_DETAIL_END_DATE <NA>
1.39 VISIT_DETAIL VISIT_DETAIL_END_DATETIME <NA>
1.40 VISIT_OCCURRENCE VISIT_START_DATE <NA>
1.41 VISIT_OCCURRENCE VISIT_START_DATETIME <NA>
1.42 VISIT_OCCURRENCE VISIT_END_DATE <NA>
1.43 VISIT_OCCURRENCE VISIT_END_DATETIME <NA>
unitConceptId sqlFile category subcategory
1.1 <NA> field_plausible_after_birth.sql Plausibility Temporal
1.2 <NA> field_plausible_after_birth.sql Plausibility Temporal
1.3 <NA> field_plausible_after_birth.sql Plausibility Temporal
1.4 <NA> field_plausible_after_birth.sql Plausibility Temporal
1.5 <NA> field_plausible_after_birth.sql Plausibility Temporal
1.6 <NA> field_plausible_after_birth.sql Plausibility Temporal
1.7 <NA> field_plausible_after_birth.sql Plausibility Temporal
1.8 <NA> field_plausible_after_birth.sql Plausibility Temporal
1.9 <NA> field_plausible_after_birth.sql Plausibility Temporal
1.10 <NA> field_plausible_after_birth.sql Plausibility Temporal
1.11 <NA> field_plausible_after_birth.sql Plausibility Temporal
1.12 <NA> field_plausible_after_birth.sql Plausibility Temporal
1.13 <NA> field_plausible_after_birth.sql Plausibility Temporal
1.14 <NA> field_plausible_after_birth.sql Plausibility Temporal
1.15 <NA> field_plausible_after_birth.sql Plausibility Temporal
1.16 <NA> field_plausible_after_birth.sql Plausibility Temporal
1.17 <NA> field_plausible_after_birth.sql Plausibility Temporal
1.18 <NA> field_plausible_after_birth.sql Plausibility Temporal
1.19 <NA> field_plausible_after_birth.sql Plausibility Temporal
1.20 <NA> field_plausible_after_birth.sql Plausibility Temporal
1.21 <NA> field_plausible_after_birth.sql Plausibility Temporal
1.22 <NA> field_plausible_after_birth.sql Plausibility Temporal
1.23 <NA> field_plausible_after_birth.sql Plausibility Temporal
1.24 <NA> field_plausible_after_birth.sql Plausibility Temporal
1.25 <NA> field_plausible_after_birth.sql Plausibility Temporal
1.26 <NA> field_plausible_after_birth.sql Plausibility Temporal
1.27 <NA> field_plausible_after_birth.sql Plausibility Temporal
1.28 <NA> field_plausible_after_birth.sql Plausibility Temporal
1.29 <NA> field_plausible_after_birth.sql Plausibility Temporal
1.30 <NA> field_plausible_after_birth.sql Plausibility Temporal
1.31 <NA> field_plausible_after_birth.sql Plausibility Temporal
1.32 <NA> field_plausible_after_birth.sql Plausibility Temporal
1.33 <NA> field_plausible_after_birth.sql Plausibility Temporal
1.34 <NA> field_plausible_after_birth.sql Plausibility Temporal
1.35 <NA> field_plausible_after_birth.sql Plausibility Temporal
1.36 <NA> field_plausible_after_birth.sql Plausibility Temporal
1.37 <NA> field_plausible_after_birth.sql Plausibility Temporal
1.38 <NA> field_plausible_after_birth.sql Plausibility Temporal
1.39 <NA> field_plausible_after_birth.sql Plausibility Temporal
1.40 <NA> field_plausible_after_birth.sql Plausibility Temporal
1.41 <NA> field_plausible_after_birth.sql Plausibility Temporal
1.42 <NA> field_plausible_after_birth.sql Plausibility Temporal
1.43 <NA> field_plausible_after_birth.sql Plausibility Temporal
context warning error
1.1 Verification NA NA
1.2 Verification NA NA
1.3 Verification NA NA
1.4 Verification NA NA
1.5 Verification NA NA
1.6 Verification NA NA
1.7 Verification NA NA
1.8 Verification NA NA
1.9 Verification NA NA
1.10 Verification NA NA
1.11 Verification NA NA
1.12 Verification NA NA
1.13 Verification NA NA
1.14 Verification NA NA
1.15 Verification NA NA
1.16 Verification NA NA
1.17 Verification NA NA
1.18 Verification NA NA
1.19 Verification NA NA
1.20 Verification NA NA
1.21 Verification NA NA
1.22 Verification NA NA
1.23 Verification NA NA
1.24 Verification NA NA
1.25 Verification NA NA
1.26 Verification NA NA
1.27 Verification NA NA
1.28 Verification NA NA
1.29 Verification NA NA
1.30 Verification NA NA
1.31 Verification NA NA
1.32 Verification NA NA
1.33 Verification NA NA
1.34 Verification NA NA
1.35 Verification NA NA
1.36 Verification NA NA
1.37 Verification NA NA
1.38 Verification NA NA
1.39 Verification NA NA
1.40 Verification NA NA
1.41 Verification NA NA
1.42 Verification NA NA
1.43 Verification NA NA
checkId
1.1 field_plausibleafterbirth_condition_era_condition_era_start_date
1.2 field_plausibleafterbirth_condition_era_condition_era_end_date
1.3 field_plausibleafterbirth_condition_occurrence_condition_start_date
1.4 field_plausibleafterbirth_condition_occurrence_condition_start_datetime
1.5 field_plausibleafterbirth_condition_occurrence_condition_end_date
1.6 field_plausibleafterbirth_condition_occurrence_condition_end_datetime
1.7 field_plausibleafterbirth_death_death_date
1.8 field_plausibleafterbirth_death_death_datetime
1.9 field_plausibleafterbirth_device_exposure_device_exposure_start_date
1.10 field_plausibleafterbirth_device_exposure_device_exposure_start_datetime
1.11 field_plausibleafterbirth_device_exposure_device_exposure_end_date
1.12 field_plausibleafterbirth_device_exposure_device_exposure_end_datetime
1.13 field_plausibleafterbirth_dose_era_dose_era_start_date
1.14 field_plausibleafterbirth_dose_era_dose_era_end_date
1.15 field_plausibleafterbirth_drug_era_drug_era_start_date
1.16 field_plausibleafterbirth_drug_era_drug_era_end_date
1.17 field_plausibleafterbirth_drug_exposure_drug_exposure_start_date
1.18 field_plausibleafterbirth_drug_exposure_drug_exposure_start_datetime
1.19 field_plausibleafterbirth_drug_exposure_drug_exposure_end_date
1.20 field_plausibleafterbirth_drug_exposure_drug_exposure_end_datetime
1.21 field_plausibleafterbirth_drug_exposure_verbatim_end_date
1.22 field_plausibleafterbirth_measurement_measurement_date
1.23 field_plausibleafterbirth_measurement_measurement_datetime
1.24 field_plausibleafterbirth_note_note_date
1.25 field_plausibleafterbirth_note_note_datetime
1.26 field_plausibleafterbirth_observation_observation_date
1.27 field_plausibleafterbirth_observation_observation_datetime
1.28 field_plausibleafterbirth_observation_period_observation_period_start_date
1.29 field_plausibleafterbirth_observation_period_observation_period_end_date
1.30 field_plausibleafterbirth_payer_plan_period_payer_plan_period_start_date
1.31 field_plausibleafterbirth_payer_plan_period_payer_plan_period_end_date
1.32 field_plausibleafterbirth_procedure_occurrence_procedure_date
1.33 field_plausibleafterbirth_procedure_occurrence_procedure_datetime
1.34 field_plausibleafterbirth_specimen_specimen_date
1.35 field_plausibleafterbirth_specimen_specimen_datetime
1.36 field_plausibleafterbirth_visit_detail_visit_detail_start_date
1.37 field_plausibleafterbirth_visit_detail_visit_detail_start_datetime
1.38 field_plausibleafterbirth_visit_detail_visit_detail_end_date
1.39 field_plausibleafterbirth_visit_detail_visit_detail_end_datetime
1.40 field_plausibleafterbirth_visit_occurrence_visit_start_date
1.41 field_plausibleafterbirth_visit_occurrence_visit_start_datetime
1.42 field_plausibleafterbirth_visit_occurrence_visit_end_date
1.43 field_plausibleafterbirth_visit_occurrence_visit_end_datetime
failed passed isError notApplicable notApplicableReason thresholdValue
1.1 0 0 0 0 NA 1
1.2 0 0 0 0 NA 1
1.3 0 0 0 0 NA 1
1.4 0 0 0 0 NA 1
1.5 0 0 0 0 NA 1
1.6 0 0 0 0 NA 1
1.7 0 0 0 0 NA 1
1.8 0 0 0 0 NA 1
1.9 0 0 0 0 NA 1
1.10 0 0 0 0 NA 1
1.11 0 0 0 0 NA 1
1.12 0 0 0 0 NA 1
1.13 0 0 0 0 NA 1
1.14 0 0 0 0 NA 1
1.15 0 0 0 0 NA 1
1.16 0 0 0 0 NA 1
1.17 0 0 0 0 NA 1
1.18 0 0 0 0 NA 1
1.19 0 0 0 0 NA 1
1.20 0 0 0 0 NA 1
1.21 0 0 0 0 NA 1
1.22 0 0 0 0 NA 1
1.23 0 0 0 0 NA 1
1.24 0 0 0 0 NA 1
1.25 0 0 0 0 NA 1
1.26 0 0 0 0 NA 1
1.27 0 0 0 0 NA 1
1.28 0 0 0 0 NA 1
1.29 0 0 0 0 NA 1
1.30 0 0 0 0 NA 1
1.31 0 0 0 0 NA 1
1.32 0 0 0 0 NA 1
1.33 0 0 0 0 NA 1
1.34 0 0 0 0 NA 1
1.35 0 0 0 0 NA 1
1.36 0 0 0 0 NA 1
1.37 0 0 0 0 NA 1
1.38 0 0 0 0 NA 1
1.39 0 0 0 0 NA 1
1.40 0 0 0 0 NA 1
1.41 0 0 0 0 NA 1
1.42 0 0 0 0 NA 1
1.43 0 0 0 0 NA 1
notesValue
1.1 NA
1.2 NA
1.3 NA
1.4 NA
1.5 NA
1.6 NA
1.7 NA
1.8 NA
1.9 NA
1.10 NA
1.11 NA
1.12 NA
1.13 NA
1.14 NA
1.15 NA
1.16 NA
1.17 NA
1.18 NA
1.19 NA
1.20 NA
1.21 NA
1.22 NA
1.23 NA
1.24 NA
1.25 NA
1.26 NA
1.27 NA
1.28 NA
1.29 NA
1.30 NA
1.31 NA
1.32 NA
1.33 NA
1.34 NA
1.35 NA
1.36 NA
1.37 NA
1.38 NA
1.39 NA
1.40 NA
1.41 NA
1.42 NA
1.43 NA
All results for visit_occurrence:
numViolatedRows pctViolatedRows numDenominatorRows executionTime
1.36 0 0 0 0.012587 secs
1.37 0 0 0 0.005390 secs
1.38 0 0 0 0.005678 secs
1.39 0 0 0 0.004983 secs
1.40 0 0 1 0.006515 secs
1.41 0 0 1 0.007083 secs
1.42 0 0 1 0.007306 secs
1.43 0 0 1 0.007472 secs
queryText
1.36 /*********\nPLAUSIBLE_AFTER_BIRTH\nChecks that all events happen after birth (PLAUSIBLE_AFTER_BIRTH == Yes)\nBirthdate is either birth_datetime or composed from year_of_birth, month_of_birth, day_of_birth (taking 1st month/1st day if missing).\nDenominator is number of events with a non-null date.\nParameters used in this template:\nschema = main\ncdmDatabaseSchema = main\ncdmTableName = VISIT_DETAIL\ncdmFieldName = VISIT_DETAIL_START_DATE\n**********/\nSELECT \n num_violated_rows, \n CASE \n WHEN denominator.num_rows = 0 THEN 0 \n ELSE 1.0*num_violated_rows/denominator.num_rows \n END AS pct_violated_rows, \n denominator.num_rows AS num_denominator_rows\nFROM\n(\n SELECT \n COUNT(violated_rows.violating_field) AS num_violated_rows\n FROM\n (\n /*violatedRowsBegin*/\n SELECT \n 'VISIT_DETAIL.VISIT_DETAIL_START_DATE' AS violating_field, \n cdmTable.*\n FROM main.VISIT_DETAIL cdmTable\n JOIN main.person p \n ON cdmTable.person_id = p.person_id\n WHERE cdmTable.VISIT_DETAIL_START_DATE IS NOT NULL AND \n CAST(STRFTIME('%s', SUBSTR(CAST(cdmTable.VISIT_DETAIL_START_DATE AS TEXT), 1, 4) || '-' || SUBSTR(CAST(cdmTable.VISIT_DETAIL_START_DATE AS TEXT), 5, 2) || '-' || SUBSTR(CAST(cdmTable.VISIT_DETAIL_START_DATE AS TEXT), 7)) AS REAL) < COALESCE(\n CAST(STRFTIME('%s', SUBSTR(CAST(p.birth_datetime AS TEXT), 1, 4) || '-' || SUBSTR(CAST(p.birth_datetime AS TEXT), 5, 2) || '-' || SUBSTR(CAST(p.birth_datetime AS TEXT), 7)) AS REAL), \n CAST(STRFTIME('%s', SUBSTR(CAST(p.year_of_birth || COALESCE(\n SUBSTR(CAST('0' || CAST(p.month_of_birth AS TEXT) AS TEXT),-2),\n '01'\n ) || COALESCE(\n SUBSTR(CAST('0' || CAST(p.day_of_birth AS TEXT) AS TEXT),-2),\n '01'\n )\n AS TEXT), 1, 4) || '-' || SUBSTR(CAST(p.year_of_birth || COALESCE(\n SUBSTR(CAST('0' || CAST(p.month_of_birth AS TEXT) AS TEXT),-2),\n '01'\n ) || COALESCE(\n SUBSTR(CAST('0' || CAST(p.day_of_birth AS TEXT) AS TEXT),-2),\n '01'\n )\n AS TEXT), 5, 2) || '-' || SUBSTR(CAST(p.year_of_birth || COALESCE(\n SUBSTR(CAST('0' || CAST(p.month_of_birth AS TEXT) AS TEXT),-2),\n '01'\n ) || COALESCE(\n SUBSTR(CAST('0' || CAST(p.day_of_birth AS TEXT) AS TEXT),-2),\n '01'\n )\n AS TEXT), 7)) AS REAL)\n )\n /*violatedRowsEnd*/\n ) violated_rows\n) violated_row_count,\n(\n SELECT \n COUNT(*) AS num_rows\n FROM main.VISIT_DETAIL cdmTable\n WHERE cdmTable.VISIT_DETAIL_START_DATE IS NOT NULL\n) denominator\n;\n
1.37 /*********\nPLAUSIBLE_AFTER_BIRTH\nChecks that all events happen after birth (PLAUSIBLE_AFTER_BIRTH == Yes)\nBirthdate is either birth_datetime or composed from year_of_birth, month_of_birth, day_of_birth (taking 1st month/1st day if missing).\nDenominator is number of events with a non-null date.\nParameters used in this template:\nschema = main\ncdmDatabaseSchema = main\ncdmTableName = VISIT_DETAIL\ncdmFieldName = VISIT_DETAIL_START_DATETIME\n**********/\nSELECT \n num_violated_rows, \n CASE \n WHEN denominator.num_rows = 0 THEN 0 \n ELSE 1.0*num_violated_rows/denominator.num_rows \n END AS pct_violated_rows, \n denominator.num_rows AS num_denominator_rows\nFROM\n(\n SELECT \n COUNT(violated_rows.violating_field) AS num_violated_rows\n FROM\n (\n /*violatedRowsBegin*/\n SELECT \n 'VISIT_DETAIL.VISIT_DETAIL_START_DATETIME' AS violating_field, \n cdmTable.*\n FROM main.VISIT_DETAIL cdmTable\n JOIN main.person p \n ON cdmTable.person_id = p.person_id\n WHERE cdmTable.VISIT_DETAIL_START_DATETIME IS NOT NULL AND \n CAST(STRFTIME('%s', SUBSTR(CAST(cdmTable.VISIT_DETAIL_START_DATETIME AS TEXT), 1, 4) || '-' || SUBSTR(CAST(cdmTable.VISIT_DETAIL_START_DATETIME AS TEXT), 5, 2) || '-' || SUBSTR(CAST(cdmTable.VISIT_DETAIL_START_DATETIME AS TEXT), 7)) AS REAL) < COALESCE(\n CAST(STRFTIME('%s', SUBSTR(CAST(p.birth_datetime AS TEXT), 1, 4) || '-' || SUBSTR(CAST(p.birth_datetime AS TEXT), 5, 2) || '-' || SUBSTR(CAST(p.birth_datetime AS TEXT), 7)) AS REAL), \n CAST(STRFTIME('%s', SUBSTR(CAST(p.year_of_birth || COALESCE(\n SUBSTR(CAST('0' || CAST(p.month_of_birth AS TEXT) AS TEXT),-2),\n '01'\n ) || COALESCE(\n SUBSTR(CAST('0' || CAST(p.day_of_birth AS TEXT) AS TEXT),-2),\n '01'\n )\n AS TEXT), 1, 4) || '-' || SUBSTR(CAST(p.year_of_birth || COALESCE(\n SUBSTR(CAST('0' || CAST(p.month_of_birth AS TEXT) AS TEXT),-2),\n '01'\n ) || COALESCE(\n SUBSTR(CAST('0' || CAST(p.day_of_birth AS TEXT) AS TEXT),-2),\n '01'\n )\n AS TEXT), 5, 2) || '-' || SUBSTR(CAST(p.year_of_birth || COALESCE(\n SUBSTR(CAST('0' || CAST(p.month_of_birth AS TEXT) AS TEXT),-2),\n '01'\n ) || COALESCE(\n SUBSTR(CAST('0' || CAST(p.day_of_birth AS TEXT) AS TEXT),-2),\n '01'\n )\n AS TEXT), 7)) AS REAL)\n )\n /*violatedRowsEnd*/\n ) violated_rows\n) violated_row_count,\n(\n SELECT \n COUNT(*) AS num_rows\n FROM main.VISIT_DETAIL cdmTable\n WHERE cdmTable.VISIT_DETAIL_START_DATETIME IS NOT NULL\n) denominator\n;\n
1.38 /*********\nPLAUSIBLE_AFTER_BIRTH\nChecks that all events happen after birth (PLAUSIBLE_AFTER_BIRTH == Yes)\nBirthdate is either birth_datetime or composed from year_of_birth, month_of_birth, day_of_birth (taking 1st month/1st day if missing).\nDenominator is number of events with a non-null date.\nParameters used in this template:\nschema = main\ncdmDatabaseSchema = main\ncdmTableName = VISIT_DETAIL\ncdmFieldName = VISIT_DETAIL_END_DATE\n**********/\nSELECT \n num_violated_rows, \n CASE \n WHEN denominator.num_rows = 0 THEN 0 \n ELSE 1.0*num_violated_rows/denominator.num_rows \n END AS pct_violated_rows, \n denominator.num_rows AS num_denominator_rows\nFROM\n(\n SELECT \n COUNT(violated_rows.violating_field) AS num_violated_rows\n FROM\n (\n /*violatedRowsBegin*/\n SELECT \n 'VISIT_DETAIL.VISIT_DETAIL_END_DATE' AS violating_field, \n cdmTable.*\n FROM main.VISIT_DETAIL cdmTable\n JOIN main.person p \n ON cdmTable.person_id = p.person_id\n WHERE cdmTable.VISIT_DETAIL_END_DATE IS NOT NULL AND \n CAST(STRFTIME('%s', SUBSTR(CAST(cdmTable.VISIT_DETAIL_END_DATE AS TEXT), 1, 4) || '-' || SUBSTR(CAST(cdmTable.VISIT_DETAIL_END_DATE AS TEXT), 5, 2) || '-' || SUBSTR(CAST(cdmTable.VISIT_DETAIL_END_DATE AS TEXT), 7)) AS REAL) < COALESCE(\n CAST(STRFTIME('%s', SUBSTR(CAST(p.birth_datetime AS TEXT), 1, 4) || '-' || SUBSTR(CAST(p.birth_datetime AS TEXT), 5, 2) || '-' || SUBSTR(CAST(p.birth_datetime AS TEXT), 7)) AS REAL), \n CAST(STRFTIME('%s', SUBSTR(CAST(p.year_of_birth || COALESCE(\n SUBSTR(CAST('0' || CAST(p.month_of_birth AS TEXT) AS TEXT),-2),\n '01'\n ) || COALESCE(\n SUBSTR(CAST('0' || CAST(p.day_of_birth AS TEXT) AS TEXT),-2),\n '01'\n )\n AS TEXT), 1, 4) || '-' || SUBSTR(CAST(p.year_of_birth || COALESCE(\n SUBSTR(CAST('0' || CAST(p.month_of_birth AS TEXT) AS TEXT),-2),\n '01'\n ) || COALESCE(\n SUBSTR(CAST('0' || CAST(p.day_of_birth AS TEXT) AS TEXT),-2),\n '01'\n )\n AS TEXT), 5, 2) || '-' || SUBSTR(CAST(p.year_of_birth || COALESCE(\n SUBSTR(CAST('0' || CAST(p.month_of_birth AS TEXT) AS TEXT),-2),\n '01'\n ) || COALESCE(\n SUBSTR(CAST('0' || CAST(p.day_of_birth AS TEXT) AS TEXT),-2),\n '01'\n )\n AS TEXT), 7)) AS REAL)\n )\n /*violatedRowsEnd*/\n ) violated_rows\n) violated_row_count,\n(\n SELECT \n COUNT(*) AS num_rows\n FROM main.VISIT_DETAIL cdmTable\n WHERE cdmTable.VISIT_DETAIL_END_DATE IS NOT NULL\n) denominator\n;\n
1.39 /*********\nPLAUSIBLE_AFTER_BIRTH\nChecks that all events happen after birth (PLAUSIBLE_AFTER_BIRTH == Yes)\nBirthdate is either birth_datetime or composed from year_of_birth, month_of_birth, day_of_birth (taking 1st month/1st day if missing).\nDenominator is number of events with a non-null date.\nParameters used in this template:\nschema = main\ncdmDatabaseSchema = main\ncdmTableName = VISIT_DETAIL\ncdmFieldName = VISIT_DETAIL_END_DATETIME\n**********/\nSELECT \n num_violated_rows, \n CASE \n WHEN denominator.num_rows = 0 THEN 0 \n ELSE 1.0*num_violated_rows/denominator.num_rows \n END AS pct_violated_rows, \n denominator.num_rows AS num_denominator_rows\nFROM\n(\n SELECT \n COUNT(violated_rows.violating_field) AS num_violated_rows\n FROM\n (\n /*violatedRowsBegin*/\n SELECT \n 'VISIT_DETAIL.VISIT_DETAIL_END_DATETIME' AS violating_field, \n cdmTable.*\n FROM main.VISIT_DETAIL cdmTable\n JOIN main.person p \n ON cdmTable.person_id = p.person_id\n WHERE cdmTable.VISIT_DETAIL_END_DATETIME IS NOT NULL AND \n CAST(STRFTIME('%s', SUBSTR(CAST(cdmTable.VISIT_DETAIL_END_DATETIME AS TEXT), 1, 4) || '-' || SUBSTR(CAST(cdmTable.VISIT_DETAIL_END_DATETIME AS TEXT), 5, 2) || '-' || SUBSTR(CAST(cdmTable.VISIT_DETAIL_END_DATETIME AS TEXT), 7)) AS REAL) < COALESCE(\n CAST(STRFTIME('%s', SUBSTR(CAST(p.birth_datetime AS TEXT), 1, 4) || '-' || SUBSTR(CAST(p.birth_datetime AS TEXT), 5, 2) || '-' || SUBSTR(CAST(p.birth_datetime AS TEXT), 7)) AS REAL), \n CAST(STRFTIME('%s', SUBSTR(CAST(p.year_of_birth || COALESCE(\n SUBSTR(CAST('0' || CAST(p.month_of_birth AS TEXT) AS TEXT),-2),\n '01'\n ) || COALESCE(\n SUBSTR(CAST('0' || CAST(p.day_of_birth AS TEXT) AS TEXT),-2),\n '01'\n )\n AS TEXT), 1, 4) || '-' || SUBSTR(CAST(p.year_of_birth || COALESCE(\n SUBSTR(CAST('0' || CAST(p.month_of_birth AS TEXT) AS TEXT),-2),\n '01'\n ) || COALESCE(\n SUBSTR(CAST('0' || CAST(p.day_of_birth AS TEXT) AS TEXT),-2),\n '01'\n )\n AS TEXT), 5, 2) || '-' || SUBSTR(CAST(p.year_of_birth || COALESCE(\n SUBSTR(CAST('0' || CAST(p.month_of_birth AS TEXT) AS TEXT),-2),\n '01'\n ) || COALESCE(\n SUBSTR(CAST('0' || CAST(p.day_of_birth AS TEXT) AS TEXT),-2),\n '01'\n )\n AS TEXT), 7)) AS REAL)\n )\n /*violatedRowsEnd*/\n ) violated_rows\n) violated_row_count,\n(\n SELECT \n COUNT(*) AS num_rows\n FROM main.VISIT_DETAIL cdmTable\n WHERE cdmTable.VISIT_DETAIL_END_DATETIME IS NOT NULL\n) denominator\n;\n
1.40 /*********\nPLAUSIBLE_AFTER_BIRTH\nChecks that all events happen after birth (PLAUSIBLE_AFTER_BIRTH == Yes)\nBirthdate is either birth_datetime or composed from year_of_birth, month_of_birth, day_of_birth (taking 1st month/1st day if missing).\nDenominator is number of events with a non-null date.\nParameters used in this template:\nschema = main\ncdmDatabaseSchema = main\ncdmTableName = VISIT_OCCURRENCE\ncdmFieldName = VISIT_START_DATE\n**********/\nSELECT \n num_violated_rows, \n CASE \n WHEN denominator.num_rows = 0 THEN 0 \n ELSE 1.0*num_violated_rows/denominator.num_rows \n END AS pct_violated_rows, \n denominator.num_rows AS num_denominator_rows\nFROM\n(\n SELECT \n COUNT(violated_rows.violating_field) AS num_violated_rows\n FROM\n (\n /*violatedRowsBegin*/\n SELECT \n 'VISIT_OCCURRENCE.VISIT_START_DATE' AS violating_field, \n cdmTable.*\n FROM main.VISIT_OCCURRENCE cdmTable\n JOIN main.person p \n ON cdmTable.person_id = p.person_id\n WHERE cdmTable.VISIT_START_DATE IS NOT NULL AND \n CAST(STRFTIME('%s', SUBSTR(CAST(cdmTable.VISIT_START_DATE AS TEXT), 1, 4) || '-' || SUBSTR(CAST(cdmTable.VISIT_START_DATE AS TEXT), 5, 2) || '-' || SUBSTR(CAST(cdmTable.VISIT_START_DATE AS TEXT), 7)) AS REAL) < COALESCE(\n CAST(STRFTIME('%s', SUBSTR(CAST(p.birth_datetime AS TEXT), 1, 4) || '-' || SUBSTR(CAST(p.birth_datetime AS TEXT), 5, 2) || '-' || SUBSTR(CAST(p.birth_datetime AS TEXT), 7)) AS REAL), \n CAST(STRFTIME('%s', SUBSTR(CAST(p.year_of_birth || COALESCE(\n SUBSTR(CAST('0' || CAST(p.month_of_birth AS TEXT) AS TEXT),-2),\n '01'\n ) || COALESCE(\n SUBSTR(CAST('0' || CAST(p.day_of_birth AS TEXT) AS TEXT),-2),\n '01'\n )\n AS TEXT), 1, 4) || '-' || SUBSTR(CAST(p.year_of_birth || COALESCE(\n SUBSTR(CAST('0' || CAST(p.month_of_birth AS TEXT) AS TEXT),-2),\n '01'\n ) || COALESCE(\n SUBSTR(CAST('0' || CAST(p.day_of_birth AS TEXT) AS TEXT),-2),\n '01'\n )\n AS TEXT), 5, 2) || '-' || SUBSTR(CAST(p.year_of_birth || COALESCE(\n SUBSTR(CAST('0' || CAST(p.month_of_birth AS TEXT) AS TEXT),-2),\n '01'\n ) || COALESCE(\n SUBSTR(CAST('0' || CAST(p.day_of_birth AS TEXT) AS TEXT),-2),\n '01'\n )\n AS TEXT), 7)) AS REAL)\n )\n /*violatedRowsEnd*/\n ) violated_rows\n) violated_row_count,\n(\n SELECT \n COUNT(*) AS num_rows\n FROM main.VISIT_OCCURRENCE cdmTable\n WHERE cdmTable.VISIT_START_DATE IS NOT NULL\n) denominator\n;\n
1.41 /*********\nPLAUSIBLE_AFTER_BIRTH\nChecks that all events happen after birth (PLAUSIBLE_AFTER_BIRTH == Yes)\nBirthdate is either birth_datetime or composed from year_of_birth, month_of_birth, day_of_birth (taking 1st month/1st day if missing).\nDenominator is number of events with a non-null date.\nParameters used in this template:\nschema = main\ncdmDatabaseSchema = main\ncdmTableName = VISIT_OCCURRENCE\ncdmFieldName = VISIT_START_DATETIME\n**********/\nSELECT \n num_violated_rows, \n CASE \n WHEN denominator.num_rows = 0 THEN 0 \n ELSE 1.0*num_violated_rows/denominator.num_rows \n END AS pct_violated_rows, \n denominator.num_rows AS num_denominator_rows\nFROM\n(\n SELECT \n COUNT(violated_rows.violating_field) AS num_violated_rows\n FROM\n (\n /*violatedRowsBegin*/\n SELECT \n 'VISIT_OCCURRENCE.VISIT_START_DATETIME' AS violating_field, \n cdmTable.*\n FROM main.VISIT_OCCURRENCE cdmTable\n JOIN main.person p \n ON cdmTable.person_id = p.person_id\n WHERE cdmTable.VISIT_START_DATETIME IS NOT NULL AND \n CAST(STRFTIME('%s', SUBSTR(CAST(cdmTable.VISIT_START_DATETIME AS TEXT), 1, 4) || '-' || SUBSTR(CAST(cdmTable.VISIT_START_DATETIME AS TEXT), 5, 2) || '-' || SUBSTR(CAST(cdmTable.VISIT_START_DATETIME AS TEXT), 7)) AS REAL) < COALESCE(\n CAST(STRFTIME('%s', SUBSTR(CAST(p.birth_datetime AS TEXT), 1, 4) || '-' || SUBSTR(CAST(p.birth_datetime AS TEXT), 5, 2) || '-' || SUBSTR(CAST(p.birth_datetime AS TEXT), 7)) AS REAL), \n CAST(STRFTIME('%s', SUBSTR(CAST(p.year_of_birth || COALESCE(\n SUBSTR(CAST('0' || CAST(p.month_of_birth AS TEXT) AS TEXT),-2),\n '01'\n ) || COALESCE(\n SUBSTR(CAST('0' || CAST(p.day_of_birth AS TEXT) AS TEXT),-2),\n '01'\n )\n AS TEXT), 1, 4) || '-' || SUBSTR(CAST(p.year_of_birth || COALESCE(\n SUBSTR(CAST('0' || CAST(p.month_of_birth AS TEXT) AS TEXT),-2),\n '01'\n ) || COALESCE(\n SUBSTR(CAST('0' || CAST(p.day_of_birth AS TEXT) AS TEXT),-2),\n '01'\n )\n AS TEXT), 5, 2) || '-' || SUBSTR(CAST(p.year_of_birth || COALESCE(\n SUBSTR(CAST('0' || CAST(p.month_of_birth AS TEXT) AS TEXT),-2),\n '01'\n ) || COALESCE(\n SUBSTR(CAST('0' || CAST(p.day_of_birth AS TEXT) AS TEXT),-2),\n '01'\n )\n AS TEXT), 7)) AS REAL)\n )\n /*violatedRowsEnd*/\n ) violated_rows\n) violated_row_count,\n(\n SELECT \n COUNT(*) AS num_rows\n FROM main.VISIT_OCCURRENCE cdmTable\n WHERE cdmTable.VISIT_START_DATETIME IS NOT NULL\n) denominator\n;\n
1.42 /*********\nPLAUSIBLE_AFTER_BIRTH\nChecks that all events happen after birth (PLAUSIBLE_AFTER_BIRTH == Yes)\nBirthdate is either birth_datetime or composed from year_of_birth, month_of_birth, day_of_birth (taking 1st month/1st day if missing).\nDenominator is number of events with a non-null date.\nParameters used in this template:\nschema = main\ncdmDatabaseSchema = main\ncdmTableName = VISIT_OCCURRENCE\ncdmFieldName = VISIT_END_DATE\n**********/\nSELECT \n num_violated_rows, \n CASE \n WHEN denominator.num_rows = 0 THEN 0 \n ELSE 1.0*num_violated_rows/denominator.num_rows \n END AS pct_violated_rows, \n denominator.num_rows AS num_denominator_rows\nFROM\n(\n SELECT \n COUNT(violated_rows.violating_field) AS num_violated_rows\n FROM\n (\n /*violatedRowsBegin*/\n SELECT \n 'VISIT_OCCURRENCE.VISIT_END_DATE' AS violating_field, \n cdmTable.*\n FROM main.VISIT_OCCURRENCE cdmTable\n JOIN main.person p \n ON cdmTable.person_id = p.person_id\n WHERE cdmTable.VISIT_END_DATE IS NOT NULL AND \n CAST(STRFTIME('%s', SUBSTR(CAST(cdmTable.VISIT_END_DATE AS TEXT), 1, 4) || '-' || SUBSTR(CAST(cdmTable.VISIT_END_DATE AS TEXT), 5, 2) || '-' || SUBSTR(CAST(cdmTable.VISIT_END_DATE AS TEXT), 7)) AS REAL) < COALESCE(\n CAST(STRFTIME('%s', SUBSTR(CAST(p.birth_datetime AS TEXT), 1, 4) || '-' || SUBSTR(CAST(p.birth_datetime AS TEXT), 5, 2) || '-' || SUBSTR(CAST(p.birth_datetime AS TEXT), 7)) AS REAL), \n CAST(STRFTIME('%s', SUBSTR(CAST(p.year_of_birth || COALESCE(\n SUBSTR(CAST('0' || CAST(p.month_of_birth AS TEXT) AS TEXT),-2),\n '01'\n ) || COALESCE(\n SUBSTR(CAST('0' || CAST(p.day_of_birth AS TEXT) AS TEXT),-2),\n '01'\n )\n AS TEXT), 1, 4) || '-' || SUBSTR(CAST(p.year_of_birth || COALESCE(\n SUBSTR(CAST('0' || CAST(p.month_of_birth AS TEXT) AS TEXT),-2),\n '01'\n ) || COALESCE(\n SUBSTR(CAST('0' || CAST(p.day_of_birth AS TEXT) AS TEXT),-2),\n '01'\n )\n AS TEXT), 5, 2) || '-' || SUBSTR(CAST(p.year_of_birth || COALESCE(\n SUBSTR(CAST('0' || CAST(p.month_of_birth AS TEXT) AS TEXT),-2),\n '01'\n ) || COALESCE(\n SUBSTR(CAST('0' || CAST(p.day_of_birth AS TEXT) AS TEXT),-2),\n '01'\n )\n AS TEXT), 7)) AS REAL)\n )\n /*violatedRowsEnd*/\n ) violated_rows\n) violated_row_count,\n(\n SELECT \n COUNT(*) AS num_rows\n FROM main.VISIT_OCCURRENCE cdmTable\n WHERE cdmTable.VISIT_END_DATE IS NOT NULL\n) denominator\n;\n
1.43 /*********\nPLAUSIBLE_AFTER_BIRTH\nChecks that all events happen after birth (PLAUSIBLE_AFTER_BIRTH == Yes)\nBirthdate is either birth_datetime or composed from year_of_birth, month_of_birth, day_of_birth (taking 1st month/1st day if missing).\nDenominator is number of events with a non-null date.\nParameters used in this template:\nschema = main\ncdmDatabaseSchema = main\ncdmTableName = VISIT_OCCURRENCE\ncdmFieldName = VISIT_END_DATETIME\n**********/\nSELECT \n num_violated_rows, \n CASE \n WHEN denominator.num_rows = 0 THEN 0 \n ELSE 1.0*num_violated_rows/denominator.num_rows \n END AS pct_violated_rows, \n denominator.num_rows AS num_denominator_rows\nFROM\n(\n SELECT \n COUNT(violated_rows.violating_field) AS num_violated_rows\n FROM\n (\n /*violatedRowsBegin*/\n SELECT \n 'VISIT_OCCURRENCE.VISIT_END_DATETIME' AS violating_field, \n cdmTable.*\n FROM main.VISIT_OCCURRENCE cdmTable\n JOIN main.person p \n ON cdmTable.person_id = p.person_id\n WHERE cdmTable.VISIT_END_DATETIME IS NOT NULL AND \n CAST(STRFTIME('%s', SUBSTR(CAST(cdmTable.VISIT_END_DATETIME AS TEXT), 1, 4) || '-' || SUBSTR(CAST(cdmTable.VISIT_END_DATETIME AS TEXT), 5, 2) || '-' || SUBSTR(CAST(cdmTable.VISIT_END_DATETIME AS TEXT), 7)) AS REAL) < COALESCE(\n CAST(STRFTIME('%s', SUBSTR(CAST(p.birth_datetime AS TEXT), 1, 4) || '-' || SUBSTR(CAST(p.birth_datetime AS TEXT), 5, 2) || '-' || SUBSTR(CAST(p.birth_datetime AS TEXT), 7)) AS REAL), \n CAST(STRFTIME('%s', SUBSTR(CAST(p.year_of_birth || COALESCE(\n SUBSTR(CAST('0' || CAST(p.month_of_birth AS TEXT) AS TEXT),-2),\n '01'\n ) || COALESCE(\n SUBSTR(CAST('0' || CAST(p.day_of_birth AS TEXT) AS TEXT),-2),\n '01'\n )\n AS TEXT), 1, 4) || '-' || SUBSTR(CAST(p.year_of_birth || COALESCE(\n SUBSTR(CAST('0' || CAST(p.month_of_birth AS TEXT) AS TEXT),-2),\n '01'\n ) || COALESCE(\n SUBSTR(CAST('0' || CAST(p.day_of_birth AS TEXT) AS TEXT),-2),\n '01'\n )\n AS TEXT), 5, 2) || '-' || SUBSTR(CAST(p.year_of_birth || COALESCE(\n SUBSTR(CAST('0' || CAST(p.month_of_birth AS TEXT) AS TEXT),-2),\n '01'\n ) || COALESCE(\n SUBSTR(CAST('0' || CAST(p.day_of_birth AS TEXT) AS TEXT),-2),\n '01'\n )\n AS TEXT), 7)) AS REAL)\n )\n /*violatedRowsEnd*/\n ) violated_rows\n) violated_row_count,\n(\n SELECT \n COUNT(*) AS num_rows\n FROM main.VISIT_OCCURRENCE cdmTable\n WHERE cdmTable.VISIT_END_DATETIME IS NOT NULL\n) denominator\n;\n
checkName checkLevel
1.36 plausibleAfterBirth FIELD
1.37 plausibleAfterBirth FIELD
1.38 plausibleAfterBirth FIELD
1.39 plausibleAfterBirth FIELD
1.40 plausibleAfterBirth FIELD
1.41 plausibleAfterBirth FIELD
1.42 plausibleAfterBirth FIELD
1.43 plausibleAfterBirth FIELD
checkDescription
1.36 The number and percent of records with a date value in the VISIT_DETAIL_START_DATE field of the VISIT_DETAIL table that occurs prior to birth.
1.37 The number and percent of records with a date value in the VISIT_DETAIL_START_DATETIME field of the VISIT_DETAIL table that occurs prior to birth.
1.38 The number and percent of records with a date value in the VISIT_DETAIL_END_DATE field of the VISIT_DETAIL table that occurs prior to birth.
1.39 The number and percent of records with a date value in the VISIT_DETAIL_END_DATETIME field of the VISIT_DETAIL table that occurs prior to birth.
1.40 The number and percent of records with a date value in the VISIT_START_DATE field of the VISIT_OCCURRENCE table that occurs prior to birth.
1.41 The number and percent of records with a date value in the VISIT_START_DATETIME field of the VISIT_OCCURRENCE table that occurs prior to birth.
1.42 The number and percent of records with a date value in the VISIT_END_DATE field of the VISIT_OCCURRENCE table that occurs prior to birth.
1.43 The number and percent of records with a date value in the VISIT_END_DATETIME field of the VISIT_OCCURRENCE table that occurs prior to birth.
cdmTableName cdmFieldName conceptId unitConceptId
1.36 VISIT_DETAIL VISIT_DETAIL_START_DATE <NA> <NA>
1.37 VISIT_DETAIL VISIT_DETAIL_START_DATETIME <NA> <NA>
1.38 VISIT_DETAIL VISIT_DETAIL_END_DATE <NA> <NA>
1.39 VISIT_DETAIL VISIT_DETAIL_END_DATETIME <NA> <NA>
1.40 VISIT_OCCURRENCE VISIT_START_DATE <NA> <NA>
1.41 VISIT_OCCURRENCE VISIT_START_DATETIME <NA> <NA>
1.42 VISIT_OCCURRENCE VISIT_END_DATE <NA> <NA>
1.43 VISIT_OCCURRENCE VISIT_END_DATETIME <NA> <NA>
sqlFile category subcategory context
1.36 field_plausible_after_birth.sql Plausibility Temporal Verification
1.37 field_plausible_after_birth.sql Plausibility Temporal Verification
1.38 field_plausible_after_birth.sql Plausibility Temporal Verification
1.39 field_plausible_after_birth.sql Plausibility Temporal Verification
1.40 field_plausible_after_birth.sql Plausibility Temporal Verification
1.41 field_plausible_after_birth.sql Plausibility Temporal Verification
1.42 field_plausible_after_birth.sql Plausibility Temporal Verification
1.43 field_plausible_after_birth.sql Plausibility Temporal Verification
warning error
1.36 NA NA
1.37 NA NA
1.38 NA NA
1.39 NA NA
1.40 NA NA
1.41 NA NA
1.42 NA NA
1.43 NA NA
checkId failed
1.36 field_plausibleafterbirth_visit_detail_visit_detail_start_date 0
1.37 field_plausibleafterbirth_visit_detail_visit_detail_start_datetime 0
1.38 field_plausibleafterbirth_visit_detail_visit_detail_end_date 0
1.39 field_plausibleafterbirth_visit_detail_visit_detail_end_datetime 0
1.40 field_plausibleafterbirth_visit_occurrence_visit_start_date 0
1.41 field_plausibleafterbirth_visit_occurrence_visit_start_datetime 0
1.42 field_plausibleafterbirth_visit_occurrence_visit_end_date 0
1.43 field_plausibleafterbirth_visit_occurrence_visit_end_datetime 0
passed isError notApplicable notApplicableReason thresholdValue notesValue
1.36 0 0 0 NA 1 NA
1.37 0 0 0 NA 1 NA
1.38 0 0 0 NA 1 NA
1.39 0 0 0 NA 1 NA
1.40 0 0 0 NA 1 NA
1.41 0 0 0 NA 1 NA
1.42 0 0 0 NA 1 NA
1.43 0 0 0 NA 1 NA
Connecting using SQLite driver
-- Column specification --------------------------------------------------------
cols(
checkLevel = col_character(),
checkName = col_character(),
checkDescription = col_character(),
kahnContext = col_character(),
kahnCategory = col_character(),
kahnSubcategory = col_character(),
sqlFile = col_character(),
evaluationFilter = col_character(),
severity = col_character()
)
CDM Tables skipped: CONCEPT, VOCABULARY, CONCEPT_ANCESTOR, CONCEPT_RELATIONSHIP, CONCEPT_CLASS, CONCEPT_SYNONYM, RELATIONSHIP, DOMAIN
Processing check description: measurePersonCompleteness
Writing results to file: /tmp/Rtmp9gDanm/dqd_b7752f6d5e9a/synthea-20260513144137.json
Execution Complete
Writing results to file: /tmp/Rtmp9gDanm/dqd_b7752f6d5e9a/reEvaluated.txt
Connecting using SQLite driver
-- Column specification --------------------------------------------------------
cols(
checkLevel = col_character(),
checkName = col_character(),
checkDescription = col_character(),
kahnContext = col_character(),
kahnCategory = col_character(),
kahnSubcategory = col_character(),
sqlFile = col_character(),
evaluationFilter = col_character(),
severity = col_character()
)
CDM Tables skipped: CONCEPT, VOCABULARY, CONCEPT_ANCESTOR, CONCEPT_RELATIONSHIP, CONCEPT_CLASS, CONCEPT_SYNONYM, RELATIONSHIP, DOMAIN
Processing check description: measurePersonCompleteness
Writing results to file: /tmp/Rtmp9gDanm/dqd_b77522f1df3c/synthea-20260513144139.json
Execution Complete
Connecting using SQLite driver
Writing results to table main.dqd_db_results
|
| | 0%
|
|=================================== | 50%
|
|======================================================================| 100%
Executing SQL took 0.00461 secs
Inserting data took 0.0479 secs
Finished writing table
Connecting using SQLite driver
Writing results to file: /tmp/Rtmp9gDanm/dqd_b77522f1df3c/dq-result-test.json
Connecting using SQLite driver
-- Column specification --------------------------------------------------------
cols(
checkLevel = col_character(),
checkName = col_character(),
checkDescription = col_character(),
kahnContext = col_character(),
kahnCategory = col_character(),
kahnSubcategory = col_character(),
sqlFile = col_character(),
evaluationFilter = col_character(),
severity = col_character()
)
CDM Tables skipped: CONCEPT, VOCABULARY, CONCEPT_ANCESTOR, CONCEPT_RELATIONSHIP, CONCEPT_CLASS, CONCEPT_SYNONYM, RELATIONSHIP, DOMAIN
Processing check description: measurePersonCompleteness
Writing results to file: /tmp/Rtmp9gDanm/dqd_b775dd4f1da/synthea-20260513144140.json
Execution Complete
Loading results from /tmp/Rtmp9gDanm/dqd_b775dd4f1da/synthea-20260513144140.json
Writing results to CSV file /tmp/Rtmp9gDanm/dqd_b775dd4f1da/results.csv
Finished writing to CSV file
Connecting using SQLite driver
Writing results to table main.dqd_json_results_table
|
| | 0%
|
|=================================== | 50%
|
|======================================================================| 100%
Executing SQL took 0.00909 secs
Inserting data took 0.0179 secs
Finished writing table
Connecting using SQLite driver
|
| | 0%
|
|======================================================================| 100%
Executing SQL took 0.00708 secs
Connecting using SQLite driver
-- Column specification --------------------------------------------------------
cols(
checkLevel = col_character(),
checkName = col_character(),
checkDescription = col_character(),
kahnContext = col_character(),
kahnCategory = col_character(),
kahnSubcategory = col_character(),
sqlFile = col_character(),
evaluationFilter = col_character(),
severity = col_character()
)
CDM Tables skipped: CONCEPT, VOCABULARY, CONCEPT_ANCESTOR, CONCEPT_RELATIONSHIP, CONCEPT_CLASS, CONCEPT_SYNONYM, RELATIONSHIP, DOMAIN
Processing check description: measurePersonCompleteness
Writing results to file: /tmp/Rtmp9gDanm/dqd_b77554bcf0e/synthea-20260513144142.json
Execution Complete
Connecting using SQLite driver
Writing results to table main.dqd_single_table
|
| | 0%
|
|=================================== | 50%
|
|======================================================================| 100%
Executing SQL took 0.00512 secs
Inserting data took 0.0233 secs
Finished writing table
Connecting using SQLite driver
|
| | 0%
|
|======================================================================| 100%
Executing SQL took 0.00436 secs
Connecting using SQLite driver
Writing results to table main.dqd_separate_tables_table
|
| | 0%
|
|=================================== | 50%
|
|======================================================================| 100%
Executing SQL took 0.00537 secs
Inserting data took 0.0252 secs
Finished writing table
|
| | 0%
|
|======================================================================| 100%
Executing SQL took 0.00442 secs
[ FAIL 1 | WARN 0 | SKIP 5 | PASS 79 ]
══ Skipped tests (5) ═══════════════════════════════════════════════════════════
• On CRAN (3): 'test-executeDqChecks.R:481:1', 'test-executeDqChecks.R:514:1',
'test-executeDqChecks.R:547:1'
• empty test (2): 'test-executeDqChecks.R:280:1',
══ Failed tests ════════════════════════════════════════════════════════════════
── Error ('test-executeDqChecks.R:709:3'): Execute a single DQ check on DuckDB ──
<rlib_error_package_not_found/rlang_error/error/condition>
Error in `Eunomia::getDatabaseFile(datasetName = "GiBleed", dbms = "duckdb")`: The package "duckdb" is required.
Backtrace:
▆
1. └─Eunomia::getDatabaseFile(datasetName = "GiBleed", dbms = "duckdb") at test-executeDqChecks.R:709:3
2. └─rlang::check_installed("duckdb")
[ FAIL 1 | WARN 0 | SKIP 5 | PASS 79 ]
Error:
! Test failures.
Warning message:
call dbDisconnect() when finished working with a connection
Execution halted
Flavor: r-devel-linux-x86_64-debian-gcc