TSQL Query column which is true if condition is met within "chain" of rows

Question

This is a follow-up from this question. This is going to get complicated.

I have a table like the following for tracking the start and end of incidents of a specific type:

+----------+-------------+------------------+------------------+
| PersonID | Weapon Used |     StartDate    |     EndDate      |
+----------+-------------|------------------+------------------+
|      006 | Knife       | 2019-12-01 09:30 | 2019-12-02 06:15 |
|      007 | Grenade     | 2019-12-01 12:15 | 2019-12-01 12:16 |
|      006 | Bat         | 2019-12-02 06:15 | 2019-12-02 15:15 |
|      006 | Pistol      | 2019-12-02 15:15 | 2019-12-02 23:01 |
|      007 | Pistol      | 2019-12-02 07:23 | 2019-12-04 08:30 |
+----------+-------------+------------------+------------------+

I'm trying to create a calculated column that returns TRUE under the following conditions:

The row event is a part of a "chain" of 1+ back to back events.
AND The pistol is used 1+ times within the chain of events.
AND The row event has the earliest StartDate within the chain.

Creating a table like this:

+----------+-------------+------------------+------------------+---------+
| PersonID | Weapon Used |     StartDate    |     EndDate      | Pistol+ |
+----------+-------------|------------------+------------------+---------+
|      006 | Knife       | 2019-12-01 09:30 | 2019-12-02 06:15 | TRUE    |
|      007 | Grenade     | 2019-12-01 12:15 | 2019-12-01 12:16 | FALSE   |
|      006 | Bat         | 2019-12-02 06:15 | 2019-12-02 15:15 | FALSE   |
|      006 | Pistol      | 2019-12-02 15:15 | 2019-12-02 23:01 | FALSE   |
|      007 | Pistol      | 2019-12-02 07:23 | 2019-12-04 08:30 | TRUE    |
+----------+-------------+------------------+------------------+---------+

It's admittedly a tall order, but one I have to do for a large amount of rows, so I'm desperately trying to avoid having to calculate it by hand and eyeball. Is this possible?

It's not a part of a back to back event chain with 007's pistol event. — Blair Cliff, Jan 10 '20 at 22:51
You have to explain better what you mean by back to back event. — forpas, Jan 10 '20 at 22:53
If an event has the same start date as another event's enddate, with the same personID, they're back to back — Blair Cliff, Jan 11 '20 at 01:04

score 0 · Accepted Answer · answered Jan 11 '20 at 08:59

You will required recursive cte to find the grp of related back-to-back rows

seq is to identify the first of the group

to identify if the group contains pistol, it uses window function sum() with conditional case

with
cte as
(
    select  *, rn = row_number() over (partition by PersonID order by StartDate)
    from    yourtable
),
rcte as
(
    select  c.PersonID, c.[Weapon Used], c.StartDate, c.EndDate, c.rn, 
            grp = 1, seq = 1
    from    cte c
    where   rn  = 1

    union all

    select  c.PersonID, c.[Weapon Used], c.StartDate, c.EndDate, c.rn,
            grp   = case    when    r.EndDate = c.StartDate
                            then    r.grp
                            else    r.grp + 1
                            end,
            seq   = case    when    r.EndDate = c.StartDate
                            then    r.seq + 1
                            else    1
                            end
    from    cte c
            inner join rcte r   on  c.PersonID  = r.PersonID
                               and  c.rn        = r.rn + 1
)
select  *,
        case    when    seq = 1
                and     sum(case when [Weapon Used] = 'Pistol' then 1 else 0 end) 
                        over (partition by PersonID, grp) >= 1
                then    'TRUE'
                else    'FALSE'
                end
from    rcte;

TSQL Query column which is true if condition is met within "chain" of rows

1 Answers1