FREE AI-Enhanced Online SQL Server Compiler

Untitled*

You must execute the code again to save those values in the database.

-- Create the table
CREATE TABLE DataTable (
    [date] DATE,
    var CHAR(1),
    val DECIMAL(4, 2)
);

-- Insert the data
INSERT INTO DataTable ([date], var, val)
VALUES 
('2022-02-01', 'A', 1.1),
('2022-03-01', 'A', 2.3),
('2022-04-01', 'A', 1.5),
('2022-05-01', 'A', 1.7),
('2022-09-01', 'B', 1.8),
('2022-10-01', 'B', 1.9),
('2022-11-01', 'B', 2.1),
('2022-12-01', 'B', 2.22);
WITH DataWithStats AS (
    SELECT 
        [date],
        var,
        val,
        AVG(val) OVER (PARTITION BY var ORDER BY [date] 
                       ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS exp_avg,
        STDEV(val) OVER (PARTITION BY var ORDER BY [date] 
                          ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS exp_std
    FROM DataTable
)
SELECT 
    [date],
    var,
    exp_avg - 2 * exp_std AS lower,
    val,
    exp_avg + 2 * exp_std AS upper,
    CASE 
        WHEN val < LAG(exp_avg - 2 * exp_std, 1) OVER (PARTITION BY var ORDER BY [date]) OR  
             val > LAG(exp_avg + 2 * exp_std, 1) OVER (PARTITION BY var ORDER BY [date]) 
        THEN 'Warning'
        ELSE 'ok'
    END AS status
FROM DataWithStats;

date	var	lower	val	upper	status
2022-02-01	A	NULL	1.10	NULL	ok
2022-03-01	A	2.9437251522861363E-3	2.30	3.3970562748477136	ok
2022-04-01	A	0.41131281467844438	1.50	2.8553531853215555	ok
2022-05-01	A	0.65000000000000113	1.70	2.6499999999999986	ok
2022-09-01	B	NULL	1.80	NULL	ok
2022-10-01	B	1.7085786437627046	1.90	1.9914213562372955	ok
2022-11-01	B	1.6278279536696216	2.10	2.2388380463303781	Warning
2022-12-01	B	1.6250000000000064	2.22	2.3849999999999936	ok

(8 rows affected)

(8 rows affected)