Instructions for requesting a batch of data from DSM
This article describes how to generate the requestor file for the DSM Lab Extract. The file is stored on a file share that DSM can access. See Instructions for importing a batch of DSM Data for the related import process.
How frequently to request
We will request twice per quarter period (max of 8 times per year).
| Quarter period | Mid Qtr_Send_Date | Mid Qtr_Record Status | End Qtr_Send Date | End_Qtr Record Status |
|---|---|---|---|---|
| Jan to Mar | <= Feb28 | Vetted only | <= Apr15 | Vetted + Incomplete w/ Dispo dt <=Mar31 |
| Apr to Jun | <= May31 | Vetted only | <= Jul15 | Vetted + Incomplete w/ Dispo dt <=Jun30 |
| Jul to Sep | <= Aug31 | Vetted only | <= Oct15 | Vetted + Incomplete w/ Dispo dt <=Sep30 |
| Oct to Dec | <= Nov30 | Vetted only | <= Jan15 | Vetted + Incomplete w/ Dispo dt <=Dec31 |
- The incompletes with dispo dt are added on the End Qtr_Send Date so that the quarter period report will have complete DSM data.
- Initially our DSM contact had concerns about workload and wanted a lower frequency, but then he said "Send on the ‘requestor’ file as often as you want to see the output and I will make the time." (as per email from Alun Carter, Monday, February 06, 2017 10:50)
Instruction/steps for requesting data
For Vetted Record Status
- copy files to local, they are large and won't work well on a share.
- make a new local directory
- Put the following into the directory
- make sure you have data where the NDC's have been completed. You should copy PREVIOUS days backup of both the centralized and PHIA data files.
- Centralized_data.accdb -the last one has been fully checked for links
- PHI.mdb
- DSM_Labs_Consistency_check.accdb
- DSM Labs data.accdb
- make sure you have data where the NDC's have been completed. You should copy PREVIOUS days backup of both the centralized and PHIA data files.
- open DSM Labs Consistency check.accdb
- click the "Export requestor file" button
- If you get the error message 58- there is already file in the folder that you need to delete.
- follow the wizard, which will export a file to the shared folder and start an email to DSM
- close Access and delete files you had copied
- enter at the top of the log below when you requested data, and for what range
- check the number requestors.xls if the same as the number you requested by using the send date time.
- check the previously sent batch if the worksheet LOSgt2_resend contains any D_IDs with LOS > 2d. If there are, add them to the requestor.xls in the shared folder before emailing DSM.
For Incomplete Records Status Being Required on the End Qtr_Send Date
This second step must be done and the output is appended to the requestor.xls of the Vetted records.
- Create a Query by using the L_Log_PHI and link the created_AdmitDtTm table so the (arrived date time is visible).
- The header row must have the same label and sequence order as the requestor.xls of the Vetted records.
- Click Datasheet view
- Click External, Export, Excel, browse (you can save on your desktop)
- Click Export data with formatting and layout
- Click Ok, then close
- Convert the Admit_DtTm, Dispo_DtTm, SentDtTm to date format (mo/day/year)
- Copy and paste only the Incomplete with the discharged date you need in the report to the requestor.xls
- Save the requestor.xls file
Dependencies
The request process asks for all, and the import process de-duplicates, so there should be no gaps or overlaps.
Requesting Lab DSM
- Need to send twice per quarter
- A complete (combined) file in addition to split files given - start date Oct 14, 2020 batch request.
- After importing the split files to DSM_Lab_accdb, the split files are deleted saving only the combined file .
Log
Put newest entry at the top
| Request Date | No_Records | Unmatched D_ID with DSM dump | SendDtTm range start | SendDtTm range end | Date Imported to Database | Comments | ||
|---|---|---|---|---|---|---|---|---|
| 2025 Jul 25 | 1562 | 2025 Jul 25 | 2025 Sept 02 | |||||
| 2025 Jul 25 | 1239 | 46 (3.7%) | 2025 Jul 4 | 2025 Jul 25 | 2025 Jul 30 | Vetted 926 + Inc with DispoDttm 313 = 1239 | ||
| 2025 Jul 04 | 2286 | 89 (3.9%) | 2025 May 14 | 2025 Jul 04 | 2025 Jul 23 | All Vetted records | ||
| 2025 May 14 | 1672 | 38 (2.3%) | 2025 Apr 4 | 2025 May 14 | 2025 May 29 | All Vetted records from CCMED. Accidentally sent N=385 dup records from Batch 14Apr2025. | ||
| 2025 Apr 14 | 2559 | 130 (5.1%) | 2025 Mar 7 | 2025 Apr 14 | 2025 Apr 30 / May 1 | CCMED=1958 (Inc=364, Vetted=1594), LAU=601 Unmatched CCMED=56(2.9%), LAU=74(12.3%) | ||
| 2025 Mar 7 | 2074 | 64 (3.1%) | 2025 Feb 3 | 2025 Mar 7 | 2025 Apr 4 | All Vetted records | ||
| 2025 Jan 21 | 1986 | 84 (4.22%) | 2024 Dec 9 | 2025 Jan 21 | 2025 Feb 3 | Vetted 1834 + Inc with DispoDttm 152 = 1986, resend from batch 09Dec2024=11, resend from batch 25Oct2024=17
The 11 + 17 = 28 resend are still unmatched (concluded as no DSM data) |
||
| 2024 Dec 09 | 1891 | 49(2.59%) | 2024 Oct 25 | 2024 Dec 09 | 2024 Dec 20 | All Vetted records. | ||
| 2024 Oct 25 | 1530 | 45(3%) | 2024 Oct 4 | 2024 Oct 25 | 2024 Oct 28 | Total records Vetted 1518 + 4 Inc =1522 + 8 (re-send from batch 2024-Sep21 because found unmatched and have LOS >2d) =1530
%unmatched from 1522= 45/1522=3% %unmatched from 8 resend = 8/8=100% (concluded as no DSM match the second time around) |
||
| 2024-Sep 21 | 1219 | 43(3.7%) | 2024 Aug 23 | 2024 Sep 21 | 2024 Oct 4 | All Vetted. Added 58 D_IDs found unmatched and have LOS >2d from past batches Apr 19 to Aug 23, 2024.
Total records =1161 + 58 resend = 1219 %unmatched from 1161=43/1161=3.7% %unmatched from 58 resend = 58/58=100% (concluded as no DSM match the second time around) |
||
| 2024 Aug 23 | 1603 | 53 (3.3%) | 2024 Jul 12 | 2024 Aug 23 | 2024 Sept 20 | All Vetted. Upload excel file from SAS. | ||
| 2024 Jul 12 | 1918 | 64 (3.3%) | 2024 May 30 | 2024 Jul 12 | 2024 Jul 19 | All vetted. Upload excel file from SAS. | ||
| 2024 May 30 | 1668 | 38 (2.3%) | 2024 Apr 19 | 2024 May 30 | 2024 Jun 20 | All vetted. Upload excel file from SAS. | ||
| 2024 Apr 19 | 3056 | 105 (3.4%) | 2024 Feb 9 | 2024 Apr 19 | 2024 Jun 13 | Vetted = 3022, Incomplete = 34. Export in ACCESS still not working. Upload excel file from SAS. | ||
| 2024 Mar 11 | 1300 | 2024 Feb 09 | 2024 Mar 11 | Export failed resent with Apr 19/24 batch. | ||||
| 2024 Feb 09 | 1581 | 49 (3.09%) | 2024 Jan 09 | 2024 Feb 09 | 2024 Feb 27 | All Vetted | ||
| 2024 Jan 09 | 1906 | 54 (2.83% ) | 2023 Nov 28 | 2024 Jan 09 | 2024 Jan 22 | All Vetted | ||
| 2023 Nov 28 | 1422 | 40 (2.81%) | 2023 Oct 25 | 2023 Nov 28 | 2023 Dec 6 | All Vetted | ||
| 2023 Oct 25 | 1598 | 47 (2.94%) | 2023 Sep 22 | 2023 Oct 25 | 2023 10 30 | Vetted = 1573, Inc = 25. The Incompletes have dispo date before or on Sept 30,2023 to be included in the July to Sept quarter report. | ||
| 2023 Sep 22 | 1341 | 40 (2.98%) | 2023 Aug 22 | 2023 Sep 22 | 2023 Oct 10 | All Vetted | ||
| 2023 Aug 22 | 1729 | 50 (2.89%) | 2023 Jul 12 | 2023 Aug 22 | 2023 Aug 28 | Contain Vetted =1713, Inc = 16. The Incompletes have dispo date before or on June 30,2023 to be included in the April to June quarter report. The new DSM_Labs_data starts on this batch. | ||
| 2023-Jul 12 | 1532 | 34 (2%) | 2023 Jun 07 | 2023 Jul 12 | 2023 Jul 26 | All Vetted. Tina split the DSM_Labs_data and relabeled it as DSM_LABS-data-pre_2023-08-27. This batch is the last entry to pre-2023-08-27. | ||
| 2023 Jun 07 | 1961 | 88 (4.49%) | 2023 Apr 25 | 2023 Jun 07 | 2023 Jun 12 | All Vetted | ||
| 2023 May 12 | 795 | 94 (11%) | 2023 May 15 | from batch 4/25/2023 - Resent 795 found unmatched and no Lab or Blanks. . | ||||
| 2023 Apr 25 | 2495 | 799 (32%) | 2023 Mar 1 | 2023 Apr 25 | 2023 May 10 | All Vetted | ||
| 2023 Mar 01 | 2634 | 126 (4.78%) | 2023- Jan 10 | 2023 Mar 1 | 2023 Apr 25 | All Vetted | ||
| 2023 Jan 10 | 2122 | 103 (4.85%) | 2022 Nov 23 | 2023 Jan 10 | 2023 Feb 2 | All Vetted | ||
| 2022 Nov 23 | 2591 | 107 (4.12%) | 2022 Oct 5 | 2022 Nov 23 | 2022 Dec 5 | All Vetted | ||
| 2022 Oct 5 | 1537 | 65 (4.22%) | 2022 Aug 31 | 2022 Oct 05 | 2022 Nov 11 | All Vetted | ||
| 2022 Aug 31 | 4176 | 181 (4.33%) | 2022 Jun 01 | 2022 Aug 31 | 2022 Sept 15 | All Vetted
Import 247783 | ||
| 2022 Jun 01 | 7337 | 354 (4.82%) | 2021 Dec 31 | 2022 Jun 01 | 2022 Jun 10 | Sent Vetted only. PT Import - 2975 Blanks (41%).
REDO: New Import 384033 - New import date 2022 Sep 14 Old Import 191606 entries. | ||
| 2022 Jan 03 | 3970 | 70 (1.8%) | 2021 Oct 13 | 2021 Dec 31 | 2022 Jun 10 | PT Sent only Vetted profiles. (JM using SAS - 95 have matched D_ID but no corresponding Lab dates.) PT import - Blanks 2522 (63%).
REDO: New Import 187808 - New import date 2022 Sep 13 Old Import 20041 entries. | ||
| 2021 Oct 13 | 3984 | 64 (1.6%) | 2021 July 20 | 2021 Oct 13 | 2022 Jun 09 | PT Sent only Vetted profiles. (JM using SAS - 6 D_IDs at requestor were re-labeled to match the L_LOG; 104 have matched D_ID but no corresponding Lab Dates.) PT import - Blanks 1275 (32%).
REDO: New Import 95765 - New import date 2022 Sep 13 Old Import 108800 entries. | ||
| 2021 July 20 | 8015 | 223 (2.78%) | 2021 Apr 15 | 2021 July 20 | 2021 Aug 12 | Included Incomplete = 3164 and Vetted = 4851
REDO: New Import 264054 - New import date 2022 Sep 09 Old Import 135903 entries. | ||
| 2021 Apr 15 | 6289 | 369 (5.86%) | 2021 Jan 19 | 2021 Apr 15 | 2021-June 09 | Included Incomplete=3163 and Vetted=3126.
REDO: New Import 212257 - New import date 2022 Sep 08 Old Import 70310 entries. | ||
| 2021 Jan 19 | 4167 | 118 ( 2.83% ) | 2020-Nov-27 | 2021-Jan-19 | 2021-Jan-27 | Included Incomplete=2076 and Vetted=2091. The following 5 Incompletes (H3_HSC_H4-19723, H3_HSC_H4-19777,H3_HSC_H4-19790, G8_GRA_N3-19794,G8_GRA_N3-19968) are not included on this sent because of D_ID changes.
REDO: New Import 196891 - New Import Date 2022 Sep 08 Old Import 47357 entries. | ||
| 2020 Dec 03 | 2580 | 170 (6.58%) | 2020 Sep 30 | 2020 Nov 27 | 2021 Jan 27 |
Started the New DSM access program to this batch on wards. REDO: New Import 99550 - New Import Date: 2022 Sep 08 Old Import resulted to 55544 entries. | ||
| 2020 Oct 14 | 1836 | 34 (1.8%) | 2020-Aug-31 | 2020-Sep-30 | 2020-Oct-29 | JM did this Oct2020: unmatched 34 (1.8%) | ||
| 2020 Aug 31 | 3589 | 237 (6.6%) | 2020-Jul-15 | 2020-Aug-31 | 2020-Sep-30 | JM re-do Oct2020: unmatched 237 (6.6%) | ||
| 2020 Jul 15 | 6848 | 1345 (19%) | 2020-Apr-21 | 2020-Jul-15 | 2020-Aug-14 | Added 793(12%) unmatched from batch 21April2020 making the total=6848; 1189 out 6848 are incomplete (398 with no dispo date and 791 with dispo date); JM re-do Oct2020: unmatched 681 (9.9%) | ||
| 2020 Apr 21 | 6269 | 766 (12%) | 2020-Jan-27 | 2020-Apr-21 | 2020-May15 | JM re-do Oct2020: unmatched 393 (6.3%) | ||
| 2020 Jan 27 | 5567 | 413 (7%) | 2019-Nov-20 | 2020-Jan-27 | 2020-Feb-10 | JM re-do Oct2020: unmatched 516 (9.3%) | ||
| 2019 Nov 21 | 7350 | 417 (5%) | 2019-Jul-31 | 2019-Nov-20 | 2019-Dec-18 | JM re-do Oct2020: unmatched 557 (7.6%) | ||
| 2019 Jul 31 | 5738 | 146 (2.5%) | 2019-May-13 | 2019-Jul-31 am | 2019-Oct-24 | JM re-do Oct2020: unmatched 410 (7.8%) | ||
| 2019 May 13 | 3583 | 491 (13%) | 2019-Mar-27 | 2019-May-13 am | 2019-May-29 | JM re-do Oct2020: unmatched 239 (6.7%) | ||
| 2019 Mar 28 | 4198 | 249 (6%) | 2019-Jan-23 | 2019-Mar-27 am | 2019-Apr-10 | JM re-do Oct2020: unmatched 320 (7.6%) | ||
| 2019 Jan 23 | 4521 | 1st- 1220 (27%), 2nd- 334 (7%) | 2018-Oct-31 | 2019-Jan-23 am | 1st- 2019-Mar-9; 2nd- 2019-Jun-14 | 1612 records of HSC have no lab data and re-send June 6, 2019; JM re-do Oct2020: unmatched 330 (7.8%) | ||
| 2018 Oct 31 | 5873 | 508 (9%) | 2018-Jul-11 | 2018-Oct-31 am | 2019-Mar-9 | |||
| 2018 Jul 11 | 1716 | 106 (6%) | 2018-Jun-19 | 2018-Jul-11 am | 2019-Mar-9 | |||
| 2018 Jun 19 | 4212 | 378 (9%) | 2018-Apr-05 | 2018-Jun-19 am | 2019-Mar-10 | |||
| 2018 Apr 5 | 4532 | 359 (8%) | 2018-Jan-25 | 2018-Apr-05 am | 2018-May-18 | |||
| 2018 Jan 25 | 5710 | 448 (8%) | 2017-Oct-26 | 2018-Jan-25 am | 2018-Apr-9 | |||
| 2017 Oct 26 | 16590 | 1246 (8%) | 2016-Dec-31 | 2017-Oct-26 am | 2018-Apr-9 | |||
| 2017-Mar-14 | 6078 | 559 (9%) | 2016-Aug-30 | 2016-Dec-31 am | 2017-Apr-12 ? |
Early requests that followed a different pattern:
- 16:05, 2016 October 17 (CDT) || 2016-08-01 (but use 1980-01-01 for import because first one) until 2016-08-30
- 2015-12-?? - Discharge date 2015-04-01 - 2015-04-30
Related articles
| Related articles: |