sql server - T-SQL: Self-Referencing calculated field

Question

I'm trying to calculate the daily total number of Day Trades based on an existing table that provides the cumulative number of Day Trades over a rolling five-day window (business days only). The data set is very simple, however, I can't seem to compute this without having to resort to endless chain of sub-queries. The challenging part is that at the end of each trading day, the total number of Day Trades from five days ago now expires and is removed from the rolling total.

Here is my data set (table):

And here is the end-result I need it to look like:

Note that the first four rows at the bottom (oldest dates) highlighted in yellow were super-easy to calculate because 11/09 was the starting point, with no prior Day Trades and therefore no Day Trades would be removed (expire) until four business days later (on 11/13).

From there, I don't know what to do. It seems like anything I try would just be a calculated field that needs to refers to itself (its own results) four rows in the past. At least, part of the calculation would. The calculation is essentially just = [Cumulative "Day Trades" as of today - (Cumulative "Day Trades" as of previous day - "Daily Total" from four days prior)]. Again, the data set only contains business days to begin with. Would a Recursive CTE work for this?

Remark 1: I don't need help with the rolling, cumulative "Day Trades" field. That is already auto-populated for me. I just need help with an adhoc calculation for the "Daily Total" (new day trades for each day.

Remark 2: A "Day Trade" is simply just BUYING and SELLING the same stock on the same day. Its a one-to-one matching of BUY & SELL orders for the same stock.

Remark 3: The solution needs to be compatible with IBM Netezza rules, preferably.

question from:https://stackoverflow.com/questions/65517110/t-sql-self-referencing-calculated-field

Answer 1

This might not be the most elegant solution, but it should give the correct answers.

DECLARE @currentDate date 
DECLARE @expiredTrades int 
DECLARE @yesterday int
DECLARE @salesDataTable TABLE
(
    dates date,
    day_trades int,
    daily_total int NULL
)

INSERT INTO @salesDataTable (dates, day_tradeS, daily_total) VALUES
('2019-01-01', 10, 10),
('2019-01-02', 27, 17),
('2019-01-03', 28, 1),
('2019-01-04', 28, 0),
('2019-01-05', 24, NULL),
('2019-01-06', 7, NULL),
('2019-01-07', 11, NULL),
('2019-01-08', 11, NULL),
('2019-01-09', 18, NULL),
('2019-01-10', 18, NULL),
('2019-01-11', 56, NULL),
('2019-01-12', 61, NULL),
('2019-01-13', 56, NULL),
('2019-01-14', 68, NULL),
('2019-01-15', 48, NULL),
('2019-01-16', 52, NULL),
('2019-01-17', 54, NULL)

WHILE 1 = (SELECT TOP 1 1 FROM @salesDataTable WHERE daily_total IS NULL)
BEGIN
    SELECT TOP 1 @currentDate = dates
    FROM @salesDataTable
    WHERE daily_total IS NULL
    ORDER BY dates
    
    SELECT 
        @expiredTrades = LAG (daily_total, 4, 0) OVER (ORDER BY dates),
        @yesterday = LAG (day_trades, 1, 0) OVER (ORDER BY dates)
    FROM @salesDataTable
    WHERE dates <= @currentDate

    UPDATE @salesDataTable
    SET daily_total = day_trades - (@yesterday - @expiredTrades)
    WHERE dates = @currentDate
END

SELECT * 
FROM @salesDataTable