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):
| TRN_DATE | WeekEndDate | Period | AltRefDate | AltPeriod | Expected |
|---|---|---|---|---|---|
| 2023-10-01 12:34 | 2023-10-01 | Ekim-1.hafta | 2023-09-28 | Eylül 4.hafta | |
| 2023-10-02 12:34 | 2023-10-08 | Ekim-2.hafta | 2023-10-05 | Ekim 1.hafta | Ekim 1.hafta |
| 2023-10-03 12:34 | 2023-10-08 | Ekim-2.hafta | 2023-10-05 | Ekim 1.hafta | Ekim 1.hafta |
| 2023-10-04 12:34 | 2023-10-08 | Ekim-2.hafta | 2023-10-05 | Ekim 1.hafta | Ekim 1.hafta |
| 2023-10-05 12:34 | 2023-10-08 | Ekim-2.hafta | 2023-10-05 | Ekim 1.hafta | Ekim 1.hafta |
| 2023-10-06 12:34 | 2023-10-08 | Ekim-2.hafta | 2023-10-05 | Ekim 1.hafta | Ekim 1.hafta |
| 2023-10-07 12:34 | 2023-10-08 | Ekim-2.hafta | 2023-10-05 | Ekim 1.hafta | Ekim 1.hafta |
| 2023-10-08 12:34 | 2023-10-08 | Ekim-2.hafta | 2023-10-05 | Ekim 1.hafta | Ekim 1.hafta |
| 2023-10-09 12:34 | 2023-10-15 | Ekim-3.hafta | 2023-10-12 | Ekim 2.hafta | Ekim 2.hafta |
| ... | ... | ... | ... | ... | ... |
| 2023-10-15 12:34 | 2023-10-15 | Ekim-3.hafta | 2023-10-12 | Ekim 2.hafta | Ekim 2.hafta |
| 2023-10-16 12:34 | 2023-10-22 | Ekim-4.hafta | 2023-10-19 | Ekim 3.hafta | Ekim 3.hafta |
| ... | ... | ... | ... | ... | ... |
| 2023-10-22 12:34 | 2023-10-22 | Ekim-4.hafta | 2023-10-19 | Ekim 3.hafta | Ekim 3.hafta |
| 2023-10-23 12:34 | 2023-10-29 | Ekim-5.hafta | 2023-10-26 | Ekim 4.hafta | Ekim 4.hafta |
| ... | ... | ... | ... | ... | ... |
| 2023-10-29 12:34 | 2023-10-29 | Ekim-5.hafta | 2023-10-26 | Ekim 4.hafta | Ekim 4.hafta |
| 2023-10-30 12:34 | 2023-11-05 | Kasım-1.hafta | 2023-11-02 | Kasım 1.hafta | Kasim 1.hafta |
| ... | ... | ... | ... | ... | ... |
| 2023-11-05 12:34 | 2023-11-05 | Kasım-1.hafta | 2023-11-02 | Kasım 1.hafta | Kasim 1.hafta |
| 2023-11-06 12:34 | 2023-11-12 | Kasım-2.hafta | 2023-11-09 | Kasım 2.hafta | Kasim 2.hafta |
| 2023-11-07 12:34 | 2023-11-12 | Kasım-2.hafta | 2023-11-09 | Kasım 2.hafta | Kasim 2.hafta |
| 2023-11-08 12:34 | 2023-11-12 | Kasım-2.hafta | 2023-11-09 | Kasım 2.hafta | Kasim 2.hafta |
| 2023-11-09 12:34 | 2023-11-12 | Kasım-2.hafta | 2023-11-09 | Kasım 2.hafta | Kasim 2.hafta |
| 2023-11-10 12:34 | 2023-11-12 | Kasım-2.hafta | 2023-11-09 | Kasım 2.hafta | Kasim 2.hafta |
| 2023-11-11 12:34 | 2023-11-12 | Kasım-2.hafta | 2023-11-09 | Kasım 2.hafta | Kasim 2.hafta |
| 2023-11-12 12:34 | 2023-11-12 | Kasım-2.hafta | 2023-11-09 | Kasım 2.hafta | ??? |
| 2023-11-13 12:34 | 2023-11-19 | Kasım-3.hafta | 2023-11-16 | Kasım 3.hafta |
See this db<>fiddle