英文:
Teradata Case condition check by ID and Date
问题
以下是翻译好的部分：
"有多条记录中有相同的ID，但具有不同的startdate和enddate时间跨度。
我需要编写一个Teradata SQL条件来按ID检查，并且其开始日期是当前月份，然后与上一条记录的enddate进行比较。
条件如下：
我需要编写一个Teradata SQL条件来按ID检查，其开始日期是当前月份，然后与上一条记录的enddate进行比较，如果差距大于3个月，则打印'是'，否则打印'否'。
顺便提一下：下面的activerec字段表示如果ID具有当前月份的开始日期，则显示为是。
例如，表数据如下：
ID startdate enddate name. activerec
101 03-01-2023. 11-30-2023 Lee yes
101 02-01-2023 02-28-2023 Lee No
101 12-01-2022 12-31-2022 Lee No
102 03-01-2023 03-31-2023 Mark yes
102 10-01-2022. 10-31-2022 Mark. No
我需要的答案如下：
ID startdate enddate name activerec
101 03-01-2023. 11-30-2023 Lee yes
101 02-01-2023 02-28-2023 Lee No
101 12-01-2022 12-31-2022 Lee No
102 03-01-2023 03-31-2023 Mark yes
102 10-01-2022. 10-31-2022 Mark. No
Flag
No
No
No
Yes
No
No
这里101号ID具有当前月份的开始日期，上一条记录的结束日期是02-28-2023。我需要比较当前月的开始日期03-01-2023和02-28-2023，看是否相差三个月以上。这个ID的时间差不到3个月，所以我需要打印'No'。
102号ID具有当前月份的开始日期，而上一条记录的结束日期是10-31-2022，相差超过3个月。因此，我需要将其视为新成员，并打印'Yes'。"
英文:
There are same IDs in multiple records with different startdate and enddate spans.
I need to write a teradata sql case condition to check by ID and whose start date is current month and compare with enddate of previous record
Case condition:
I need to write a teradata sql case condition to check by ID and whose start date is current month and compare with enddate of previous record, if there is a difference greater than 3 months then print 'yes' else 'no'
Just FYI: below activerec field is if the ID has a start date of current month it is shown as yes.
Example the table data is as below:
ID startdate enddate name. activerec
101 03-01-2023. 11-30-2023 Lee yes
101 02-01-2023 02-28-2023 Lee No
101 12-01-2022 12-31-2022 Lee No
102 03-01-2023 03-31-2023 Mark yes
102 10-01-2022. 10-31-2022 Mark. No
I need the answer as below:
ID startdate enddate name activerec
101 03-01-2023. 11-30-2023 Lee yes
101 02-01-2023 02-28-2023 Lee No
101 12-01-2022 12-31-2022 Lee No
102 03-01-2023 03-31-2023 Mark yes
102 10-01-2022. 10-31-2022 Mark. No
Flag
No
No
No
Yes
No
No
Here 101 ID has a startdate as current month and the enddate of previous record date span is 02-28-2023. I need to compare current month's start date 03-01-2023 with 02-28-2023 to see if it has difference of three months or not. This ID doesn't have a difference of 3 months so I need to print 'No'.
102 ID has a startdate as current month and it's previous record end date 10-31-2022, it has a difference of more than 3 months. So, I need consider this a new member and print as 'Yes'.
I Couldn't do it at all
答案1
得分: 0
这是一个SQL翻译：
case -- 开始日期是当前月
when startdate = trunc(current_date, 'mm')
-- 差异大于3个月
and add_months(lag(enddate) over (partition by id order by startdate), 3)
< startdate
then 'y'
else 'n'
end
英文:
This is a translation to SQL:
case -- start date is current month
when startdate = trunc(current_date, 'mm')
-- difference greater than 3 months
and add_months(lag(enddate) over (partition by id order by startdate), 3)
< startdate
then 'y'
else 'n'
end
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论