SQL Server 2019

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

CREATE TABLE member (
  name     VARCHAR(32),
  birthday DATE
);

DECLARE @today     DATE = CAST(GETDATE() AS DATE)

DECLARE @dob_today DATE = DATEADD(year,  -21, @today)
DECLARE @dob_week  DATE = DATEADD(day,     7, @dob_today)
DECLARE @dob_month DATE = DATEADD(month,   1, @dob_today)



INSERT INTO
  member
          SELECT 'today-9', DATEADD(day, -9, @dob_today)

UNION ALL SELECT 'today-1', DATEADD(day, -1, @dob_today)
UNION ALL SELECT 'today+0', DATEADD(day,  0, @dob_today)
UNION ALL SELECT 'today+1', DATEADD(day,  1, @dob_today)

UNION ALL SELECT 'week-1', DATEADD(day, -1, @dob_week)
UNION ALL SELECT 'week+0', DATEADD(day,  0, @dob_week)
UNION ALL SELECT 'week+1', DATEADD(day,  1, @dob_week)

UNION ALL SELECT 'month-1', DATEADD(day, -1, @dob_month)
UNION ALL SELECT 'month+0', DATEADD(day,  0, @dob_month)
UNION ALL SELECT 'month+1', DATEADD(day,  1, @dob_month)

SELECT * FROM member


name
birthday


today-9
2002-03-22

today-1
2002-03-30

today+0
2002-03-31

today+1
2002-04-01

week-1
2002-04-06

week+0
2002-04-07

week+1
2002-04-08

month-1
2002-04-29

month+0
2002-04-30

month+1
2002-05-01


SELECT
  member.*,
  next.birthday   AS next_birthday,
  CASE WHEN next.birthday  =                   CAST(GETDATE() AS DATE)  THEN 0
       WHEN next.birthday <= DATEADD(DAY,   7, CAST(GETDATE() AS DATE)) THEN 1
       WHEN next.birthday <= DATEADD(MONTH, 1, CAST(GETDATE() AS DATE)) THEN 2
                                                                        ELSE 3
  END
      AS next_birthday_type
FROM
  member
CROSS APPLY
(
  SELECT DATEADD(year, DATEDIFF(year, member.birthday, CAST(GETDATE() AS DATE)), member.birthday)
)
  AS this_year(birthday)
CROSS APPLY
(
  SELECT CASE WHEN this_year.birthday < CAST(GETDATE() AS DATE)
              THEN DATEADD(year, 1, this_year.birthday)
              ELSE                  this_year.birthday
          END
)
  AS next(birthday)


name
birthday
next_birthday
next_birthday_type


today-9
2002-03-22
2024-03-22
3

today-1
2002-03-30
2024-03-30
3

today+0
2002-03-31
2023-03-31
0

today+1
2002-04-01
2023-04-01
1

week-1
2002-04-06
2023-04-06
1

week+0
2002-04-07
2023-04-07
1

week+1
2002-04-08
2023-04-08
2

month-1
2002-04-29
2023-04-29
2

month+0
2002-04-30
2023-04-30
2

month+1
2002-05-01
2023-05-01
3


SELECT
  member.*,
  next.birthday   AS next_birthday,
  CASE WHEN next.birthday  =                   '2021-09-09'  THEN 0
       WHEN next.birthday <= DATEADD(DAY,   7, '2021-09-09') THEN 1
       WHEN next.birthday <= DATEADD(MONTH, 1, '2021-09-09') THEN 2
                                                                        ELSE 3
  END
      AS next_birthday_type
FROM
  member
CROSS APPLY
(
  SELECT DATEADD(year, DATEDIFF(year, member.birthday, '2021-09-09'), member.birthday)
)
  AS this_year(birthday)
CROSS APPLY
(
  SELECT CASE WHEN this_year.birthday < '2021-09-09'
              THEN DATEADD(year, 1, this_year.birthday)
              ELSE                  this_year.birthday
          END
)
  AS next(birthday)


name
birthday
next_birthday
next_birthday_type


today-9
2002-03-22
2022-03-22
3

today-1
2002-03-30
2022-03-30
3

today+0
2002-03-31
2022-03-31
3

today+1
2002-04-01
2022-04-01
3

week-1
2002-04-06
2022-04-06
3

week+0
2002-04-07
2022-04-07
3

week+1
2002-04-08
2022-04-08
3

month-1
2002-04-29
2022-04-29
3

month+0
2002-04-30
2022-04-30
3

month+1
2002-05-01
2022-05-01
3


SELECT
  member.*,
  next.birthday   AS next_birthday,
  CASE WHEN next.birthday  =                   today.date  THEN 0
       WHEN next.birthday <= DATEADD(DAY,   7, today.date) THEN 1
       WHEN next.birthday <= DATEADD(MONTH, 1, today.date) THEN 2
                                                                        ELSE 3
  END
      AS next_birthday_type
FROM
  member
CROSS APPLY
(
  SELECT CAST('2021-09-09' AS DATE)
)
  today(date)
CROSS APPLY
(
  SELECT DATEADD(year, DATEDIFF(year, member.birthday, today.date), member.birthday)
)
  AS this_year(birthday)
CROSS APPLY
(
  SELECT CASE WHEN this_year.birthday < today.date
              THEN DATEADD(year, 1, this_year.birthday)
              ELSE                  this_year.birthday
          END
)
  AS next(birthday)


name
birthday
next_birthday
next_birthday_type


today-9
2002-03-22
2022-03-22
3

today-1
2002-03-30
2022-03-30
3

today+0
2002-03-31
2022-03-31
3

today+1
2002-04-01
2022-04-01
3

week-1
2002-04-06
2022-04-06
3

week+0
2002-04-07
2022-04-07
3

week+1
2002-04-08
2022-04-08
3

month-1
2002-04-29
2022-04-29
3

month+0
2002-04-30
2022-04-30
3

month+1
2002-05-01
2022-05-01
3

name	birthday
today-9	2002-03-22
today-1	2002-03-30
today+0	2002-03-31
today+1	2002-04-01
week-1	2002-04-06
week+0	2002-04-07
week+1	2002-04-08
month-1	2002-04-29
month+0	2002-04-30
month+1	2002-05-01

name	birthday	next_birthday	next_birthday_type
today-9	2002-03-22	2024-03-22	3
today-1	2002-03-30	2024-03-30	3
today+0	2002-03-31	2023-03-31	0
today+1	2002-04-01	2023-04-01	1
week-1	2002-04-06	2023-04-06	1
week+0	2002-04-07	2023-04-07	1
week+1	2002-04-08	2023-04-08	2
month-1	2002-04-29	2023-04-29	2
month+0	2002-04-30	2023-04-30	2
month+1	2002-05-01	2023-05-01	3

name	birthday	next_birthday	next_birthday_type
today-9	2002-03-22	2022-03-22	3
today-1	2002-03-30	2022-03-30	3
today+0	2002-03-31	2022-03-31	3
today+1	2002-04-01	2022-04-01	3
week-1	2002-04-06	2022-04-06	3
week+0	2002-04-07	2022-04-07	3
week+1	2002-04-08	2022-04-08	3
month-1	2002-04-29	2022-04-29	3
month+0	2002-04-30	2022-04-30	3
month+1	2002-05-01	2022-05-01	3