条件滞后/线索 [英] Conditional Lag/Lead
问题描述
我是SQL服务器语法的新手,我正在寻求以下方案的帮助。
I am fairly new to SQL server syntax and am looking for help with the following scenario.
我有一个如下表格(前3列)。
I have a table like the following (with the first 3 columns).
ID和日期的组合为我提供了该行的唯一标识符。 我需要开始约会 和结束日期列。
Combination of ID and Date gives me a unique identifier for the row. I am needing to get start date and end date columns.
对于开始日期,如果是line = header,则返回Date。 如果=详细信息,那么我需要上一行的开始日期。
For start date, If line =header, then return Date. If =Detail, then I need the start date from the previous row.
这不像使用滞后/潜在客户那么简单,因为我需要计算的开始日期上一行。 更复杂的是,我不能简单地依赖ID并使用min / max,因为这些#会在以后的日期被回收。
It's not as simple as using lag/lead, as I'm needing the start date that was calculated in the previous row. What adds to the complication is that I can't simply rely on ID and use min/max, as these #'s are recycled with later dates.
ID     类型             日期                    开始日期   结束日期
2    详情    &NBSP l /2019分之2        &NBSP l /2019分之1    1/4/2019
2 Detail 1/2/2019 1/1/2019 1/4/2019
2    详情      1/4/2019年        1/1/2019年    1/4/2019
2 Detail 1/4/2019 1/1/2019 1/4/2019
4    头            12 /2018分之31            12 /2018分之31        12/31/2018
5      头          1/10/2019年            1/10/2019年          1/20/2019
有关如何最好地完成此任务的任何建议吗?
Any suggestions as to how to best accomplish this?
推荐答案
嗨康妮,
试试这个:
创建表格hd(id int,类型varchar(50),[date] date, [开始日期]日期,[结束日期]日期)
create table hd (id int, type varchar(50), [date] date, [Start Date] date, [End Date] date)
插入高清值(1,'标题','1/1/2019','1/1/2019',' 1/4/2019'),(2,'细节','1/2/201','1/1/201','1/4/2019'),(3,'细节','1 / 4/2019','1/1/2019','1/4/2019'),(4,'Header','12/31/201','12/31/201','12/31 / 2018'),(5,'Header','1/10/2019','1/10/2019','1/20/2019')
insert hd values (1,'Header','1/1/2019','1/1/2019','1/4/2019'), (2,'Detail','1/2/2019','1/1/2019','1/4/2019'), (3,'Detail','1/4/2019','1/1/2019','1/4/2019'), (4,'Header','12/31/2018','12/31/2018','12/31/2018'), (5,'Header','1/10/2019','1/10/2019','1/20/2019')
选择*,案例
          当type ='Header'时,[date]
when type = 'Header' then [date]
          当type ='Detail'时滞后([开始日期])结束(按ID排序)
when type = 'Detail' then lag([Start Date]) over (order by id)
          结束为新
end as New
来自hd
如果有帮助,请标记为答案。谢谢。
Mark as answer if it helps. Thanks.
这篇关于条件滞后/线索的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!