Correcting Fields Based on Previous Records

[Anonymous]

Hello,

I have a table like below and I am working on a PeriOp application that calculate the total minutes a room is utilized.

There are three shifts in day with the first shift being 07:00-15:30

The field Turn around time calculates the difference in minutes from Out Time of the previous Records and the In Time of the next Record.  For Example the third row In time is 14:46 and the Out time of the Second Row is 13:28 therefore 14:46 - 13:28 = 78 minutes of Turn around time which is recorded in the third row.

Day	Room	Shift	In Room Time	Out Time	Turn Around Time
1/1/2017	OR#5	Third	00:43	1:38	0
1/1/2017	OR#5	First	11:00	13:28	562
1/1/2017	OR#5	First	14:46	15:30	78

The issue that I am having and hoping that someone can help me with, is that according to Operating Logic, The first Record of the First Shift (in this case 2nd record in the table) Should have a turn around time of 0.  Instead the table is calculating the Turn Around Time from the Third Shift which is incorrect.

I need an output table to show the following ( Second row, which is the first record in the First shift should have a Turn Around Time of 0).

Day	Room	Shift	In Room Time	Out Time	Turn Around Time
1/1/2017	OR#5	Third	00:43	1:38	0
1/1/2017	OR#5	First	11:00	13:28	0
1/1/2017	OR#5	First	14:46	15:30	78

Please help

[mato32188]

Hi Salamon,

I would propose to create flag field in script, that would track the shift change.

Load *,

         if(@ShiftChange=1,0,InRoomTime-Previous(OutTime) as TurnAround;

Load

Day,

Room,

Shift,

InRoomTime,

OutTime,

If(Shift=Previous(Shift),0,1) as @ShiftChange

FROM ...

BR

Martin