mercredi 4 mars 2015

A query for merging date intervals


I have a simple table with 3 columns:



(cid, from, to) -> (int, datetime, datetime)


The difference between dateFrom and dateTo should not be greater than 1 month. And unfortunately i have some corrupt data which do not satisfy this condition.



INPUT:

An example data (cid - from - to) :
8 - 01.05.2012 - 01.09.2012
8 - 02.09.2012 - 10.09.2012
8 - 11.09.2012 - 18.09.2012
8 - 18.09.2012 - 02.10.2012
8 - 03.10.2012 - 04.10.2013


DESIRED OUTPUT in a temp table:


I want to merge the dates whose interval is less than a month into 1 row:



8 - 01.05.2012 - 01.09.2012
8 - 02.09.2012 - 02.10.2012
8 - 03.10.2012 - 04.10.2013


So everything between 02.09.2012 - 02.10.2012 is merged into one row.


Is this possible using only T-SQL? (even without a cursor)





Aucun commentaire:

Enregistrer un commentaire