I have a SQL Server table that looks like this:

Fix Date    Risk Level  Issue Count
------------------------------------
2/28/25        High        4
3/31/25      Critical      1
3/31/25        High        4
4/30/25        High        2
5/31/25        High        3
6/30/25      Critical      1
6/30/25        High        4
7/31/25        High        2
8/31/25        High        1
9/30/25        High        2
10/31/25     Critical      1
10/31/25       High        1
11/30/25       High        1
12/31/25       High        1
3/31/26        High        1

I am creating a burndown chart that shows the active issue counts by Fix date and risk level. Using the table above, this means on 2/28/25 the projected count is the total-i.e, 26 High and 3 Critical; on 3/31 the projected count remaining would be 22 High and 3 Critical; on 4/30/25 the projected count remaining would be 18 High and 2 Critical, etc.
This is the SQL query

Select fix_date, risk_level, sum(issue_count) over (partition by risk_level 
order by fix_date rows between current row and unbounded following)

The result of the SQL query should look like this, so I could then plot it.:

Fix Date    Risk Level  Issue Count
-----------------------------------
2/28/25       High         26
2/28/25     Critical        3
3/31/25       High         22
3/31/25     Critical        3
4/30/25       High         18
4/30/25     Critical        2
5/31/25       High         16
5/31/25     Critical        2
6/30/25       High         13
6/30/25     Critical        2
7/31/25       High          9
7/31/25     Critical        1
8/30/25       High          7
8/30/25     Critical        1
9/30/25       High          6
9/30/25     Critical        1
10/31/25      High          4
10/31/25    Critical        1
11/30/25      High          3
12/31/25      High          2
1/31/26       High          1
2/28/26       High          1
3/31/26       High          1

I am using the rows between current row and unbounded following clause, but the results are only showing a value of Critical for a month if there is one in the table for it for that month. I need two records for each month, one for High and one for Critical, and if there isn't a value for Critical in a month there still should be a record for it for that month and the value should be what it was in the prior month there was a value.

Do I need to create some kind of date table and join to this table?

I have a SQL Server table that looks like this:

Fix Date    Risk Level  Issue Count
------------------------------------
2/28/25        High        4
3/31/25      Critical      1
3/31/25        High        4
4/30/25        High        2
5/31/25        High        3
6/30/25      Critical      1
6/30/25        High        4
7/31/25        High        2
8/31/25        High        1
9/30/25        High        2
10/31/25     Critical      1
10/31/25       High        1
11/30/25       High        1
12/31/25       High        1
3/31/26        High        1

I am creating a burndown chart that shows the active issue counts by Fix date and risk level. Using the table above, this means on 2/28/25 the projected count is the total-i.e, 26 High and 3 Critical; on 3/31 the projected count remaining would be 22 High and 3 Critical; on 4/30/25 the projected count remaining would be 18 High and 2 Critical, etc.
This is the SQL query

Select fix_date, risk_level, sum(issue_count) over (partition by risk_level 
order by fix_date rows between current row and unbounded following)

The result of the SQL query should look like this, so I could then plot it.:

Fix Date    Risk Level  Issue Count
-----------------------------------
2/28/25       High         26
2/28/25     Critical        3
3/31/25       High         22
3/31/25     Critical        3
4/30/25       High         18
4/30/25     Critical        2
5/31/25       High         16
5/31/25     Critical        2
6/30/25       High         13
6/30/25     Critical        2
7/31/25       High          9
7/31/25     Critical        1
8/30/25       High          7
8/30/25     Critical        1
9/30/25       High          6
9/30/25     Critical        1
10/31/25      High          4
10/31/25    Critical        1
11/30/25      High          3
12/31/25      High          2
1/31/26       High          1
2/28/26       High          1
3/31/26       High          1

I am using the rows between current row and unbounded following clause, but the results are only showing a value of Critical for a month if there is one in the table for it for that month. I need two records for each month, one for High and one for Critical, and if there isn't a value for Critical in a month there still should be a record for it for that month and the value should be what it was in the prior month there was a value.

Do I need to create some kind of date table and join to this table?

• sql-server
• burndowncharts

• Show us your full attempt not just part of it. The snippet of SQL you showed us is part of a windowed function, however, without the rest of it we don't why it's not doing what you expect. – Thom A Commented Mar 15 at 10:09
• A reasonable chart should be able to handle missing points, but yeah, a calendar table join and some lag/leads should probably solve your issue – siggemannen Commented Mar 15 at 11:03
• SQL statement added. I believe I may need to write new SQL that uses a calendar table and join it to this table to get this to work – 52414246 Commented Mar 15 at 14:16

1 Answer 1

Yes, you will need either a calendar table or a date range generator to fill in the missing rows. For SQL Server 2022 and later, the GENERATE_SERIES(), DATE_ADD(), and EOMONTH() functions can be used to generate a range of EOM dates, after the start, end, and difference are identified in a subquery using MIN() and MAX().

If you cross join the dates with DISTINCT risk_level values, you can then use the CROSS APPLY (SELECT TOP 1 ...) pattern to look up the appropriate value from your intermediate results.

With Dates As (
    Select eomonth(dateadd(month, S.value, MM.fix_date_min)) As fix_date
    From (
        Select
            min(R.fix_date) As fix_date_min,
            datediff(month, min(R.fix_date), max(R.fix_date)) As fix_date_diff
        From ResolvedIssues R
    ) MM
    Cross Apply generate_series(0, MM.fix_date_diff) S
),
RiskLevels As (
    Select Distinct R.risk_level
    From ResolvedIssues R
),
CummulativeResolvedIssues As (
    Select
        fix_date,
        risk_level,
        sum(issue_count)
            over (partition by risk_level 
                  order by fix_date
                  rows between current row and unbounded following)
            As issue_count
    From ResolvedIssues
)
Select
    D.fix_Date,
    R.risk_level,
    C1.issue_count
From Dates D
Cross Join RiskLevels R
Cross Apply (
    Select Top 1 C.*
    From CummulativeResolvedIssues C
    Where C.risk_level = R.risk_level
    And C.fix_Date >= D.fix_Date
    Order By C.fix_Date
) C1
Order By D.fix_date, R.risk_level

If using a SQL Server version prior to 2022, a recursive CTE can instead be be used to generate the date series.

With Dates As (
    Select
        eomonth(min(R.fix_date)) As fix_date,
        eomonth(max(R.fix_date)) As last_fix_date
    From ResolvedIssues R
    Union All
    Select
        eomonth(dateadd(day, 1, D.fix_date)) As fix_date,
        last_fix_date
    From Dates D
    Where D.fix_date < D.last_fix_date
),
...

Results (same for both versions):

If you wish to also generate 0 rows for the "Critical" cases at the end of the date sequence, you can change the Cross Apply or Outer Apply and change the C1.issue_count select item to ISNULL(C1.issue_count, 0) As issue_count.

This will add the following rows to the results.

See this db<>fiddle for a demo.