SQL Query data from another table to fetch data from previous date

Question

the SQL query retrieves information about options traded from the "tbl_SAFEX_EDM_Options_Traded" table and joins it with the "tbl_SAFEX_EDM_Statistics" table to get additional statistics. The query then calculates various values based on the retrieved data and presents the result with specific formatting and ordering.

USE [SC_Equity]

;WITH TagLabelCTE AS (
    SELECT
        [Date],
        [Contract],
        [Expiry Date],
        [Contract Type],
        [Strike Price],
        [Quantity],
        [Spot Price],
        [Price],
        [Rate],
        [Origin],
        CONCAT(
            LEFT(DATENAME(MONTH, [Expiry Date]), 3),
            '-',
            FORMAT([Expiry Date], 'yy', 'en-US'),
            '_',
            [Contract],
            '_',
            [Strike Price],
            '_',
            [Contract Type],
            '_',
            [Quantity]
        ) AS [TagLabel]
    FROM [dbo].[tbl_SAFEX_EDM_Options_Traded]
    WHERE [Date] = '20230727'
        AND [Spot Price] <> '0'
        AND [Quantity] <> '0'
        AND [Contract] NOT IN (
            'ALSI', 'ALSX', 'DTOP', 'DTOX', 'DTOR', 'DCAP', 'DCAR', 'DCAX', 'INIM', 'BANK', 'YLOQ', 'ALSW', 'YMIQ',
            'DTOW', 'ALMI', 'YLUQ', 'YKKQ', 'YMGQ', 'YJNQ', 'YMUQ', 'YNDQ', 'FINI', 'BC06', 'YNXQ', 'BC01', 'YKNQ',
            'YPCQ', 'YPDQ', 'YPBQ', 'YPAQ', 'YNAQ', 'BC02', 'FNDI', 'YODQ', 'YOBQ', 'RESI'
        )
)
SELECT
    t.[Date],
    t.[Time],
    t.[Contract],
    t.[Expiry Date],
    t.[Quantity],
    t.[Strike Price],
    t.[Contract Type],
    t.[Spot Price],
    t.[Price],
    t.[Rate],
    t.[Origin],
    tl.[TagLabel] AS [Tag],
    tl.[TagLabel] AS [Label],
    CASE
        WHEN t.[Contract] IN ('ALSI', 'ALSX', 'DTOP', 'DTOX', 'DTOR', 'DCAP', 'DCAR', 'DCAX', 'INIM', 'BANK')
        THEN (t.[Quantity] * t.[Spot Price] * 10) / 1000000
        ELSE (t.[Quantity] * t.[Spot Price] * 100) / 1000000
    END AS [Notional (ZARm)],
    t.[Strike Price] / t.[Spot Price] * 100 AS [Strike(%Spot)],
    t.[Rate] AS [Vol MTM],
    'S' AS [B/S Tag],
    t.[Price] AS [Premium],
    t.[Price] * t.[Quantity] / 1000000 AS [Total Prem (ZARm)],
    (t.[Price] * t.[Quantity] / 1000000) /
    CASE
        WHEN t.[Contract] IN (
            'ALSI', 'ALSX', 'DTOP', 'DTOX', 'DTOR', 'DCAP', 'DCAR', 'DCAX', 'INIM', 'BANK', 'YLOQ', 'ALSW', 'YMIQ',
            'DTOW', 'ALMI', 'YLUQ', 'YKKQ', 'YMGQ', 'YJNQ', 'YMUQ', 'YNDQ', 'FINI', 'BC06', 'YNXQ', 'BC01', 'YKNQ',
            'YPCQ', 'YPDQ', 'YPBQ', 'YPAQ', 'YNAQ', 'BC02', 'FNDI', 'YODQ', 'YOBQ'
        )
        THEN (t.[Quantity] * t.[Spot Price] * 10) / 1000000
        ELSE (t.[Quantity] * t.[Spot Price] * 100) / 1000000
    END * 100 AS [Prem(%)],
    s.[Open Interest] AS [OI(t)]
FROM TagLabelCTE tl
INNER JOIN [dbo].[tbl_SAFEX_EDM_Options_Traded] t
    ON tl.[Date] = t.[Date]
    AND tl.[Contract] = t.[Contract]
    AND tl.[Expiry Date] = t.[Expiry Date]
    AND tl.[Contract Type] = t.[Contract Type]
    AND tl.[Strike Price] = t.[Strike Price]
INNER JOIN [dbo].[tbl_SAFEX_EDM_Statistics] s
    ON tl.[Date] = s.[Date]
    AND tl.[Contract] = s.[Contract]
    AND tl.[Expiry Date] = s.[Expiry Date]
    AND tl.[Contract Type] = s.[Contract Type]
    AND tl.[Strike Price] = s.[Strike Price]
WHERE t.[Date] = '20230727'
AND s.[Date] = '20230727'
ORDER BY
    t.[Date] DESC,
    [TagLabel] ASC,
    t.[Contract] ASC,
    t.[Expiry Date] ASC,
    t.[Spot Price] ASC,
    t.[Strike Price] ASC;

The next step I want to add to the code is to create a new column, "OI(t-1)," which retrieves the open interest from "tbl_SAFEX_EDM_Statistics" for the date preceding the current date ('20230727').

I have been prompting Chat GPT unsuccessfully. Please assist if you can.

Answer 1

You can join like this (as was pointed out in the comments using a LEFT JOIN not an INNER JOIN, as there will be no previous day's statistics, for the first day of a contract):

LEFT JOIN [dbo].[tbl_SAFEX_EDM_Statistics] prevs
ON DATEADD(DD, -1, tl.[Date]) = prevs.[Date]
AND tl.[Contract] = prevs.[Contract]
AND tl.[Expiry Date] = prevs.[Expiry Date]
AND tl.[Contract Type] = prevs.[Contract Type]
AND tl.[Strike Price] = prevs.[Strike Price]

This of course assumes that your statistics table has data for every date (including weekends and public holidays). If it doesn't then you have (at least) three options: firstly write a FUNCTION that returns the greatest date in the statistics table lower than the date passed as a parameter (assuming all your contracts are on the same exchange - if they aren't, then you will need to provide other parameters), secondly create a mapping table of business days and previous business days, thirdly have a table of public holidays and then write a 'FUNCTION' that returns previous business day, by reference to both day of week and the contents of the holiday table. Of the three, the second would be my personal choice, since, although it will (probably) take longer to set up, it will be faster at runtime.

A few other points to consider. Firstly AND s.[Date] = '20230727' in the WHERE is superfluous; s.[Date] is linked to tl.[Date], which in turn is linked to t.[Date], so WHERE t.[Date] = '20230727' is sufficient. Similarly t.[Date] DESC in the ORDER BY is unnecessary, as you are only selecting data for one date. Also your query would be much more flexible, if '20230727' was a parameter. (It is of course possible that in your "real" query you are not restricting the output to one date). Finally whilst you are allowed to use data types as column names, it is generally considered not advisable: I would suggest, you rename them to something else, e.g. [Value Date] (although I don't personally like having spaces in names!).