Time utilization from date ranges with gaps

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Time utilization from date ranges with gaps

Author

Topic

quarter
Starting Member

8 Posts

Posted - 2006-05-19 : 14:29:41

Hi. I've been working on a set based solution to finding time in date range series with gaps. I have a loop based solution and can post that if anyone wants.

beginning ending
----------------------- -----------------------
2004-12-20 08:00:00.000 2004-12-20 10:00:00.000
2004-12-20 12:00:00.000 2004-12-20 14:30:00.000
2004-12-20 04:00:00.000 2004-12-20 09:00:00.000

What I am seeking to do here is get the time spans calculated from 0400 to 1000 and 1200 to 1430 for a total of 8.5 hours. I.e. no double counting of the time between 0800 and 1000 and no count of the time from 1000 to 1200.

What I have so far is not pretty, but will give you an idea where im at:

SELECT t0.beginning, t1.ending
FROM (SELECT * FROM dbo.vw_1) t0
CROSS JOIN (SELECT * FROM dbo.vw_1) t1
WHERE t0.beginning < dateadd(hh,23,@date) AND t0.ending > @date
AND t0.beginning > t1.ending

@date is not that important atm, except that the query will eventually do this for days. @date will be a reference to a date in an outer table.

For now assume all the data is for the same day.

SELECT t0.beginning, t1.ending
FROM (SELECT * FROM dbo.vw_1) t0
CROSS JOIN (SELECT * FROM dbo.vw_1) t1
WHERE t0.beginning < t1.ending

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-05-19 : 17:09:52

[code]declare @dates table
(
id int identity(0, 1),
beginning smalldatetime,
ending smalldatetime
)

insert into @dates
(
beginning,
ending
)
select distinct beginning,
ending
from table1 -- this is your original table
order by beginning,
ending

select convert(smalldatetime, floor(cast(d1.beginning as smallmoney))) theDate,
sum(case when d1.ending < d2.beginning or d2.id is null then datediff(mi, d1.beginning, d1.ending) / 60.0 else datediff(mi, d1.beginning, d2.beginning) / 60.0 end) theHours
from @dates d1
left join (
select id,
beginning
from @dates
) d2 on d1.id = d2.id - 1
group by convert(smalldatetime, floor(cast(d1.beginning as smallmoney)))
order by convert(smalldatetime, floor(cast(d1.beginning as smallmoney))) desc[/code]

quarter
Starting Member

8 Posts

Posted - 2006-05-19 : 20:57:16

Peso, thanks for your help. The join on the -1 if most interesting and I may be albe to use that in the future.

However, for now the query does not produce enough hours. I've disected it and am working from what you provided and a cross join to find the result.

bout to hit a brick wall on this, but i'll post more if i get any better solution.

quarter
Starting Member

8 Posts

Posted - 2006-05-19 : 21:10:45

I did find the below which produces the exact opposite of what I'm looking for. Also, currently in the process of attempting to simplify and 'reverse' engineer the below:

----------------------
DECLARE @table TABLE(beginning DATETIME, ending DATETIME)

INSERT @table(beginning, ending) VALUES('2005-07-12 11:45:00.000', '2005-07-12 12:30:00.000')--
INSERT @table(beginning, ending) VALUES('2005-07-12 05:45:00.000', '2005-07-12 06:30:00.000')
INSERT @table(beginning, ending) VALUES('2005-07-12 05:45:00.000', '2005-07-12 06:30:00.000')--
INSERT @table(beginning, ending) VALUES('2005-07-12 08:45:00.000', '2005-07-12 10:30:00.000')--
INSERT @table(beginning, ending) VALUES('2005-07-12 09:15:00.000', '2005-07-12 09:30:00.000')
INSERT @table(beginning, ending) VALUES('2005-07-12 05:45:00.000', '2005-07-12 05:30:00.000')
INSERT @table(beginning, ending) VALUES('2005-07-12 05:30:00.000', '2005-07-12 05:50:00.000')--
INSERT @table(beginning, ending) VALUES('2005-07-12 15:45:00.000', '2005-07-12 16:30:00.000')--

SELECT e.ending AS Gap_start, s.beginning AS Gap_end
FROM (SELECT ending
FROM @table AS a1
WHERE NOT EXISTS
(SELECT *
FROM @table AS a2
WHERE a2.beginning <= a1.ending
AND a2.ending > a1.ending)) AS e
INNER JOIN (SELECT beginning
FROM @table AS b1
WHERE NOT EXISTS
(SELECT *
FROM @table AS b2
WHERE b2.beginning < b1.beginning
AND b2.ending >= b1.beginning)) AS s
ON s.beginning > e.ending
WHERE NOT EXISTS
(SELECT *
FROM @table AS btwn
WHERE btwn.beginning > e.ending
AND btwn.ending < s.beginning)

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-05-20 : 00:48:15

This might helps http://www.mindsdoor.net/SQLTsql/FindGapsInSequence.html

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-05-22 : 06:28:53

Hi all,

quarter - I don't know if you managed to solve this, but here's one way to try (assuming I understand what you're asking).

The method is to create a table of all the minutes present, add the boundary points, and then find the gaps using a query based on the link khtan provided.

--data
DECLARE @table TABLE(beginning DATETIME, ending DATETIME)

INSERT @table(beginning, ending) VALUES('2005-07-12 11:45:00.000', '2005-07-12 12:30:00.000')--
INSERT @table(beginning, ending) VALUES('2005-07-12 05:45:00.000', '2005-07-12 06:30:00.000')
INSERT @table(beginning, ending) VALUES('2005-07-12 05:45:00.000', '2005-07-12 06:30:00.000')--
INSERT @table(beginning, ending) VALUES('2005-07-12 08:45:00.000', '2005-07-12 10:30:00.000')--
INSERT @table(beginning, ending) VALUES('2005-07-12 09:15:00.000', '2005-07-12 09:30:00.000')
INSERT @table(beginning, ending) VALUES('2005-07-12 05:45:00.000', '2005-07-12 05:30:00.000')
INSERT @table(beginning, ending) VALUES('2005-07-12 05:30:00.000', '2005-07-12 05:50:00.000')--
INSERT @table(beginning, ending) VALUES('2005-07-12 15:45:00.000', '2005-07-12 16:30:00.000')--

--input
declare @date datetime
set @date = '2005-07-12'

--calculation
declare @numbers table (i int identity(0, 1), x bit)
insert @numbers select top 1440 null from master.dbo.syscolumns a, master.dbo.syscolumns b

declare @minutes table (d datetime)
insert @minutes --minutes present
select dateadd(minute, i, @date)
from @numbers
    inner join @table t on dateadd(minute, i, @date) between beginning and ending
where i between 4*60 and 10*60 or i between 12*60 and 14.5*60

insert @minutes --boundary points
          select dateadd(hour, 4, @date)
union all select dateadd(hour, 10, @date)
union all select dateadd(hour, 12, @date)
union all select dateadd(minute, 14.5*60, @date)

select distinct t1.d as beginning, t3.d as ending
from @minutes t1
    left join @minutes t2 on t1.d = dateadd(minute, -1, t2.d)
    inner join @minutes t3 on t3.d = (select min(t4.d) from @minutes t4 where t4.d > t1.d)
where t2.d is null and not t1.d = dateadd(hour, 10, @date)
order by t1.d

--results
beginning                                              ending                                                 
------------------------------------------------------ ------------------------------------------------------ 
2005-12-07 04:00:00.000                                2005-12-07 05:30:00.000
2005-12-07 06:30:00.000                                2005-12-07 08:45:00.000
2005-12-07 12:30:00.000                                2005-12-07 14:30:00.000

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-05-22 : 12:54:37

Here is a slightly different solution, producing all occupied timeslots.
It is very easy to fetch all timeslots that are NOT occupied just by changing
"where m not in (" to "where m in (".


------------------------------------------------------------------------
DECLARE @table TABLE (beginning DATETIME, ending DATETIME) 

INSERT @table(beginning, ending) VALUES('2005-07-12 11:45:00.000', '2005-07-12 12:30:00.000') 
INSERT @table(beginning, ending) VALUES('2005-07-12 05:45:00.000', '2005-07-12 06:30:00.000') 
INSERT @table(beginning, ending) VALUES('2005-07-12 05:45:00.000', '2005-07-12 06:30:00.000') 
INSERT @table(beginning, ending) VALUES('2005-07-12 08:45:00.000', '2005-07-12 10:30:00.000') 
INSERT @table(beginning, ending) VALUES('2005-07-12 09:15:00.000', '2005-07-12 09:30:00.000') 
INSERT @table(beginning, ending) VALUES('2005-07-12 05:45:00.000', '2005-07-12 05:30:00.000') 
INSERT @table(beginning, ending) VALUES('2005-07-12 05:30:00.000', '2005-07-12 05:50:00.000') 
INSERT @table(beginning, ending) VALUES('2005-07-12 15:45:00.000', '2005-07-12 16:30:00.000') 
------------------------------------------------------------------------
declare @minutes table (m smallint)

insert @minutes 
select	m1.m + m2.m + m3.m m 
from	(select 0 m union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) m1,
	(select 0 m union all select 10 union all select 20 union all select 30 union all select 40 union all select 50 union all select 60 union all select 70 union all select 80 union all select 90 union all select 100 union all select 110) m2,
	(select 0 m union all select 120 union all select 240 union all select 360 union all select 480 union all select 600 union all select 720 union all select 840 union all select 960 union all select 1080 union all select 1200 union all select 1320) m3

delete @minutes
where m not in (
select m.m
from	@minutes m,
	@table t
where	m.m between datediff(mi, 0, t.beginning) % 1440 and datediff(mi, 0, t.ending) % 1440 )

declare @basedate datetime

select @basedate= datediff(dd, 0, min(beginning)) from @table

declare @result table (beginning datetime, ending datetime, hours smallmoney)
insert @result

SELECT    dateadd(mi, m,@basedate) FromDateTime ,
(
SELECT   dateadd(mi, min(m), @basedate) 
FROM @minutes A 
WHERE NOT EXISTS (SELECT B.m FROM @minutes B WHERE A.m + 1 = B.m ) and a.m > c.m
 ) ToDateTime

FROM @minutes C
WHERE NOT EXISTS (SELECT B.m FROM @minutes B WHERE C.m - 1 = B.m) 
order by m

update @result set hours = datediff(mi, beginning, ending) / 60.0

select * from @result
-------------------------------------------------------------------------
with the testdata above, the result is

2005-07-12 05:30:00.000   2005-07-12 06:30:00.000   1.0000
2005-07-12 08:45:00.000   2005-07-12 10:30:00.000   1.7500
2005-07-12 11:45:00.000   2005-07-12 12:30:00.000    .7500
2005-07-12 15:45:00.000   2005-07-12 16:30:00.000    .7500

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-05-22 : 13:18:54

quote:
Originally posted by quarter

INSERT @table(beginning, ending) VALUES('2005-07-12 05:45:00.000', '2005-07-12 05:30:00.000')

Is this a valid combination of beginning and ending time?

quarter
Starting Member

8 Posts

Posted - 2006-05-22 : 15:02:41

Thank you for your replies!!! Presently trying these suggestions.

Peso, thankfully, I don't need to deal with any negative times. Just put that one in for my tests to make sure how it was handled.

quarter
Starting Member

8 Posts

Posted - 2006-05-22 : 17:54:50

Whoo Hoo!!
With all your help I got very good one.

Peso, your query produces the exact results I was looking for. Thank you for taking the time to produce this query. However, I did not understand the use of unions in your query and so I played around with it more.

It did not occur to me to use a tally table for this. With all of your ideas I was able to come up with the following (its real short too!):

-----------------------

declare @numbers table (i int identity(0, 1), x bit)
insert @numbers select top 1440 null from master.dbo.syscolumns a, master.dbo.syscolumns b

DECLARE @table TABLE(beginning DATETIME, ending DATETIME)

INSERT @table(beginning, ending) VALUES('2005-07-12 11:45:00.000', '2005-07-12 12:30:00.000')--
INSERT @table(beginning, ending) VALUES('2005-07-12 05:45:00.000', '2005-07-12 06:30:00.000')
INSERT @table(beginning, ending) VALUES('2005-07-12 05:45:00.000', '2005-07-12 06:30:00.000')--
INSERT @table(beginning, ending) VALUES('2005-07-12 08:45:00.000', '2005-07-12 10:30:00.000')--
INSERT @table(beginning, ending) VALUES('2005-07-12 09:15:00.000', '2005-07-12 09:30:00.000')
INSERT @table(beginning, ending) VALUES('2005-07-12 05:45:00.000', '2005-07-12 05:30:00.000')
INSERT @table(beginning, ending) VALUES('2005-07-12 05:30:00.000', '2005-07-12 05:50:00.000')--
INSERT @table(beginning, ending) VALUES('2005-07-12 15:45:00.000', '2005-07-12 16:30:00.000')--

-- 5:30 - 6:30 1 hour
-- 8:45 - 10:30 1.75 hour
-- 11:45 - 12:30 .75 hour
-- 15:45 - 16:30 .75 hour
-- total 4.25 hours = 255 mins

SELECT COUNT(DISTINCT n1.i)
FROM @table t0
INNER JOIN @numbers n1 ON n1.i > DATEPART(hh, t0.beginning) * 60 + DATEPART(mi, t0.beginning)
AND n1.i <= DATEPART(hh, t0.ending) * 60 + DATEPART(mi, t0.ending)

------------------

The query gets the minute numbers from the tally table and adds up the number of minutes used in a day with the count distinct. It is necessary to have one of the conditions to not be an = or I got an extra minute for each time span.

This technique will only work if the time data being processed is all for the same day.

I still believe some kind of cross join or correlated query will produce the same result, but probably not any faster for my purposes using a tally table.

If I come accross or come up with any such query that would be better for multiple day time spans, I will post it here.

Thanks again for your help. This is my first time to post here and I hope to be able to help someone else in the future as well.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-05-23 : 00:50:35

If you only want the number of minutes occupied during a day, there is no need for temporary tables and table variables. Then you can accomplish the task in one single query. Sorry if I have misunderstood your original posting.

Try this!


-----------------------------------------------------------------------
select     count(distinct n.m)
from       @table t
inner join (
              select m1.m + m2.m + m3.m m
              from   (select 0 m union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) m1,
                     (select 0 m union all select 10 union all select 20 union all select 30 union all select 40 union all select 50 union all select 60 union all select 70 union all select 80 union all select 90 union all select 100 union all select 110) m2,
                     (select 0 m union all select 120 union all select 240 union all select 360 union all select 480 union all select 600 union all select 720 union all select 840 union all select 960 union all select 1080 union all select 1200 union all select 1320) m3
           ) n ON n.m >= DATEDIFF(mi, 0, t.beginning) % 1440 AND n.m < DATEDIFF(mi, 0, t.ending) % 1440
option (force order)   -- This is not always necessary,
                       -- it depends on the version of SQL Server you're using
-----------------------------------------------------------------------

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-05-23 : 00:56:58

I will try to explain the cross joins/unions for you.

The cross joins combines all possible combinations (cartesian product) from three vectors.

Vector m1 contains {0, 1, 2, 3, 4, 5, 6, 7, 8, 9}.
Vector m2 contains {0, 10, 20, 30, 40, 50, 60, 70, 80, 90, 100, 110}
Vector m3 contains {0, 120, 240, 360, 480, 600, 720, 840, 960, 1080, 1200, 1320}.

Try to run only the cross joins and unions as below. I have added an ORDER BY just for clarification!
----------------------------------------------------------------


select m3.m 'm3.m', m2.m 'm2.m', m1.m 'm1.m', m1.m + m2.m + m3.m m
from (select 0 m union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) m1,
(select 0 m union all select 10 union all select 20 union all select 30 union all select 40 union all select 50 union all select 60 union all select 70 union all select 80 union all select 90 union all select 100 union all select 110) m2,
(select 0 m union all select 120 union all select 240 union all select 360 union all select 480 union all select 600 union all select 720 union all select 840 union all select 960 union all select 1080 union all select 1200 union all select 1320) m3
order by m1.m + m2.m + m3.m

I hope this explanation transformed your question mark into an exclamation mark.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-05-23 : 13:44:15

Here is a function that takes two dates as arguments; @FromDate and @ToDate.
The function will return a resultset with all present dates in the source table "table1" and the minutes for each day that are "taken". Enjoy!
---------------------------------------------------------------------------------


CREATE FUNCTION dbo.GetSlots
(
	@FromDate DATETIME,
	@ToDate DATETIME
)
RETURNS @Values TABLE (theDate DATETIME, theMinutes SMALLINT)
AS

BEGIN

DECLARE @Minutes TABLE (mi SMALLINT)

INSERT @Minutes 
SELECT m1.m + m2.m + m3.m 
FROM (SELECT 0 m UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) m1,
(SELECT 0 m UNION ALL SELECT 10 UNION ALL SELECT 20 UNION ALL SELECT 30 UNION ALL SELECT 40 UNION ALL SELECT 50 UNION ALL SELECT 60 UNION ALL SELECT 70 UNION ALL SELECT 80 UNION ALL SELECT 90 UNION ALL SELECT 100 UNION ALL SELECT 110) m2,
(SELECT 0 m UNION ALL SELECT 120 UNION ALL SELECT 240 UNION ALL SELECT 360 UNION ALL SELECT 480 UNION ALL SELECT 600 UNION ALL SELECT 720 UNION ALL SELECT 840 UNION ALL SELECT 960 UNION ALL SELECT 1080 UNION ALL SELECT 1200 UNION ALL SELECT 1320) m3

DECLARE @Dates TABLE (dd DATETIME)

INSERT @Dates
SELECT DISTINCT d.dd
FROM	(
		SELECT CONVERT(VARCHAR(8), Beginning, 112) dd from table1
		UNION ALL
		SELECT CONVERT(VARCHAR(8), Ending, 112) from table1
	) d
WHERE  d.dd BETWEEN CONVERT(VARCHAR(8), @FromDate, 112) AND CONVERT(VARCHAR(8), @ToDate, 112)

DECLARE @Range TABLE (ddmi DATETIME)

INSERT @Range
SELECT DATEADD(mi, m.mi, d.dd)
FROM   @Dates d,
	@Minutes m

DELETE @Range
FROM	@Range, Table1
WHERE ddmi >= Beginning AND ddmi < Ending

insert @values
select convert(varchar(8), ddmi, 112),
	1440 - count(*)
from	@range
group by convert(varchar(8), ddmi, 112)
having count(*) <> 1440
order by convert(varchar(8), ddmi, 112)

RETURN

END

quarter
Starting Member

8 Posts

Posted - 2006-05-23 : 15:30:38

Peso,

Yes I see how the minutes table was generated by the unions and the addition !!!

Presently, I am calculating the time utilization for each day as needed. This can be up to a year with 7 different time spans for each day to calculate. So, that is over 2000 times the query would run for one request. For the data/hardaware this means the query runs in just under 1 minute.

As I understand your use of the @Range table above, your method would result in many millions of records there. And, I believe this would result in longer processing time than running once for each day from a standard number tally table.

This is why I am still on the lookout for a query that can do the calculation without the use of a tally table. Though I am not sure that the use of any cross join/corr subquery method would be faster than the use of a tally table.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-05-23 : 15:41:15

quote:
Originally posted by quarter

Peso,

Yes I see how the minutes table was generated by the unions and the addition !!!

Presently, I am calculating the time utilization for each day as needed. This can be up to a year with 7 different time spans for each day to calculate. So, that is over 2000 times the query would run for one request. For the data/hardaware this means the query runs in just under 1 minute.

As I understand your use of the @Range table above, your method would result in many millions of records there. And, I believe this would result in longer processing time than running once for each day from a standard number tally table.

This is why I am still on the lookout for a query that can do the calculation without the use of a tally table. Though I am not sure that the use of any cross join/corr subquery method would be faster than the use of a tally table.

That's the beauty of the function above. It only takes the days actually present. So if you have 366 days the range will become 366*1440 rows = 527 040, which is not much at all... Also, the function only returns the number of minutes used per day, so the result is at most 366 rows.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-06-17 : 01:10:05

I know this is an old topic,
but the solutions posted look way too complicated.

This seems like a lot simpler way to get the time,
and there is no tally table to deal with.

It also works for multiple days.



-- Load Time Ranges
declare @range table (RangeNo int,fr datetime, tr datetime )
insert into @range
select RangeNo = 1,fr= '04:00:00.000', tr='10:00:00.000' union all
select RangeNo = 2,fr= '12:00:00.000', tr='14:30:00.000'
order by 1


-- Load Test Data Begin and End Times
declare @data table (st datetime,et datetime, seq int identity(1,1))
insert into @data
select st= '2004-12-20 08:00:00.000', et='2004-12-20 10:00:00.000' union all
select st= '2004-12-20 12:00:00.000', et='2004-12-20 14:30:00.000' union all
select st= '2004-12-20 04:00:00.000', et='2004-12-20 09:00:00.000' union all
select st= '2004-12-21 00:00:00.000', et='2004-12-21 23:00:00.000' union all
select st= '2004-12-22 09:01:01.003', et='2004-12-22 13:15:23.443' union all
select st= '2004-12-23 10:00:00.000', et='2004-12-23 11:00:00.000' union all
select st= '2004-12-23 11:00:00.000', et='2004-12-23 12:00:00.000' union all
select st= '2004-12-24 00:00:00.000', et='2004-12-24 03:59:59.000'  
order by 1,2




-- Compute the time inside each range
select
	[Time in Range] =
	-- Get amout of time inside the reange
	right(convert(varchar(30),
	case when a.ett > b.tr then b.tr else a.ett end-
	case when a.sst < b.fr then b.fr else a.sst end, 121),12),

	[Begin Time] = convert(varchar(23),a.st,121),
	[End Time] = convert(varchar(23),a.et,121),

	[Range Start] = right(convert(varchar(23),b.fr,121),12),
	[Range End] =   right(convert(varchar(23),b.tr,121),12),
	Range = b.RangeNo
from
	(
	select
		st,et,seq,
		-- Get time only part of begin time
		sst=st-dateadd(dd,datediff(dd,0,st),0) ,
		-- Get time only part of end time
		ett=et-dateadd(dd,datediff(dd,0,st),0) 
	from
		@data
	) a
	join
	@range b
	on	( a.sst >= b.fr and a.sst < b.tr )  or
		( a.ett > b.fr and a.ett <= b.tr )  or
		( a.sst < b.fr and a.ett > b.tr )
order by
	a.Seq,
	b.RangeNo

Results:


Time in Range Begin Time              End Time                Range Start  Range End    Range       
------------- ----------------------- ----------------------- ------------ ------------ ----------- 
05:00:00.000  2004-12-20 04:00:00.000 2004-12-20 09:00:00.000 04:00:00.000 10:00:00.000 1
02:00:00.000  2004-12-20 08:00:00.000 2004-12-20 10:00:00.000 04:00:00.000 10:00:00.000 1
02:30:00.000  2004-12-20 12:00:00.000 2004-12-20 14:30:00.000 12:00:00.000 14:30:00.000 2
06:00:00.000  2004-12-21 00:00:00.000 2004-12-21 23:00:00.000 04:00:00.000 10:00:00.000 1
02:30:00.000  2004-12-21 00:00:00.000 2004-12-21 23:00:00.000 12:00:00.000 14:30:00.000 2
00:58:58.997  2004-12-22 09:01:01.003 2004-12-22 13:15:23.443 04:00:00.000 10:00:00.000 1
01:15:23.443  2004-12-22 09:01:01.003 2004-12-22 13:15:23.443 12:00:00.000 14:30:00.000 2

(7 row(s) affected)

CODO ERGO SUM

quarter
Starting Member

8 Posts

Posted - 2006-07-07 : 21:22:01

Michael,

Thank you, your post teaches me ideas about ranges. Also, the case inside the convert is pretty cute.

However, your post will not sort out time utilization which is the main requirement of this topic: i.e. no double counting of time, max of 24 hours in a day.

Personally, I don't have a problem with the use of a tally table since the databases i work with all have a table called number that contains, well, rows of sequential numbers. Peso, the unions are interesting but I would never use them to make a tally table.

For the time being, I still consider the below to be the best solution (faster, shorter code, works in nested selects):

------------------------

declare @numbers table (i int identity(0, 1), x bit)
insert @numbers select top 1440 null from master.dbo.syscolumns a, master.dbo.syscolumns b

DECLARE @table TABLE(beginning DATETIME, ending DATETIME)

INSERT @table(beginning, ending) VALUES('2005-07-12 11:45:00.000', '2005-07-12 12:30:00.000')--
INSERT @table(beginning, ending) VALUES('2005-07-12 05:45:00.000', '2005-07-12 06:30:00.000')
INSERT @table(beginning, ending) VALUES('2005-07-12 05:45:00.000', '2005-07-12 06:30:00.000')--
INSERT @table(beginning, ending) VALUES('2005-07-12 08:45:00.000', '2005-07-12 10:30:00.000')--
INSERT @table(beginning, ending) VALUES('2005-07-12 09:15:00.000', '2005-07-12 09:30:00.000')
INSERT @table(beginning, ending) VALUES('2005-07-12 05:45:00.000', '2005-07-12 05:30:00.000')
INSERT @table(beginning, ending) VALUES('2005-07-12 05:30:00.000', '2005-07-12 05:50:00.000')--
INSERT @table(beginning, ending) VALUES('2005-07-12 15:45:00.000', '2005-07-12 16:30:00.000')--

-- 5:30 - 6:30 1 hour
-- 8:45 - 10:30 1.75 hour
-- 11:45 - 12:30 .75 hour
-- 15:45 - 16:30 .75 hour
-- total 4.25 hours = 255 mins

SELECT COUNT(DISTINCT n1.i)
FROM @table t0
INNER JOIN @numbers n1 ON n1.i > DATEPART(hh, t0.beginning) * 60 + DATEPART(mi, t0.beginning)
AND n1.i <= DATEPART(hh, t0.ending) * 60 + DATEPART(mi, t0.ending)

------------------

However, I am still hoping to find a better solution (faster) that would not need a tally table or the minute count, while keeping it short enough to be easily used in nested select statements.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-07-08 : 00:19:09

quarter,

You seem to have skipped some of the requirements
thst you posted in your first post:
To only count time ranges from 0400 to 1000 and 1200 to 1430.
That makes a big difference.

For example:
5:30 - 6:30 should be 1.00 hours, OK
8:45 - 10:30 should be 1.25 hours (8:45 to 10:00), not 1.75
11:45 - 12:30 should be 0.50 hours (12:00 to 12:30), not 0.75
15:45 - 16:30 should be 0.00 hours, not 0.75
Correct Total: 2.75 hours

I didn't notice your requirement about overlaps in the first post.
However, what I did is easy to adapt to that.
This is the solution with your latest set of test data,
producing the correct answer of 2.75 hours.

Note: For the number table,
I used function F_TABLE_NUMBER_RANGE from this link.
Very handy to have, because it is fast,
and can generate any range of numbers
you select for up to 16,777,216 numbers.
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47685


-- Load Time Ranges
declare @range table (RangeNo int,fr datetime, tr datetime )
insert into @range
select RangeNo = 1,fr= '04:00:00.000', tr='10:00:00.000' union all
select RangeNo = 2,fr= '12:00:00.000', tr='14:30:00.000'
order by 1


-- Load Test Data Begin and End Times
declare @data table (st datetime,et datetime, seq int identity(1,1))
insert into @data
select st= '2005-07-12 11:45:00.000', et= '2005-07-12 12:30:00.000'  union all
select st= '2005-07-12 05:45:00.000', et= '2005-07-12 06:30:00.000'  union all
select st= '2005-07-12 05:45:00.000', et= '2005-07-12 06:30:00.000'  union all
select st= '2005-07-12 08:45:00.000', et= '2005-07-12 10:30:00.000'  union all
select st= '2005-07-12 09:15:00.000', et= '2005-07-12 09:30:00.000'  union all
select st= '2005-07-12 05:30:00.000', et= '2005-07-12 05:45:00.000'  union all
select st= '2005-07-12 05:30:00.000', et= '2005-07-12 05:50:00.000'  union all
select st= '2005-07-12 15:45:00.000', et= '2005-07-12 16:30:00.000'
order by 1,2

-- Calculate hours
select
	[Hours] = 
	convert(numeric(10,2),round(count(distinct a.NUMBER)/60.0000,2))
from
	-- Function available in script library forum
	dbo.F_TABLE_NUMBER_RANGE(0,1439) a
	join
	(
	-- Compute the time inside each range
	select
		START_MINUTE = 
		datediff(mi,0,case when a.sst < b.fr then b.fr else a.sst end),
		END_MINUTE =
		datediff(mi,0,case when a.ett > b.tr then b.tr else a.ett end)
	from
		(
		select
			-- Get time only part of begin time
			sst=st-dateadd(dd,datediff(dd,0,st),0) ,
			-- Get time only part of end time
			ett=et-dateadd(dd,datediff(dd,0,st),0) 
		from
			@data
		) a
		join
		@range b
		on	( a.sst >= b.fr and a.sst < b.tr )  or
			( a.ett > b.fr and a.ett <= b.tr )  or
			( a.sst < b.fr and a.ett > b.tr )
	) t
	on
	a.NUMBER >= t.START_MINUTE and 
	a.NUMBER <  t.END_MINUTE

Results:


(2 row(s) affected)


(8 row(s) affected)

Hours        
------------ 
2.75

(1 row(s) affected)

CODO ERGO SUM

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-07-08 : 00:59:23

Here is a query without a tally table!

SELECT		SUM(t.Mins) Minutes
FROM		(
			SELECT		t0.Beginning,
					t0.Ending,
					MIN(CASE
						WHEN t0.Beginning <= t1.Beginning AND t1.Beginning <= t0.Ending AND t0.Ending <= t1.Ending THEN DATEDIFF(mi, t0.Beginning, t1.Beginning)
						WHEN t0.Beginning <= t1.Beginning AND t1.Beginning <= t1.Ending AND t1.Ending <= t0.Ending THEN DATEDIFF(mi, t0.Beginning, t0.Ending)
						WHEN t1.Beginning <= t0.Beginning AND t0.Beginning <= t1.Ending AND t1.Ending <= t0.Ending THEN DATEDIFF(mi, t0.Beginning, t0.Ending)
						WHEN t1.Beginning <= t0.Beginning AND t0.Beginning <= t0.Ending AND t0.Ending <= t1.Ending THEN 0
						ELSE DATEDIFF(mi, t0.Beginning, t0.Ending)
					END) Mins
			FROM		(
						SELECT	DISTINCT Beginning,
							Ending
						FROM	@Table
						WHERE	Beginning <= Ending
					) t0
			INNER JOIN	(
						SELECT	DISTINCT Beginning,
							Ending
						FROM	@Table
						WHERE	Beginning <= Ending
					) t1 ON t0.Beginning <> t1.Beginning AND t0.ending <> t1.ending
			GROUP BY	t0.Beginning,
					t0.Ending
		) t

Peter Larsson
Helsingborg, Sweden

EDIT: Removed two redundant rows in CASE statement

[code]SELECT		SUM(t.Mins) Minutes
FROM		(
			SELECT		t0.Beginning,
					t0.Ending,
					MIN(CASE
						WHEN t0.Beginning <= t1.Beginning AND t1.Beginning <= t0.Ending AND t0.Ending <= t1.Ending THEN DATEDIFF(mi, t0.Beginning, t1.Beginning)
						WHEN t1.Beginning <= t0.Beginning AND t0.Beginning <= t0.Ending AND t0.Ending <= t1.Ending THEN 0
						ELSE DATEDIFF(mi, t0.Beginning, t0.Ending)
					END) Mins
			FROM		(
						SELECT	DISTINCT Beginning,
							Ending
						FROM	@Table
						WHERE	Beginning <= Ending
					) t0
			INNER JOIN	(
						SELECT	DISTINCT Beginning,
							Ending
						FROM	@Table
						WHERE	Beginning <= Ending
					) t1 ON t0.Beginning <> t1.Beginning AND t0.ending <> t1.ending
			GROUP BY	t0.Beginning,
					t0.Ending
		) t

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-07-08 : 01:20:58

Michael, you know as well as I now, that sometimes there is difference between "This is what I want" and "This is what I need".

quote:
Originally posted by quarter

Whoo Hoo!!
With all your help I got very good one.

Peso, your query produces the exact results I was looking for.

And that query of mine did not comply with original posting. But after I looked at his query that "did exactly the opposite", I was clear about his needs.

Nice touch with having Ranges included also

Now it is very easy to exclude lunch hours.

Peter Larsson
Helsingborg, Sweden

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-07-08 : 03:05:07

This eliminates the tally table by using
a while loop to eliminate the range overlaps.
It still shows the time within the hour ranges.

I added a second days test data, and grouped by date.

The while loop is a pain, but it seems fast,
because it only has to run a few iterations.
Perhaps a function could by used for this part
to make it more convenient.

By eliminating the time overlaps first, the precision
of the time calculation is in milliseconds.



set nocount on
-- Load Time Ranges
declare @range table (RangeNo int,fr datetime, tr datetime )
insert into @range
select RangeNo = 1,fr= '04:00:00.000', tr='10:00:00.000' union all
select RangeNo = 2,fr= '12:00:00.000', tr='14:30:00.000'
order by 1


declare @data table (st datetime,et datetime, seq int identity(1,1))
insert into @data
select
	top 100 percent
	st,et
from
(
select st= '2005-07-12 11:45:00.000', et= '2005-07-12 12:30:00.000'  union all
select st= '2005-07-12 05:45:00.000', et= '2005-07-12 06:30:00.000'  union all
select st= '2005-07-12 05:45:00.000', et= '2005-07-12 06:30:00.000'  union all
select st= '2005-07-12 08:45:00.000', et= '2005-07-12 10:30:00.000'  union all
select st= '2005-07-12 08:45:00.000', et= '2005-07-12 10:30:00.000'  union all
select st= '2005-07-12 08:45:00.000', et= '2005-07-12 10:30:00.000'  union all
select st= '2005-07-12 09:15:00.000', et= '2005-07-12 09:30:00.000'  union all
select st= '2005-07-12 05:30:00.000', et= '2005-07-12 05:45:00.000'  union all
select st= '2005-07-12 05:30:00.000', et= '2005-07-12 05:50:00.000'  union all
select st= '2005-07-12 15:45:00.000', et= '2005-07-12 16:30:00.000'  union all

select st= '2005-07-14 11:45:00.000', et= '2005-07-14 12:30:00.000'  union all
select st= '2005-07-14 05:00:01.000', et= '2005-07-14 06:30:00.000'  union all
select st= '2005-07-14 05:45:00.000', et= '2005-07-14 06:30:00.000'  union all
select st= '2005-07-14 08:45:00.000', et= '2005-07-14 10:30:00.000'  union all
select st= '2005-07-14 08:45:00.000', et= '2005-07-14 10:30:00.000'  union all
select st= '2005-07-14 08:45:00.000', et= '2005-07-14 10:30:00.000'  union all
select st= '2005-07-14 09:15:00.000', et= '2005-07-14 09:30:00.000'  union all
select st= '2005-07-14 05:30:00.000', et= '2005-07-14 05:45:00.000'  union all
select st= '2005-07-14 05:30:00.000', et= '2005-07-14 05:50:00.000'  union all
select st= '2005-07-14 15:45:00.000', et= '2005-07-14 16:30:00.000'

) a
order by 1,2

-- Setup work table for while loop
declare @d1 table (st datetime,et datetime)
declare @rows_1 int
declare @rows_2 int
select @rows_1 = 200
select @rows_2 = -1

set nocount on
-- Use while loop to eliminate overlaped time ranges
while @rows_1 > @rows_2
	begin
	select @rows_1 = count(*) from @data
	delete from @d1

	insert into @d1
	select
		st = min(st),
		et
	from
	(
	select
		st = case when b.st < a.st then b.st else a.st end,
		et = max(case when b.et > a.et then b.et else a.et end)
	from
		@data a
		left join
		@data b
		on
		a.seq <> b.seq and
		(
		(b.st <   a.st and b.et > a.st ) or
		(a.st <= b.st and a.et > b.st ) or
		(a.st <= b.et and a.et > b.et))
	group by
		case when b.st < a.st then b.st else a.st end
	) a
	group by
		et
	
	delete from @data
	insert into @data (st,et) select * from @d1 order by 1,2
	set @rows_2 = @@rowcount
	
	end  -- end While


-- Compute the time inside each range
select
	[Date] ,
	[Hours] =
		convert(numeric(10,5),sum(round(
		datediff(ms,
		case when a.sst < b.fr then b.fr else a.sst end,
		case when a.ett > b.tr then b.tr else a.ett end)
		/3600000.00000,5)))
from
	(
	select
		[Date] = dateadd(dd,datediff(dd,0,st),0) ,
		-- Get time only part of begin time
		sst=st-dateadd(dd,datediff(dd,0,st),0) ,
		-- Get time only part of end time
		ett=et-dateadd(dd,datediff(dd,0,st),0) 
	from
		@data
	) a
	join
	@range b
	on	( a.sst >= b.fr and a.sst < b.tr )  or
		( a.ett > b.fr and a.ett <= b.tr )  or
		( a.sst < b.fr and a.ett > b.tr )
group by
	[Date]
order by
	[Date]

Results:


Date                     Hours
-----------------------  -------
2005-07-12 00:00:00.000  2.75000
2005-07-14 00:00:00.000  3.24972

CODO ERGO SUM

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources