troubleshooting Question

T-SQL to group contiguous overlapping dates

Avatar of Cluskitt
CluskittFlag for Portugal asked on
Microsoft SQL ServerMicrosoft SQL Server 2005SQL
26 Comments1 Solution1746 ViewsLast Modified:
This is related to this question:
https://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL-Server-2005/Q_27943350.html

The solution in there works fine for a table that has no overlapping dates. However, there is another table which has overlapping dates, so we either need a different solution or to perfect the one we have.

Basically, I have a table, in this case called tblTrabalho32 (FK_ID_intEmpresa int,FK_ID_intFuncionario int,FK_ID_intTipoTrabalho int,datHoraEntradaTrabalho smalldatetime,datHoraSaidaTrabalho smalldatetime).
FK_ID_intEmpresa is part of a composite key, but in this case it will always be the same, so you can ignore it. FK_ID_intFuncionario is the key (the rest of the composite) and FK_ID_intTipoTrabalho is a foreign key which is important for grouping.

The goal is to group contiguous dates (which we can later check for duration of 30 days or more) and to group the same FK_ID_intTipoTrabalho when it's contiguous.

This is a small sample of the data:
INSERT INTO tblTrabalho32 VALUES (32,128,'Doença','20120823','20120907')
INSERT INTO tblTrabalho32 VALUES (32,2376,'Doença','20120913','20120924')
INSERT INTO tblTrabalho32 VALUES (32,2376,'Doença','20120925','20121006')
INSERT INTO tblTrabalho32 VALUES (32,2376,'Parto','20121007','20121015')
INSERT INTO tblTrabalho32 VALUES (32,2376,'Parto','20121016','20121115')
INSERT INTO tblTrabalho32 VALUES (32,2376,'Parto','20121116','20121215')
INSERT INTO tblTrabalho32 VALUES (32,3746,'Assist. Familia','20120206','20120207')
INSERT INTO tblTrabalho32 VALUES (32,3746,'Assist. Familia','20121025','20121027')
INSERT INTO tblTrabalho32 VALUES (32,6136,'Doença','20120101','20120102')
INSERT INTO tblTrabalho32 VALUES (32,6136,'Doença','20120103','20120106')
INSERT INTO tblTrabalho32 VALUES (32,6136,'Doença','20120212','20120213')
INSERT INTO tblTrabalho32 VALUES (32,6136,'Doença','20120612','20120623')
INSERT INTO tblTrabalho32 VALUES (32,6136,'Doença','20120624','20120711')
INSERT INTO tblTrabalho32 VALUES (32,6136,'Doença','20120712','20120810')
INSERT INTO tblTrabalho32 VALUES (32,6136,'Doença','20120811','20120909')
INSERT INTO tblTrabalho32 VALUES (32,6136,'Doença','20120910','20121009')
INSERT INTO tblTrabalho32 VALUES (32,6136,'Doença','20121010','20121108')
INSERT INTO tblTrabalho32 VALUES (32,6745,'Assist. Familia','20120228','20120315')
INSERT INTO tblTrabalho32 VALUES (32,6745,'Assist. Familia','20120317','20120403')
INSERT INTO tblTrabalho32 VALUES (32,6745,'Acid. Trabalho','20120918','20121012')
INSERT INTO tblTrabalho32 VALUES (32,6853,'Doença','20120621','20120719')
INSERT INTO tblTrabalho32 VALUES (32,6853,'Doença','20120720','20120819')
INSERT INTO tblTrabalho32 VALUES (32,6853,'Doença','20120820','20120927')
INSERT INTO tblTrabalho32 VALUES (32,6853,'Parto','20120828','20120915')
INSERT INTO tblTrabalho32 VALUES (32,6853,'Parto','20120916','20121015')
INSERT INTO tblTrabalho32 VALUES (32,6853,'Parto','20121016','20121115')
INSERT INTO tblTrabalho32 VALUES (32,6853,'Parto','20121116','20121215')
INSERT INTO tblTrabalho32 VALUES (32,7331,'Doença','20120801','20120805')
INSERT INTO tblTrabalho32 VALUES (32,7331,'Doença','20120806','20120813')
INSERT INTO tblTrabalho32 VALUES (32,7331,'Doença','20120814','20120823')
INSERT INTO tblTrabalho32 VALUES (32,7331,'Doença','20120824','20120913')
INSERT INTO tblTrabalho32 VALUES (32,7331,'Doença','20120914','20121013')
INSERT INTO tblTrabalho32 VALUES (32,7331,'Doença','20121014','20121212')
INSERT INTO tblTrabalho32 VALUES (32,7331,'Lic. Parental','20121127','20121231')
INSERT INTO tblTrabalho32 VALUES (32,9045,'Doença','20120101','20120104')
INSERT INTO tblTrabalho32 VALUES (32,9045,'Lic. Parental','20120105','20120602')
INSERT INTO tblTrabalho32 VALUES (32,9045,'Parto','20120105','20120131')
INSERT INTO tblTrabalho32 VALUES (32,9045,'Parto','20120105','20120229')
INSERT INTO tblTrabalho32 VALUES (32,9045,'Parto','20120201','20120331')
INSERT INTO tblTrabalho32 VALUES (32,9045,'Parto','20120315','20120416')
INSERT INTO tblTrabalho32 VALUES (32,9280,'Doença','20120110','20120115')
INSERT INTO tblTrabalho32 VALUES (32,9280,'Doença','20120315','20120326')
INSERT INTO tblTrabalho32 VALUES (32,9280,'Doença','20120327','20120423')
INSERT INTO tblTrabalho32 VALUES (32,9280,'Doença','20120511','20120521')
INSERT INTO tblTrabalho32 VALUES (32,9280,'Doença','20120522','20120618')
INSERT INTO tblTrabalho32 VALUES (32,9280,'Doença','20120619','20120718')
INSERT INTO tblTrabalho32 VALUES (32,9280,'Doença','20120719','20120820')
INSERT INTO tblTrabalho32 VALUES (32,9280,'Doença','20120918','20121008')
INSERT INTO tblTrabalho32 VALUES (32,9280,'Doença','20121009','20121107')
INSERT INTO tblTrabalho32 VALUES (32,9280,'Lic. Parental','20121107','20121130')
INSERT INTO tblTrabalho32 VALUES (32,9280,'Lic. Parental','20121201','20121231')
INSERT INTO tblTrabalho32 VALUES (32,13053,'Assist. Familia','20120101','20120125')
INSERT INTO tblTrabalho32 VALUES (32,13053,'Assist. Familia','20120101','20120131')
INSERT INTO tblTrabalho32 VALUES (32,13053,'Assist. Familia','20120201','20120224')
INSERT INTO tblTrabalho32 VALUES (32,13053,'Doença','20120225','20120325')
INSERT INTO tblTrabalho32 VALUES (32,13053,'Doença','20120326','20120424')
INSERT INTO tblTrabalho32 VALUES (32,13053,'Assist. Familia','20120326','20120424')
INSERT INTO tblTrabalho32 VALUES (32,13053,'Assist. Familia','20120425','20120524')
INSERT INTO tblTrabalho32 VALUES (32,13053,'Assist. Familia','20120524','20120623')
INSERT INTO tblTrabalho32 VALUES (32,13053,'Assist. Familia','20120624','20120723')
INSERT INTO tblTrabalho32 VALUES (32,13053,'Assist. Familia','20120724','20120822')
INSERT INTO tblTrabalho32 VALUES (32,13053,'Assist. Familia','20120823','20120921')
INSERT INTO tblTrabalho32 VALUES (32,13053,'Assist. Familia','20120922','20121021')
INSERT INTO tblTrabalho32 VALUES (32,13053,'Assist. Familia','20121022','20121120')
INSERT INTO tblTrabalho32 VALUES (32,13053,'Assist. Familia','20121121','20121220')
INSERT INTO tblTrabalho32 VALUES (32,13750,'Doença','20120112','20120122')
INSERT INTO tblTrabalho32 VALUES (32,13750,'Doença','20120123','20120131')
INSERT INTO tblTrabalho32 VALUES (32,13750,'Doença','20120201','20120302')
INSERT INTO tblTrabalho32 VALUES (32,13750,'Doença','20120326','20120406')
INSERT INTO tblTrabalho32 VALUES (32,13750,'Doença','20120407','20120418')
INSERT INTO tblTrabalho32 VALUES (32,13750,'Doença','20120419','20120518')
INSERT INTO tblTrabalho32 VALUES (32,13750,'Doença','20120519','20120617')
INSERT INTO tblTrabalho32 VALUES (32,13750,'Doença','20120618','20120717')
INSERT INTO tblTrabalho32 VALUES (32,13750,'Doença','20120718','20120726')
INSERT INTO tblTrabalho32 VALUES (32,13750,'Doença','20120727','20120825')
INSERT INTO tblTrabalho32 VALUES (32,13750,'Parto','20120914','20121015')
INSERT INTO tblTrabalho32 VALUES (32,13750,'Parto','20121116','20121215')
INSERT INTO tblTrabalho32 VALUES (32,14853,'Doença','20120102','20120113')
INSERT INTO tblTrabalho32 VALUES (32,14853,'Doença','20120114','20120114')
INSERT INTO tblTrabalho32 VALUES (32,14853,'Doença','20120129','20120205')
INSERT INTO tblTrabalho32 VALUES (32,14853,'Doença','20120302','20120326')
INSERT INTO tblTrabalho32 VALUES (32,14853,'Doença','20120327','20120425')
INSERT INTO tblTrabalho32 VALUES (32,14853,'Doença','20120426','20120525')
INSERT INTO tblTrabalho32 VALUES (32,14853,'Assist. Familia','20120505','20120511')
INSERT INTO tblTrabalho32 VALUES (32,14853,'Assist. Familia','20120512','20120531')
INSERT INTO tblTrabalho32 VALUES (32,14853,'Lic. Parental','20120601','20121101')
Expected results are:
Emp	Num	Tipo	Ent	Sai	Dias	Periodo	DiasTotal
32	128	Doença	23-08-2012	07-09-2012	16	23-08-2012 to 07-09-2012	16
32	2376	Doença	13-09-2012	06-10-2012	24	13-09-2012 to 15-12-2012	94
32	2376	Parto	07-10-2012	15-12-2012	70	13-09-2012 to 15-12-2012	94
32	3746	Assist. Familia	06-02-2012	07-02-2012	2	06-02-2012 to 07-02-2012	2
32	3746	Assist. Familia	25-10-2012	27-10-2012	3	25-10-2012 to 27-10-2012	3
32	6136	Doença	01-01-2012	06-01-2012	6	01-01-2012 to 06-01-2012	6
32	6136	Doença	12-02-2012	13-02-2012	2	12-02-2012 to 13-02-2012	2
32	6136	Doença	12-06-2012	08-11-2012	150	12-06-2012 to 08-11-2012	150
32	6745	Assist. Familia	28-02-2012	15-03-2012	17	28-02-2012 to 15-03-2012	17
32	6745	Assist. Familia	17-03-2012	03-04-2012	18	17-03-2012 to 03-04-2012	18
32	6745	Acid. Trabalho	18-09-2012	12-10-2012	25	18-09-2012 to 12-10-2012	25
32	6853	Doença	21-06-2012	27-09-2012	99	21-06-2012 to 15-12-2012	178
32	6853	Parto	28-08-2012	15-12-2012	110	21-06-2012 to 15-12-2012	178
32	7331	Doença	01-08-2012	12-12-2012	134	01-08-2012 to 31-12-2012	153
32	7331	Lic. Parental	27-11-2012	31-12-2012	35	01-08-2012 to 31-12-2012	153
32	9045	Doença	01-01-2012	04-01-2012	4	01-01-2012 to 02-06-2012	154
32	9045	Parto	05-01-2012	16-04-2012	103	01-01-2012 to 02-06-2012	154
32	9045	Lic. Parental	05-01-2012	02-06-2012	150	01-01-2012 to 02-06-2012	154
32	9280	Doença	10-01-2012	15-01-2012	6	10-01-2012 to 15-01-2012	6
32	9280	Doença	15-03-2012	23-04-2012	40	15-03-2012 to 23-04-2012	40
32	9280	Doença	11-05-2012	20-08-2012	102	11-05-2012 to 20-08-2012	102
32	9280	Doença	18-09-2012	07-11-2012	51	18-09-2012 to 31-12-2012	105
32	9280	Lic. Parental	07-11-2012	31-12-2012	55	18-09-2012 to 31-12-2012	105
32	13053	Assist. Familia	01-01-2012	24-02-2012	55	01-01-2012 to 20-12-2012	355
32	13053	Doença	25-02-2012	24-04-2012	60	01-01-2012 to 20-12-2012	355
32	13053	Assist. Familia	26-03-2012	20-12-2012	270	01-01-2012 to 20-12-2012	355
32	13750	Doença	12-01-2012	02-03-2012	51	12-01-2012 to 02-03-2012	51
32	13750	Doença	26-03-2012	25-08-2012	153	26-03-2012 to 25-08-2012	153
32	13750	Parto	14-09-2012	15-10-2012	32	14-09-2012 to 15-10-2012	32
32	13750	Parto	16-11-2012	15-12-2012	30	16-11-2012 to 15-12-2012	30
32	14853	Doença	02-01-2012	14-01-2012	13	02-01-2012 to 14-01-2012	13
32	14853	Doença	29-01-2012	05-02-2012	8	29-01-2012 to 05-02-2012	8
32	14853	Doença	02-03-2012	25-05-2012	85	02-03-2012 to 01-11-2012	55
32	14853	Assist. Familia	05-05-2012	31-05-2012	27	02-03-2012 to 01-11-2012	245
32	14853	Lic. Parental	01-06-2012	01-11-2012	154	02-03-2012 to 01-11-2012	245
I've used the same output that evolved from the previous question, but it does contain all the important info.
Important things to consider:
1- Any contiguous dates from the same FK_ID_intTipoTrabalho have to be grouped in the same line, regardless of different ones overlapping or even being contained in that range.
2- All different FK_ID_intTipoTrabalho have to be listed, even if they overlap or are contained in another range.
3- Dates for the total contiguous period are required as they are the ones that actually define the range, even though they may be composed of more than one FK_ID_intTipoTrabalho.

The examples provided cover some of the more tricky cases as well as some basic ones. If necessary I can provide more, but I preferred not to include too many as that might confuse the issue.
ASKER CERTIFIED SOLUTION
Mark Wills
Topic Advisor
Join our community to see this answer!
Unlock 1 Answer and 26 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 26 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros