SPLIT Week between months

Question

Requirement is to split weeks between Months i.e. TECHNICAL_WEEK. populate tech week as Monday date but If month within the same week then populate 1st day of month and remaining days in week with e the 1st day of month. the Next week should be populated as Monday date.

Please help to build a HANA SQL Example result below

DAY          -------        TECH_WEEK  
20230529     -------        20230529  
20230530     -------        20230529  
20230531     -------        20230529  
20230601     -------        20230601    
20230602     -------        20230601  
20230603     -------        20230601    
20230604     -------        20230601    

Answer 1

Let's take some example data:

SELECT GENERATED_PERIOD_START DAY
FROM SERIES_GENERATE_DATE('INTERVAL 1 DAY', '2023-01-01', '2024-01-01')

As per my understanding of your requirement, this statement should do the job:

SELECT 
    DAY, add_days(DAY, -1 * CASE WHEN dayofmonth(day) - 1 < weekday(day) THEN dayofmonth(day) - 1 ELSE weekday(day) END) TECH_WEEK
FROM
(
    SELECT GENERATED_PERIOD_START DAY
    FROM SERIES_GENERATE_DATE('INTERVAL 1 DAY', '2023-01-01', '2024-01-01')
)