Hi All. This problem has stymied report writers in our clinics much more experienced than I. In short, what we need is to condense multiple records of consecutive dates into a single record containing the first in the series and then a count of the consecutive dates. We want to then suppress all the constituent records and output just the summary line to a .csv for upload to Epic. There needs to be a control break at each change in medical record number (MRN), and then--in order of subordination--at each change in workstation ID, then DSM code, then HCPCS code, then finally at each break in consecutive dates of service. Thus, from the following data:
MRN01, wkstation1, DSM1, HCPCS1, 11/1/2013
MRN01, wkstation1, DSM1, HCPCS1, 11/2/2013
MRN01, wkstation2, DSM1, HCPCS1, 11/3/2013
MRN01, wkstation2, DSM1, HCPCS1, 11/4/2013
MRN01, wkstation2, DSM1, HCPCS1, 11/5/2013
MRN02, wkstation2, DSM1, HCPCS1, 11/1/2013
MRN02, wkstation2, DSM2, HCPCS1, 11/2/2013
MRN02, wkstation2, DSM2, HCPCS1, 11/3/2013
MRN02, wkstation2, DSM2, HCPCS2, 11/4/2013
MRN02, wkstation2, DSM2, HCPCS2, 11/5/2013
MRN02, wkstation2, DSM2, HCPCS2, 11/8/2013 we would produce:
MRN01, wkstation1, DSM1, HCPCS1, 11/1/2013, 2
MRN01, wkstation2, DSM1, HCPCS1, 11/3/2013, 3
MRN02, wkstation2, DSM1, HCPCS1, 11/1/2013, 1
MRN02, wkstation2, DSM2, HCPCS1, 11/2/2013, 2
MRN02, wkstation2, DSM2, HCPCS2, 11/4/2013, 2
MRN02, wkstation2, DSM2, HCPCS2, 11/8/2013, 1
Until now, we've been using Crystal to group by variables in the requisite order then sort by date then add a calculation variable to show whether a record is consecutive within the last group break. Then we've exported to Excel in order to subtotal at each break, suppress details, and export the resulting visible records to a .csv. We need to be able automate that much within just Crystal. (It'll have to go through a PERL script to convert for upload.)
Bigger brains than mine are sleeping on it for the moment. Any suggestions?
Chris Nolan
IP Logged
