Complex day-to-day difference of historical data in SQL server

Question

Setting:

I have a table (let's call it "HistoricalData") with 10 attribute columns, a date (in string format) and a value. The table has a unique clustered index on (date, idcol1, idcol2,..idcol10), a non-unique non-clustered index on date and non-unique non-clustered indexes on some of the idcolN.

Every day a process appends approx 100k new rows with the current date. The table has several million rows. All good on this side though.

My goal:

I need a view that has all original columns, and in addition to that: (1) for each row the previous available date across the whole table, (2) the value on that date and (3) the date-to-date difference in values.

• The previous date is for each row the latest date before the date on current row for the whole table (not for this specific combination of the identifier columns idcol1, .. , idcol10)
• The previous value for each row is the value corresponding to the same id columns idcol1, .. , idcol10 and the previous date. If no value exists, this value is zero.
• The date-to-date difference for each row is the current value minus the previous value but whenever a specific combination of idcol1, .. idcol10 disappears, it needs to display the corresponding negative change in value (say, I have the row idcol1="I1", .. , idcol10="I10", date="2023-11-01", value = 10 but no idcol1="I1", .. , idcol10="I10" for 2023-11-02, then I want to add a row for 2023-11-02 that has idcol1="I1", .. , idcol10="I10" and value difference = -10)

My poor solution

This is the monster I came up with.

1- First create a view linking dates together:

CREATE VIEW DatesView AS
WITH RankedDates AS (
SELECT 
current_date, ROW_NUMBER() OVER (ORDER BY date) AS rn
FROM (
   SELECT DISTINCT current_date
   FROM HistoricalData) AS DistinctDates
)
SELECT 
a.current_date AS date, 
b.current_date AS previous_date, 
c.current_date AS next_date
FROM RankedDates a 
LEFT JOIN RankedDates b ON a.rn = b.rn + 1 
LEFT JOIN RankedDates c ON a.rn = c.rn - 1

2- Create a view doing the self-joins:

CREATE VIEW HistoricalDtDDiff AS
WITH HistoricalWithDates AS (
SELECT t.*,
d.previous_date AS previous_date,
d.next_date AS next_date
FROM HistoricalData AS t
LEFT JOIN DatesView AS d
ON t.date = d.date
)
SELECT
coalesce(t1.idcol1, t2.idcol1) as idcol1,
/* same for other idcols */
coalesce(t1.idcol10, t2.idcol10) AS idcol10,
coalesce(t1.date, t2.next_date) AS current_date,
coalesce(t1.value,0) AS current_value,
coalesce(t2.value,0) AS previous_value,
coalesce(t1.value,0) - coalesce(t2.value,0) AS value_DtD_diff
FROM HistoricalWithDates AS t1
FULL OUTER JOIN HistoricalWithDates AS t2
ON 
t1.previous_date = t2.date AND
t1.idcol1 = t2.idcol1 AND
/* ..you get the idea..*/
t1.idcol10 = t2.idcol10

3- Query the view HistoricalDtDDiff when needed with the needed filters:

SELECT *
FROM HistoricalDtDDiff
WHERE
idcol3 = "ID3" AND
idcol5 = "ID5" AND
idcol6 = "ID6"

Problems & some troubleshooting:

• The query is frustratingly slow. I tried a filter that extracts only 8 rows from the original table and it's taking over 2 minutes to give me the result of the view at point 3 above. (for reference, I can self join the whole table in R in a few minutes on my local machine)
• From the "estimated execution plan" in SQLQuery it looks like the filtering is being done at the end. This is madness since when filtering on id columns the result is the same as filtering on the alias "HistoricalWithDates" at the beginning. This would mean doing an outer join between two 8-row tables. Is there any way to make the planner plan better?
• I also tried to hardcode the same filter in the definition of "HistoricalWithDates". As follows: WITH HistoricalWithDates AS ( SELECT t.*, d.previous_date AS previous_date, d.next_date AS next_date FROM HistoricalData AS t LEFT JOIN DatesView AS d ON t.date = d.date WHERE idcol3 = "ID3" AND idcol5 = "ID5" AND idcol6 = "ID6" ). I expected then the view to be actually a join between two very small tables, but the performance didn't improve.

Questions:

• What did I do wrong to deserve this? I am open to any suggestion or criticism, even rebuilding the original table or choosing a whole different system is an option.
• What is the best practice to store historical data in a relational database? How should I define indexes for my use case? Would changing the table schema improve performance?
• Is there an alternative way of getting the same result without a self-join? Notice that I can't use LAG() because it would fetch the previous date for the specific idcol1, .., idcol10
• How can I speed-up the query in point 3 above? QUERY PLAN IS NOT AVAILABLE since the query doesn't even finish trying to return a few rows. Also I'd rather not post some details like the filters I am using and the names of the columns.

EDIT: To explain why I can't use LAG()

Say I have the following data:

date        idcol1   idcol2  idcol3   value
2023-11-02  A1       B1      C1       5
2023-11-10  A1       B1      C1       20
2023-11-09  A1       B2      C1       1  
2023-11-10  A1       B2      C1       2

Now in an attempt to find the day-to-day change for all categories I use the following to get the previous value:

LAG(value, 1,0) OVER (PARTITION BY idcol1, idcol2, idcol3 ORDER BY date DESC) AS previous_value

and for previous date:

LAG(date, 1) OVER (PARTITION BY idcol1, idcol2, idcol3 ORDER BY date DESC) AS previous_date

This would give the following for 2023-11-10:

date        idcol1   idcol2  idcol3   value   previous_value previous_date dtd_diff
2023-11-10  A1       B1      C1       20      5              2023-11-02    15
2023-11-10  A1       B2      C1       2       1              2023-11-09    1

However the first row above should actually refer to 2023-11-09 as the previous date and have 0 as previous value (say, data wasn't appended because that identifier was missing on 2023-11-09, making the value 0).

This is because when looking at dtd_difference for idcol1="A1" and any other idcol2, idcol3, in order to meaningfully sum the dtd_diff for 20223-11-10 I would need something like this:

date        idcol1   idcol2  idcol3   value   previous_value previous_date dtd_diff
2023-11-10  A1       B1      C1       20      0              NULL          20
2023-11-10  A1       B2      C1       2       1              2023-11-09    1