Test
-- Refactored SQL using CTEs for clarity and potential performance improvements
WITH CTE_EX_DATA AS (
-- Clearing Data
SELECT
CM.REPORTING_FIRM_ID,
MOptD.CONTRACT_MARKET_CODE,
MOptD.FUTURES_EXPIRATION_DATE,
MOptD.REPORT_DATE,
SUM(MOptD.LONG) AS LONG,
SUM(MOptD.SHORT) AS SHORT,
MOptD.OPTION_CLASS_CODE,
MOptD.EXPIRATION_ID_DATE,
MOptD.PUT_CALL_INDICATOR,
CAST(MOptD.STRIKE_PRICE AS decimal(18, 6)) AS STRIKE_PRICE
FROM MEMBER_OPTION_DAY AS MOptD WITH (NOLOCK)
INNER JOIN CLEARING_MEMBERSHIP AS CM WITH (NOLOCK)
ON MOptD.EXCHANGE_INITIALS = CM.EXCHANGE_INITIALS
AND MOptD.CLEARING_MEMBERSHIP_ID = CM.CLEARING_MEMBERSHIP_ID
GROUP BY
CM.REPORTING_FIRM_ID,
MOptD.CONTRACT_MARKET_CODE,
MOptD.FUTURES_EXPIRATION_DATE,
MOptD.REPORT_DATE,
MOptD.OPTION_CLASS_CODE,
MOptD.EXPIRATION_ID_DATE,
MOptD.PUT_CALL_INDICATOR,
MOptD.STRIKE_PRICE
),
CTE_OM_DATA AS (
-- Omni Data
SELECT
AOD.CONTRACT_MARKET_CODE,
AOD.FUTURES_EXPIRATION_DATE,
AOD.REPORT_DATE,
SUM(AOD.LONG) AS LONG,
SUM(AOD.SHORT) AS SHORT,
AOD.OWNER_ID,
AOD.TRADER_SUFFIX,
REPORTING_FIRM.REPORTING_FIRM_ID AS OMNI_RF,
AOD.OPTION_CLASS_CODE,
AOD.EXPIRATION_ID_DATE,
AOD.PUT_CALL_INDICATOR,
CAST(AOD.STRIKE_PRICE AS decimal(18, 6)) AS STRIKE_PRICE
FROM ACCOUNT_OPTION_DAY AS AOD WITH (NOLOCK)
INNER JOIN REPORTING_FIRM WITH (NOLOCK)
ON AOD.OWNER_ID = REPORTING_FIRM.OWNER_ID
AND AOD.TRADER_SUFFIX = REPORTING_FIRM.TRADER_SUFFIX
WHERE AOD.ACCOUNT_TYPE_CODE > 0
GROUP BY
REPORTING_FIRM.REPORTING_FIRM_ID,
AOD.CONTRACT_MARKET_CODE,
AOD.FUTURES_EXPIRATION_DATE,
AOD.REPORT_DATE,
AOD.OWNER_ID,
AOD.TRADER_SUFFIX,
AOD.OPTION_CLASS_CODE,
AOD.EXPIRATION_ID_DATE,
AOD.PUT_CALL_INDICATOR,
AOD.STRIKE_PRICE
),
CTE_LT_DATA AS (
-- LT Data
SELECT
AOD.REPORTING_FIRM_ID,
AOD.CONTRACT_MARKET_CODE,
CM.EXCHANGE_INITIALS + ' - ' + CM.CONTRACT_MARKET_NAME AS CONTRACT_MARKET,
AOD.FUTURES_EXPIRATION_DATE,
AOD.REPORT_DATE,
AOD.OPTION_CLASS_CODE,
AOD.EXPIRATION_ID_DATE,
AOD.PUT_CALL_INDICATOR,
CAST(AOD.STRIKE_PRICE AS decimal(18, 6)) AS STRIKE_PRICE,
SUM(AOD.LONG) AS LONG,
SUM(AOD.SHORT) AS SHORT
FROM ACCOUNT_OPTION_DAY AS AOD WITH (NOLOCK)
INNER JOIN CONTRACT_MARKET AS CM WITH (NOLOCK)
ON AOD.CONTRACT_MARKET_CODE = CM.CONTRACT_MARKET_CODE
GROUP BY
AOD.REPORTING_FIRM_ID,
AOD.CONTRACT_MARKET_CODE,
AOD.FUTURES_EXPIRATION_DATE,
AOD.REPORT_DATE,
AOD.OPTION_CLASS_CODE,
AOD.EXPIRATION_ID_DATE,
AOD.PUT_CALL_INDICATOR,
AOD.STRIKE_PRICE,
CM.CONTRACT_MARKET_NAME,
CM.EXCHANGE_INITIALS
)
SELECT *
FROM (
SELECT
LT_DATA.REPORTING_FIRM_ID,
LT_DATA.CONTRACT_MARKET_CODE,
LT_DATA.CONTRACT_MARKET,
LT_DATA.FUTURES_EXPIRATION_DATE,
LT_DATA.OPTION_CLASS_CODE,
LT_DATA.EXPIRATION_ID_DATE AS 'OPTIONS_EXPIRATION_DATE',
LT_DATA.PUT_CALL_INDICATOR,
LT_DATA.STRIKE_PRICE,
LT_DATA.REPORT_DATE,
COALESCE(EX_DATA.LONG, 0) AS 'L_Exch',
COALESCE(OM_DATA.LONG, 0) AS L_CB,
COALESCE(LT_DATA.LONG, 0) AS L_LargeTrader,
COALESCE(EX_DATA.LONG, 0) + COALESCE(OM_DATA.LONG, 0) - COALESCE(LT_DATA.LONG, 0) AS L_Difference,
COALESCE(EX_DATA.SHORT, 0) AS 'S_Exch',
COALESCE(OM_DATA.SHORT, 0) AS S_CB,
COALESCE(LT_DATA.SHORT, 0) AS S_LargeTrader,
COALESCE(EX_DATA.SHORT, 0) + COALESCE(OM_DATA.SHORT, 0) - COALESCE(LT_DATA.SHORT, 0) AS S_Difference,
OptCrDt.LAST_PROCESSING_DATE,
REPORTING_FIRM_RESP_PER.SYSUSER_ID,
CM.CURRENT_RPT_LEVEL
FROM CTE_LT_DATA AS LT_DATA
INNER JOIN OPTIONS_CRITICAL_DATE AS OptCrDt WITH (NOLOCK)
ON LT_DATA.CONTRACT_MARKET_CODE = OptCrDt.CONTRACT_MARKET_CODE
AND LT_DATA.EXPIRATION_ID_DATE = OptCrDt.EXPIRATION_ID_DATE
AND LT_DATA.OPTION_CLASS_CODE = OptCrDt.OPTION_CLASS_CODE
AND LT_DATA.FUTURES_EXPIRATION_DATE = OptCrDt.FUTURES_EXPIRATION_DATE
INNER JOIN REPORTING_FIRM_RESP_PER WITH (NOLOCK)
ON LT_DATA.REPORTING_FIRM_ID = REPORTING_FIRM_RESP_PER.REPORTING_FIRM_ID
INNER JOIN CONTRACT_MARKET AS CM WITH (NOLOCK)
ON CM.CONTRACT_MARKET_CODE = LT_DATA.CONTRACT_MARKET_CODE
LEFT JOIN CTE_OM_DATA AS OM_DATA
ON OM_DATA.CONTRACT_MARKET_CODE = LT_DATA.CONTRACT_MARKET_CODE
AND OM_DATA.FUTURES_EXPIRATION_DATE = LT_DATA.FUTURES_EXPIRATION_DATE
AND OM_DATA.REPORT_DATE = LT_DATA.REPORT_DATE
AND OM_DATA.STRIKE_PRICE = LT_DATA.STRIKE_PRICE
AND OM_DATA.PUT_CALL_INDICATOR = LT_DATA.PUT_CALL_INDICATOR
AND OM_DATA.EXPIRATION_ID_DATE = LT_DATA.EXPIRATION_ID_DATE
AND OM_DATA.OPTION_CLASS_CODE = LT_DATA.OPTION_CLASS_CODE
AND OM_DATA.OMNI_RF = LT_DATA.REPORTING_FIRM_ID
LEFT JOIN CTE_EX_DATA AS EX_DATA
ON EX_DATA.CONTRACT_MARKET_CODE = LT_DATA.CONTRACT_MARKET_CODE
AND EX_DATA.FUTURES_EXPIRATION_DATE = LT_DATA.FUTURES_EXPIRATION_DATE
AND EX_DATA.REPORT_DATE = LT_DATA.REPORT_DATE
AND EX_DATA.STRIKE_PRICE = LT_DATA.STRIKE_PRICE
AND EX_DATA.PUT_CALL_INDICATOR = LT_DATA.PUT_CALL_INDICATOR
AND EX_DATA.EXPIRATION_ID_DATE = LT_DATA.EXPIRATION_ID_DATE
AND EX_DATA.OPTION_CLASS_CODE = LT_DATA.OPTION_CLASS_CODE
AND EX_DATA.REPORTING_FIRM_ID = LT_DATA.REPORTING_FIRM_ID
) AS Opt_00_01
WHERE
(L_Difference < CURRENT_RPT_LEVEL * -1 OR S_Difference < CURRENT_RPT_LEVEL * -1)
AND LAST_PROCESSING_DATE > REPORT_DATE
AND REPORT_DATE BETWEEN '2023-01-01' AND '2023-04-30'
AND SYSUSER_ID LIKE '%blindsey%';