SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE [dbo].[StockIntervals](
[Id] [uniqueidentifier] NOT NULL,
[Name] [nvarchar](255) NOT NULL,
[IntervalInYears] [int] NOT NULL,
 CONSTRAINT [PK_dbo.StockIntervals] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

ALTER TABLE [dbo].[StockIntervals] ADD  DEFAULT ((0)) FOR [IntervalInYears]


CREATE TABLE [dbo].[StockRequests](
[Id] [uniqueidentifier] NOT NULL,
[Count] [int] NOT NULL,
[DateRequested] [datetime] NOT NULL,
[StartYear] [int] NOT NULL,
[StockIntervalId] [uniqueidentifier] NOT NULL,
[EndYear] [int] NULL,


CONSTRAINT [PK_dbo.StockRequests] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]


ALTER TABLE [dbo].[StockRequests]  WITH CHECK ADD  CONSTRAINT [FK_dbo.StockRequests_dbo.StockIntervals_StockIntervalId] FOREIGN KEY([StockIntervalId])
REFERENCES [dbo].[StockIntervals] ([Id])


ALTER TABLE [dbo].[StockRequests] CHECK CONSTRAINT [FK_dbo.StockRequests_dbo.StockIntervals_StockIntervalId]
INSERT INTO [dbo].[StockIntervals]
       ([Id]
       ,[Name]
       ,[IntervalInYears])
 VALUES
       ('738A431E-D517-4C17-9ECA-A1A0942E236B', 'Non-recurring one time', 0),
       ('CCB746A7-F644-4C7E-ADBE-AE14DE01B19E', 'Annual', 1),
       ('80C6CAE6-5287-41E6-A5FE-AAA53035EC19', 'Every 2 years', 2),
       ('B34EE256-C40B-4F03-8232-14B681186C7A', 'Every 3 years', 3)

INSERT INTO [dbo].[StockRequests]
       ([Id]
       ,[Count]
       ,[DateRequested]
       ,[StartYear]
       ,[StockIntervalId]
       ,[EndYear])
 VALUES
       ('4a5ae94e-0a85-4195-8e7e-8cc556307b30'
       ,15
       ,'2022-01-11 15:16:41.567'
       ,2021
       ,'738A431E-D517-4C17-9ECA-A1A0942E236B'
       ,null),
       ('f0d83b68-0da1-4824-9eeb-2e52ff369db5'
       ,60
       ,'2022-01-11 15:16:41.567'
       ,2020
       ,'CCB746A7-F644-4C7E-ADBE-AE14DE01B19E'
       ,null),
       ('a49b4b9e-80d6-4fca-ad78-6c8996616c97'
       ,1000
       ,'2022-01-11 15:16:41.567'
       ,2019
       ,'80C6CAE6-5287-41E6-A5FE-AAA53035EC19'
       ,null),
       ('cc21a265-f8df-4d2d-9eae-5f6f97ef9909'
       ,50
       ,'2022-01-11 15:16:41.567'
       ,2018
       ,'B34EE256-C40B-4F03-8232-14B681186C7A'
       ,null)
8 rows affected

DECLARE @stockYear int = 2021

SELECT * FROM 
dbo.StockRequests
INNER JOIN dbo.StockIntervals on StockIntervalId = StockIntervals.Id
WHERE 
    StockRequests.[StartYear] <= @stockYear
    AND 
    (
        StockRequests.[StartYear] = @stockYear OR 
        (
            0 <> StockIntervals.[IntervalInYears]  AND 
            0 = (@stockYear - StockRequests.[StartYear]) % StockIntervals.[IntervalInYears] 
        )
    )


Id
Count
DateRequested
StartYear
StockIntervalId
EndYear
Id
Name
IntervalInYears


f0d83b68-0da1-4824-9eeb-2e52ff369db5
60
2022-01-11 15:16:41.567
2020
ccb746a7-f644-4c7e-adbe-ae14de01b19e
null
ccb746a7-f644-4c7e-adbe-ae14de01b19e
Annual
1

cc21a265-f8df-4d2d-9eae-5f6f97ef9909
50
2022-01-11 15:16:41.567
2018
b34ee256-c40b-4f03-8232-14b681186c7a
null
b34ee256-c40b-4f03-8232-14b681186c7a
Every 3 years
3

a49b4b9e-80d6-4fca-ad78-6c8996616c97
1000
2022-01-11 15:16:41.567
2019
80c6cae6-5287-41e6-a5fe-aaa53035ec19
null
80c6cae6-5287-41e6-a5fe-aaa53035ec19
Every 2 years
2

4a5ae94e-0a85-4195-8e7e-8cc556307b30
15
2022-01-11 15:16:41.567
2021
738a431e-d517-4c17-9eca-a1a0942e236b
null
738a431e-d517-4c17-9eca-a1a0942e236b
Non-recurring one time
0


DECLARE @stockYear int = 2021

SELECT * FROM 
dbo.StockRequests
INNER JOIN dbo.StockIntervals on StockIntervalId = StockIntervals.Id
WHERE 
    StockRequests.[StartYear] <= @stockYear
    AND 
    (
        StockRequests.[StartYear] = @stockYear OR 
        (
            StockIntervals.[IntervalInYears] <> 0 AND 
            0 = (@stockYear - StockRequests.[StartYear]) % StockIntervals.[IntervalInYears] 
        )
    )


Id
Count
DateRequested
StartYear
StockIntervalId
EndYear
Id
Name
IntervalInYears


f0d83b68-0da1-4824-9eeb-2e52ff369db5
60
2022-01-11 15:16:41.567
2020
ccb746a7-f644-4c7e-adbe-ae14de01b19e
null
ccb746a7-f644-4c7e-adbe-ae14de01b19e
Annual
1

cc21a265-f8df-4d2d-9eae-5f6f97ef9909
50
2022-01-11 15:16:41.567
2018
b34ee256-c40b-4f03-8232-14b681186c7a
null
b34ee256-c40b-4f03-8232-14b681186c7a
Every 3 years
3

a49b4b9e-80d6-4fca-ad78-6c8996616c97
1000
2022-01-11 15:16:41.567
2019
80c6cae6-5287-41e6-a5fe-aaa53035ec19
null
80c6cae6-5287-41e6-a5fe-aaa53035ec19
Every 2 years
2

4a5ae94e-0a85-4195-8e7e-8cc556307b30
15
2022-01-11 15:16:41.567
2021
738a431e-d517-4c17-9eca-a1a0942e236b
null
738a431e-d517-4c17-9eca-a1a0942e236b
Non-recurring one time
0


DECLARE @stockYear int = 2021

SELECT * FROM 
dbo.StockRequests
INNER JOIN dbo.StockIntervals on StockIntervalId = StockIntervals.Id
WHERE 
    StockRequests.[StartYear] <= @stockYear
    AND 
    (
        StockRequests.[StartYear] = @stockYear OR
        (
            0 = (@stockYear - StockRequests.[StartYear]) % StockIntervals.[IntervalInYears] AND
            0 <> StockIntervals.[IntervalInYears]
        )
    )


Id
Count
DateRequested
StartYear
StockIntervalId
EndYear
Id
Name
IntervalInYears


f0d83b68-0da1-4824-9eeb-2e52ff369db5
60
2022-01-11 15:16:41.567
2020
ccb746a7-f644-4c7e-adbe-ae14de01b19e
null
ccb746a7-f644-4c7e-adbe-ae14de01b19e
Annual
1

cc21a265-f8df-4d2d-9eae-5f6f97ef9909
50
2022-01-11 15:16:41.567
2018
b34ee256-c40b-4f03-8232-14b681186c7a
null
b34ee256-c40b-4f03-8232-14b681186c7a
Every 3 years
3

a49b4b9e-80d6-4fca-ad78-6c8996616c97
1000
2022-01-11 15:16:41.567
2019
80c6cae6-5287-41e6-a5fe-aaa53035ec19
null
80c6cae6-5287-41e6-a5fe-aaa53035ec19
Every 2 years
2

4a5ae94e-0a85-4195-8e7e-8cc556307b30
15
2022-01-11 15:16:41.567
2021
738a431e-d517-4c17-9eca-a1a0942e236b
null
738a431e-d517-4c17-9eca-a1a0942e236b
Non-recurring one time
0


DECLARE @stockYear int = 2021

SELECT * FROM 
dbo.StockRequests
INNER JOIN dbo.StockIntervals on StockIntervalId = StockIntervals.Id
WHERE 
    StockRequests.[StartYear] <= @stockYear
    AND 
    (
        (
            0 <> StockIntervals.[IntervalInYears] AND
            0 = (@stockYear - StockRequests.[StartYear]) % StockIntervals.[IntervalInYears]
        )
        OR StockRequests.[StartYear] = @stockYear
    )
Msg 8134 Level 16 State 1 Line 3
Divide by zero error encountered.

DECLARE @stockYear int = 2021

SELECT * FROM 
dbo.StockRequests
INNER JOIN dbo.StockIntervals on StockIntervalId = StockIntervals.Id
WHERE 
    StockRequests.[StartYear] <= @stockYear
    AND 
    (
        (
            0 = (@stockYear - StockRequests.[StartYear]) % StockIntervals.[IntervalInYears] AND
            0 <> StockIntervals.[IntervalInYears]
        )
        OR StockRequests.[StartYear] = @stockYear
    )
Msg 8134 Level 16 State 1 Line 3
Divide by zero error encountered.

Id	Count	DateRequested	StartYear	StockIntervalId	EndYear	Id	Name	IntervalInYears
f0d83b68-0da1-4824-9eeb-2e52ff369db5	60	2022-01-11 15:16:41.567	2020	ccb746a7-f644-4c7e-adbe-ae14de01b19e	null	ccb746a7-f644-4c7e-adbe-ae14de01b19e	Annual	1
cc21a265-f8df-4d2d-9eae-5f6f97ef9909	50	2022-01-11 15:16:41.567	2018	b34ee256-c40b-4f03-8232-14b681186c7a	null	b34ee256-c40b-4f03-8232-14b681186c7a	Every 3 years	3
a49b4b9e-80d6-4fca-ad78-6c8996616c97	1000	2022-01-11 15:16:41.567	2019	80c6cae6-5287-41e6-a5fe-aaa53035ec19	null	80c6cae6-5287-41e6-a5fe-aaa53035ec19	Every 2 years	2
4a5ae94e-0a85-4195-8e7e-8cc556307b30	15	2022-01-11 15:16:41.567	2021	738a431e-d517-4c17-9eca-a1a0942e236b	null	738a431e-d517-4c17-9eca-a1a0942e236b	Non-recurring one time	0