how to write this query?

Question

Post reply

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

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323577 More actions · Answer 1

to me it looks like a group by query will satisfy it.

SYMBOL TRADE_DATE Maxt1Level Mint2Level TheLevel

abc 2025-10-20 2.00000 2.50000 2.00000

and my query:

SELECT

t1.[SYMBOL],

t1.[TRADE_DATE],

MAX(t1.[LEVEL]) AS Maxt1Level,

MIN(t2.[LEVEL]) As Mint2Level,

ISNULL(MAX(t1.[LEVEL]), MIN(t2.[LEVEL])) AS TheLevel

FROM #nr_data t1

LEFT JOIN #nx_data t2

ON t1.[SYMBOL] = t2.[SYMBOL]

AND t1.[TRADE_DATE] = t2.[TRADE_DATE]

LEFT JOIN #price_cutoff t3

ON t1.[SYMBOL] = t3.[SYMBOL]

AND t1.[TRADE_DATE] = t3.[TRADE_DATE]

WHERE t1.[LEVEL] <= t3.[NR_PRICE]

GROUP BY t1.[SYMBOL],t1.[TRADE_DATE]

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!