getting sum values for non overlapping rows by datetime

Question

Post reply

getting sum values for non overlapping rows by datetime

ot 60857

SSC Rookie

Points: 26
More actions
October 13, 2014 at 4:04 am

#291700

I want to count persons for non overlapping intervalls by room number. Here the data:
SET DATEFORMAT mdy;
DECLARE @PersonTime TABLE
(
ID INT,
Person INT,
Room INT,
Coming DATETIME,
Going DATETIME
);
INSERT INTO @PersonTime
SELECT 1, 1001, 23, '10/13/2014 8:00:00', '10/13/2014 9:00:00' UNION ALL
SELECT 2, 1002, 23, '10/13/2014 8:05:00', '10/13/2014 9:05:00' UNION ALL
SELECT 3, 2001, 24, '10/13/2014 8:00:00', '10/13/2014 9:00:00' UNION ALL
SELECT 4, 2002, 24, '10/13/2014 9:30:00', '10/13/2014 10:00:00' UNION ALL
SELECT 5, 3001, 25, '10/13/2014 8:00:00', '10/13/2014 10:00:00' UNION ALL
SELECT 6, 3002, 25, '10/13/2014 8:30:00', '10/13/2014 09:30:00' UNION ALL
SELECT 7, 4001, 26, '10/13/2014 8:00:00', '10/13/2014 10:00:00' UNION ALL
SELECT 8, 4002, 26, '10/13/2014 8:00:00', '10/13/2014 10:00:00'
The output data should look like this:
Room Start End Count
----------- ----------------------- ----------------------- ------
23 2014-10-13 08:00:00.000 2014-10-13 08:05:00.000 1
23 2014-10-13 08:05:00.000 2014-10-13 09:00:00.000 2
23 2014-10-13 09:00:00.000 2014-10-13 09:05:00.000 1
24 2014-10-13 08:00:00.000 2014-10-13 09:00:00.000 1
24 2014-10-13 09:30:00.000 2014-10-13 10:00:00.000 1
25 2014-10-13 08:00:00.000 2014-10-13 08:30:00.000 1
25 2014-10-13 08:30:00.000 2014-10-13 09:30:00.000 2
25 2014-10-13 09:30:00.000 2014-10-13 10:30:00.000 1
26 2014-10-13 08:00:00.000 2014-10-13 10:00:00.000 2

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

serg-52 SSCrazy Eights Points: 9913 More actions · Answer 1

Try

SET DATEFORMAT mdy;

DECLARE @PersonTime TABLE

(

ID INT,

Person INT,

Room INT,

Coming DATETIME,

Going DATETIME

);

INSERT INTO @PersonTime

SELECT 1, 1001, 23, '10/13/2014 8:00:00', '10/13/2014 9:00:00' UNION ALL

SELECT 2, 1002, 23, '10/13/2014 8:05:00', '10/13/2014 9:05:00' UNION ALL

SELECT 3, 2001, 24, '10/13/2014 8:00:00', '10/13/2014 9:00:00' UNION ALL

SELECT 4, 2002, 24, '10/13/2014 9:30:00', '10/13/2014 10:00:00' UNION ALL

SELECT 5, 3001, 25, '10/13/2014 8:00:00', '10/13/2014 10:00:00' UNION ALL

SELECT 6, 3002, 25, '10/13/2014 8:30:00', '10/13/2014 09:30:00' UNION ALL

SELECT 7, 4001, 26, '10/13/2014 8:00:00', '10/13/2014 10:00:00' UNION ALL

SELECT 8, 4002, 26, '10/13/2014 8:00:00', '10/13/2014 10:00:00'

;

declare @interval int = 5;

with prms as (

select start = min(Coming), cnt = datediff(minute, min(Coming), max (Going)) / @interval

from @PersonTime

), all_intvls as (

select strt = dateadd(minute, (N-1)*@interval, prms.Start)

, finish = dateadd(minute, N*@interval, prms.Start)

from prms

cross apply dbo.ufn_Tally2(1, prms.cnt, 1)

), c1 as (

select pt.Room, ai.strt, ai.finish, fits = count(*)

, grp = row_number() over (partition by pt.Room order by ai.strt) - row_number() over (partition by pt.Room, count(*) order by ai.strt)

from all_intvls ai

join @PersonTime pt on pt.Coming < ai.finish and ai.strt < pt.Going

group by pt.Room, ai.strt, ai.finish

)

select Room, Start= min(strt), [End] = max(finish), [Count] = max(fits)

from c1

group by Room, grp

order by Room, min(strt)

Here ufn_Tally2() is a tally constructor, kind of

CREATE FUNCTION [dbo].[ufn_Tally2](

@pStartValue bigint= 1,

@pEndValue bigint= 1000000,

@pIncrement bigint= 1

)

RETURNS TABLE

AS

return(

with L0(N) as (

select top(100) null from sys.all_objects

),L1(N) as (-- 10**2

select null

from L0 a1

cross join L0 a2

),L2(N) as (-- 10**2**2

select null

from L1 a1

cross join L1 a2

)

select top ((abs(case when @pStartValue < @pEndValue

then @pEndValue

else @pStartValue

end -

case when @pStartValue < @pEndValue

then @pStartValue

else @pEndValue

end))/abs(@pIncrement) + 1)

N = @pStartValue + @pIncrement *(row_number() over(order by a1.N)-1)

from L2 a1

cross join L2 a2

);

ot 60857 SSC Rookie Points: 26 More actions · Answer 2

Hello serg-52,

thank you for your answer.

It looks ... strange und i don't understand the algorithm at the moment, but the result for room 24 is wrong.

ot 60857 SSC Rookie Points: 26 More actions · Answer 3

This is my solution.

SET DATEFORMAT mdy;

DECLARE @PersonTime TABLE

(

ID INT,

Person INT,

Room INT,

Coming DATETIME,

Going DATETIME

);

INSERT INTO @PersonTime

SELECT 1, 1001, 23, '10/13/2014 8:00:00', '10/13/2014 9:00:00' UNION ALL

SELECT 2, 1002, 23, '10/13/2014 8:05:00', '10/13/2014 9:05:00' UNION ALL

SELECT 3, 2001, 24, '10/13/2014 8:00:00', '10/13/2014 9:00:00' UNION ALL

SELECT 4, 2002, 24, '10/13/2014 9:30:00', '10/13/2014 10:00:00' UNION ALL

SELECT 5, 3001, 25, '10/13/2014 8:00:00', '10/13/2014 10:00:00' UNION ALL

SELECT 6, 3002, 25, '10/13/2014 8:30:00', '10/13/2014 09:30:00' UNION ALL

SELECT 7, 4001, 26, '10/13/2014 8:00:00', '10/13/2014 10:00:00' UNION ALL

SELECT 8, 4002, 26, '10/13/2014 8:00:00', '10/13/2014 10:00:00';

;WITH t1 AS

(

SELECT

times.Room,

times.Coming Zeit

FROM @PersonTime times

UNION

SELECT

times.Room,

times.Going

FROM @PersonTime times

),

t2 AS

(

SELECT

ROW_NUMBER() OVER(PARTITION BY Room ORDER BY Zeit) RowNr,

t.Room,

t.Zeit

FROM t1 t

),

t3 AS

(

SELECT

t2.Room,

t2.Zeit Coming,

t.Zeit Going

FROM t2

INNER JOIN t2 t ON t.Room = t2.Room AND t.RowNr = t2.RowNr + 1

),

t4 AS

(

SELECT

t3.Room,

t3.Coming,

t3.Going,

(SELECT COUNT(*)

FROM @PersonTime times

WHERE times.Room = t3.Room AND times.Coming <= t3.Coming AND times.Going >= t3.Going) Number

FROM t3

)

SELECT

t.Room,

t.Coming,

t.Going,

t.Number

FROM t4 t

WHERE t.Number > 0

ORDER BY t.Room, t.Coming

serg-52 SSCrazy Eights Points: 9913 More actions · Answer 4

ot 60857, for

INSERT INTO @PersonTime

SELECT 1, 1001, 23, '10/13/2014 8:00:00', '10/13/2014 8:30:00' UNION ALL

SELECT 11, 1011, 23, '10/13/2014 8:30:00', '10/13/2014 9:00:00'

your solution gives

232014-10-13 08:00:00.0002014-10-13 08:30:00.0001

232014-10-13 08:30:00.0002014-10-13 09:00:00.0001

Is it correct ? If you need it as a single interval try

SET DATEFORMAT mdy;

DECLARE @PersonTime TABLE

(

ID INT,

Person INT,

Room INT,

Coming DATETIME,

Going DATETIME

);

INSERT INTO @PersonTime

SELECT 1, 1001, 23, '10/13/2014 8:00:00', '10/13/2014 8:30:00' UNION ALL

SELECT 11, 1011, 23, '10/13/2014 8:30:00', '10/13/2014 9:00:00' UNION ALL

SELECT 2, 1002, 23, '10/13/2014 8:05:00', '10/13/2014 9:05:00' UNION ALL

SELECT 3, 2001, 24, '10/13/2014 8:00:00', '10/13/2014 9:00:00' UNION ALL

SELECT 4, 2002, 24, '10/13/2014 9:30:00', '10/13/2014 10:00:00' UNION ALL

SELECT 5, 3001, 25, '10/13/2014 8:00:00', '10/13/2014 10:00:00' UNION ALL

SELECT 6, 3002, 25, '10/13/2014 8:30:00', '10/13/2014 09:30:00' UNION ALL

SELECT 7, 4001, 26, '10/13/2014 8:00:00', '10/13/2014 10:00:00' UNION ALL

SELECT 8, 4002, 26, '10/13/2014 8:00:00', '10/13/2014 10:00:00'

;

declare @interval int = 5;

with prms as (

select room, start = min(Coming), cnt = datediff(minute, min(Coming), max (Going)) / @interval

from @PersonTime

group by room

), all_intvls as (

select Room

, strt = dateadd(minute, (N-1)*@interval, prms.Start)

, finish = dateadd(minute, N*@interval, prms.Start)

from prms

cross apply dbo.ufn_Tally2(1, prms.cnt, 1)

), c1 as (

select ai.Room, ai.strt, ai.finish, [count] = count(pt.Room)

, grp = row_number() over (partition by ai.Room order by ai.strt) - row_number() over (partition by ai.Room, count(pt.Room) order by ai.strt)

from all_intvls ai

left join @PersonTime pt on ai.room = pt.room and pt.Coming < ai.finish and ai.strt < pt.Going

group by ai.Room, ai.strt, ai.finish

)

select Room, Start= min(strt), [End] = max(finish), [count]

from c1

group by Room, [count], grp

--where [count] > 0

order by Room, min(strt)

The idea is for every room enumerate all 'elementary' intervals and then group them by lasting count of persons.