SQLize Online / PHPize Online  /  SQLtest Online

A A A
Share      Blog   Popular
Copy Format Clear
CREATE TABLE t ( id INT NOT NULL , name VARCHAR(20) , dt datetime ) ; INSERT INTO t VALUES (1, 'A', GetUtcDate()) ; INSERT INTO t VALUES (2, 'A', DATEADD(day, 1, GetUTCDate())) ; INSERT INTO t VALUES (3, 'A', DATEADD(day, 2, GetUTCDate())); INSERT INTO t VALUES (4, 'B', GetUTCDate()) ; INSERT INTO t VALUES (5, 'B', DATEADD(day, 3, GetUTCDate())) ; ;WITH CTE AS ( SELECT 2021 as Y, 12 as M UNION ALL SELECT 2022 as Y, 8 as M UNION ALL SELECT 2022 as Y, 9 as M UNION ALL SELECT 2022 as Y, 10 as M UNION ALL SELECT 2022 as Y, 11 as M ), DocT as ( SELECT 'A' as DocType UNION ALL SELECT 'B' ), Crossed as ( select * from CTE CROSS APPLY DocT ), Log as ( select YEAR(dt) as Y, MONTH(dt) as M, ABS(DATEDIFF(day, dt, dt)) as DaysBetween, name from t group by name, YEAR(dt), MONTH(dt) ), R as ( select AVG(DaysBetween) as DaysBetween, name from Log group by name, Y, M ) select * from R
Stuck with a problem? Got Error? Ask ChatGPT!
Copy Clear