repost on subtracting date range data between tables

14 Jan 2006 6:45 PM

peter walker

Hi everyone. I posted this previously but made an error in my original
posting. So i am posting again with corrections in place, in hopes someone
might shed light on a possible solution.

What i have is two tables containing date range date. I need to find the
difference between this date. So for example if i have:

table a:

[start] [finish]
1 10
18 19
23 26
28 31

table b:

[start] [finish]
4 5
18 18
25 28

Then the result of table a - table b:

[start] [finish]
1 3
6 10
19 19
23 24
29 31

in effect, if a date range in table_b intersects a date range in table_a,
then that data in the intersection is removed from the date range in table_a

for example, the following are example cases where we subtract from a range
in table_a where there are ranges in table_b which intersect with that range
in table_a:

[From Table A ] - [ From Table B]:
1. {23.....37} - {25....29} = {23....24}
2. {23.....37} - {26....32} = {23....25}, {33....37}
3. {23.....37} - [ {25...27} , {31...33} ] = {23....24}, {28...30},
{34...37}

I am considering solving this problem by using: A - (A AND B). Here A AND B
is a pure subset of A. Maybe this would make the problem easier to solve.

Any help on this would be really appreciated!
Many thanks.

peter

14 Jan 2006 9:03 PM

markc600

-- There are probably lots of ways of doing this, the code
-- below uses recursive CTEs (you'll need SQL Server 2005)

CREATE TABLE TabA(start INT, finish INT)
INSERT INTO TabA(start,finish)
SELECT 1, 10 UNION ALL
SELECT 18, 19 UNION ALL
SELECT 23, 26 UNION ALL
SELECT 28, 31;

CREATE TABLE TabB(start INT, finish INT)
INSERT INTO TabB(start, finish)
SELECT 4, 5 UNION ALL
SELECT 18, 18 UNION ALL
SELECT 25, 28;

WITH CTE_Recur(startgrp,finishgrp,level,start,finish) AS
(
SELECT a.start,a.finish,1,a.start,a.finish
FROM TabA a
WHERE EXISTS (SELECT * FROM TabB b WHERE a.start<=b.finish AND
a.finish>=a.start)
AND NOT EXISTS (SELECT * FROM TabB b WHERE a.start>=b.start AND
a.finish<=b.finish)
UNION ALL
SELECT a.startgrp,a.finishgrp,level+1,a.start,b.start-1
FROM CTE_Recur a
INNER JOIN TabB b ON a.start<b.start AND a.finish>=b.start
UNION ALL
SELECT a.startgrp,a.finishgrp,level+1,b.finish+1,a.finish
FROM CTE_Recur a
INNER JOIN TabB b ON a.finish>b.finish AND a.start<=b.finish
),
CTE_Leaves(startgrp,finishgrp,level) AS
(
SELECT startgrp,finishgrp,max(level)
FROM CTE_Recur
GROUP BY startgrp,finishgrp
)
SELECT r.start,r.finish
FROM CTE_Recur r
INNER JOIN CTE_Leaves l ON l.startgrp=r.startgrp AND
l.finishgrp=r.finishgrp AND l.level=r.level
UNION
SELECT a.start,a.finish
FROM TabA a
WHERE NOT EXISTS (SELECT * FROM TabB b WHERE a.start<=b.finish AND
a.finish>=a.start)
ORDER BY 1,2

15 Jan 2006 12:48 AM

peter walker

hi, thanks for that.but would you know how to write this is sql 2000?

much appreciated
peter

<markc***@hotmail.com> wrote in message
Show quote

news:1137272625.701449.190200@g43g2000cwa.googlegroups.com...
>
> -- There are probably lots of ways of doing this, the code
> -- below uses recursive CTEs (you'll need SQL Server 2005)
>
>
> CREATE TABLE TabA(start INT, finish INT)
> INSERT INTO TabA(start,finish)
> SELECT 1, 10 UNION ALL
> SELECT 18, 19 UNION ALL
> SELECT 23, 26 UNION ALL
> SELECT 28, 31;
>
> CREATE TABLE TabB(start INT, finish INT)
> INSERT INTO TabB(start, finish)
> SELECT 4, 5 UNION ALL
> SELECT 18, 18 UNION ALL
> SELECT 25, 28;
>
> WITH CTE_Recur(startgrp,finishgrp,level,start,finish) AS
> (
> SELECT a.start,a.finish,1,a.start,a.finish
> FROM TabA a
> WHERE EXISTS (SELECT * FROM TabB b WHERE a.start<=b.finish AND
> a.finish>=a.start)
> AND NOT EXISTS (SELECT * FROM TabB b WHERE a.start>=b.start AND
> a.finish<=b.finish)
> UNION ALL
> SELECT a.startgrp,a.finishgrp,level+1,a.start,b.start-1
> FROM CTE_Recur a
> INNER JOIN TabB b ON a.start<b.start AND a.finish>=b.start
> UNION ALL
> SELECT a.startgrp,a.finishgrp,level+1,b.finish+1,a.finish
> FROM CTE_Recur a
> INNER JOIN TabB b ON a.finish>b.finish AND a.start<=b.finish
> ),
> CTE_Leaves(startgrp,finishgrp,level) AS
> (
> SELECT startgrp,finishgrp,max(level)
> FROM CTE_Recur
> GROUP BY startgrp,finishgrp
> )
> SELECT r.start,r.finish
> FROM CTE_Recur r
> INNER JOIN CTE_Leaves l ON l.startgrp=r.startgrp AND
> l.finishgrp=r.finishgrp AND l.level=r.level
> UNION
> SELECT a.start,a.finish
> FROM TabA a
> WHERE NOT EXISTS (SELECT * FROM TabB b WHERE a.start<=b.finish AND
> a.finish>=a.start)
> ORDER BY 1,2
>

15 Jan 2006 10:34 PM

markc600

-- This isn't quite equivalent and has some restrictions
-- such as table B must not have any overlapping ranges,
-- but will work on SQL Server 2000

CREATE TABLE TabA(start INT, finish INT)
INSERT INTO TabA(start,finish)
SELECT 1, 10 UNION ALL
SELECT 18, 19 UNION ALL
SELECT 23, 26 UNION ALL
SELECT 28, 31;

CREATE TABLE TabB(start INT, finish INT)
INSERT INTO TabB(start, finish)
SELECT 4, 5 UNION ALL
SELECT 18, 18 UNION ALL
SELECT 25, 28;

SELECT COALESCE((SELECT MAX(b2.finish)+1 FROM TabB b2 WHERE b2.finish <
b.start and b2.finish > a.start),a.start) as start,
b.start-1 as finish
FROM TabA a
INNER JOIN TabB b ON a.start<b.start AND a.finish>=b.start
WHERE EXISTS (SELECT * FROM TabB b WHERE a.start<=b.finish AND
a.finish>=a.start)
AND NOT EXISTS (SELECT * FROM TabB b WHERE a.start>=b.start AND
a.finish<=b.finish)

UNION

SELECT b.finish+1,
COALESCE((SELECT MIN(b2.start)-1 FROM TabB b2 WHERE b2.start >
b.finish and b2.start < a.finish),a.finish)
FROM TabA a
INNER JOIN TabB b ON a.finish>b.finish AND a.start<=b.finish
WHERE EXISTS (SELECT * FROM TabB b WHERE a.start<=b.finish AND
a.finish>=a.start)
AND NOT EXISTS (SELECT * FROM TabB b WHERE a.start>=b.start AND
a.finish<=b.finish)

UNION

SELECT a.start,a.finish
FROM TabA a
WHERE NOT EXISTS (SELECT * FROM TabB b WHERE a.start<=b.finish AND
a.finish>=a.start)

ORDER BY 1,2

15 Jan 2006 10:55 PM

Hugo Kornelis

On Sun, 15 Jan 2006 04:45:27 +1000, peter walker wrote:

>Hi everyone. I posted this previously but made an error in my original
>posting. So i am posting again with corrections in place, in hopes someone
>might shed light on a possible solution.
(snip)

Hi Peter,

I just posted a reply to your first message about this problem.

--
Hugo Kornelis, SQL Server MVP

16 Jan 2006 2:51 AM

Alexander Kuznetsov

yet another approach would be to use a calendar table:

select [date] date_in_range from calendar c
where exists(select 1 from a
where c.[date] between a.[start] and a.[finish])
and not exists(select 1 from b
where c.[date] between b.[start] and b.[finish])

The query will return a set of dates. If you need intervals, that's
also quite easy to accomplish