difference between dates from 2 different columns and rows

[sbihorel]

Hello,

I need assistance creating an expression that will show the duration between (hh:mm) a date/time stamp from one column and the previous date/time stamp from a different column.  It also needs to be limited by date.  so for example in the attached screenshot, for January 7th, I want to subtract the time from column (Patient In Room Date and Time) minus (Patient Out Room Date and Time) which should equal 28 minutes.  if there isn't a previous (such as January 1) then show as null.  Any help would be greatly appreciated !

[jochem_zw · Accepted solution]

You can do it in script, something like:

Load 

Field1,Field2,[Patient in Room Date and Time],[Patient Out Room Date and Time],

[Patient in Room Date and Time]-previous([Patient Out Room Date and Time]) as Duration

from Source

Order by Field1,Field2,[Patient in Room Date and Time],[Patient Out Room Date and Time];

In this example Field1 and Field2 are not taken into account.