how to write this query?

• water490

  Ten Centuries

  Points: 1207

  More actions

  October 21, 2025 at 6:23 am

  #4675556

  Hi everyone

  I am a bit stuck on how to write this query.

  Below is test data:

  drop table if exists #price_cutoff
  drop table if exists #nr_data
  drop table if exists #nt_data
  
  create table #price_cutoff
  (
  [SYMBOL] [nvarchar](10) NOT NULL,
  [TRADE_DATE] [date] NOT NULL,
  [NR_PRICE] [float] NULL,
  [NT_PRICE] [float] NULL
  )
  
  create table #nr_data
  (
  [SYMBOL] [nvarchar](10) NOT NULL,
  [TRADE_DATE] [date] NOT NULL,
  [LEVEL] [numeric](12, 5) NOT NULL
  )
  
  create table #nx_data
  (
  [SYMBOL] [nvarchar](10) NOT NULL,
  [TRADE_DATE] [date] NOT NULL,
  [LEVEL] [numeric](12, 5) NOT NULL
  )
  
  insert into #price_cutoff
  values ('abc', '2025-10-20', '2.399', '2.424')
  
  insert into #nr_data
  values ('abc', '2025-10-20', '0.5'),
         ('abc', '2025-10-20', '1'),
     ('abc', '2025-10-20', '1.5'),
     ('abc', '2025-10-20', '2'),
     ('abc', '2025-10-20', '2.5'),
     ('abc', '2025-10-20', '3'),
     ('abc', '2025-10-20', '3.5')
  
  insert into #nx_data
  values ('abc', '2025-10-20', '2.5'),
         ('abc', '2025-10-20', '5'),
     ('abc', '2025-10-20', '7.5')
  

  Explanation of expected outcome:

  There are two tables (nr_data and nx_data) that store essentially the same data but for different time frames so the processing logic is the same for both tables.

  I want to find the highest LEVEL that is less than or equal to nr_price in the nr_data table.  If no such record can be found then return the lowest LEVEL in the nr_data table.  Same logic for nx_data table but use nx_price and nx_data table instead.

  The nr_price is 2.399.  Looking at the nr_data table, the highest LEVEL that is less than or equal to 2.399 is 2 so return 2.

  The nx_price is 2.424.  Looking at the nx_data table, there is no LEVEL that is less than or equal to 2.424 to return 2.5 (the lowest LEVEL).

  It is possible in some months nr_data may have no record that meets the above criteria but in other months nr_data may have a record.  Same for nx_data.

  How can I do this in SQL?

  Thank you