sql-server - 对事件进行排序并按顺序在每个位置保留最早和最新的时间戳

Question

我有一些数据记录了一些顺序处理事件。

每个事件发生在一个位置 (Location) 并包括开始日期 (BeginDT) 和结束日期 (EndDT)。

一个位置的处理可能涉及多个连续步骤，每个步骤都用 BeginDT 和 EndDT 记录。

在某个位置的非最终步骤应记录为完成（完成为真 (1)）。

在某个位置完成的步骤应记录为不完整（完整为假（0））。

但是，由于某些缺失或错误的记录，一个位置的处理可能会不完整。当一个位置的处理有一个（或多个）指示不完整的步骤（或多个步骤）但没有指示完成的后续最终步骤时，则不完整记录（或该位置序列的最近不完整记录）将被视为最终记录.

可能会在同一位置按顺序进行多次处理。当在同一位置按顺序处理多次并且该位置的主要事件已完成时，则每个事件都将被视为完全独立的。

我已经包含了一种方法，我试图使用该方法通过识别前一个和下一个记录来帮助确定如何处理每个事件，但我不确定这是否是一种有用的方法。

所需的最终结果是 Location 处每个顺序处理事件集的 Order、Location、最早的 BeginDT 和最新的 EndDT：

/*
[Order] Location    BeginDT     EndDT
1       A           2000-01-01  2000-01-06
2       B           2000-01-10  2000-01-11
3       C           2000-01-20  2000-01-21
4       D           2000-01-30  2000-01-31
5       A           2000-02-01  2000-02-02
6       C           2000-02-10  2000-02-11
7       C           2000-02-20  2000-02-21
8       B           2000-03-01  2000-03-02
9       D           2000-03-10  2000-03-11
10      D           2000-03-20  2000-03-23
11      E           2000-03-30  2000-03-31
*/

declare @T table (
ID int identity,
Location varchar(20),
Complete bit,
BeginDT date,
EndDT date
);

insert @T (Location, Complete, BeginDT, EndDT)
values
('A', 0, '2000-01-01', '2000-01-02'),
('A', 0, '2000-01-03', '2000-01-04'),
('A', 1, '2000-01-05', '2000-01-06'),
('B', 1, '2000-01-10', '2000-01-11'),
('C', 1, '2000-01-20', '2000-01-21'),
('D', 0, '2000-01-30', '2000-01-31'),
('A', 1, '2000-02-01', '2000-02-02'),
('C', 1, '2000-02-10', '2000-02-11'),
('C', 1, '2000-02-20', '2000-02-21'),
('B', 1, '2000-03-01', '2000-03-02'),
('D', 1, '2000-03-10', '2000-03-11'),
('D', 0, '2000-03-20', '2000-03-21'),
('D', 1, '2000-03-22', '2000-03-23'),
('E', 1, '2000-03-30', '2000-03-31');

with cte as (
select
    row_number() over (order by BeginDT) as [Order],
    Location,
    Complete,
    BeginDT,
    EndDT
from @T
)
select
*
from cte
order by
[Order],
Complete;

with cte as (
select
    row_number() over (order by BeginDT) as [Order],
    Location,
    Complete,
    BeginDT,
    EndDT
from @T
)
select
    [Order],
    Location,
    BeginDT,
    EndDT,
    Complete,
    max(Previous) as Previous,
    max(cast(PreviousComplete as int)) as PreviousComplete,
    max([Next]) as [Next]
from (
    select
        cte.[Order],
        cte.Location,
        cte.BeginDT,
        cte.EndDT,
        cte.Complete,
        case
            when cte.[Order] = j.[Order] + 1
                then j.Location
                else NULL
            end as Previous,
        case
            when cte.[Order] = j.[Order] + 1
                then j.Complete
                else NULL
            end as PreviousComplete,
        case
            when cte.[Order] = j.[Order] - 1
                then j.Location
                else NULL
            end as [Next]
    from cte
    cross join (
        select
            [Order],
            Location,
            Complete
        from cte
        ) as j
    ) as S
group by
    [Order],
    Location,
    BeginDT,
    EndDT,
    Complete;

我开发了一个解决方案：

with
cte1 as (
    select
        row_number() over (order by BeginDT, EndDT) as [Order],
        Location,
        IsComplete,
        BeginDT,
        EndDT
    from @T
    ),
cte2 as (
    select
        [Order],
        Location,
        case
            when 1 <> 1
                or Location <> Previous
                or Previous is NULL
                or (
                    1 = 1
                    and Location = Previous
                    and PreviousIsComplete = 1
                    )
                then BeginDT
            end as BeginDT,
        case
            when 1 <> 1
                or IsComplete = 1
                or (
                    IsComplete = 0
                    and (
                        1 <> 1
                        or Location <> [Next]
                        or [Next] is NULL
                        )
                    )
                then EndDT
            end as EndDT
    from (
        select
            [Order],
            Location,
            BeginDT,
            EndDT,
            IsComplete,
            max(Previous) as Previous,
            max(cast(PreviousIsComplete as int)) as PreviousIsComplete,
            max([Next]) as [Next]
        from (
            select
                cte1.[Order],
                cte1.Location,
                cte1.BeginDT,
                cte1.EndDT,
                cte1.IsComplete,
                case
                    when cte1.[Order] = cj1.[Order] + 1
                        then cj1.Location
                        else NULL
                    end as Previous,
                case
                    when cte1.[Order] = cj1.[Order] + 1
                        then cj1.IsComplete
                        else NULL
                    end as PreviousIsComplete,
                case
                    when cte1.[Order] = cj1.[Order] - 1
                        then cj1.Location
                        else NULL
                    end as [Next]
            from cte1
            cross join (
                select
                    [Order],
                    Location,
                    IsComplete
                from cte1
                ) as cj1
            ) as T1
        group by
            [Order],
            Location,
            BeginDT,
            EndDT,
            IsComplete
        ) as T2
    ),
cte3 as (
    select
        'Begin' as [Type],
        row_number() over (order by [Order]) as [Order],
        Location,
        min(BeginDT) as DT
    from cte2
    where BeginDT is not NULL
    group by
        [Order],
        Location
    union
    select
        'End' as [Type],
        row_number() over (order by [Order]) as [Order],
        Location,
        min(EndDT) as DT
    from cte2
    where EndDT is not NULL
    group by
        [Order],
        Location
    )
select
    [Order],
    Location,
    [Begin] as BeginDT,
    [End] as EndDT
from (
    select
        [Type],
        [Order],
        Location,
        DT
    from cte3
    ) as T
pivot (
    max(DT) for [Type] in ([Begin], [End])
    ) as pT
order by [Order];

我欢迎任何改进。谢谢。

标签： sql-serversql-server-2008sql-server-2008-r2