CODEWITHSUNDEEP
sqloracle-databaseoracle11g
Oyvind
Oyvind

Reputation: 65

Getting date closest to variable

I am working on a report application for rotating equipment. We calculate the status of the equipment every 3 hours and store the calculation in an Oracle database.

Before the report presented only the "current state" (where we only retrieved the latest calculation). But now the user is going to have the option to scroll back in time and look at earlier calculations.

SELECT *
FROM G_RunningHoursEvent
WHERE GRTE_TagName='#!VARIABLE_TAGNAME#' AND
      GRTE_ValuesUpdate IN (SELECT MAX(GRTE_ValuesUpdate)
                            FROM G_RunningHoursEvent
                            WHERE GRTE_TagName='#!VARIABLE_TAGNAME#')

SQL is not my strongest suit and most of colleagues are on holiday. I need some tips on how I can extract the latest calculation closest to the new variable #!VARIABLE_TIME#

Is there someone that have any suggestions ?

Upvotes: 0

Views: 73

Answers (2)

APC
APC

Reputation: 146189

If I understand correctly, the user will select a datetime value and you need to select the Event record which is closest to that time.

The following query does some magic in the sub-query to establish the record which is closest to the #!VARIABLE_TIME# value. 

select 
from g_runninghoursevent
where grte_tagname = '#!VARIABLE_TAGNAME#'
and grte_valuesupdate in 
     ( select grte_valuesupdate 
       from ( with data as 
                   ( select grte_valuesupdate 
                            , abs(grte_valuesupdate  -  #!VARIABLE_TIME#) tdiff
                     from g_runninghoursevent
                     where grte_tagname = '#!VARIABLE_TAGNAME#` )
              select grte_valuesupdate 
                     , rank() over (order by tdiff asc) rnk
              from data )
       where rnk = 1 )

Notes

  • The ABS() call means the sub-query will return the record with the smallest difference, either before or after the input parameter.
  • The sub-query uses IN rather than equality because you might get a tie for the smallest difference
  • I have assumed #!VARIABLE_TIME# is a datetime; if it is a string you will need to cast it to a date with teh appropriate mask.

Upvotes: 1

araknoid
araknoid

Reputation: 3125

This query should solve your problem:

SELECT *
FROM G_RunningHoursEvent
WHERE GRTE_TagName='#!VARIABLE_TAGNAME#' AND
      TRUNC(GRTE_ValuesUpdate) = (SELECT TRUNC(GRTE_ValuesUpdate)
                                  FROM (SELECT GRTE_ValuesUpdate
                                        FROM G_RunningHoursEvent
                                        WHERE GRTE_TagName='#!VARIABLE_TAGNAME#'
                                        ORDER BY GRTE_ValuesUpdate DESC)
                                  WHERE ROWNUM = 1);

Doing this you are ordering the G_RunningHoursEvent records by date having the closest record (in time) in the 1st row. With the other SELECT, you just extract this 1st record.

Upvotes: 0

Related Questions