Subtract with previous record in a different column

[MSK]

Hi,

I'm facing an issue with previous functionality.

Below is the sample I have. And I want the Turnover Time as the result column.

The logic should be In Time Column 2nd row minus Out Time Column 1st Row, that gives me Turnover as -18
the next result should be In Time 3rd column minus Out Time 2nd Column should give me -72 and so on...

MRN	OR	Schedueld Time	In Time	Out Time	Turnover Time
29402	2	7:30 AM	7:23 AM	7:45 AM
30771	3	7:45 AM	7:27 AM	9:23 AM	-18
30836	2	8:15 AM	8:11 AM	9:15 AM	-72
30136	2	9:30 AM	9:26 AM	9:57 AM	11
30766	3	9:00 AM	9:43 AM	10:53 AM	-14
30736	2	10:40 AM	10:12 AM	10:45 AM	-41
30801	2	11:10 AM	10:55 AM	11:21 AM	10
30640	3	10:00 AM	11:11 AM	12:19 PM	-10
30639	2	12:00 PM	11:32 AM	11:51 AM	-47
30741	2	11:30 AM	12:00 PM	12:32 PM	9
30249	3	11:00 AM	12:28 PM	1:05 PM	-4
30643	2	12:35 PM	12:41 PM	1:00 PM	-24
30394	2	12:15 PM	1:13 PM	1:39 PM	13

I tried previous function along with row() as well and grouping them in ASC order. Doesn't give me expected result. Any help is appreciated.

[Aurelien_Martinez]

Hi,

If you want to do that in script :

data:
LOAD
MRN,
OR,
Time#([Schedueld Time], 'hh:mm') as [Schedueld Time],
Time#([In Time], 'hh:mm') as [In Time],
Time#([Out Time], 'hh:mm') as [Out Time]
Inline [
MRN, OR , Schedueld Time, In Time, Out Time
29402, 2, 7:30, 7:23, 7:45,
30771, 3, 7:45, 7:27, 9:23
30836, 2, 8:15, 8:11, 9:15
30136, 2, 9:30, 9:26, 9:57
30766, 3, 9:00, 9:43, 10:53
30736, 2, 10:40, 10:12, 10:45
30801, 2, 11:10, 10:55, 11:21
30640, 3, 10:00, 11:11, 12:19
30639, 2, 12:00, 11:32, 11:51
30741, 2, 11:30, 12:00, 12:32
30249, 3, 11:00, 12:28, 13:05
30643, 2, 12:35, 12:41, 13:00
30394, 2, 12:15, 13:13, 13:39
];

LOAD
MRN,
OR,
[Schedueld Time],
[In Time],
[Out Time],
Peek([Out Time]) as peek,
Interval([In Time] - Peek([Out Time]), 'm') as [Turnover Time]
Resident data
//Order by
// [Schedueld Time] asc
;

DROP Table data;

Aurélien

[MSK]

Tried the bottom script, doesn't give me same results.

MRN, OR are coming from different table. 

attached is the actual fields from my table. The timestamp format is different here etc. Maybe the way I'm joining my tables is not giving me right results?

[BrunPierre]

@MSK You can also do it differently with if function.

Load *,

if(RecNo()=2,Interval([In Time]- peek([Out Time]),'m'),
Interval(([In Time])- Previous([Out Time]),'m')) as [Turnover Time];

LOAD RN,
[OR ],
time([Schedueld Time]) as [Schedueld Time],
time([In Time]) as [In Time],
time([Out Time]) as [Out Time]
FROM SourceTable:

[MSK]

I still dont get the same result

My backend script w/o changes and the other image is when I use  your expression (with arrow).

[BrunPierre]

@MSK maybe it's nothing, nevertheless, you do realize there's a filter on PhyTurnover table. I'll suggest you place the IF syntax in the final table.

[MSK]

Attached is the actual data.

My timestamps are different... I'm looking for Operative Start date time - previous Operative stop date time

[MSK]

Tried replacing my if statement with yours, I get all positive values.