? examples of DR reports involving ADM events | Forum

Topic location: Forum home » Reporting » DR Reporting
Apr 4 '16
2016-04-04 17:18:16
Thanks for your suggestions, with the code/detailed explanation. I should also consider using Joe C’s technique using “Qualifiers”, but in SQL, as another alternative technique.
And as a bonus, an example of a CTE as well in these suggestions. I always wondered how frequently the SQL pros use it versus the more basic techniques that I tend to use. I had never heard of CTE until a while ago when someone sent a great example showing how you can get all the diagnoses for an account in multiple columns in one row with just one join using a CTE (versus the level 101-technique of having a zillion outer joins that I used to go with before).
Ganesh Seshadri
Analyst
Indiana Regional Medical Center
(724) 357-7012
From: Meditech-l [mailto:meditech-l-bounces@mtusers.com] On Behalf Of Mat Osmanski
Sent: Monday, April 04, 2016 10:33 AM
To: Ian Proffer; meditech-l@mtusers.com
Subject: Re: [MT-L] ? examples of DR reports involving ADM events
Here is some code I used to create and event history table. You can use the delete flag to remove these events if you want. Deletingfixing an event in MT often results in a sequence of events in Adm Visits being deleted, so it is usually a range of events that needs to be marked as deleted and it could be over several days so that needs to be considered as well.
With AdmDelEventsCte AS (
SELECT ae.SourceID
,ae.VisitID
,ae.UndoneDateTime StartDeleteDateTime
,ae.UndoneSeqID StartDelSeqID
,ae.EventDateTime EndDeleteDateTime
,ae.EventSeqID EndDelSeqID
FROM livendb.dbo.AdmVisitEvents ae
WHERE ae.SourceID = '<YourSourceID>'
AND ae.UndoneDateTime IS NOT NULL;
)
Select
yada,
yada,
yada,
,CASE WHEN del.VisitID IS NULL THEN 0 ELSE 1 END AS DelFlag
FROM livendb.dbo.AdmVisitEvents ae
LEFT JOIN AdmDelEventsCte del ON del.SourceID = ae.SourceID AND del.VisitID = ae.VisitID
AND (ae.Qualifiers IS NOT NULL OR ae.UndoneSeqID IS NOT NULL)
AND ae.EventDateTime BETWEEN del.StartDeleteDateTime AND del.EndDeleteDateTime
AND ae.EventSeqID BETWEEN
CASE WHEN ae.EventDateTime = del.StartDeleteDateTime THEN del.StartDelSeqID ELSE 0 END
AND
CASE WHEN ae.EventDateTime = del.EndDeleteDateTime THEN del.EndDelSeqID ELSE 999 END

Thanks, Mat
On Mon, Apr 4, 2016 at 10:16 AM Ian Proffer <iproffer@acmeware.com<mailto:iproffer@acmeware.com>> wrote:
Hi Ganesh,
The key is knowing which event Codes from AdmVisitEvents (assuming you are NOT 6.1) are defined as undos (or fixes, or edits). Most of these codes are standardized in Meditech, so you can predict what a valid admit, transfer or discharge event is, for example. However this list can be customized with other hospital defined events, such as undos, fixes, edits, etc.
Our general approach is something like this:
1. Get all the visits or events of interest using your base criteria first. Assemble these records into a #temp table or use CTE to define this recordset.
2. Once you’ve determined what all the possible “undo” codes are, re-evaluate the list from step 1 and determine if any of the matching key values have any applicable negating events – also put these into a #temp table or use CTE, including the relevant EventDateTime and EffectiveDateTime.
3. Using date-based or other criteria for your situation (you’ll almost always want to evaluate the EffectiveDateTime), select your final results from #table 1 while accounting for any items in #table 2 that might negate them. You can write this using an exclusive LEFT JOIN to #table 2 (checking for a NULL primary key) or a simple DELETE statement from #table 1 if the negating event applies from #table 2.
This approach is best used when evaluating a fairly narrow date range as a starting point to ensure acceptable performance. Smart non-clustered indexes will also help, mostly so on the “Code” column, if you’re looking for specific codes to include or exclude, as well as the “EffectiveDateTime” column if you’re evaluating that in a WHERE clause.
Cordially,
-- Ian
Ian Proffer
Vice President, Consulting Services
781.329.4300 x202
[cid:image001.png@01D18E5A.A7465420]<http://www.acmeware.com/>;
Data Repository Experts Since 1998

From: Meditech-l [mailto:meditech-l-bounces@mtusers.com<mailto:meditech-l-bounces@mtusers.com>] On Behalf Of Ganesh Seshadri
Sent: Monday, April 04, 2016 9:24 AM
To: meditech-l@mtusers.com<mailto:meditech-l@mtusers.com>
Subject: [MT-L] ? examples of DR reports involving ADM events
Can someone share the SQL code for DR reports involving table AdmVisitEvents that takes into account Undo transactions? Is there a straightforward way to take the UndoneDateTime and UndoneSeqID (or some other strategy) to eliminate or edit the rows that were affected? This requirement has crept in a few reports – some involving OBS to IN and IN to OBS type reports, and some looking at other patients that a patient shared a room with..
Thanks,
Ganesh Seshadri
Analyst
Indiana Regional Medical Center
(724) 357-7012
Apr 4 '16
2016-04-04 17:58:50
CTE’s are good for doing some very complex things, but performance isn’t as good as with temp tables and other options. If you search around Google there are some good discussions on the pros & cons.
Thanks,
John Taylor
Interoperability Analyst
Information Technology | Augusta Health
Office: (540)332-4882
Apr 4 '16
2016-04-04 18:12:08
To John’s point regarding CTE: your mileage will vary.
With T-SQL in general, but with Data Repository in particular, there are no absolute rules for things like CTE vs. temp tables vs. a table variable. Performance is definitely one consideration, so any solution deserves ample testing to determine the best method for your situation: hardware, database size, MT platform, etc.
We have a SQL routine we use for inpatient readmissions that by default uses temp tables. When we tested it against a CTE method, we received interesting results: about 1/3 of the time CTE was faster, another third was faster with #temp tables, and the rest were about the same. This was with different platforms (MAGIC, C/S and 6.0) and across a range of MT clients of different sizes (from 200GB to 1.5TB live databases).
So my advice is: experiment with different methods, and see what works best. In the test system first, of course!
Ian Proffer | 781.329.4300 x202 | Acmeware, Inc.<http://www.acmeware.com/>;
Apr 4 '16
2016-04-04 18:21:59
I agree performance Ian. Testing is very important and the use case may
matter as well. Sometimes you can't use temp tables. Small changes in how
we write the query can have a dramatic impact on the performance.
I would recommend you "prime" the list of patients you are reporting on
then join to the CTEs using the primary keys of the tables you are joining
to within the CTE if you can.
This example below gets a list of patients from Abstracting first using an
index scan (slower process but much smaller record table so not a huge
impact), then joins them to the AdmVisitEvents (Large transaction table)
using the SourceID and VisitID through the CTE. This uses and index seek on
AdmVisitEvents table because the join columns are the first 2 columns of
the primary key. No additional custom indexes are needed.
If you joined these tables just on VisitID and there was no index on that
column, the performance would really suffer.

With AdmDelEventsCte AS (
SELECT
ae.SourceID
,ae.VisitID
,ae.UndoneDateTime StartDeleteDateTime
,ae.UndoneSeqID StartDelSeqID
,ae.EventDateTime EndDeleteDateTime
,ae.EventSeqID EndDelSeqID
FROM livendb.dbo.AdmVisitEvents ae
WHERE ae.UndoneDateTime IS NOT NULL
)
,AdmEventsCte AS (
SELECT
ae.SourceID
,ae.VisitID
,ae.EventDateTime
,ae.Code
,ae.Description
,ae.EffectiveDateTime
,ae.Qualifiers
,ae.EventActualDateTime
,CASE WHEN del.VisitID IS NULL THEN 0 ELSE 1 END AS DelFlag
FROM livendb.dbo.AdmVisitEvents ae
LEFT JOIN AdmDelEventsCte del ON del.SourceID = ae.SourceID AND del.VisitID
= ae.VisitID
AND (ae.Qualifiers IS NOT NULL OR ae.UndoneSeqID IS NOT NULL)
AND ae.EventDateTime BETWEEN del.StartDeleteDateTime AND
del.EndDeleteDateTime
AND ae.EventSeqID BETWEEN
CASE WHEN ae.EventDateTime = del.StartDeleteDateTime THEN del.StartDelSeqID
ELSE 0 END
AND
CASE WHEN ae.EventDateTime = del.EndDeleteDateTime THEN del.EndDelSeqID
ELSE 999 END
)
SELECT ad.SourceID
,ad.[VisitID]
,ad.FacilityID
,CAST(ad.AdmitDateTime AS date) AdmitDate
,ae.EventDateTime
,ae.Code
,ae.Description
,ae.EffectiveDateTime
,ae.Qualifiers
,ae.EventActualDateTime
,ae.DelFlag
FROM [livendb].[dbo].[AbstractData] ad
LEFT JOIN livendb.dbo.DMisFacilityDatabase fac ON fac.SourceID = '<Your
MIS Database SourceID>' AND fac.FacilityID = ad.FacilityID AND
fac.Application = 'ADM'
LEFT JOIN AdmEventsCte ae ON ae.SourceID = fac.DatabaseSourceID AND
ae.VisitID = ad.VisitID
WHERE CAST(ad.AdmitDateTime as date) BETWEEN dateadd(dd,-7,GETDATE()) AND
GETDATE()
Thanks, Mat
On Mon, Apr 4, 2016 at 1:18 PM Ganesh Seshadri <gseshadri@indianarmc.org>
wrote:
>
Apr 4 '16
2016-04-04 19:21:24
Checking for contains <FIXED> strikes me as easier because MT itself does this flagging during the undo process so you know just which events have become irrelevant and can be ignored and you do not have to do your own effective date/time based head banging. This assumes that flag gets over to the DR and MT updates the event record rather than just tacking on a new one with the flag.
Note that qualifiers can have <LATE><FIXED> if it was backdated and then undone.

Maxxx
Maxxx Jun 13
http://audiobookkeeper.ruhttp://cottagenet.ruhttp://eyesvision.ruhttp://eyesvisions.comhttp://factoringfee.ruhttp://filmzones.ruhttp://gadwall.ruhttp://gaffertape.ruhttp://gageboard.ruhttp://gagrule.ruhttp://gallduct.ruhttp://galvanometric.ruhttp://gangforeman.ruhttp://gangwayplatform.ruhttp://garbagechute.ruhttp://gardeningleave.ruhttp://gascautery.ruhttp://gashbucket.ruhttp://gasreturn.ruhttp://gatedsweep.ruhttp://gaugemodel.ruhttp://gaussianfilter.ruhttp://gearpitchdiameter.ruhttp://geartreating.ru http://generalizedanalysis.ruhttp://generalprovisions.ruhttp://geophysicalprobe.ruhttp://geriatricnurse.ruhttp://getintoaflap.ruhttp://getthebounce.ruhttp://habeascorpus.ruhttp://habituate.ruhttp://hackedbolt.ruhttp://hackworker.ruhttp://hadronicannihilation.ruhttp://haemagglutinin.ruhttp://hailsquall.ruhttp://hairysphere.ruhttp://halforderfringe.ruhttp://halfsiblings.ruhttp://hallofresidence.ruhttp://haltstate.ruhttp://handcoding.ruhttp://handportedhead.ruhttp://handradar.ruhttp://handsfreetelephone.ruhttp://hangonpart.ruhttp://haphazardwinding.ru http://hardalloyteeth.ruhttp://hardasiron.ruhttp://hardenedconcrete.ruhttp://harmonicinteraction.ruhttp://hartlaubgoose.ruhttp://hatchholddown.ruhttp://haveafinetime.ruhttp://hazardousatmosphere.ruhttp://headregulator.ruhttp://heartofgold.ruhttp://heatageingresistance.ruhttp://heatinggas.ruhttp://heavydutymetalcutting.ruhttp://jacketedwall.ruhttp://japanesecedar.ruhttp://jibtypecrane.ruhttp://jobabandonment.ruhttp://jobstress.ruhttp://jogformation.ruhttp://jointcapsule.ruhttp://jointsealingmaterial.ruhttp://journallubricator.ruhttp://juicecatcher.ruhttp://junctionofchannels.ru http://justiciablehomicide.ruhttp://juxtapositiontwin.ruhttp://kaposidisease.ruhttp://keepagoodoffing.ruhttp://keepsmthinhand.ruhttp://kentishglory.ruhttp://kerbweight.ruhttp://kerrrotation.ruhttp://keymanassurance.ruhttp://keyserum.ruhttp://kickplate.ruhttp://killthefattedcalf.ruhttp://kilowattsecond.ruhttp://kingweakfish.ruhttp://kinozones.ruhttp://kleinbottle.ruhttp://kneejoint.ruhttp://knifesethouse.ruhttp://knockonatom.ruhttp://knowledgestate.ruhttp://kondoferromagnet.ruhttp://labeledgraph.ruhttp://laborracket.ruhttp://labourearnings.ru http://labourleasing.ruhttp://laburnumtree.ruhttp://lacingcourse.ruhttp://lacrimalpoint.ruhttp://lactogenicfactor.ruhttp://lacunarycoefficient.ruhttp://ladletreatediron.ruhttp://laggingload.ruhttp://laissezaller.ruhttp://lambdatransition.ruhttp://laminatedmaterial.ruhttp://lammasshoot.ruhttp://lamphouse.ruhttp://lancecorporal.ruhttp://lancingdie.ruhttp://landingdoor.ruhttp://landmarksensor.ruhttp://landreform.ruhttp://landuseratio.ruhttp://languagelaboratory.ruhttp://largeheart.ruhttp://lasercalibration.ruhttp://laserlens.ruhttp://laserpulse.ru http://laterevent.ruhttp://latrinesergeant.ruhttp://layabout.ruhttp://leadcoating.ruhttp://leadingfirm.ruhttp://learningcurve.ruhttp://leaveword.ruhttp://machinesensible.ruhttp://magneticequator.ruhttp://magnetotelluricfield.ruhttp://mailinghouse.ruhttp://majorconcern.ruhttp://mammasdarling.ruhttp://managerialstaff.ruhttp://manipulatinghand.ruhttp://manualchoke.ruhttp://medinfobooks.ruhttp://mp3lists.ruhttp://nameresolution.ruhttp://naphtheneseries.ruhttp://narrowmouthed.ruhttp://nationalcensus.ruhttp://naturalfunctor.ruhttp://navelseed.ru http://neatplaster.ruhttp://necroticcaries.ruhttp://negativefibration.ruhttp://neighbouringrights.ruhttp://objectmodule.ruhttp://observationballoon.ruhttp://obstructivepatent.ruhttp://oceanmining.ruhttp://octupolephonon.ruhttp://offlinesystem.ruhttp://offsetholder.ruhttp://olibanumresinoid.ruhttp://onesticket.ruhttp://packedspheres.ruhttp://pagingterminal.ruhttp://palatinebones.ruhttp://palmberry.ruhttp://papercoating.ruhttp://paraconvexgroup.ruhttp://parasolmonoplane.ruhttp://parkingbrake.ruhttp://partfamily.ruhttp://partialmajorant.ruhttp://quadrupleworm.ru http://qualitybooster.ruhttp://quasimoney.ruhttp://quenchedspark.ruhttp://quodrecuperet.ruhttp://rabbetledge.ruhttp://radialchaser.ruhttp://radiationestimator.ruhttp://railwaybridge.ruhttp://randomcoloration.ruhttp://rapidgrowth.ruhttp://rattlesnakemaster.ruhttp://reachthroughregion.ruhttp://readingmagnifier.ruhttp://rearchain.ruhttp://recessioncone.ruhttp://recordedassignment.ruhttp://rectifiersubstation.ruhttp://redemptionvalue.ruhttp://reducingflange.ruhttp://referenceantigen.ruhttp://regeneratedprotein.ruhttp://reinvestmentplan.ruhttp://safedrilling.ruhttp://sagprofile.ru http://salestypelease.ruhttp://samplinginterval.ruhttp://satellitehydrology.ruhttp://scarcecommodity.ruhttp://scrapermat.ruhttp://screwingunit.ruhttp://seawaterpump.ruhttp://secondaryblock.ruhttp://secularclergy.ruhttp://seismicefficiency.ruhttp://selectivediffuser.ruhttp://semiasphalticflux.ruhttp://semifinishmachining.ruhttp://spicetrade.ruhttp://spysale.ruhttp://stungun.ruhttp://tacticaldiameter.ruhttp://tailstockcenter.ruhttp://tamecurve.ruhttp://tapecorrection.ruhttp://tappingchuck.ruhttp://taskreasoning.ruhttp://technicalgrade.ruhttp://telangiectaticlipoma.ru http://telescopicdamper.ruhttp://temperateclimate.ruhttp://temperedmeasure.ruhttp://tenementbuilding.rutuchkashttp://ultramaficrock.ruhttp://ultraviolettesting.ru