5 Replies Latest reply on Dec 18, 2014 8:00 AM by Steven Hawkins

    converting time values to time series.

    gadeyne.bram Master

      Hi,

       

      I have a table that contains some temperature values. I'd like to make some time series selecting the previous value by joining all previous values and selecting the first previous value using the rank function.

       

      e.g.

       

      select v.*

      from(

          select mv1.admissionid, mv1.time as temptime, mv1.value as degrees, mv2.time as previoustime, mv2.value as previousdegrees,

              rank() over(partition by mv1.admissionid, mv1.time order by mv2.time desc) as rang

          from cos2_monitorvalue mv1

          left join cos2_monitorvalue mv2 on

              mv2.admissionid = mv1.admissionid and

              mv2.time < mv1.time and

              mv2.variableid = mv1.variableid

          where mv1.variableid = 'TEMPCENTR'

      ) v

      where v.rang = 1 and

      v.admissionid = 11111

       

      I'm not sure if I can do this in a different way.

       

      Is there a way to tell Teiid that v.admissionid should be used when selecting rows from mv1? e.g. by using a virtual view or something like that? I know that this could be solved by a virtual procedure but unfortunately I can not use procedures in my current software.