how to return the min value of a column if a sub-query returns no results

Question

Post reply

how to return the min value of a column if a sub-query returns no results

water490

SSC Eights!

Points: 955

March 28, 2024 at 9:37 pm

#4404063

Hi everyone

My query works for most cases. There is one edge case where it failed and I don't know how to fix it.

Query:

INSERT INTO DBO.FORWARD
SELECT T1.UNDERLYING_SYMBOL,
T1.QUOTE_DATE,
T1.NEAR_TERM_STRIKE,
T2.NEXT_TERM_STRIKE
FROM(
SELECT T1.UNDERLYING_SYMBOL, T1.QUOTE_DATE, MAX(T1.STRIKE) AS NEAR_TERM_STRIKE
FROM DBO.NearTermData AS T1 INNER JOIN DBO.ForwardPrice AS T2 
ON T1.UNDERLYING_SYMBOL = T2.UNDERLYING_SYMBOL AND 
T1.QUOTE_DATE = T2.QUOTE_DATE
WHERE T1.STRIKE <= T2.FORWARD_NEAR_PRICE
GROUP BY T1.UNDERLYING_SYMBOL, T1.QUOTE_DATE 
) AS T1 INNER JOIN 
(
SELECT T1.UNDERLYING_SYMBOL, T1.QUOTE_DATE, MAX(T1.STRIKE) AS NEXT_TERM_STRIKE
FROM DBO.NextTermData AS T1 INNER JOIN DBO.ForwardPrice AS T2 
ON T1.UNDERLYING_SYMBOL = T2.UNDERLYING_SYMBOL AND 
T1.QUOTE_DATE = T2.QUOTE_DATE
WHERE T1.STRIKE <= T2.FORWARD_NEXT_PRICE
GROUP BY T1.UNDERLYING_SYMBOL, T1.QUOTE_DATE 
) AS T2 
ON T1.UNDERLYING_SYMBOL = T2.UNDERLYING_SYMBOL AND 
   T1.QUOTE_DATE = T2.QUOTE_DATE

There are some cases where the WHERE clause produces no records. If this happens the query return nothing. This is not how it should be working. If either of the sub-queries can't find any records that meet either of the conditions (T1.STRIKE <= T2.FORWARD_NEAR_PRICE or T1.STRIKE <= T2.FORWARD_NEXT_PRICE) then it should find the smallest STRIKE value from its respective tables (NearTermData and NextTermData)

Currently, it is doing this:

NEAR_TERM_STRIKE = 4125 (correct)

NEXT_TERM_STRIKE = empty (not correct)

The correct outcome should be

NEAR_TERM_STRIKE = 4125

NEXT_TERM_STRIKE = 200 (it is the lowest STRIKE)

Even though only one sub-query shows the problem the problem actually exists for both the sub-queries so both need to be updated.

How can I fix my code so it behaves this way?

Test Data:

/****** Object:  Table [dbo].[ForwardPrice]    Script Date: 2024-03-28 2:19:56 PM ******/SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ForwardPrice](
[UNDERLYING_SYMBOL] [nvarchar](10) NOT NULL,
[QUOTE_DATE] [date] NOT NULL,
[FORWARD_NEAR_PRICE] [float] NULL,
[FORWARD_NEXT_PRICE] [float] NULL
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[NearTermData]    Script Date: 2024-03-28 2:19:56 PM ******/SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[NearTermData](
[UNDERLYING_SYMBOL] [nvarchar](10) NOT NULL,
[QUOTE_DATE] [date] NOT NULL,
 [int] NOT NULL
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[NextTermData]    Script Date: 2024-03-28 2:19:56 PM ******/SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[NextTermData](
[UNDERLYING_SYMBOL] [nvarchar](10) NOT NULL,
[QUOTE_DATE] [date] NOT NULL,
 [int] NOT NULL
) ON [PRIMARY]
GO
INSERT [dbo].[ForwardPrice] ([UNDERLYING_SYMBOL], [QUOTE_DATE], [FORWARD_NEAR_PRICE], [FORWARD_NEXT_PRICE]) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 4144.5525490636837, 198.47185994377355)
GO
INSERT [dbo].[NearTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 6500)
GO
INSERT [dbo].[NearTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 6600)
GO
INSERT [dbo].[NearTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 6800)
GO
INSERT [dbo].[NearTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 6900)
GO
INSERT [dbo].[NearTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 4375)
GO
INSERT [dbo].[NearTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 4425)
GO
INSERT [dbo].[NearTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 4450)
GO
INSERT [dbo].[NearTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 4525)
GO
INSERT [dbo].[NearTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 4675)
GO
INSERT [dbo].[NearTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 2750)
GO
INSERT [dbo].[NearTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 2775)
GO
INSERT [dbo].[NearTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 2825)
GO
INSERT [dbo].[NearTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 4925)
GO
INSERT [dbo].[NearTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 3800)
GO
INSERT [dbo].[NearTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 3725)
GO
INSERT [dbo].[NearTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 3775)
GO
INSERT [dbo].[NearTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 3825)
GO
INSERT [dbo].[NearTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 3875)
GO
INSERT [dbo].[NearTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 3925)
GO
INSERT [dbo].[NearTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 3950)
GO
INSERT [dbo].[NearTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 3975)
GO
INSERT [dbo].[NearTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 4075)
GO
INSERT [dbo].[NearTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 5075)
GO
INSERT [dbo].[NearTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 5125)
GO
INSERT [dbo].[NearTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 5250)
GO
INSERT [dbo].[NearTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 3375)
GO
INSERT [dbo].[NearTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 3400)
GO
INSERT [dbo].[NearTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 3450)
GO
INSERT [dbo].[NearTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 3525)
GO
INSERT [dbo].[NearTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 3550)
GO
INSERT [dbo].[NearTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 3575)
GO
INSERT [dbo].[NearTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 3625)
GO
INSERT [dbo].[NearTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 3650)
GO
INSERT [dbo].[NearTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 4150)
GO
INSERT [dbo].[NearTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 4175)
GO
INSERT [dbo].[NearTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 4200)
GO
INSERT [dbo].[NearTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 4225)
GO
INSERT [dbo].[NearTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 4250)
GO
INSERT [dbo].[NearTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 4275)
GO
INSERT [dbo].[NearTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 4550)
GO
INSERT [dbo].[NearTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 4575)
GO
INSERT [dbo].[NearTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 4600)
GO
INSERT [dbo].[NearTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 4650)
GO
INSERT [dbo].[NearTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 4700)
GO
INSERT [dbo].[NearTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 4750)
GO
INSERT [dbo].[NearTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 4350)
GO
INSERT [dbo].[NearTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 4100)
GO
INSERT [dbo].[NearTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 4800)
GO
INSERT [dbo].[NearTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 4825)
GO
INSERT [dbo].[NearTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 4900)
GO
INSERT [dbo].[NearTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 100)
GO
INSERT [dbo].[NearTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 3125)
GO
INSERT [dbo].[NearTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 5350)
GO
INSERT [dbo].[NearTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 5400)
GO
INSERT [dbo].[NearTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 5450)
GO
INSERT [dbo].[NearTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 3900)
GO
INSERT [dbo].[NearTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 4025)
GO
INSERT [dbo].[NearTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 800)
GO
INSERT [dbo].[NearTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 900)
GO
INSERT [dbo].[NearTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 1400)
GO
INSERT [dbo].[NearTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 1700)
GO
INSERT [dbo].[NearTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 1750)
GO
INSERT [dbo].[NearTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 1900)
GO
INSERT [dbo].[NearTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 1950)
GO
INSERT [dbo].[NearTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 3600)
GO
INSERT [dbo].[NearTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 5100)
GO
INSERT [dbo].[NearTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 5200)
GO
INSERT [dbo].[NearTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 3325)
GO
INSERT [dbo].[NearTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 5500)
GO
INSERT [dbo].[NearTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 6300)
GO
INSERT [dbo].[NearTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 2500)
GO
INSERT [dbo].[NearTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 3075)
GO
INSERT [dbo].[NearTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 3100)
GO
INSERT [dbo].[NearTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 3175)
GO
INSERT [dbo].[NearTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 3225)
GO
INSERT [dbo].[NearTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 3300)
GO
INSERT [dbo].[NearTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 3050)
GO
INSERT [dbo].[NearTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 2925)
GO
INSERT [dbo].[NearTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 2950)
GO
INSERT [dbo].[NearTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 3025)
GO
INSERT [dbo].[NearTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 2575)
GO
INSERT [dbo].[NearTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 2675)
GO
INSERT [dbo].[NearTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 2725)
GO
INSERT [dbo].[NearTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 5600)
GO
INSERT [dbo].[NearTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 5700)
GO
INSERT [dbo].[NearTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 3700)
GO
INSERT [dbo].[NearTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 4725)
GO
INSERT [dbo].[NearTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 4950)
GO
INSERT [dbo].[NearTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 4975)
GO
INSERT [dbo].[NearTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 3500)
GO
INSERT [dbo].[NearTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 5150)
GO
INSERT [dbo].[NearTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 2600)
GO
INSERT [dbo].[NearTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 2650)
GO
INSERT [dbo].[NearTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 2700)
GO
INSERT [dbo].[NearTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 2800)
GO
INSERT [dbo].[NearTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 2350)
GO
INSERT [dbo].[NearTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 1000)
GO
INSERT [dbo].[NearTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 1500)
GO
INSERT [dbo].[NearTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 1600)
GO
INSERT [dbo].[NearTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 3750)
GO
INSERT [dbo].[NearTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 3475)
GO
INSERT [dbo].[NearTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 2450)
GO
INSERT [dbo].[NearTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 2100)
GO
INSERT [dbo].[NearTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 1100)
GO
INSERT [dbo].[NearTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 5300)
GO
INSERT [dbo].[NearTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 4500)
GO
INSERT [dbo].[NearTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 5025)
GO
INSERT [dbo].[NearTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 6100)
GO
INSERT [dbo].[NearTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 4400)
GO
INSERT [dbo].[NearTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 4625)
GO
INSERT [dbo].[NearTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 3850)
GO
INSERT [dbo].[NearTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 3200)
GO
INSERT [dbo].[NearTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 2975)
GO
INSERT [dbo].[NearTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 3000)
GO
INSERT [dbo].[NearTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 2200)
GO
INSERT [dbo].[NearTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 6700)
GO
INSERT [dbo].[NearTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 1300)
GO
INSERT [dbo].[NearTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 1800)
GO
INSERT [dbo].[NearTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 5000)
GO
INSERT [dbo].[NearTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 6200)
GO
INSERT [dbo].[NearTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 5050)
GO
INSERT [dbo].[NearTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 300)
GO
INSERT [dbo].[NearTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 500)
GO
INSERT [dbo].[NearTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 2625)
GO
INSERT [dbo].[NearTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 3150)
GO
INSERT [dbo].[NearTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 2300)
GO
INSERT [dbo].[NearTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 4850)
GO
INSERT [dbo].[NearTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 4050)
GO
INSERT [dbo].[NearTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 2900)
GO
INSERT [dbo].[NearTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 2150)
GO
INSERT [dbo].[NearTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 200)
GO
INSERT [dbo].[NearTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 600)
GO
INSERT [dbo].[NearTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 2000)
GO
INSERT [dbo].[NearTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 6000)
GO
INSERT [dbo].[NearTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 4125)
GO
INSERT [dbo].[NearTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 2875)
GO
INSERT [dbo].[NearTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 4475)
GO
INSERT [dbo].[NearTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 4875)
GO
INSERT [dbo].[NearTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 4000)
GO
INSERT [dbo].[NearTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 7200)
GO
INSERT [dbo].[NearTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 3350)
GO
INSERT [dbo].[NearTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 2850)
GO
INSERT [dbo].[NearTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 7100)
GO
INSERT [dbo].[NearTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 1850)
GO
INSERT [dbo].[NearTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 400)
GO
INSERT [dbo].[NearTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 6400)
GO
INSERT [dbo].[NearTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 7000)
GO
INSERT [dbo].[NearTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 1200)
GO
INSERT [dbo].[NearTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 2050)
GO
INSERT [dbo].[NearTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 4325)
GO
INSERT [dbo].[NearTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 4775)
GO
INSERT [dbo].[NearTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 3425)
GO
INSERT [dbo].[NearTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 3675)
GO
INSERT [dbo].[NearTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 3250)
GO
INSERT [dbo].[NearTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 3275)
GO
INSERT [dbo].[NearTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 5900)
GO
INSERT [dbo].[NearTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 2400)
GO
INSERT [dbo].[NearTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 2550)
GO
INSERT [dbo].[NearTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 2250)
GO
INSERT [dbo].[NearTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 5800)
GO
INSERT [dbo].[NearTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 4300)
GO
INSERT [dbo].[NearTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 7300)
GO
INSERT [dbo].[NearTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 700)
GO
INSERT [dbo].[NextTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 2500)
GO
INSERT [dbo].[NextTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 2875)
GO
INSERT [dbo].[NextTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 2300)
GO
INSERT [dbo].[NextTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 2550)
GO
INSERT [dbo].[NextTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 2100)
GO
INSERT [dbo].[NextTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 1700)
GO
INSERT [dbo].[NextTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 2900)
GO
INSERT [dbo].[NextTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 2450)
GO
INSERT [dbo].[NextTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 2600)
GO
INSERT [dbo].[NextTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 2675)
GO
INSERT [dbo].[NextTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 2700)
GO
INSERT [dbo].[NextTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 2725)
GO
INSERT [dbo].[NextTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 2750)
GO
INSERT [dbo].[NextTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 2775)
GO
INSERT [dbo].[NextTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 2800)
GO
INSERT [dbo].[NextTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 2825)
GO
INSERT [dbo].[NextTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 2850)
GO
INSERT [dbo].[NextTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 2925)
GO
INSERT [dbo].[NextTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 1400)
GO
INSERT [dbo].[NextTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 1600)
GO
INSERT [dbo].[NextTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 1800)
GO
INSERT [dbo].[NextTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 1900)
GO
INSERT [dbo].[NextTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 2000)
GO
INSERT [dbo].[NextTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 2400)
GO
INSERT [dbo].[NextTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 2650)
GO
INSERT [dbo].[NextTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 2350)
GO
INSERT [dbo].[NextTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 3550)
GO
INSERT [dbo].[NextTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 3575)
GO
INSERT [dbo].[NextTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 3725)
GO
INSERT [dbo].[NextTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 2950)
GO
INSERT [dbo].[NextTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 2975)
GO
INSERT [dbo].[NextTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 3000)
GO
INSERT [dbo].[NextTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 3025)
GO
INSERT [dbo].[NextTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 3050)
GO
INSERT [dbo].[NextTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 3075)
GO
INSERT [dbo].[NextTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 3100)
GO
INSERT [dbo].[NextTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 3125)
GO
INSERT [dbo].[NextTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 3150)
GO
INSERT [dbo].[NextTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 3225)
GO
INSERT [dbo].[NextTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 3250)
GO
INSERT [dbo].[NextTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 3275)
GO
INSERT [dbo].[NextTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 3325)
GO
INSERT [dbo].[NextTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 3350)
GO
INSERT [dbo].[NextTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 3375)
GO
INSERT [dbo].[NextTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 4125)
GO
INSERT [dbo].[NextTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 4150)
GO
INSERT [dbo].[NextTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 4175)
GO
INSERT [dbo].[NextTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 4225)
GO
INSERT [dbo].[NextTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 4250)
GO
INSERT [dbo].[NextTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 4300)
GO
INSERT [dbo].[NextTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 4450)
GO
INSERT [dbo].[NextTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 4800)
GO
INSERT [dbo].[NextTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 4325)
GO
INSERT [dbo].[NextTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 3775)
GO
INSERT [dbo].[NextTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 3800)
GO
INSERT [dbo].[NextTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 3825)
GO
INSERT [dbo].[NextTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 3875)
GO
INSERT [dbo].[NextTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 3925)
GO
INSERT [dbo].[NextTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 3950)
GO
INSERT [dbo].[NextTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 3975)
GO
INSERT [dbo].[NextTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 4025)
GO
INSERT [dbo].[NextTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 3525)
GO
INSERT [dbo].[NextTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 3675)
GO
INSERT [dbo].[NextTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 3750)
GO
INSERT [dbo].[NextTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 5600)
GO
INSERT [dbo].[NextTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 3425)
GO
INSERT [dbo].[NextTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 3450)
GO
INSERT [dbo].[NextTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 3500)
GO
INSERT [dbo].[NextTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 6600)
GO
INSERT [dbo].[NextTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 1200)
GO
INSERT [dbo].[NextTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 4000)
GO
INSERT [dbo].[NextTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 6000)
GO
INSERT [dbo].[NextTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 400)
GO
INSERT [dbo].[NextTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 600)
GO
INSERT [dbo].[NextTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 800)
GO
INSERT [dbo].[NextTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 1000)
GO
INSERT [dbo].[NextTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 3650)
GO
INSERT [dbo].[NextTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 3200)
GO
INSERT [dbo].[NextTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 3300)
GO
INSERT [dbo].[NextTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 3600)
GO
INSERT [dbo].[NextTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 4100)
GO
INSERT [dbo].[NextTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 3625)
GO
INSERT [dbo].[NextTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 3175)
GO
INSERT [dbo].[NextTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 4350)
GO
INSERT [dbo].[NextTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 3850)
GO
INSERT [dbo].[NextTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 6400)
GO
INSERT [dbo].[NextTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 4075)
GO
INSERT [dbo].[NextTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 4900)
GO
INSERT [dbo].[NextTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 200)
GO
INSERT [dbo].[NextTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 4500)
GO
INSERT [dbo].[NextTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 5200)
GO
INSERT [dbo].[NextTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 6200)
GO
INSERT [dbo].[NextTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 3475)
GO
INSERT [dbo].[NextTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 4700)
GO
INSERT [dbo].[NextTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 4275)
GO
INSERT [dbo].[NextTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 3700)
GO
INSERT [dbo].[NextTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 3400)
GO
INSERT [dbo].[NextTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 4050)
GO
INSERT [dbo].[NextTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 4400)
GO
INSERT [dbo].[NextTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 4600)
GO
INSERT [dbo].[NextTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 5000)
GO
INSERT [dbo].[NextTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 3900)
GO
INSERT [dbo].[NextTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 5800)
GO
INSERT [dbo].[NextTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 5400)
GO
INSERT [dbo].[NextTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 4200)
GO
INSERT [dbo].[NextTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 2200)
GO

Thank you

Viewing 14 posts - 1 through 13 (of 13 total)

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

water490 SSC Eights! Points: 955 More actions · Answer 1

I gave this a bit more thought...

Returning the MIN value of STRIKE will not work. Instead, do the following...

If the sub-query ever returns an empty result then return the result from previous day's value for NEAR_TERM_STRIKE and/or NEXT_TERM_STRIKE. The table is below

CREATE TABLE [dbo].[Forward](
[UNDERLYING_SYMBOL] [nvarchar](10) NOT NULL,
[QUOTE_DATE] [date] NOT NULL,
[NEAR_TERM_STRIKE] [numeric](12, 5) NOT NULL,
[NEXT_TERM_STRIKE] [numeric](12, 5) NOT NULL
) ON [PRIMARY]
GO
INSERT [dbo].[Forward] ([UNDERLYING_SYMBOL], [QUOTE_DATE], [NEAR_TERM_STRIKE], [NEXT_TERM_STRIKE]) VALUES (N'ABC', CAST(N'2022-12-13' AS Date), CAST(4125.00000 AS Numeric(12, 5)), CAST(4150.00000 AS Numeric(12, 5)))
GO
INSERT [dbo].[Forward] ([UNDERLYING_SYMBOL], [QUOTE_DATE], [NEAR_TERM_STRIKE], [NEXT_TERM_STRIKE]) VALUES (N'ABC', CAST(N'2022-12-12' AS Date), CAST(4075.00000 AS Numeric(12, 5)), CAST(4125.00000 AS Numeric(12, 5)))
GO

Thank you

pietlinden SSC Guru Points: 63503 More actions · Answer 2

So use LAG() to return the previous day's value or LEAD() to return the next day's value. Well, assuming that gaps are only a single day. But you know what they say about assuming.... Maybe TOP(1) with a subquery? (Yeah, hideous, I know, but then you can remove nulls etc).

drew.allen SSC Guru Points: 76985 More actions · Answer 3

Please clean up your code before posting. There are several issues with the code for your test data.

You used PERMANENT tables rather than TEMP tables.
1. The easier you make it for other people, the more likely you are to get help. By using permanent tables, you run the risk, however small, that people already have a permanent table with that name, and the script will error out because of that.
You didn't clean up after yourself.
1. You created permanent tables, but didn't include DROP TABLE statements to clean up those permanent tables.
2. One reason to use temp tables is that they automatically drop themselves when the connection that created them is closed.
You used unnecessary square brackets.
1. BBCode uses square brackets to indicate a command, so BBCode will try to interpret anything within square brackets as a command.
2. STRIKE is a valid BBCode command, so it will interpret it as a command rather than displaying it as a field name.
3. This leads to missing field names, which leads to a multitude of error messages. INSERT [dbo].[NextTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], ) instead of INSERT [dbo].[NextTermData] ([UNDERLYING_SYMBOL], [QUOTE_DATE], STRIKE)

And while this isn't a problem, It is annoying. VALUES has been updated to allow multiple sets of values, but the option to script a table has not been updated to reflect that, so it produces unnecessarily verbose scripts. Failing to clean this up shows a lack of consideration for others, which makes it much less appealing to volunteer to help.

/*  Original verbose code  */
INSERT #NearTermData (UNDERLYING_SYMBOL, QUOTE_DATE, STRIKE) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 6500)
GO
INSERT #NearTermData (UNDERLYING_SYMBOL, QUOTE_DATE, STRIKE) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 6600)
GO
INSERT #NearTermData (UNDERLYING_SYMBOL, QUOTE_DATE, STRIKE) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 6800)
GO
INSERT #NearTermData (UNDERLYING_SYMBOL, QUOTE_DATE, STRIKE) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 6900)
GO
INSERT #NearTermData (UNDERLYING_SYMBOL, QUOTE_DATE, STRIKE) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 4375)
GO
INSERT #NearTermData (UNDERLYING_SYMBOL, QUOTE_DATE, STRIKE) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 4425)
GO
INSERT #NearTermData (UNDERLYING_SYMBOL, QUOTE_DATE, STRIKE) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 4450)
GO
INSERT #NearTermData (UNDERLYING_SYMBOL, QUOTE_DATE, STRIKE) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 4525)
GO
INSERT #NearTermData (UNDERLYING_SYMBOL, QUOTE_DATE, STRIKE) VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 4675)
GO


/* Cleaned up code  */
INSERT #NearTermData (UNDERLYING_SYMBOL, QUOTE_DATE, STRIKE)
VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 6500)
     , (N'ABC', CAST(N'2022-12-14' AS Date), 6600)
     , (N'ABC', CAST(N'2022-12-14' AS Date), 6800)
     , (N'ABC', CAST(N'2022-12-14' AS Date), 6900)
     , (N'ABC', CAST(N'2022-12-14' AS Date), 4375)
     , (N'ABC', CAST(N'2022-12-14' AS Date), 4425)
     , (N'ABC', CAST(N'2022-12-14' AS Date), 4450)
     , (N'ABC', CAST(N'2022-12-14' AS Date), 4525)
     , (N'ABC', CAST(N'2022-12-14' AS Date), 4675)

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

drew.allen SSC Guru Points: 76985 More actions · Answer 4

One more thing. There is no reason to use an explicit CAST here, and using an implicit CAST makes it much easier to read.

/* Explicit CAST  */
INSERT #NearTermData (UNDERLYING_SYMBOL, QUOTE_DATE, STRIKE)
VALUES (N'ABC', CAST(N'2022-12-14' AS Date), 6500)
     , (N'ABC', CAST(N'2022-12-14' AS Date), 6600)
     , (N'ABC', CAST(N'2022-12-14' AS Date), 6800)
     , (N'ABC', CAST(N'2022-12-14' AS Date), 6900)
     , (N'ABC', CAST(N'2022-12-14' AS Date), 4375)
     , (N'ABC', CAST(N'2022-12-14' AS Date), 4425)
     , (N'ABC', CAST(N'2022-12-14' AS Date), 4450)
     , (N'ABC', CAST(N'2022-12-14' AS Date), 4525)
     , (N'ABC', CAST(N'2022-12-14' AS Date), 4675)

/* Implicit CAST  */
INSERT #NearTermData (UNDERLYING_SYMBOL, QUOTE_DATE, STRIKE)
VALUES (N'ABC', N'2022-12-14', 6500)
     , (N'ABC', N'2022-12-14', 6600)
     , (N'ABC', N'2022-12-14', 6800)
     , (N'ABC', N'2022-12-14', 6900)
     , (N'ABC', N'2022-12-14', 4375)
     , (N'ABC', N'2022-12-14', 4425)
     , (N'ABC', N'2022-12-14', 4450)
     , (N'ABC', N'2022-12-14', 4525)
     , (N'ABC', N'2022-12-14', 4675)

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

Jonathan AC Roberts SSCoach Points: 18354 More actions · Answer 5

Are you expecting at most one row back for each UNDERLYING_SYMBOL, QUOTE_DATE?

Jonathan AC Roberts SSCoach Points: 18354 More actions · Answer 6

I think OUTER APPLY should work. I don't think this is quite correct as I'm not too sure about your specifications for what happens if nothing is found on the QUOTE_DATE.

Try this, then give feedback and we'll see if it can be adjusted.

SELECT FP.UNDERLYING_SYMBOL,
       FP.QUOTE_DATE  AS QUOTE_DATE_FP,
       Nr.QUOTE_DATE  AS QUOTE_DATE_NEAR,
       Nr.STRIKE      AS NEAR_TERM_STRIKE,
       Nxt.QUOTE_DATE AS QUOTE_DATE_NEXT,
       Nxt.STRIKE     AS NEXT_TERM_STRIKE
  FROM dbo.ForwardPrice AS FP
 OUTER APPLY(SELECT TOP(1) NTD.UNDERLYING_SYMBOL, NTD.QUOTE_DATE, NTD.STRIKE
               FROM NextTermData NTD
              WHERE NTD.UNDERLYING_SYMBOL = FP.UNDERLYING_SYMBOL
                AND NTD.QUOTE_DATE <= FP.QUOTE_DATE
              ORDER BY NTD.QUOTE_DATE DESC, NTD.STRIKE DESC) Nxt
 OUTER APPLY(SELECT TOP(1) NTD.UNDERLYING_SYMBOL, NTD.QUOTE_DATE, NTD.STRIKE
               FROM NearTermData NTD
              WHERE NTD.UNDERLYING_SYMBOL = FP.UNDERLYING_SYMBOL
                AND NTD.QUOTE_DATE <= FP.QUOTE_DATE
              ORDER BY NTD.QUOTE_DATE DESC, NTD.STRIKE DESC) Nr
;

Jonathan AC Roberts SSCoach Points: 18354 More actions · Answer 7

After reading again. I think this is what you want:

SELECT FP.UNDERLYING_SYMBOL,
       FP.QUOTE_DATE                                  AS QUOTE_DATE_FP,
       ISNULL(Nr.QUOTE_DATE, NrNotFound.QUOTE_DATE)   AS QUOTE_DATE_NEAR,
       ISNULL(Nxt.QUOTE_DATE, NxtNotFound.QUOTE_DATE) AS QUOTE_DATE_NEXT,
       ISNULL(Nr.STRIKE, NrNotFound.STRIKE)           AS NEAR_TERM_STRIKE,
       ISNULL(Nxt.STRIKE, NxtNotFound.STRIKE)         AS NEXT_TERM_STRIKE
  FROM dbo.ForwardPrice AS FP
 OUTER APPLY(SELECT TOP(1) NTD.UNDERLYING_SYMBOL, NTD.QUOTE_DATE, NTD.STRIKE
               FROM NextTermData NTD
              WHERE NTD.UNDERLYING_SYMBOL = FP.UNDERLYING_SYMBOL
                AND NTD.QUOTE_DATE = FP.QUOTE_DATE
              ORDER BY NTD.STRIKE DESC) Nxt
 OUTER APPLY(SELECT TOP(1) NTD.UNDERLYING_SYMBOL, NTD.QUOTE_DATE, NTD.STRIKE
               FROM NearTermData NTD
              WHERE NTD.UNDERLYING_SYMBOL = FP.UNDERLYING_SYMBOL
                AND NTD.QUOTE_DATE = FP.QUOTE_DATE
              ORDER BY NTD.STRIKE DESC) Nr
 OUTER APPLY(SELECT TOP(1) NTD.UNDERLYING_SYMBOL, NTD.QUOTE_DATE, NTD.STRIKE
               FROM NextTermData NTD
              WHERE NTD.UNDERLYING_SYMBOL = FP.UNDERLYING_SYMBOL
                AND NTD.QUOTE_DATE < FP.QUOTE_DATE
              ORDER BY NTD.QUOTE_DATE DESC, NTD.STRIKE ASC) NxtNotFound
 OUTER APPLY(SELECT TOP(1) NTD.UNDERLYING_SYMBOL, NTD.QUOTE_DATE, NTD.STRIKE
               FROM NearTermData NTD
              WHERE NTD.UNDERLYING_SYMBOL = FP.UNDERLYING_SYMBOL
                AND NTD.QUOTE_DATE < FP.QUOTE_DATE
              ORDER BY NTD.QUOTE_DATE DESC, NTD.STRIKE ASC) NrNotFound
;

water490 SSC Eights! Points: 955 More actions · Answer 8

thank you everyone for your feedback. I will test the code and see how the output looks

drew.allen SSC Guru Points: 76985 More actions · Answer 9

This gives the exact same results as Jonathan's but only reads the near and next term data tables once each.

SELECT FP.UNDERLYING_SYMBOL,
       FP.QUOTE_DATE                                  AS QUOTE_DATE_FP,
       Nr.QUOTE_DATE                                  AS QUOTE_DATE_NEAR,
       Nxt.QUOTE_DATE                                 AS QUOTE_DATE_NEXT,
       Nr.STRIKE                                      AS NEAR_TERM_STRIKE,
       Nxt.STRIKE                                     AS NEXT_TERM_STRIKE
  FROM #ForwardPrice AS FP
 OUTER APPLY(SELECT TOP(1) NTD.UNDERLYING_SYMBOL, NTD.QUOTE_DATE, NTD.STRIKE
               FROM #NextTermData NTD
              WHERE NTD.UNDERLYING_SYMBOL = FP.UNDERLYING_SYMBOL
                AND NTD.QUOTE_DATE <= FP.QUOTE_DATE
              ORDER BY NTD.STRIKE DESC) Nxt
 OUTER APPLY(SELECT TOP(1) NTD.UNDERLYING_SYMBOL, NTD.QUOTE_DATE, NTD.STRIKE
               FROM #NearTermData NTD
              WHERE NTD.UNDERLYING_SYMBOL = FP.UNDERLYING_SYMBOL
                AND NTD.QUOTE_DATE <= FP.QUOTE_DATE
              ORDER BY NTD.STRIKE DESC) Nr
;

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

Jonathan AC Roberts SSCoach Points: 18354 More actions · Answer 10

drew.allen wrote:

This gives the exact same results as Jonathan's but only reads the near and next term data tables once each.

SELECT FP.UNDERLYING_SYMBOL,
       FP.QUOTE_DATE                                  AS QUOTE_DATE_FP,
       Nr.QUOTE_DATE                                  AS QUOTE_DATE_NEAR,
       Nxt.QUOTE_DATE                                 AS QUOTE_DATE_NEXT,
       Nr.STRIKE                                      AS NEAR_TERM_STRIKE,
       Nxt.STRIKE                                     AS NEXT_TERM_STRIKE
  FROM #ForwardPrice AS FP
 OUTER APPLY(SELECT TOP(1) NTD.UNDERLYING_SYMBOL, NTD.QUOTE_DATE, NTD.STRIKE
               FROM #NextTermData NTD
              WHERE NTD.UNDERLYING_SYMBOL = FP.UNDERLYING_SYMBOL
                AND NTD.QUOTE_DATE <= FP.QUOTE_DATE
              ORDER BY NTD.STRIKE DESC) Nxt
 OUTER APPLY(SELECT TOP(1) NTD.UNDERLYING_SYMBOL, NTD.QUOTE_DATE, NTD.STRIKE
               FROM #NearTermData NTD
              WHERE NTD.UNDERLYING_SYMBOL = FP.UNDERLYING_SYMBOL
                AND NTD.QUOTE_DATE <= FP.QUOTE_DATE
              ORDER BY NTD.STRIKE DESC) Nr
;

Drew

The OP said they wanted the lowest strike for the previous day if no match was found on the day.

drew.allen SSC Guru Points: 76985 More actions · Answer 11

Jonathan AC Roberts wrote:

drew.allen wrote:

This gives the exact same results as Jonathan's but only reads the near and next term data tables once each.

SELECT FP.UNDERLYING_SYMBOL,
       FP.QUOTE_DATE                                  AS QUOTE_DATE_FP,
       Nr.QUOTE_DATE                                  AS QUOTE_DATE_NEAR,
       Nxt.QUOTE_DATE                                 AS QUOTE_DATE_NEXT,
       Nr.STRIKE                                      AS NEAR_TERM_STRIKE,
       Nxt.STRIKE                                     AS NEXT_TERM_STRIKE
  FROM #ForwardPrice AS FP
 OUTER APPLY(SELECT TOP(1) NTD.UNDERLYING_SYMBOL, NTD.QUOTE_DATE, NTD.STRIKE
               FROM #NextTermData NTD
              WHERE NTD.UNDERLYING_SYMBOL = FP.UNDERLYING_SYMBOL
                AND NTD.QUOTE_DATE <= FP.QUOTE_DATE
              ORDER BY NTD.STRIKE DESC) Nxt
 OUTER APPLY(SELECT TOP(1) NTD.UNDERLYING_SYMBOL, NTD.QUOTE_DATE, NTD.STRIKE
               FROM #NearTermData NTD
              WHERE NTD.UNDERLYING_SYMBOL = FP.UNDERLYING_SYMBOL
                AND NTD.QUOTE_DATE <= FP.QUOTE_DATE
              ORDER BY NTD.STRIKE DESC) Nr
;

Drew

The OP said they wanted the lowest strike for the previous day if no match was found on the day.

The OP should have provided data for this use case. It's extremely difficult to test for use cases that don't exist in the data. Even so, that's only a minor tweak to the ORDER BY clause.

ORDER BY NTD.QUOTE_DATE DESC, CASE WHEN NTD.QUOTE_DATE = fp.QUOTE_DATE THEN NTD.STRIKE ELSE -NTD.STRIKE END DESC

Drew

This reply was modified 7 months, 3 weeks ago by drew.allen.

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

Jonathan AC Roberts SSCoach Points: 18354 More actions · Answer 12

drew.allen wrote:

Jonathan AC Roberts wrote:

drew.allen wrote:

This gives the exact same results as Jonathan's but only reads the near and next term data tables once each.

SELECT FP.UNDERLYING_SYMBOL,
       FP.QUOTE_DATE                                  AS QUOTE_DATE_FP,
       Nr.QUOTE_DATE                                  AS QUOTE_DATE_NEAR,
       Nxt.QUOTE_DATE                                 AS QUOTE_DATE_NEXT,
       Nr.STRIKE                                      AS NEAR_TERM_STRIKE,
       Nxt.STRIKE                                     AS NEXT_TERM_STRIKE
  FROM #ForwardPrice AS FP
 OUTER APPLY(SELECT TOP(1) NTD.UNDERLYING_SYMBOL, NTD.QUOTE_DATE, NTD.STRIKE
               FROM #NextTermData NTD
              WHERE NTD.UNDERLYING_SYMBOL = FP.UNDERLYING_SYMBOL
                AND NTD.QUOTE_DATE <= FP.QUOTE_DATE
              ORDER BY NTD.STRIKE DESC) Nxt
 OUTER APPLY(SELECT TOP(1) NTD.UNDERLYING_SYMBOL, NTD.QUOTE_DATE, NTD.STRIKE
               FROM #NearTermData NTD
              WHERE NTD.UNDERLYING_SYMBOL = FP.UNDERLYING_SYMBOL
                AND NTD.QUOTE_DATE <= FP.QUOTE_DATE
              ORDER BY NTD.STRIKE DESC) Nr
;

Drew

The OP said they wanted the lowest strike for the previous day if no match was found on the day.

The OP should have provided data for this use case. It's extremely difficult to test for use cases that don't exist in the data. Even so, that's only a minor tweak to the ORDER BY clause.

ORDER BY NTD.QUOTE_DATE DESC, CASE WHEN NTD.QUOTE_DATE = fp.QUOTE_DATE THEN NTD.STRIKE ELSE -NTD.STRIKE END DESC

Drew

Yes, very good.

water490 SSC Eights! Points: 955 More actions · Answer 13

Thank you everyone!

I will try them and report back any issues