Return Duration Of An Item From Its Transactions, Many To Many, Sql

Hopefully I can get some help on this. Situation There are two incoming stations and one outgoing station. Items are scanned in and out. I need to know how long an item was in the

Solution 1:

This is a gaps-and-island problem. An approach is to define groups using a cumulative sum that increments for every incoming record, and use that for aggregation:

select
    itemID,
    min(dates) incoming,
    max(dates) outgoing,
    datediff(second, min(dates), max(dates)) /60.0/60/24 days_in_station
from (
    select
        t.*,
        sum(casewhen type ='Incoming'then1else0end)
            over(partitionby itemID orderby dates) grp
    from mytable t
) t
groupby itemID, grp

Your question does not specify what should happen when incoming/outgoing records do not properly interleave for a given item. Here is how the query would handle that:

• if there are two consecutive incoming records, this generates a row in the resultset where the incoming and outgoing dates are identical, and days at station is 0

• if there are two or more consecutive outgoing records, only the last one is considered

These could be fine tuned if more details on the requirement were provided.