Lab identification in the DSM data: Difference between revisions

From CCMDB Wiki
Jump to navigation Jump to search
mNo edit summary
 
(12 intermediate revisions by 2 users not shown)
Line 7: Line 7:
The field '''Lab_DtTm''' in [[L_Labs_DSM table]] is generated as follows from the inconsistenly populated fields in the export: <!-- as of 2019-12-09-->
The field '''Lab_DtTm''' in [[L_Labs_DSM table]] is generated as follows from the inconsistenly populated fields in the export: <!-- as of 2019-12-09-->
* <code>LabDtTm_str = "CDate(Nz([COLLECTEDDATE], [arriveddate])) + CDate(Nz([CollectedTime], [LabarrivedTime]))"</code>
* <code>LabDtTm_str = "CDate(Nz([COLLECTEDDATE], [arriveddate])) + CDate(Nz([CollectedTime], [LabarrivedTime]))"</code>
** ie use collected dttm if available, else use arrived dttm
=== Start of date range ===
The Lab_DtTm is compared to the admit time of the patient as follows:
* <code>" WHERE ([Admit_DtTm]<=" & LabDtTm_str & ") AND (" & LabDtTm_str & "<=nz([Dispo_DtTm],now())) "</code>
** ie we start the range with the [[Admit DtTm]]
=== date constraints ===
the data we would want to use, but instead on which data was consistently available. Not all these columns are always filled.
*<code>[Arrive_DtTm]<=CDate(Nz([COLLECTEDDATE],[arriveddate]))+CDate(Nz([CollectedTime],[LabarrivedTime]))</code>
*<code>CDate(Nz([COLLECTEDDATE],[arriveddate]))+CDate(Nz([CollectedTime],[LabarrivedTime]))<=[Dispo_DtTm]</code>


=== counts ===
=== counts ===
We originally did labs as counts.  
We originally did labs as counts, but now store them with results. A count of them can be viewed using query ''L_Labs_Flowsheet_DSM_sum''.
A count of them can be viewed using query ''L_Labs_Flowsheet_DSM_sum''.


=== individual record ===
=== individual record ===
Line 17: Line 27:
=== group of records ===
=== group of records ===
Some other lab tests lead to multiple results and are reported as a group of lines per lab done. The best way to identify them was based on the GRPTEST column in the [[DSM Lab Extract]]. That column is compared to table ''s_group'' by query ''2_D_counts_group_appender'' to populate table ''L_Labs_Flowsheet_DSM''.
Some other lab tests lead to multiple results and are reported as a group of lines per lab done. The best way to identify them was based on the GRPTEST column in the [[DSM Lab Extract]]. That column is compared to table ''s_group'' by query ''2_D_counts_group_appender'' to populate table ''L_Labs_Flowsheet_DSM''.
The s_mapping_lab table in [[Instructions for importing a batch of DSM Data]] defines which labs will be counted as which tests.
On every run of [[Instructions for importing a batch of DSM Data]], there is a check to find out if any new labs are in the new data that are not in the table. During testing, most times included a few new labs. For these new labs it would need to be decided if they are a type we collect before finishing the import.


== Updates / new lab Name or Labels in DSM ==
== Updates / new lab Name or Labels in DSM ==

Latest revision as of 14:53, 2023 May 17

This article describes how labs are identified in the DSM Lab Extract during importing.

Matching

Each lab is imported with a value.

Lab_DtTm

The field Lab_DtTm in L_Labs_DSM table is generated as follows from the inconsistenly populated fields in the export:

  • LabDtTm_str = "CDate(Nz([COLLECTEDDATE], [arriveddate])) + CDate(Nz([CollectedTime], [LabarrivedTime]))"
    • ie use collected dttm if available, else use arrived dttm

Start of date range

The Lab_DtTm is compared to the admit time of the patient as follows:

  • " WHERE ([Admit_DtTm]<=" & LabDtTm_str & ") AND (" & LabDtTm_str & "<=nz([Dispo_DtTm],now())) "

date constraints

the data we would want to use, but instead on which data was consistently available. Not all these columns are always filled.

  • [Arrive_DtTm]<=CDate(Nz([COLLECTEDDATE],[arriveddate]))+CDate(Nz([CollectedTime],[LabarrivedTime]))
  • CDate(Nz([COLLECTEDDATE],[arriveddate]))+CDate(Nz([CollectedTime],[LabarrivedTime]))<=[Dispo_DtTm]

counts

We originally did labs as counts, but now store them with results. A count of them can be viewed using query L_Labs_Flowsheet_DSM_sum.

individual record

Most of the lab tests we count are one DSM line per lab done, and the best way to identify them was based on the TESTCODE column in the DSM Lab Extract. hat column is compared to table s_mapping_lab by query 2_D_counts_test_appender to populate table L_Labs_Flowsheet_DSM.

group of records

Some other lab tests lead to multiple results and are reported as a group of lines per lab done. The best way to identify them was based on the GRPTEST column in the DSM Lab Extract. That column is compared to table s_group by query 2_D_counts_group_appender to populate table L_Labs_Flowsheet_DSM.

The s_mapping_lab table in Instructions for importing a batch of DSM Data defines which labs will be counted as which tests. On every run of Instructions for importing a batch of DSM Data, there is a check to find out if any new labs are in the new data that are not in the table. During testing, most times included a few new labs. For these new labs it would need to be decided if they are a type we collect before finishing the import.

Updates / new lab Name or Labels in DSM

The import process checks for values not previously seen on each import. If there are new ones it adds them to the list and alerts the user to decide if they should count toward one of the labs we collect.

Related articles

Related articles: