Merge dateranges ?

11 Aug 2006 10:34 AM

KL

Is there an easy way to merge overlapping dateranges from several tables ?

Thank you
KL.

11 Aug 2006 10:58 AM

ML

Perhaps there is, perhaps there isn't... What does your data (and DDL) look
like?

Maybe something along the lines of:

select <key column>
         ,min(<start date column>) as MinDate
         ,max(<end date column>) as MaxDate
         from (
                 select <key column>
                          ,<start date column>
                          ,<end date column>
                          from <table 1>
                 union
                 select <key column>
                          ,<start date column>
                          ,<end date column>
                          from <table 2>
                 union
                 ...
                 ) dates
          group by <key column>

ML

---
http://milambda.blogspot.com/

12 Aug 2006 6:37 AM

KL

Ok, thanks, that puts me on the right track, however it more complicated
since I have gaps in the dateranges.

I have one table containing membership dates and another table that contains
the periods they were inactive for whatever reason and the result I want
when querying a member is something like:

20050203 - 20050901
20051031 - 20060303

Both tables have a "StartDate" and "EndDate" column, one table is for
membership, the other for inactivity.

Complicated, but that's the way it is.

KL.

Show quote

"ML" <M*@discussions.microsoft.com> skrev i meddelandet
news:EE2C044B-59E8-4B52-9269-445AD5D7260C@microsoft.com...
> Perhaps there is, perhaps there isn't... What does your data (and DDL)
> look
> like?
>
> Maybe something along the lines of:
>
> select <key column>
>         ,min(<start date column>) as MinDate
>         ,max(<end date column>) as MaxDate
>         from (
>                 select <key column>
>                          ,<start date column>
>                          ,<end date column>
>                          from <table 1>
>                 union
>                 select <key column>
>                          ,<start date column>
>                          ,<end date column>
>                          from <table 2>
>                 union
>                 ...
>                 ) dates
>          group by <key column>
>
>
> ML
>
> ---
> http://milambda.blogspot.com/

11 Aug 2006 11:05 AM

Tav

KL wrote:

> Is there an easy way to merge overlapping dateranges from several tables ?
>
> Thank you
> KL.

KL,

Yes use UNION and UNION ALL, for example,

SELECT Column1, Column2, FullName AS Column3 FROM Server1.dbo.TableName
UNION
SELECT Column1, Column2, Column3 FROM Server1.dbo.TableName
UNION
SELECT Column1, Value AS Column2, Column3 FROM Server2.dbo.TableName

ORDER BY Column3

You can UNION any number of tables and from different servers, all you
have to do is make sure the table names and datatypes are the same. As
the example shows you can ORDER BY the complete UNION'ed table.

Regards,

-Tav.-

Tavis Pitt