HOWTO Increase time duration of event when rows are by time period using T-SQL

I am working on an SQL Server application that makes complex calculations based on flows in a network.

I have a table of data with values for each link by time period. The time period are fixed and represent 15 minute intervals. For example I have

Link A   Time Period=0    Value=0<br>
Link A   Time Period=1    Value=0<br>
Link A   Time Period=2    Value=1<br>
Link A   Time Period=3    Value=1<br>
Link A   Time Period=4    Value=1<br>
Link A   Time Period=5    Value=1<br>
Link A   Time Period=6    Value=0<br>
Link A   Time Period=7    Value=0<br>
Link A   Time Period=8    Value=0<br>
Link A   Time Period=9    Value=0<br>
Link A   Time Period=10   Value=0<br>

The values indica开发者_如何学JAVAte that an event has taken place on that link at that time period. The duration of the event is the difference between the last time period and the the first time period. In the example, duration is time period 5- time period 2.

The inputs to the calculation specify an increase or decrease in the duration. Let's assume the duration has to be increased by 25%. In the example the data now looks like

Link A   Time Period=0    Value=0<br>
Link A   Time Period=1    Value=0<br>
Link A   Time Period=2    Value=1<br>
Link A   Time Period=3    Value=1<br>
Link A   Time Period=4    Value=1<br>
Link A   Time Period=5    Value=1<br>
Link A   Time Period=6    Value=1<br>
Link A   Time Period=7    Value=0<br>
Link A   Time Period=8    Value=0<br>
Link A   Time Period=9    Value=0<br>
Link A   Time Period=10   Value=0<br>

How can I apply the increase or decrease in duration to the data using T-SQL? I have a large dataset, events can be of any duration and the increase/decrease in duration applies to all events.

There is also the issue of overlap if an event duration is increased so that it overlap with another existing event. In that case the value for the overlap period is the sum of the existing value and the value from the increase/decrease in duration.

Any help greatly appreciated.

James

To isolate the various time periods with the same number of events, you can use a query like the following:

declare @events table (
    timeNbr int,
    eventCount int
)

insert into @events values (0, 0)
insert into @events values (1, 0)
insert into @events values (2, 1)
insert into @events values (3, 1)
insert into @events values (4, 1)
insert into @events values (5, 1)
insert into @events values (6, 0)
insert into @events values (7, 0)
insert into @events values (8, 0)
insert into @events values (9, 0)
insert into @events values (10, 0)
insert into @events values (11, 0)
insert into @events values (12, 1)
insert into @events values (13, 1)
insert into @events values (14, 1)
insert into @events values (15, 1)
insert into @events values (16, 0)
insert into @events values (17, 2)
insert into @events values (18, 0)
insert into @events values (19, 0)

select startEvent.timeNbr as timeNbrStart, endEvent.timeNbr as timeNbrEnd,
startEvent.eventCount
from @events startEvent
    join @events endEvent
        on startEvent.timeNbr <= endEvent.timeNbr
where startEvent.eventCount > 0
    and endEvent.eventCount = startEvent.eventCount
    -- ensure that there all records in between these two records have the same number of events
    and not exists (
        select top 1 1 from @events
        where timeNbr between startEvent.timeNbr and endEvent.timeNbr
            and eventCount <> startEvent.eventCount
    -- ensure that the start record is actually at the start of a period
    ) and not exists (
        select top 1 1 from @events
        where timeNbr = startEvent.timeNbr - 1
            and eventCount = startEvent.eventCount
    -- ensure that the end record is actually at the end of a period
    ) and not exists (
        select top 1 1 from @events
        where timeNbr = endEvent.timeNbr + 1
            and eventCount = endEvent.eventCount
    )

This query will allow you to pick out all of the time periods with the same number of events. From there, it is possible to determine how long each period is, when each period starts and stops, and how many events are in each period.