Convert date column into week

Normally, I write the weeks of the month in my SQL query as follows but I will prepare a dashboard and this dashboard will be based on 2023. For this reason, I cannot write all months this way. How can I write this in SQL in an easy way, and the start date of the week will be Saturday.

Here is my query

select 
    dtp.MAIN_CUSTOMER_NUMBER, dtp.TRN_DATE,
    case
        when dtp.TRN_DATE between '20231002' and '20231009' 
            then 'Ekim 1.hafta'
        when dtp.TRN_DATE  between '20231009' and '20231016' 
            then 'Ekim 2.hafta'
        when dtp.TRN_DATE  between '20231016' and '20231023' 
            then 'Ekim 3.hafta'
        when dtp.TRN_DATE  between '20231023' and '20231030' 
            then 'Ekim 4.hafta'
        when dtp.TRN_DATE  between '20231030' and '20231106' 
            then 'Kasım 1.hafta'
        when dtp.TRN_DATE  between '20231106' and '20231112' 
            then 'Kasım 2.hafta'
from 
    BOACARD.TRN.DAILY_TRANSACTION_POOL dtp with (nolock)

If you first calculate WeekEndingDate (Sunday) with a bit of date arithmetic, you can extract the month name using DATENAME(month, WeekEndingDate) and the week number (within each month) using the calculation (DAY(ED.WeekEndingDate) + 6) / 7.

Resulting logic (including a date-generator).

SET LANGUAGE Turkish
SET DATEFIRST 1 -- Set Monday (Pazartesi) as first day of week

SELECT
    dtp.TRN_DATE,
    ED.WeekEndingDate,
    CONCAT(
        DATENAME(month, ED.WeekEndingDate),
        ' ',
        (DAY(ED.WeekEndingDate) + 6) / 7,
        '.hafta'
        ) AS Period
FROM (
    SELECT DATEADD(day, S.value, '20231001 12:34') AS TRN_DATE
    FROM GENERATE_SERIES(0, 50) S
) dtp
CROSS APPLY (
    SELECT DATEADD(
        day,
        7 - DATEPART(weekday, dtp.TRN_DATE),
        CONVERT(DATE, dtp.TRN_DATE)
        ) AS WeekEndingDate
) ED
ORDER BY dtp.TRN_DATE

However, the results are slightly different from your original mappings. I am not clear as to why 2023-10-01 is not week 1 (Ekim 1.hafta) and dates 2023-10-08 through 2023-10-08 is not week 2 (Ekim 2.hafta). In contrast, your posted logic places 2023-11-01 is not week 1 (Kasım 1.hafta).

If the selected month and week-number are based on a day other than the last day of the period (perhaps some day mid-week such as Thursday), you may need to calculate slightly different reference-date and perform your final calculations based on that.

CROSS APPLY (
    SELECT DATEADD(
        day,
        4 - DATEPART(weekday, dtp.TRN_DATE),  -- Mid-week Thursday
        CONVERT(DATE, dtp.TRN_DATE)
        ) AS AltReferenceDate
) RD

This assigns the week to the month having the most days (4 or more) from that week.

Results (showing both calcuations):

See this db<>fiddle