Finding datetime range intersections and durations
-
I need to find inclusive datetime ranges to calculate time-in-state durations. Exclusive is fairly simple. Any help on finding the combinations listed below would be much appreciated!
/*
Exclusive durations (below handles this)
P1 = 1 hr 1-2
P2 = 2 hr 1-3
P3 = 3 hr 1-4
Inclusive durations (HELP!)
P1 = 0 hr in
P2 = 0 hr in
P3 = 1 hr in 3-4
P1&P2 = 1 hr 1-2
P1&P3 = 1 hr 1-2
P2&P3 = 2 hr 1-3
P1&P2&P3 = 1 hr 1-2
*/
DECLARE @History table (Datetime datetime,Tagname varchar(256), Value real)
INSERT INTO @History (Datetime,Tagname,Value)
VALUES ('2004-10-01 01:00:00.000', 'P1',1)
INSERT INTO @History (Datetime,Tagname,Value)
VALUES ('2004-10-01 01:00:00.000', 'P2',1)
INSERT INTO @History (Datetime,Tagname,Value)
VALUES ('2004-10-01 01:00:00.000', 'P3',1)
INSERT INTO @History (Datetime,Tagname,Value)
VALUES ('2004-10-01 02:00:00.000', 'P1',0)
INSERT INTO @History (Datetime,Tagname,Value)
VALUES ('2004-10-01 03:00:00.000', 'P2',0)
INSERT INTO @History (Datetime,Tagname,Value)
VALUES ('2004-10-01 04:00:00.000', 'P3',0)
DECLARE @T2 table (StartTime datetime,EndTime datetime,Tagname varchar(256),Dur real)
INSERT INTO @T2
SELECT t3.Datetime AS StartTime,t3.NextDatetime AS EndTime,t3.Tagname,Datediff(hh,t3.Datetime,t3.NextDatetime) AS 'Dur'
FROM (
SELECT t1.Tagname , t1.Datetime , t1.Value , MIN( t2.Datetime )
FROM @History AS t1
JOIN @History AS t2
ON t1.Tagname = t2.TagName
WHERE t2.Datetime > t1.Datetime and t2.Value = 0
GROUP BY t1.Tagname , t1.Datetime , t1.Value
) AS t3 ( tagname , Datetime , value , NextDateTime )
WHERE t3.value = 1
GROUP BY t3.Tagname, t3.Value, t3.Datetime, t3.NextDatetime
ORDER BY t3.Datetime ASC
SELECT * From @T2
-- Now I need inclusive ranges and durations.
bg
-
DECLARE @hours TABLE (Tagname varchar(256), H int)
insert into @hours
select c.Tagname,v.number
from (
select a.Tagname,datepart(hour,a.[Datetime]) as [H], min(datepart(hour,b.[Datetime])) as [H2]
from @History a
inner join @History b
on b.Tagname = a.Tagname and b.[Datetime] > a.[Datetime]
group by a.Tagname,a.[Datetime]
) c
inner join master.dbo.spt_values v on v.type = 'P' and v.number between c.H and c.H2
DECLARE @tag TABLE (Tags varchar(256), tagname varchar(256), CT int)
DECLARE @upd int
insert into @tag
select a.tagname+'&'+b.tagname,b.tagname,2
from (
select distinct tagname
from @History
where tagname < (select max(tagname) from @History)
) a
inner join (select distinct tagname from @History) b
on b.tagname > a.tagname
select @upd = @@rowcount
while (@upd > 0)
begin
insert into @tag
select t.tags+'&'+h.tagname,h.tagname,CT + 1
from @tag t
inner join (select distinct tagname from @History) h
on h.tagname > t.tagname
where not exists(select 1 from @tag x where x.tags = (t.tags+'&'+h.tagname))
select @upd = @@rowcount
end
select tags,count(*)-1 as [Duration]
from (
select t.tags,t.CT,h.H
from @tag t
inner join @hours h
on charindex('&'+h.tagname+'&','&'+t.tags+'&')>0
group by t.tags,t.CT,h.H
having t.CT = count(*)
) a
group by tags
Far away is close at hand in the images of elsewhere.
Anon. - Thanks for the reply David.I think I understand your technique in the insert into @hours portion, but I need precision to at least seconds, better would be milliseconds. Also, the datetime ranges can span from any time to any time, crossing multiple dates.Can this method still be used to get to a higher precision? If so, how?Is a custom table to replace the join to master.dbo.spt_values required?Would such a table have to contain a table which has a sequential datetime number, e.g., 20041001000000001,20041001000000002,20041001000000003... etc?Or am I missing it?
-
Sorry that it did not help, since you query used whole hours for duration I continued that in my solution
Did not realise that the dates came into it as well
This will need more careful thought.
I assume your question is to find the total common (where time matches) time (hours, mins, secs & ms?) for each combination of tag.
Far away is close at hand in the images of elsewhere.
Anon. - Yes, dates must be included. Sorry for the confusion.And yes, I need the total common time for each combination (intersection) i.e., ...P1&P2, P1&P3, P2&P3, P1&P2&P3AND the exclusive time for each individual tag, i.e., ...P1 only, P2 only, P3 only.Thanks a ton for the help.
-
Use a between in the JOIN and CASE for result columns
SELECT CASE a.begin_date< b.begin_date THEN b.begin_date ELSE a.begin_date END AS common_from_date
,CASE a.end_date<b.end_date THEN a.end_date ELSE b.end_date END AS common_to_date
FROM tbl1 a
JOIN tbl1 b ON a.begindate between b.begin_date and b.end_date
OR a.end_date between b.begin_date and b.end_date
Then you can embed this query and join to it using datefunctions for calculation.
-
Still thinking about this but all I could up with so far is to put all the dates in order (start & end), work out the date segments and then match this with the start & end dates to fine commonality.
Very interesting problem
Far away is close at hand in the images of elsewhere.
Anon. -
I'll help a little bit with the URL
http://www.cs.arizona.edu/people/rts/tdbbook.pdf and
http://www.cs.arizona.edu/people/rts/cdrom.zip
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply