SQL - 基于另一列的开始和结束日期
问题描述:
简化的结构。SQL - 基于另一列的开始和结束日期
我需要一个有4动作类型和1
动作类型的记录可能是在该州多次,我需要他们的时候
单独行的记录之间的两个日期例如,对于IncidentId = 1
- 行1 - 开始时间= 2017年1月1日14点(ID:3) - 结束时间= 2017年1月1日20点(ID:5)
- 第2行 - StartTime = 2017-01-01 21:00(id:6) - End Time = 2017- 01-02 11:00(ID:9)
CREATE TABLE #returntable ( [incidentid] INT, [starttime] DATETIME, [endtime] DATETIME ) CREATE TABLE #testtableofdoom ( [incidentlogid] INT, [incidentid] INT, [timestamp] DATETIME, [actiontypeid] INT ) INSERT INTO #testtableofdoom (incidentlogid, incidentid, timestamp, actiontypeid) VALUES (1, 1, '2017-01-01 09:00', 1) , (2, 1, '2017-01-01 11:00', 1) , (3, 1, '2017-01-01 14:00', 4) , (4, 1, '2017-01-01 16:00', 4) , (5, 1, '2017-01-01 20:00', 1) , (6, 1, '2017-01-01 21:00', 4) , (7, 1, '2017-01-02 09:00', 4) , (8, 2, '2017-01-02 10:00', 1) , (9, 1, '2017-01-02 11:00', 1) , (10, 1, '2017-01-02 14:00', 1) , (11, 2, '2017-01-02 15:00', 4) , (12, 1, '2017-01-02 16:00', 1) , (13, 1, '2017-01-02 17:00', 1) , (14, 1, '2017-01-02 18:00', 1) , (15, 2, '2017-01-02 15:00', 1); DROP TABLE #testtableofdoom DROP TABLE #returntable
答
我使用表变量来代替临时表,和更短的列名比你多,但这个工程:
declare @tt TABLE (
logId INT, iId INT,
dt DATETIME, atId INT
INSERT @tt (logId, iId,
dt, atId) values
(1, 1, '2017-01-01 09:00', 1),
(2, 1, '2017-01-01 11:00', 1),
(3, 1, '2017-01-01 14:00', 4),
(4, 1, '2017-01-01 16:00', 4),
(5, 1, '2017-01-01 20:00', 1),
(6, 1, '2017-01-01 21:00', 4),
(7, 1, '2017-01-02 09:00', 4),
(8, 2, '2017-01-02 10:00', 1),
(9, 1, '2017-01-02 11:00', 1),
(10, 1, '2017-01-02 14:00', 1),
(11, 2, '2017-01-02 15:00', 4),
(12, 1, '2017-01-02 16:00', 1),
(13, 1, '2017-01-02 17:00', 1),
(14, 1, '2017-01-02 18:00', 1),
(15, 2, '2017-01-02 15:00', 1)
Select s.logId startLogid, e.logId endLogId,
s.iID, s.dt startTime, e.dt endTime
from @tt s join @tt e
on e.logId =
(Select min(logId) from @tt
where iId = s.iID
and atId = 1
and logId > s.logId)
where s.aTid = 4
and ((Select atId from @tt
Where logId =
(Select Max(logId) from @tt
where logId < s.LogId
and iId = s.iId)) = 1
or Not Exists
(Select * from @tt
Where logId < s.LogId
and iId = s.iID))
这将产生如下:
startLogid endLogId iID startTime endTime
----------- ----------- ---- ---------------- ----------------
3 5 1 2017-01-01 14:00 2017-01-01 20:00
6 9 1 2017-01-01 21:00 2017-01-02 11:00
11 15 2 2017-01-02 15:00 2017-01-02 15:00
它使用自连接。 s
代表动作类型4的第一条(开始)记录,e
代表动作类型1的结束记录。由于logId
增量,结束记录的起始记录必须比起始记录高出logId
,并且它必须比起始记录高出logId
具有相同iId
和atId
= 1
Select s.iID, s.dt startTime, e.dt endTime
from @tt s join @tt e
on e.logId =
(Select min(logId) from @tt -- lowest log greater than start logId
where iId = s.iID -- same iId
and atId = 1 -- with atId = 1
and logId > s.logId) -- greater than start logId
最后,开始记录必须被限制到那些“4”记录之前它其或者没有其他相同事件记录或有一个“1”记录之前立即到它。
where s.aTid = 4
and ((Select atId from @tt -- atId of immed prior = 1
Where logId =
(Select Max(logId) from @tt
where logId < s.LogId
and iId = s.iId)) = 1
or Not Exists -- or there is no prior record
(Select * from @tt
Where logId < s.LogId
and iId = s.iID))
答
这样的事情?
select
d.[timestamp] as StartDate,
(select top 1 [timestamp]
from #testTableOfDoom d2
where d2.incidentid = 1 and d2.[timestamp] > d.[timestamp] and actiontypeid = 1
order by d2.[timestamp] asc
) as EndDate
from
(select
p.[timestamp],
LAG(p.actiontypeid) OVER (ORDER BY incidentlogid asc) PrevValue,
p.actiontypeid
from #testTableOfDoom p
where p.incidentid = 1) d
where d.actiontypeid = 4
and d.PrevValue <> 4
你怎么知道哪个'StartTime'(4)与哪个'EndTime'(1)一致?例如:IncidentLogID的#6和#7都表示'IncidentID'#1的开始。你怎么知道'IncidentLogID'#8是否与#6或#7配对? – 3BK
这就是数据格式化的方式。当事件处于OnHold状态时,它保持ActionTypeId为4.它保持这样,直到它不再被保留,但其他字段可以在没有4变化的情况下改变。中间记录(例如行号7)可以在返回值时忽略。 – Kieran