Identifier on yearly base

Question

I want a monotonic increasing identifier for each year, starting with 1 on each year. There could be rows with NULL in BusinessKey column. I'll call the function in an INSERT trigger:

DROP TABLE IF EXISTS [dbo].[Test];

CREATE TABLE [dbo].[Test] (
  [Id] [int] IDENTITY(1, 1) PRIMARY KEY CLUSTERED,
  [Year] [int] NOT NULL,
  [BusinessKey] [int]
)
GO

INSERT INTO [dbo].[Test]([Year], [BusinessKey]) VALUES
(2024, 1),
(2024, 2),
(2024, 3),
(2024, NULL),
(2024, NULL),
(2025, 1),
(2025, 2),
(2025, NULL);

-- Make a hole (gap) in the id column
DELETE FROM [dbo].[Test] WHERE [Id] = 2
GO

SELECT
  *,
  ROW_NUMBER() OVER (PARTITION BY [Year] ORDER BY [Id]) AS BuisnessKeyCalculated
FROM [dbo].[Test]

Which gives:

Id	Year	BusinessKey	BuisnessKeyCalculated	Expected
1	2024	1	1	1
3	2024	3	2	3
4	2024	NULL	3	4
5	2024	NULL	4	5
6	2025	1	1	1
7	2025	2	2	2
8	2025	NULL	3	3

Does anybody know how to solve that with window functions?

Answer 1

The following seems to produce the expected result:

select *, coalesce(
  businesskey,
  count(case when businesskey is null then 1 end) over (partition by year order by businesskey, id) + 
  max(businesskey) over (partition by year)
) as expected
from t

The count() over () trick assigns increasing row number to null values. The max() over () adjusts those row numbers.