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.