SQL Counting Total Time but resetting total if large gap

I have a table containing device movements.

MoveID DeviceID Start End

I want to find out if there is a way to sum up the total movement days for each device to the present. However if there is a gap 6 weeks bewtween an end date and the next start date then the time count is reset.

MoveID    DeviceID    Start     End
1           1         2011-1-1  2011-2-1
2           1         2011-9-1  2011-9-20
3           1         2011-9-25 2011-9-28

The total for device should be 24 days as because there is a gap of greater than 6 weeks. Also I'd like to find out the number of days since the first movement in the group in this case 28 days as the latest count group started on the 2011-9-1

I thought I could do it with a stored proc and a cursor etc (which is not good) just wondered if there was anything bett开发者_C百科er?

Thanks

Graeme

create table #test
(
MoveID int,
DeviceID int,
Start date,
End_time date
)
--drop table #test
insert into #test values
(1,1,'2011-1-1','2011-2-1'),
(2,1,'2011-9-1','2011-9-20'),
(3,1,'2011-9-25','2011-9-28')

select 
   a.DeviceID, 
   sum(case when datediff(dd, a.End_time, isnull(b.Start, a.end_time)) > 42 /*6 weeks = 42 days*/ then 0 else datediff(dd,a.Start, a.End_time)+1 /*we will count also the last day*/ end) as movement_days,

sum(case when datediff(dd, a.End_time, isnull(b.Start, a.end_time)) > 42 /6 weeks = 42 days/ then 0 else datediff(dd,a.Start, a.End_time)+1 /we will count also the last day/ end + case when b.MoveID is null then datediff(dd, a.Start, a.End_time) + 1 else 0 end) as total_days
from #test a left join #test b on a.DeviceID = b.DeviceID and a.MoveID + 1 = b.MoveID group by a.DeviceID

Let me know if you need some explanation - there can be more ways to do that...

DECLARE @Times TABLE
(
    MoveID INT,
    DeviceID INT,
    Start DATETIME,
    [End] DATETIME
)

INSERT INTO @Times VALUES (1, 1, '1/1/2011', '2/1/2011')
INSERT INTO @Times VALUES (2, 1, '9/1/2011', '9/20/2011')
INSERT INTO @Times VALUES (3, 1, '9/25/2011', '9/28/2011')

INSERT INTO @Times VALUES (4, 2, '1/1/2011', '2/1/2011')
INSERT INTO @Times VALUES (5, 2, '3/1/2011', '4/20/2011')
INSERT INTO @Times VALUES (6, 2, '5/1/2011', '6/20/2011')

DECLARE @MaxGapInWeeks INT
SET @MaxGapInWeeks = 6

SELECT
    validTimes.DeviceID,
    SUM(DATEDIFF(DAY, validTimes.Start, validTimes.[End]) + 1) AS TotalDays,
    DATEDIFF(DAY, MIN(validTimes.Start), MAX(validTimes.[End])) + 1 AS TotalDaysInGroup
FROM 
    @Times validTimes LEFT JOIN
    @Times timeGap 
        ON timeGap.DeviceID = validTimes.DeviceID
        AND timeGap.MoveID <> validTimes.MoveID
        AND DATEDIFF(WEEK, validTimes.[End], timeGap.Start) > @MaxGapInWeeks
WHERE timeGap.MoveID IS NULL
GROUP BY validTimes.DeviceID