As Of (Prevailing)
==================


Exact Time Match
################

Data at a given timestamp can be retrieved by specifying ``TIMESTAMP`` ``=`` the specified timestamp.

.. code-block:: sql
   :caption: Retrieve Records at specified Timestamp

   SELECT * FROM LSE.TRD                   
   WHERE SYMBOL_NAME='VOD' 
   and TIMESTAMP = '2024-01-03 08:22:51.848 UTC'
   LIMIT 10


.. csv-table:: Retrieve Records at specified Timestamp results
   :header: Symbol,Timestamp,PRICE,SIZE,SYMBOL_NAME,TICK_TYPE,OMDSEQ,EXCH_TIME,TRADE_ID,TRADE_TYPE,TRADE_VENUE,PUB_VENUE,TRADE_CURRENCY,MMT_MKT_MECH,MMT_TRD_MODE,MMT_TRANS_CAT,MMT_NEGOTIATED_IND,MMT_CROSS_IND,MMT_MOD_IND,MMT_BENCHMARK_IND,MMT_DIVIDEND_IND,MMT_OFF_BOOK_AUTO_IND,MMT_PRICE_FORMING_IND,MMT_ALGO_IND,MMT_PUB_MODE,MMT_DEFERRAL_TYPE,MMT_DUP_IND,DELETED_TIME,TICK_STATUS

   LSE::VOD,2024-01-03 08:22:50.847,0.818,200,LSE::VOD,TRD,1,2024-01-03 08:22:50.763,427637175188410480,OB,SINT,ECEU,EUR,4,7,-,-,-,-,-,-,,P,-,-,-,-,,0


Most Recent / Prevailing Value
##############################

To return the most recent / prevailing value as of the specified time, additionally define a lookback period  ``init_lookback`` in seconds.

.. code-block:: sql
   :caption: Retrieve the most recent trade as of the specified Timestamp

   SELECT * FROM LSE.TRD t                     
   WHERE SYMBOL_NAME='VOD' 
   and TIMESTAMP = '2024-01-03 08:22:51.848 UTC'
   and t.init_lookback = 86400                  
   LIMIT 10


.. csv-table:: Retrieve the most recent trade as of the specified Timestamp results
   :header: Symbol,Timestamp,PRICE,SIZE,SYMBOL_NAME,TICK_TYPE,OMDSEQ,EXCH_TIME,TRADE_ID,TRADE_TYPE,TRADE_VENUE,PUB_VENUE,TRADE_CURRENCY,MMT_MKT_MECH,MMT_TRD_MODE,MMT_TRANS_CAT,MMT_NEGOTIATED_IND,MMT_CROSS_IND,MMT_MOD_IND,MMT_BENCHMARK_IND,MMT_DIVIDEND_IND,MMT_OFF_BOOK_AUTO_IND,MMT_PRICE_FORMING_IND,MMT_ALGO_IND,MMT_PUB_MODE,MMT_DEFERRAL_TYPE,MMT_DUP_IND,DELETED_TIME,TICK_STATUS

   LSE::VOD,2024-01-03 08:22:51.848,0.818,200,LSE::VOD,TRD,1,2024-01-03 08:22:50.763,427637175188410480,OB,SINT,ECEU,EUR,4,7,-,-,-,-,-,-,,P,-,-,-,-,,0


As Of Join
##########

To retrieve trades joined to prevailing quotes an ``asof`` join is specified using the ``sametime_as_existing`` function, specifying the trade timestamp as the leading source, followed by the quote timestamp.

.. code-block:: sql
   :caption: As of Join, Joining Trades to Prevailing Quotes

   SELECT t.PRICE as PRICE,t.SIZE as SIZE,
   q.TIMESTAMP QTE_TIME, q.BID_PRICE as BID_PRICE, q.ASK_PRICE as ASK_PRICE  
   FROM LSE_SAMPLE.TRD t, LSE_SAMPLE.QTE q         
   WHERE t.SYMBOL_NAME='VOD' and q.SYMBOL_NAME='VOD'   
   and sametime_as_existing(t.timestamp, q.timestamp, 0) = TRUE   
   and TIMESTAMP >= '2024-01-03 00:00:00 UTC'
   and TIMESTAMP < '2024-01-04 00:00:00 UTC'
   LIMIT 10

.. csv-table:: As of Join, Joining Trades to Prevailing Quotes results
   :header: Symbol,Timestamp,PRICE,SIZE,BID_PRICE,ASK_PRICE,QTE_TIME

   LSE_SAMPLE::VOD,2024-01-03 07:15:10.133,69.76,"40,000",69,71.82,2024-01-03 05:00:07.876
   LSE_SAMPLE::VOD,2024-01-03 08:00:06.232,70,"184,613",70.01,70.19,2024-01-03 08:00:06.226
   LSE_SAMPLE::VOD,2024-01-03 08:00:06.233,70.01,140,70.01,70.19,2024-01-03 08:00:06.226
   LSE_SAMPLE::VOD,2024-01-03 08:00:06.287,70.01,500,70.01,70.19,2024-01-03 08:00:06.287
   LSE_SAMPLE::VOD,2024-01-03 08:00:08.113,70.104,"2,800",70.08,70.18,2024-01-03 08:00:07.747
   LSE_SAMPLE::VOD,2024-01-03 08:00:09.380,70.137,49,70.08,70.18,2024-01-03 08:00:08.234
   LSE_SAMPLE::VOD,2024-01-03 08:00:09.943,70.0981,"66,908",70.08,70.18,2024-01-03 08:00:08.234
   LSE_SAMPLE::VOD,2024-01-03 08:00:09.944,70.1097,214,70.08,70.18,2024-01-03 08:00:08.234
   LSE_SAMPLE::VOD,2024-01-03 08:00:10.119,70.146,840,70.08,70.18,2024-01-03 08:00:08.234
   LSE_SAMPLE::VOD,2024-01-03 08:00:10.933,70.132,344,70.08,70.18,2024-01-03 08:00:08.234