英文:
SPLIT Week between months
问题
要求是将周分割成月份，即将TECHNICAL_WEEK分割成不同的部分。将技术周的开始日期设为星期一，但如果月份在同一周内，则将月份的第一天设为开始日期，并将该周的其余日期设为月份的第一天。下一周的开始日期应为星期一。
请帮助构建一个HANA SQL查询。
示例结果如下：
DAY ------- TECH_WEEK
20230529 ------- 20230529
20230530 ------- 20230529
20230531 ------- 20230529
20230601 ------- 20230601
20230602 ------- 20230601
20230603 ------- 20230601
20230604 ------- 20230601
英文:
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
答案1
得分: 2
以下是翻译好的部分：
让我们来看一些示例数据：
SELECT GENERATED_PERIOD_START DAY
FROM SERIES_GENERATE_DATE('INTERVAL 1 DAY', '2023-01-01', '2024-01-01')
根据我对您需求的理解，以下语句应该可以完成任务：
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')
)
英文:
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')
)
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论