Indeed an iterative solution is the way to go here, but before I go there, permit to discuss date formats, since this caused some confusion in this thread.
In most countries, people write dates as day-month-year with some delimiter. In a few, but whereof some are very popolous, people use year-month-day. And then there is a single country (but which is the third largest by population) where they use month-day-year.
In international contexts, like these forums, it is recommendable to use year-month-day with four digit years, as that format can not be misunderstood by other forum participants.
In SQL Server, the best format is YYYYMMDD, because that format is always interpreted the same. SQL Server is able to misinterpret the format YYYY-MM-DD in some settings, so be careful.
Anyway, over to the acutal problem, which certainly goes beyond what you could expect in a Getting Started forum. It's a non-trivial problem, particularly, if the requirement is to achieve a single-statement solution. I have not tried to do so, because I
quickly realised that this problem belongs to the class of problems which can be solved in a single pass over the data, but where there is no syntax in T-SQL to express this. For such problems, some sort of iteration is usually more efficient, and my assumption
is that efficiency is more important than beauty.
I have however chosen to use so-called set-based iteration. That is, I number the rows in batches, and then I iterate over the batches, handling all rows in a batch in one go. This is usually a performance winner.
Before I give the solution, I should also try to explain how I have interpreted the problem: count slots of 24 hours, where an account has been active. A slot starts when the account logs in. If the account is logged in at this point 24h later, a new slot
starts at that point. Else the next slot starts when the next login starts.
Here is my solution:
declare @t table(Name varchar(100),StartTime datetime,EndTime datetime)
insert into @t values ('Mike','20130101 12:00','20130101 12:33') --1
insert into @t values ('Mike','20130101 15:22','20130101 16:53') --1
insert into @t values ('Mike','20130101 20:01','20130101 21:49') --1
insert into @t values ('Mike','20130102 15:00','20130102 17:33') --2
insert into @t values ('Mike','20130102 18:21','20130102 20:11') --2
insert into @t values ('Mike','20130103 14:21','20130103 22:11') --2 and 3 starts 03.01. 15:00 because 2 started 02.01. 15:00 (+24h)
insert into @t values ('Mike','20130104 02:21','20130104 04:13') --3
insert into @t values ('Moes','20130102 18:21','20130102 20:11') --1
insert into @t values ('Moes','20130102 20:21','20130102 22:11') --2
insert into @t values ('Moes','20130103 02:21','20130103 04:11') --2
insert into @t values ('Moes','20130103 17:21','20130106 20:10') --2, 3, 4, 5
DECLARE @temp TABLE (batchno int NOT NULL,
Name varchar(100) NOT NULL,
StartTime int NOT NULL,
EndTime int NOT NULL,
PRIMARY KEY (batchno, Name)
)
DECLARE @names TABLE (Name varchar(100) NOT NULL PRIMARY KEY,
Start24h int NOT NULL,
NoOf24h int NOT NULL
)
INSERT @temp(batchno, Name, StartTime, EndTime)
SELECT row_number() OVER(PARTITION BY Name ORDER BY StartTime),
Name, datediff(ss, '19700101', StartTime),
datediff(ss, '19700101', EndTime)
FROM @t
INSERT @names (Name, Start24h, NoOf24h)
SELECT Name, StartTime, 1
FROM @temp
WHERE batchno = 1
DECLARE @batchno int = 2
WHILE EXISTS (SELECT * FROM @temp WHERE batchno = @batchno)
BEGIN
UPDATE @names
SET Start24h = CASE WHEN t.EndTime - n.Start24h < 86400
THEN n.Start24h
WHEN t.StartTime - n.Start24h > 86400
THEN t.StartTime
ELSE t.StartTime + 86400 * ((t.EndTime - t.StartTime) / 86400 + 1)
END,
NoOf24h += CASE WHEN t.EndTime - n.Start24h < 86400
THEN 0
ELSE (t.EndTime -
CASE WHEN t.StartTime > n.Start24h + 86400
THEN t.StartTime
ELSE n.Start24h + 86400
END) / 86400 + 1
END
FROM @names n
JOIN @temp t ON t.Name = n.Name
WHERE t.batchno = @batchno
SELECT @batchno += 1
END
SELECT Name, NoOf24h
FROM @names
ORDER BY Name