TL;DR, I'll post back later with more info including table, view, and index definitions; before/after results; and query plans to help to answer this question.
I'm trying to use a self-join to a view to get values from the next available day. The idea is to be able to calculate the change in values for a given day when values are only reported once at the beginning of the day. So the current day's beginning values = the prior day's ending values. In this case, the application is stock option open interest, but this type of JOIN can apply to other scenarios.
The tricky part of the JOIN is that there are multiple rows for each day, and there are gaps between days for weekends and holidays. The query I'm writing currently SUMs values, then does a non-equi self-join to optionally filter out rows while still returning correct SUMs overall. (That self-join works and is not important to this question, I'm just explaining its existence in the query.) The query's results are then PIVOTed (not shown). Using CTEs with PIVOTs is a whole different beast that I'm trying to avoid. In any case, I think a self-join is the best and easiest solution, if only I can get the logic right.
The view's columns contain raw data, aggregates, and analytic functions, and would be harder to edit to show aggregates of the next available day's data than editing the query. The underlying table has a clustered index on the columns AsOf, Expiration, and Strike, which are 3 of the 4 columns I'm joining on. I don't believe joining using ROW_NUMBER() would work because there are multiple rows with the same date, and multiple other columns being joined on, but I could be wrong. I've tried the LEAD() function in the query's SELECT, but Microsoft's LEAD() function doesn't support ignoring NULLs. Also, having to group by the LEAD()'s column ungroups everything else and throws off the SUMs. So my ideal solution is back to using a self-join. I've tried joining using EXISTS and IN in a CASE statement (current AsOf date +1, +2, etc until a match) but those were extremely slow and they joined not just the next available day's rows, but all of the consecutive dates until they hit a non-consecutive date going from Fri to Mon. Maybe that can work, but I couldn't get it to.
I've now got a JOIN (shown below) that I believe is correct and less slow than testing individual EXISTS or INs in a CASE statement, but it's still too slow. At the bottom, I've got another JOIN using a COALESCE that's much faster, but flawed. It correctly joins once to the next day, but it doesn't work with non-consecutive dates. Any help is much appreciated. My brain is turning to mush :)
I've simplified the query below. The self-join to get rows from the next available day (LEFT JOIN OCCALLS AS ND) returns the correct results, but it takes minutes to run for just a few days, which won't work over longer periods. For reference, the query returns results for all dates (1M+ rows in source table) in 0.00 seconds without the troublesome self-join (which is the same as the working self join above it, alias B, except for the logic on the AsOf date column).
SELECT A.Ticker ,'Call' AS 'Option' ,A.AsOf --Data AsOf, contains one row per experation per strike price for each trading day ,ND.AsOf AS 'NextDay'--Data AsOf Next [available] Day ,B.CallDelta ,SUM(A.[CVol@Del]) AS 'CallVol'--Total Vol. If B.CallDelta range is specified, 1 row returned for total B.CallDelta in range, and 1 row returned for total outside of range ,SUM(A.OIB) AS 'OIB' --OI at BOD ,SUM(ND.OIB) AS 'OIE' --OI at EOD = Next day BOD ,A.[V:DTot] --Total daily call vol FROM OCCALLS AS A LEFT JOIN OCCALLS AS B ON A.Ticker = B.Ticker AND A.AsOf = B.AsOf AND A.Expiration = B.Expiration AND A.Strike = B.Strike -- AND B.CallDelta BETWEEN 0.95 AND 1.00 LEFT JOIN OCCALLS AS ND --Next [available] Day ON A.Ticker = ND.Ticker AND (SELECT MIN(AsOf) FROM OCCALLS WHERE AsOf > A.AsOf) = ND.AsOf AND A.Expiration = ND.Expiration AND A.Strike = ND.Strike WHERE 1=1 AND A.Ticker = 'SPY' -- AND A.AsOf >= '2020-01-01' AND A.AsOf BETWEEN '2022-07-07' AND '2022-07-11' GROUP BY A.Ticker ,A.AsOf ,ND.AsOf ,B.CallDelta ,A.[V:DTot] ,A.[Open] ,A.[High] ,A.[Low] ,A.[Close] ORDER BY A.AsOf , B.CallDelta
Another option is using COALESCE (shown below) or CASE in the JOIN's ON clause. With CASE, I found that it doesn't stop evaluating expressions after the first successful one and so joins all consecutive dates until it hits a gap between Fri and Mon. I got closer with COALESCE in that it only joins once to the next day, but it also only joins when the next date is consecutive. So COALESCE is the next closest I've come to a correct and fast join. I know my logic for it is incorrect, but I'm not sure how to fix it with valid syntax. I'm guessing it'll have to be rewritten back to a more complex CASE. I want to believe COALESCE or CASE can work and could be the fastest solutions, but maybe ROW_NUMBER() is an option too? In my example below, I'd limit it to checking 7 days ahead for a date for which rows exist, which should be more than enough.
LEFT JOIN OCCALLS AS ND --Next [available] Day ON A.Ticker = ND.Ticker AND A.AsOf = COALESCE( DATEADD(d,-1,ND.AsOf) ,DATEADD(d,-2,ND.AsOf) ,DATEADD(d,-3,ND.AsOf) ,DATEADD(d,-4,ND.AsOf) ,DATEADD(d,-5,ND.AsOf) ,DATEADD(d,-6,ND.AsOf) ,DATEADD(d,-7,ND.AsOf)) AND A.Expiration = ND.Expiration AND A.Strike = ND.Strike