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

  • 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

     

     

     

  • 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

  • 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).

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

    1. 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.

    2. 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.

    3. 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

  • 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

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

  • 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
    ;
  • 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
    ;
  • thank you everyone for your feedback.  I will test the code and see how the output looks

  • 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

  • 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.

  • 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 2 weeks, 2 days ago by  drew.allen.

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • 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.

     

  • Thank you everyone!

    I will try them and report back any issues

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

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