By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
SET DATEFORMAT YDM;
CREATE TABLE Issues
(IssueID int,IssueName varchar(10),created datetime, closed datetime);
INSERT INTO Issues
VALUES
(1,'a','2012-01-01 00:00:00', '2012-05-01 00:00:00'),
(2,'b','2012-03-01 00:00:00', '2012-06-01 00:00:00');
2 rows affected
WITH CTE_DatesTable
AS
(
SELECT CAST('20120101' as datetime) AS [date]
UNION ALL
SELECT DATEADD(dd, 1, [date])
FROM CTE_DatesTable
WHERE DATEADD(dd, 1, [date]) <= '20120106'
)
SELECT [date] ,AVG( DATEDIFF(DAY,i.created,d.[date]))
FROM CTE_DatesTable d
CROSS JOIN Issues i
WHERE d.date BETWEEN i.created AND i.closed
GROUP BY [date]
ORDER BY [date]
OPTION (MAXRECURSION 0)
| date | (No column name) |
|---|---|
| 01/01/2012 00:00:00 | 0 |
| 02/01/2012 00:00:00 | 1 |
| 03/01/2012 00:00:00 | 1 |
| 04/01/2012 00:00:00 | 2 |
| 05/01/2012 00:00:00 | 3 |
| 06/01/2012 00:00:00 | 3 |