May 8, 2008 at 3:22 am
Dear All,
I have following schema
Table: ProcessCellAllocation
ProcessCell
DateFrom
DateTo
I need to find unallocated date ranges for a given machine. Table may contain overlapping allocations as well (overallocation). How to write query to find out unallocated date ranges for a given machine? For reference I am attaching a sql file that would create table and sample data, a text file with expected result is also included
Thanks in advance,
Abhishek
May 9, 2008 at 8:15 am
I think the approach is to find all possible gaps (DatoTo-DateFrom) intervals. Then for each such gap see if it overlaps any of the original DateFrom-DateTo intervals. If no, it's a free gap. This is the query.
select distinct g.GapDateFrom,g.GapDateTo
from
(
select c1.DateTo GapDateFrom,c2.DateFrom GapDateTo
from ProcessCellAllocation c1
join ProcessCellAllocation c2 on c1.AllocationId<>c2.AllocationId and c1.DateTo<c2.DateFrom
) g
where (select count(*) from ProcessCellAllocation c where dbo.overlaphours(g.GapDateFrom,g.GapDateTo,c.DateFrom,c.DateTo)<>0)=0
order by g.GapDateFrom,g.GapDateTo
You'll need this overlaphours function.
if exists (select name from sysobjects where name='overlaphours' and type='FN') drop function overlaphours
go
create function overlaphours(@s1 as datetime,@f1 as datetime,@s2 as datetime,@f2 as datetime) returns integer as
begin
declare @result as int
set @result=0
if @s2>=@s1 and @f2<=@f1
set @result=datediff(hh,@s2,@f2)
else if @s2 =@s1 and @f2<=@f1
set @result=datediff(hh,@s1,@f2)
else if @s2>=@s1 and @s2 @f1
set @result=datediff(hh,@s2,@f1)
else if @s2 =@f1
set @result=datediff(hh,@s1,@f1)
return @result
end
go
In retrospect, there is a simpler way determine IF two date intervals overlap. But this kills two birds with one stone (it tells you IF it overlaps and HOW MUCH).
However, I do get one extra result not in your result set.
2008-05-07 09:00:00.0002008-05-07 10:00:00.000
2008-05-07 16:00:00.0002008-05-08 10:00:00.000
2008-05-08 12:00:00.0002008-05-08 14:00:00.000 <---
2008-05-08 16:00:00.0002008-05-08 17:00:00.000
2008-05-09 00:00:00.0002008-05-10 17:00:00.000
2008-05-11 16:00:00.0002008-05-12 00:00:00.000
2008-05-12 13:00:00.0002008-05-12 18:00:00.000
Did I miss something?
May 9, 2008 at 6:41 pm
Michael,
You have a line of code that looks like the following...
else if @s2>=@s1 and @s2 @f1
What is the missing operator after AND @s2?
--Jeff Moden
Change is inevitable... Change for the better is not.
May 10, 2008 at 12:19 am
So what's the proper way to cut/paste code without all the relational operators getting clobbered and parenthesis becoming smiley faces?
In any case, I attach the files.
May 10, 2008 at 7:57 am
Thanks, Michael.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 12, 2008 at 12:54 am
Many thanks Michael for taking pains in solving my problem! I tried solution on my data but I find some incorrect records. For example add a record for 7-May-2008 10:30 hrs to 7-May-2008 12:30 hrs (I use 24 hrs notation). You would find that system returns two gaps
7-may-2008 9:00 to 7-may-2008 10:00 hrs
and
7-may-2008 9:00 to 7-may-2008 10:30 hrs
which are incorrect. There is only one gap 9:00 hrs to 10:00 hrs. All other gaps are overlapped.
Please let me know if we can correct this problem as well
thanks,
Abhishek Jain
May 12, 2008 at 1:12 am
Also see http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=88422
N 56°04'39.16"
E 12°55'05.25"
May 12, 2008 at 2:28 am
No problem. I thought all your hours were of the form 10:00, 13:00, etc.
All you need to do is change the overlaphours function and calculate the overlap in minutes.
Instead of
datediff(hh,...
use
datediff(mi,...
It might then be more appropriate to call the function overlapminutes.
May 12, 2008 at 7:11 am
I'll pitch in too (even if it's late).
No function. This also separats different ProcessCell.
Damn editor!
I attached the source code instead.
N 56°04'39.16"
E 12°55'05.25"
May 12, 2008 at 7:20 am
Peso, when I run this no rows are generated.
May 12, 2008 at 7:31 am
For a sample set of 1000 date pairs, my suggestion runs in 59 seconds and needs 1,119,549 reads.
Michaels suggestion runs in 334 seconds and needs 572,852 reads.
The SQL Server 2005 approach runs in 1 second and needs 28,012 reads.
See previous attached file Peso.zip
N 56°04'39.16"
E 12°55'05.25"
May 12, 2008 at 11:14 am
Peso,
Aaahh, I didn't know you are a performance freak. The last time I did some performance tests was on a thread dealing with hours between two dates exlcuding weekends. Jeff's code came in with 12 seconds, mine came in with 17 seconds and yours came in with 780 seconds. But I guess you wrote your code just to check our results.
I'm no expert on the variants of joins, but when you write something like
select ...
from mytable t1
join mytable t2 on t1.f=t2.f
and you (but not the rest of the world) know that all values of column f are the same, it is like saying
select ...
from mytable t1
join mytable t2 on 1=1
which is like saying
select ...
from mytable t1
cross join mytable t2
But I would say the latter is the clearest of them all.
Your HAVING clause is what determines just overlap without calculating how much (my function). In any case, I have found a neat way to remember this using an X-paradigm.
S1 F1
X
S2 F2
Thus two intervals S1-F1 and S2-F2 overlap if S1 < F2 and S2 < F1.
I will have another look at both of our queries to see if I can beat yours performance wise on SS2K. Of course, SS 2005 beats them all.
May 12, 2008 at 11:59 am
You are referring to this topic?
http://www.sqlservercentral.com/Forums/Topic317259-9-1.aspx
Yes, I wrote that just for testing output from yours faster functions, which initially had some flaws.
I am happy we worked them out and now they fly!
Yes, performance is always a factor to consider.
What good does it do to index a column and then use
DATEDIFF(DAY, t.theIndexedCol, GETDATE()) > 7
???
I am looking forward to your testing!
N 56°04'39.16"
E 12°55'05.25"
May 12, 2008 at 8:50 pm
Peso (5/12/2008)
For a sample set of 1000 date pairs, my suggestion runs in 59 seconds and needs 1,119,549 reads.Michaels suggestion runs in 334 seconds and needs 572,852 reads.
The SQL Server 2005 approach runs in 1 second and needs 28,012 reads.
See previous attached file Peso.zip
Well done, Peter. Would it be possible for you to gen a script and zip it for the generation of those 1000 rows? I have an idea that I'd like to try and would like to use the same data as you have...
--Jeff Moden
Change is inevitable... Change for the better is not.
May 13, 2008 at 3:03 am
Jeff, this generates random intervals based on some parameters. Give it a try.
Viewing 15 posts - 1 through 15 (of 38 total)
You must be logged in to reply to this topic. Login to reply