Date Filtering Functionality

Question

Question

Scott Roth · Jun 19, 2017

#Ensemble #Business Rules #DTL #HL7 #ObjectScript

I have come across several cases where I need to set a Date filter to send only send any admit/scheduled date past a certain point. We have a couple different date comparison functions but none looking at the true date. Most of them are date + 15 or etc. Does anyone have a good date function they have written to say if x > 20170102 ?

Thanks

Scott

Discussion (14)2

Log in or sign up to continue

Amir Samary · Jun 19, 2017

Hi!

I am not sure if I understood your questions. But here is an explanation that may help you...

If you want to run a SQL query filtering by a date

Let's take Sample.Person class on the SAMPLES namespace as an example. There is a DOB (date of birth) field of type %Date. This stores dates in the $Horolog format of Caché (an integer that counts the number of dates since 12/32/1940.

If your date is in the format DD/MM/YYYY (for instance), you can use TO_DATE() function to run your query and convert this date string to the $Horolog number:

select * from Sample.Person where DOB=TO_DATE('27/11/1950','DD/MM/YYYY')

That will work independently of the runtime mode you are on (Display, ODBC or Logical).

On the other hand, if you are running your query with Runtime Select Mode ODBC, you could reformat your date string to the ODBC format (YYYY-MM-DD) and don't use TO_DATE():

select * from Sample.Person where DOB='1950-11-27'

That still is converting the string '1950-11-27' to the internal $Horolog number that is:

USER>w $ZDateH("1950-11-27",3)

40142

If you already has the date on the internal $Horolog format you could run your query using Runtime Select Mode Logical:

select * from Sample.Person where DOB=40142

You can try these queries on the management portal. Just remember changing the

If you are using dynamic queries with %Library.ResultSet or %SQL.Statement, set the Runtime Mode (%SelectMode property on %SQL.Statement) before running your query.

If you want to find records from a moving window of 30 days

The previous query brought, on my system, the person "Jafari,Zeke K.". He was born on 1950-11-27. The following query will bring all people that was born on '1950-11-27' and 30 days before '1950-11-27'. I will use DATE_ADD function to calculate this window. I have also selected ODBC Runtime Select Mode to run the query:

select Name, DOB from Sample.Person where DOB between DATEADD(dd,-30,'1950-11-27') and '1950-11-27'

Two people will appear on my system: Jafari and Quixote. Quixote was born '1950-11-04'. That is inside the window.

Moving window with current_date

You can use current_date to write queries such as "who has been born between today and 365 days ago?":

select Name, DOB from Sample.Person where DOB between DATEADD(dd,-365,current_date) and current_date

Using greater than or less than

You can also use >, >=, < or <= with dates like this:

select Name, DOB from Sample.Person where DOB >= DATEADD(dd,-365,current_date)

Just be careful with the Runtime Select Mode. The following works with ODBC Runtime Select Mode, but won't work with Display or Logical Mode:

select Name, DOB from Sample.Person where DOB >= DATEADD(dd,-30,'1950-11-27') and DOB<='1950-11-27'

To make this work with Logical Mode, you would have to apply TO_DATE to the dates first:

select Name, DOB from Sample.Person where DOB >= DATEADD(dd,-30,TO_DATE('1950-11-27','YYYY-MM-DD')) and DOB<=TO_DATE('1950-11-27','YYYY-MM-DD')

To make it work with display mode, format the date accordingly to your NLS configuration. Mine would be 'DD/MM/YYYY' because I am using a spanish location.

2 0

Jeffrey Drumm · Sep 3, 2020

Here's a method that, once created and compiled, will appear in your dropdown list of functions in the rule editor:

Class User.Util.DateTime Extends Ens.Rule.FunctionSet
{

ClassMethod DaysPrior(pDays As %Integer) As %String
{
    Return $ZDATE($H - pDays,8)
}

}

You'd use it in your rule like this:

Caveat: The birthdate is assumed to be valid and 8 characters in length. If there's a possibility that you would get an invalid or missing date, the ">" comparison will not be valid.

0 0

score 0 · Answer 1 · 2017-06-20T10:55:26-04:00

I am looking more for a function that I can call in a Business Rule or Data Transformation to compare the dates.

For example

In a Business Rule I might want to say only send messages that have an Admit Date after 7/1/2017.
In a Data transformation I might want to say only populate certain fields if Admit Date is after 7/1/2017

Thanks

score 0 · Answer 2 · 2020-09-01T20:27:25-04:00

Ahmad Bukhtiar · Sep 1, 2020

Hi Scott, did you figure out your scenario, pls advice as I am running into similar issue. thanks

0 0

score 0 · Answer 3 · 2020-09-02T09:56:59-04:00

Scott Roth · Sep 2, 2020

In a Business Rule, I have used...

0 0

score 0 · Answer 4 · 2020-09-02T17:46:40-04:00

Thanks instead of hard code date, can we put something like HL7.{PID:DateofBirth},"1","8")<today date -3 days. I want to check if coming msg DOB is within 3 days then its new born message so i can check validate fields, such as mother identifier should not be empty in case of new born patient date coming from external system.

score 0 · Answer 5 · 2020-09-03T07:55:04-04:00

I maybe wrong... But since the native format of dates in Ensemble are not strings, unless you create a function to convert $HOROLOG into a string and set it equal to the variable of Today, I do not think this is possible.

http://intengtest:57772/csp/docbook/DocBook.UI.Page.cls?KEY=RCOS_vhorolog

/// Provide the current date HL7 Format YYYYMMDD
ClassMethod getCurrentDateHL7() As %Numeric
{
quit $ZDATE($H,8)
}

/// Compare a Message Date/Time with the CurrentDate
ClassMethod DateCompare(DateString As %String) As %Boolean [ Final ]
{
Set CurDate=$PIECE($HOROLOG,",",1)
Set MyDate=$EXTRACT(DateString,1,8)
Set TestDate=$ZDATEH(MyDate,8) //Calculation example: 20150304 - 20150306 = -2, function returns '0'
If (TestDate - CurDate < 0)
{
quit 0
}
Else
{
quit 1
}
}

score 0 · Answer 6 · 2020-09-03T09:12:50-04:00

Bukhtiar Ahmad · Sep 3, 2020

Hi, How you call function from the business rules editor?

0 0

score 0 · Answer 7 · 2020-09-08T04:36:04-04:00

Hi Scott, need another help. I have code that check a value in HL7 msg and send NACK msg. the code works fine for IRIS For Health but not for HealthShare as %ErrorStatus property not available in HealthShare. Any guidance to fix in the code

Error coming in compiling the below rules.

///
Class Hospital.HospitalProd.RoutingRule Extends Ens.Rule.Definition
{
Parameter RuleAssistClass = "EnsLib.MsgRouter.RuleAssist";
XData RuleDefinition [ XMLNamespace = "http://www.intersystems.com/rule" ]
{
<ruleDefinition alias="" context="EnsLib.MsgRouter.RoutingEngine" production="Hospital.HospitalProd">
<ruleSet name="" effectiveBegin="" effectiveEnd="">
<rule name="Valid Facility Code Required">
<when condition="Lookup("OnboardedFacilities",HL7.{MSH:SendingFacility.NamespaceID},"No","No")!="Yes"">
<assign property="%ErrorStatus" value="GenerateNACK("Invalid Sending Facility Code",%ErrorStatus)"></assign>
</when>
</rule>

Below class compiling on HealthShare

Class Hospital.DHARule Extends Ens.Rule.FunctionSet
{

ClassMethod GenerateNACK(text As %String, status As %Status) As %Status [ CodeMode = expression, Final ]
{
$$$ERROR($$$GeneralError,$zstrip($piece($System.Status.GetErrorText(status),"#5001:",2),"*C")_"~"_text)
}

}

Error Msg

score 0 · Answer 8 · 2020-09-08T08:28:17-04:00

I haven't spent any time doing anything with NACK's as of yet. I would suggest creating another post for that question to see if other Developers can help you out.

score 0 · Answer 9 · 2020-09-08T08:38:38-04:00

Bukhtiar Ahmad · Sep 8, 2020

Thanks, I did already like couple of days ago but no response came from anyone yet, fingers crossed.

0 0

score 0 · Answer 10 · 2020-09-03T09:16:19-04:00

Thanks for the guidance, i was able to run and test it, it worked. for the invalid date how can we handle it?

score 0 · Answer 11 · 2020-09-03T09:40:30-04:00

I guess that depends on what you want to do if you get an invalid date. Here's a method you can add to the class I referenced above:

ClassMethod IsValidBirthDate(pDate As %String) As %Boolean
{
    Try {
            Set tHDate = $ZDATEH(pDate)
    } Catch ex {
        Return 0
    }
    Return 1
}

And you'd use it something like this:

score 0 · Answer 12 · 2020-09-03T10:47:01-04:00

Thanks, it worked. Actually i am putting all rules from the specs doc, and generating custom NACK msgs, for example here i have rule, for newborn mother identifier value should not be empty, so 1) DOB is valid, 2) Age is less than 3 days , 3) Mother MRN not empty, then process else send NACK ("For NewBorn Encounters, PID.21 Mother Identifier should not be empty"). Similarly I have like 30 + rules from specs doc, so adding one by one, learning and getting help from community. These rules I will put in Edge router before message routes to multiple edges.