Comparing durations

Dear
Can I compare two duration groups? e.g.,
table1
--
id, start, end
--
1, 2005-01-01, 2005-01-30
2, 2005-02-01, 2005-02-28
table2
--
id_ref, start, end
--
1, 2005-01-01, 2005-01-15
1, 2005-01-17, 2005-01-30
The table1 is a parent of the table2. The duration /2005-01-01 ~ 2005-01-30/
has two child durations in the table2. But the table1 has one missing day or
duration in the table2.
2005-01-06
I want to know if there is a missing day or duration in a given duration in
the table1.
Theoritically,
1. enumerate all the days belonged to the duration of table1
2. check each days against the durations of the table2
Then I may know if there is a missing day or not. But looks inefficient.
Do you have some better idea?
Pohwan Han. Seoul. Have a nice day.OOPS, I mean

> 2005-01-16
by

> 2005-01-06
Pohwan Han. Seoul. Have a nice day.
"Han" <hp4444@.kornet.net.korea> wrote in message
news:uh77TExpFHA.3084@.TK2MSFTNGP09.phx.gbl...
> Dear
> Can I compare two duration groups? e.g.,
> table1
> --
> id, start, end
> --
> 1, 2005-01-01, 2005-01-30
> 2, 2005-02-01, 2005-02-28
> table2
> --
> id_ref, start, end
> --
> 1, 2005-01-01, 2005-01-15
> 1, 2005-01-17, 2005-01-30
> The table1 is a parent of the table2. The duration /2005-01-01 ~
> 2005-01-30/ has two child durations in the table2. But the table1 has one
> missing day or duration in the table2.
> 2005-01-06
> I want to know if there is a missing day or duration in a given duration
> in the table1.
> Theoritically,
> 1. enumerate all the days belonged to the duration of table1
> 2. check each days against the durations of the table2
> Then I may know if there is a missing day or not. But looks inefficient.
> Do you have some better idea?
> --
> Pohwan Han. Seoul. Have a nice day.|||Please post DDL and DML statements.
Regards,
"Han" wrote:

> Dear
> Can I compare two duration groups? e.g.,
> table1
> --
> id, start, end
> --
> 1, 2005-01-01, 2005-01-30
> 2, 2005-02-01, 2005-02-28
> table2
> --
> id_ref, start, end
> --
> 1, 2005-01-01, 2005-01-15
> 1, 2005-01-17, 2005-01-30
> The table1 is a parent of the table2. The duration /2005-01-01 ~ 2005-01-3
0/
> has two child durations in the table2. But the table1 has one missing day
or
> duration in the table2.
> 2005-01-06
> I want to know if there is a missing day or duration in a given duration i
n
> the table1.
> Theoritically,
> 1. enumerate all the days belonged to the duration of table1
> 2. check each days against the durations of the table2
> Then I may know if there is a missing day or not. But looks inefficient.
> Do you have some better idea?
> --
> Pohwan Han. Seoul. Have a nice day.
>|||OK, here is one.
drop table table1
drop table table2
create table table1 (i int, start1 datetime, end1 datetime)
insert into table1 values(1, '2005-01-01', '2005-01-30')
insert into table1 values(2, '2005-02-01', '2005-02-28')
create table table2 (i int, start2 datetime, end2 datetime)
insert into table2 values(1, '2005-01-01', '2005-01-15')
insert into table2 values(1, '2005-01-17', '2005-01-30')
Pohwan Han. Seoul. Have a nice day.
"Enric" <Enric@.discussions.microsoft.com> wrote in message
news:81AFFC28-8CE9-432E-A220-BC7E265014FC@.microsoft.com...
> Please post DDL and DML statements.
> Regards,
> "Han" wrote:
>|||Han
Create a calendar table (look at Aaron's web site www.aspfaq.com) and chek
it againts your table
Why does it seem inefficient to you?
"Han" <hp4444@.kornet.net.korea> wrote in message
news:OFIXJPxpFHA.708@.TK2MSFTNGP09.phx.gbl...
> OK, here is one.
> drop table table1
> drop table table2
> create table table1 (i int, start1 datetime, end1 datetime)
> insert into table1 values(1, '2005-01-01', '2005-01-30')
> insert into table1 values(2, '2005-02-01', '2005-02-28')
> create table table2 (i int, start2 datetime, end2 datetime)
> insert into table2 values(1, '2005-01-01', '2005-01-15')
> insert into table2 values(1, '2005-01-17', '2005-01-30')
> --
> Pohwan Han. Seoul. Have a nice day.
> "Enric" <Enric@.discussions.microsoft.com> wrote in message
> news:81AFFC28-8CE9-432E-A220-BC7E265014FC@.microsoft.com...
>|||I think I got it myself.
select count(*) from table2
where
i=1 and
convert(int, start2) not in (select convert(int, end2)+1 from table2)
will check if every start2 is continued by end2 of the previous record. The
result should be 1, or the data is incorrect.
The rest is just house keeping.
Pohwan Han. Seoul. Have a nice day.
"Han" <hp4444@.kornet.net.korea> wrote in message
news:uh77TExpFHA.3084@.TK2MSFTNGP09.phx.gbl...
> Dear
> Can I compare two duration groups? e.g.,
> table1
> --
> id, start, end
> --
> 1, 2005-01-01, 2005-01-30
> 2, 2005-02-01, 2005-02-28
> table2
> --
> id_ref, start, end
> --
> 1, 2005-01-01, 2005-01-15
> 1, 2005-01-17, 2005-01-30
> The table1 is a parent of the table2. The duration /2005-01-01 ~
> 2005-01-30/ has two child durations in the table2. But the table1 has one
> missing day or duration in the table2.
> 2005-01-06
> I want to know if there is a missing day or duration in a given duration
> in the table1.
> Theoritically,
> 1. enumerate all the days belonged to the duration of table1
> 2. check each days against the durations of the table2
> Then I may know if there is a missing day or not. But looks inefficient.
> Do you have some better idea?
> --
> Pohwan Han. Seoul. Have a nice day.