DateTime based on lowest SeqID

[#1]

Good day, all.  I'm having a script challenge this morning.

NOTE:  Edited to include entire table.

My script looks like this:

EdmPatientStatusEventHistory:
LOAD

SourceID,
VisitID,
SeqID,
EventID,
EventName,
HistoryFunction,
HistoryUser,
StartRW,
RowUpdateDateTime,
HistoryUserType,
Comment,
StartDateTime,
ActualFileDateTime;
SQL SELECT *
FROM livedb.dbo.EdmPatientStatusEventHistory;

What I need is for each VisitID, the DateTime associated with the lowest value in SeqID, only where EventID = 'IN ROOM'.

Any ideas?  I'm sure this is a simple fix for those who are better at this than I am.

Message was edited by: Sam Chambers

[alexandros17]

Try with this:

MyTable:

LOAD
VisitID,
SeqID,
EventID,
EventName,
HistoryFunction,
Comment,
DateTime;
SQL SELECT *
FROM livedb.dbo.EdmPatientStatusEventHistory
WHERE EventID = 'IN ROOM';

Left Join

Load Min(DateTime) as Mini resident MyTable Group by VisitID, SeqID;

Let me know

[Anonymous]

See if this works,

LOAD

VisitID,SeqID,EventID,EventName,HistoryFunction,Comment,DateTime

;

SQL SELECT *

FROM livedb.dbo.EdmPatientStatusEventHistory

WHERE EventID = 'IN ROOM';

Data2:

Load

VisitID&'-'&SeqID as Key

;

LOAD

VisitID,

min(SeqID) as SeqID

resident Data

group by VisitID

;

Data3:

NoConcatenate

Load

*,

if(exists(Key,VisitID&'-'&SeqID),DateTime) as urdesireddate

resident Data;

drop table Data;

drop table Data2;

[#4]

Thanks, Alessandro.

After running your script with a limited load of 10,000 lines, each VisitID has thousands of values for 'Mini', rather than just one.

I'm going to update my original post to include the entire table.  Maybe that will help.

[ramoncova06]

I believe Alessandro code just had a typo, modified so it should work no

MyTable:

LOAD

VisitID,

SeqID,

EventID,

EventName,

HistoryFunction,

Comment,

DateTime;

SQL SELECT *

FROM livedb.dbo.EdmPatientStatusEventHistory

WHERE EventID = 'IN ROOM';

Left Join

Load Min(DateTime) as DateTime,

'Yes' as Mini resident MyTable

Group by VisitID, SeqID;

[maxgro · Accepted solution]

EdmPatientStatusEventHistory:
LOAD

SourceID,
VisitID,
SeqID,
EventID,
EventName,
HistoryFunction,
HistoryUser,
StartRW,
RowUpdateDateTime,
HistoryUserType,
Comment,
StartDateTime,
ActualFileDateTime;
SQL SELECT *
FROM livedb.dbo.EdmPatientStatusEventHistory;

left join (EdmPatientStatusEventHistory)

load VisitID, StartDateTime as MinStartDateTime           // StartDateTime or DateTime or ....

resident EdmPatientStatusEventHistory

where EventID = 'IN ROOM'

and peek(VisitID)<>VisitID

order by VisitID, SeqID

[#7]

Thank you, Massimo!  There's more testing to be done, but it appears to be working well!