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,
DATEDIFF(day, dt, dt) as DaysBetween,
name
from t
),
R as (
select AVG(DaysBetween) as DaysBetween, name
from Log
group by name, Y, M )
select * from Log
Telegram: DB developers channel