repost on subtracting date range data between tables

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-- 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|||hi, thanks for that.but would you know how to write this is sql 2000?
much appreciated
peter
<markc600@.hotmail.com> wrote in message
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
>|||-- 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|||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|||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

Repost : Custom tools

Hi
i am posting this message again.
We have the chart control. But i know that we can change the chart type dynamically. So is it posible that we can change the chart control dll and add some combobox along with it which will give the various types of chart. On selecting the type , the corresponding chart type would be shown.I mean to say can i add custom tool which will have the existing chart control of report designer and an extra combo box to select the type at runtime. MrRobert Bruckner [MSFT] has given one solution for that is to have multiple chart types and make any one visible based on some parameter. But then doing that the report will become very heavy.
Can some one throw some light on the approach i have put .
Or is this feature available in the SP1.
Thanks in advanceCombo boxes (or custom controls) are not available in a report. You could
have a set of hyperlinks that changed the type via a parameter. I'm not sure
why you mean it will become "very heavy".
--
Brian Welcker
Group Program Manager
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Hari" <Hari@.discussions.microsoft.com> wrote in message
news:15F6CB61-A8EE-484D-BE0E-6AAFD396BA76@.microsoft.com...
> Hi
> i am posting this message again.
> We have the chart control. But i know that we can change the chart type
dynamically. So is it posible that we can change the chart control dll and
add some combobox along with it which will give the various types of chart.
On selecting the type , the corresponding chart type would be shown.I mean
to say can i add custom tool which will have the existing chart control of
report designer and an extra combo box to select the type at runtime.
MrRobert Bruckner [MSFT] has given one solution for that is to have
multiple chart types and make any one visible based on some parameter. But
then doing that the report will become very heavy.
> Can some one throw some light on the approach i have put .
> Or is this feature available in the SP1.
> Thanks in advance|||Dynamic (expression-based) chart types will not be available in SP1. We
understand that having multiple charts side-by-side and only one visible at
runtime (based on parameter) will make the design more complex. However, the
impact at runtime is very limited because if chart instances are hidden, no
processing/rendering of the chart will happen.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Hari" <Hari@.discussions.microsoft.com> wrote in message
news:15F6CB61-A8EE-484D-BE0E-6AAFD396BA76@.microsoft.com...
> Hi
> i am posting this message again.
> We have the chart control. But i know that we can change the chart type
dynamically. So is it posible that we can change the chart control dll and
add some combobox along with it which will give the various types of chart.
On selecting the type , the corresponding chart type would be shown.I mean
to say can i add custom tool which will have the existing chart control of
report designer and an extra combo box to select the type at runtime.
MrRobert Bruckner [MSFT] has given one solution for that is to have
multiple chart types and make any one visible based on some parameter. But
then doing that the report will become very heavy.
> Can some one throw some light on the approach i have put .
> Or is this feature available in the SP1.
> Thanks in advance|||Thanks for the reply Brian, but my question was more of asking whether custom controls can be made or not.
And why i meant it heavy is that when the report is processing it will take more time since it has process data for two chart separetely and while showing it , it is showing only one but inside both the charts are processed. Even if we use hyperlinks we must have defferent chart for each type. Is it available in SP1.
"Brian Welcker [MSFT]" wrote:
> Combo boxes (or custom controls) are not available in a report. You could
> have a set of hyperlinks that changed the type via a parameter. I'm not sure
> why you mean it will become "very heavy".
> --
> Brian Welcker
> Group Program Manager
> SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Hari" <Hari@.discussions.microsoft.com> wrote in message
> news:15F6CB61-A8EE-484D-BE0E-6AAFD396BA76@.microsoft.com...
> > Hi
> > i am posting this message again.
> >
> > We have the chart control. But i know that we can change the chart type
> dynamically. So is it posible that we can change the chart control dll and
> add some combobox along with it which will give the various types of chart.
> On selecting the type , the corresponding chart type would be shown.I mean
> to say can i add custom tool which will have the existing chart control of
> report designer and an extra combo box to select the type at runtime.
> MrRobert Bruckner [MSFT] has given one solution for that is to have
> multiple chart types and make any one visible based on some parameter. But
> then doing that the report will become very heavy.
> > Can some one throw some light on the approach i have put .
> > Or is this feature available in the SP1.
> > Thanks in advance
>
>