6 Replies Latest reply: Apr 14, 2015 2:47 PM by Sam Chambers RSS

    DateTime based on lowest SeqID

      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

        • Re: DateTime based on lowest SeqID
          Alessandro Saccone

          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

           

          • Re: DateTime based on lowest SeqID
            Amar Bogam

            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;

            • Re: DateTime based on lowest SeqID
              Ramon Covarrubias

              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;

               

              • Re: DateTime based on lowest SeqID
                Massimo Grossi

                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