Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
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;
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.
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;
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
Thank you, Massimo! There's more testing to be done, but it appears to be working well!