Take the first and the last value of different fields in a single line.

[#1]

Good morning/night Folks.

I'm in trouble. Now, I have something like that moviment table.

Pacient	seq_mov	Unit	bed	moved in	moved out	previous Unit	Prev Moved In	prev Moved Out
00001	1	Surgery Center	SC05	01/05/2017 14:05:00	01/05/2017 16:00:25	Clinical Center	01/05/2017 13:15:00	01/05/2017 14:05:00
00001	2	UTI	UTI02	01/05/2017 16:00:25	08/05/2017 12:00:25	Surgery Center	01/05/2017 14:05:00	01/05/2017 16:00:25
00001	3	UTI	UTI07	08/05/2017 12:00:25	15/05/2017 03:50:00	UTI	01/05/2017 16:00:25	08/05/2017 12:00:25
00001	4	UTI	UTI07	15/05/2017 03:50:00	17/05/2017 22:25:00	UTI	08/05/2017 12:00:25	15/05/2017 03:50:00
00001	5	Recovery	REC01	17/05/2017 22:25:00	18/05/2017 09:00:08	UTI	15/05/2017 03:50:00	17/05/2017 22:25:00
00001	6	Hemodynamic	HEM02	18/05/2017 09:00:08	18/05/2017 09:40:00	Recovery	17/05/2017 22:25:00	18/05/2017 09:00:08
00001	7	UTI	UTI01	18/05/2017 09:40:00	21/05/2017 05:13:00	Hemodynamic	18/05/2017 09:00:08	18/05/2017 09:40:00
00001	8	UTI	UTI04	21/05/2017 05:13:00	27/05/2017 16:05:10	UTI	18/05/2017 09:40:00	21/05/2017 05:13:00
00001	9	Recovery	REC05	27/05/2017 16:05:10	28/05/2017 00:00:00	UTI	21/05/2017 05:13:00	27/05/2017 16:05:10
00002	10	Hemodynamic	HEM25	02/05/2017 23:45:00	03/05/2017 00:16:49	Primary Care	02/05/2017 22:25:00	02/05/2017 23:45:00
00002	11	....	....	....	....	....	....	....

• I need to have the entry moment of each pacient at the unit, and when he left that unit but in a single line.
• If a pacient enter the unit twice, it need to show him twice too.

So, if I filter the unit "UTI"  it gonna show something like this

pacient	Unit	Moved In	Moved out
00001	UTI	01/05/2017 16:00:25	17/05/2017 22:25:00
00001	UTI	18/05/2017 09:40:00	27/05/2017 16:05:10

Anyone Have an Idea?

[sunny_talwar]

Something like this

Where Flag is created in the script like this

Table:

LOAD * INLINE [

    Pacient, seq_mov, Unit, bed, moved in, moved out, previous Unit, Prev Moved In, prev Moved Out

    00001, 1, Surgery Center, SC05, 01/05/2017 14:05:00, 01/05/2017 16:00:25, Clinical Center, 01/05/2017 13:15:00, 01/05/2017 14:05:00

    00001, 2, UTI, UTI02, 01/05/2017 16:00:25, 08/05/2017 12:00:25, Surgery Center, 01/05/2017 14:05:00, 01/05/2017 16:00:25

    00001, 3, UTI, UTI07, 08/05/2017 12:00:25, 15/05/2017 03:50:00, UTI, 01/05/2017 16:00:25, 08/05/2017 12:00:25

    00001, 4, UTI, UTI07, 15/05/2017 03:50:00, 17/05/2017 22:25:00, UTI, 08/05/2017 12:00:25, 15/05/2017 03:50:00

    00001, 5, Recovery, REC01, 17/05/2017 22:25:00, 18/05/2017 09:00:08, UTI, 15/05/2017 03:50:00, 17/05/2017 22:25:00

    00001, 6, Hemodynamic, HEM02, 18/05/2017 09:00:08, 18/05/2017 09:40:00, Recovery, 17/05/2017 22:25:00, 18/05/2017 09:00:08

    00001, 7, UTI, UTI01, 18/05/2017 09:40:00, 21/05/2017 05:13:00, Hemodynamic, 18/05/2017 09:00:08, 18/05/2017 09:40:00

    00001, 8, UTI, UTI04, 21/05/2017 05:13:00, 27/05/2017 16:05:10, UTI, 18/05/2017 09:40:00, 21/05/2017 05:13:00

    00001, 9, Recovery, REC05, 27/05/2017 16:05:10, 28/05/2017 00:00:00, UTI, 21/05/2017 05:13:00, 27/05/2017 16:05:10

    00002, 10, Hemodynamic, HEM25, 02/05/2017 23:45:00, 03/05/2017 00:16:49, Primary Care, 02/05/2017 22:25:00, 02/05/2017 23:45:00

];

FinalTable:

LOAD *,

If(Unit = Previous(Unit), Alt(Peek('Flag'), 1), RangeSum(Peek('Flag'), 1)) as Flag

Resident Table

Order By seq_mov;

DROP Table Table;

[marcus_malinow]

Something like this:

_PatientData:

LOAD * INLINE [

Pacient, seq_mov, Unit, bed, moved in, moved out, previous Unit, Prev Moved In, prev Moved Out

00001, 1, Surgery Center, SC05, 01/05/2017 14:05:00, 01/05/2017 16:00:25, Clinical Center, 01/05/2017 13:15:00, 01/05/2017 14:05:00

00001, 2, UTI, UTI02, 01/05/2017 16:00:25, 08/05/2017 12:00:25, Surgery Center, 01/05/2017 14:05:00, 01/05/2017 16:00:25

00001, 3, UTI, UTI07, 08/05/2017 12:00:25, 15/05/2017 03:50:00, UTI, 01/05/2017 16:00:25, 08/05/2017 12:00:25

00001, 4, UTI, UTI07, 15/05/2017 03:50:00, 17/05/2017 22:25:00, UTI, 08/05/2017 12:00:25, 15/05/2017 03:50:00

00001, 5, Recovery, REC01, 17/05/2017 22:25:00, 18/05/2017 09:00:08, UTI, 15/05/2017 03:50:00, 17/05/2017 22:25:00

00001, 6, Hemodynamic, HEM02, 18/05/2017 09:00:08, 18/05/2017 09:40:00, Recovery, 17/05/2017 22:25:00, 18/05/2017 09:00:08

00001, 7, UTI, UTI01, 18/05/2017 09:40:00, 21/05/2017 05:13:00, Hemodynamic, 18/05/2017 09:00:08, 18/05/2017 09:40:00

00001, 8, UTI, UTI04, 21/05/2017 05:13:00, 27/05/2017 16:05:10, UTI, 18/05/2017 09:40:00, 21/05/2017 05:13:00

00001, 9, Recovery, REC05, 27/05/2017 16:05:10, 28/05/2017 00:00:00, UTI, 21/05/2017 05:13:00, 27/05/2017 16:05:10

00002, 10, Hemodynamic, HEM25, 02/05/2017 23:45:00, 03/05/2017 00:16:49, Primary Care, 02/05/2017 22:25:00, 02/05/2017 23:45:00

];

PatientData:

NOCONCATENATE

LOAD

    Pacient,

    Unit,

    min([moved in]) as [moved in],

    max([moved out]) as [moved out],

    PatientUnitCounter

GROUP BY     Pacient, Unit, PatientUnitCounter

;

LOAD

    Pacient,

    seq_mov,

    Unit,

    [moved in],

    [moved out],

    if(isnull(peek('Pacient', -1)), //first record

        1,

        if(Pacient=peek('Pacient', -1) //same pacient as previous row

            and Unit=peek('Unit', -1), //same unit as previous row

            peek('PatientUnitCounter', -1),

            peek('PatientUnitCounter', -1) + 1

            )

        ) as PatientUnitCounter

RESIDENT _PatientData

ORDER BY Pacient, [moved in]

;

DROP TABLE _PatientData