SQL Server 2017

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]) <= '20120107'
)
SELECT [date] , i.IssueId,  DATEDIFF(DAY,i.created,d.[date]) AS Days_Open
FROM CTE_DatesTable d
LEFT JOIN Issues i
  ON d.date BETWEEN i.created AND i.closed
ORDER BY [date], IssueId
OPTION (MAXRECURSION 0);


date
IssueId
Days_Open


2012-01-01 00:00:00.000
1
0

2012-01-02 00:00:00.000
1
1

2012-01-03 00:00:00.000
1
2

2012-01-03 00:00:00.000
2
0

2012-01-04 00:00:00.000
1
3

2012-01-04 00:00:00.000
2
1

2012-01-05 00:00:00.000
1
4

2012-01-05 00:00:00.000
2
2

2012-01-06 00:00:00.000
2
3

2012-01-07 00:00:00.000
null
null


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]) <= '20120107'
)
SELECT [date] , COALESCE(AVG( DATEDIFF(DAY,i.created,d.[date])),0)
FROM CTE_DatesTable d
LEFT JOIN Issues i
  ON d.date BETWEEN i.created AND i.closed
GROUP BY [date]
ORDER BY [date]
OPTION (MAXRECURSION 0);


date
(No column name)


2012-01-01 00:00:00.000
0

2012-01-02 00:00:00.000
1

2012-01-03 00:00:00.000
1

2012-01-04 00:00:00.000
2

2012-01-05 00:00:00.000
3

2012-01-06 00:00:00.000
3

2012-01-07 00:00:00.000
0


Warning: Null value is eliminated by an aggregate or other SET operation.

date	IssueId	Days_Open
2012-01-01 00:00:00.000	1	0
2012-01-02 00:00:00.000	1	1
2012-01-03 00:00:00.000	1	2
2012-01-03 00:00:00.000	2	0
2012-01-04 00:00:00.000	1	3
2012-01-04 00:00:00.000	2	1
2012-01-05 00:00:00.000	1	4
2012-01-05 00:00:00.000	2	2
2012-01-06 00:00:00.000	2	3
2012-01-07 00:00:00.000	null	null