FREE AI-Enhanced Online SQL Server Compiler

Untitled*

You must execute the code again to save those values in the database.

/*
    record sample data in temp table, with row_number() for self join below
*/
create table #PeopleInArea (r int, [date] time, areaName varchar(10), [node_address/person] int)
insert into #PeopleInArea (r, [date], areaName, [node_address/person])
select 
    row_number() over(order by areaName, [date]) as r, 
    x.*
from
(
    select '8:15' as [date], 'area1' as areaName, 1 as [node_adress/person]
    union all select '8:20', 'area1', 1
    union all select '8:25', 'area1', 1
    union all select '8:30', 'area1', 1
    union all select '8:45', 'area1', 1
    union all select '9:00', 'area1', 1
    union all select '8:15', 'area2', 2
    union all select '8:20', 'area2', 2
    union all select '9:00', 'area2', 1
    union all select '9:05', 'area2', 2
    union all select '9:10', 'area2', 2
) x

/*
    self join, to show each sighting against the previous sighting, for a particular person in a given area
*/
select
    pa.*, 
    pa2.r as [lastRow],
    pa2.[date] as lastDate,
    pa2.areaName as lastArea,
    datediff([minute], pa2.[date], pa.[date]) as timeSinceLast,
    pa2.[node_address/person] as lastNodePerson,
    case 
        when datediff([minute], pa2.[date], pa.[date]) < 10
            and pa2.areaName = pa.areaName
            and pa2.[node_address/person] = pa.[node_address/person]
        then 1 else 0 
    end as IsConsecutive    /* sighting of particular person in a given area follows consecutively, within last 10 mins, a previous sighting of that person in the same area */
into #lastSightings
from #PeopleInArea pa
    left join #PeopleInArea pa2 on 
    (
        pa.areaName = pa2.areaName
        and pa.[node_address/person] = pa2.[node_address/person]
        and pa.r = pa2.r + 1
    )

--select * from #lastSightings order by pa.areaName, pa.[node_address/person], pa.[date]

/*
    identify consecutive sightings, create a unique identifier for each
*/
select y.*,
    sum(IsBlockStart) over(order by areaName, [node_address/person], [date]) as BlockId     /* distinguish consecutive blocks of one or more sightings of a particular person in a given area */
into  #ConsecutiveBlocks
from
(
    select x.*,
        case 
            when 
                [lastRow] is null 
                or lastArea != areaName
                or [lastNodePerson] != [node_address/person] 
                or timeSinceLast > 10
            then 1 else 0
        end as IsBlockStart     /* 1 when particular sighting is the first in a sequence or 1 or more sightings, of a particular person in a given area */
    from #lastSightings x
    --order by areaName, [node_address/person], [date]
) y
-- order by areaName, [node_address/person], [date]

--select * from #ConsecutiveBlocks order by areaName, [node_address/person], [date]

/*
    apply business logic, re amount of time allocated to a given sighting

1. A particular person sighted consecutively in a given area over intervals less than 10 minutes apart is attributed the full duration, i.e. duration since the last sighting of that person in that area.
        2. A particular person sighted in a given area only once in a 10 minute interval is considered to have been there for exactly 5 minutes.
*/
select
    cb.*,
    case        /* consecutive sightings within 10 mins of each other are attributed their full duration */
        when IsConsecutive = 1 and timeSinceLast < 10 then timeSinceLast
        when    /* isolated sightings are attributed 5 mins only */
            IsBlockStart = 1
            and (not exists (select 1 from #ConsecutiveBlocks cbx where cbx.BlockId = cb.BlockId and cbx.IsConsecutive = 1))
        then 5
    end as consecutiveTime
into #ConsecutiveTime
from #ConsecutiveBlocks cb

-- select * from #ConsecutiveTime order by areaName, [node_address/person], [date]

/*
    aggregate total time spent by given person in given area
*/
select
    areaName,
    sum(consecutiveTime) as [time]
from #ConsecutiveTime 
group by areaName
order by areaName

areaName	time
area1	25
area2	15

(11 rows affected)

(11 rows affected)

(11 rows affected)

(11 rows affected)

(2 rows affected)