Tricky T-SQL query

so start off with your calendar dates and return them in a query as 1 in a range or 0 out of a range

select C.ADate,
          CASE WHERE EXISTS(SELECT 0 FROM YourDataTable Y WHERE C.ADate Between Y.Start And Y.End ) THEN 1 ELSE 0 END AS FUNC
  From Calendar C


so that gives you a series of records with 0 or 1 in runs

e.g

2012-01-01    0
2012-01-02    1
2012-01-03    1
2012-01-04    1
2012-01-05    1
2012-01-01    0

ok so imagine you wanted to find any run of 4 exactly, how does it help?

 now get the difference in your calculated values between one day and the next (put the 1st query in a CTE for ease)

WITH CTE AS (
select C.ADate,
          CASE WHERE EXISTS(SELECT 0 FROM YourDataTable Y WHERE C.ADate Between Y.Start And Y.End ) THEN 1 ELSE 0 END AS FUNC
  From Calendar C)
SELECT C1.Adate, C1.FUNC- C2.FUNC AS Dif FROM CTE C1 JOIN CTE C2 ON (c1.Adate - 1) = C2.Adate

so that should give yoiu

2012-01-01    0
2012-01-02    1
2012-01-03    0
2012-01-04    0
2012-01-05    0
2012-01-01    -1

so then you have to find 1 and -1 that are separated by 30 days (with only zeroes between)

then you could do

WITH CTE AS (
select C.ADate,
          CASE WHERE EXISTS(SELECT 0 FROM YourDataTable Y WHERE C.ADate Between Y.Start And Y.End ) THEN 1 ELSE 0 END AS FUNC
  From Calendar C),
CTE_DIF AS
(SELECT C1.Adate, C1.FUNC- C2.FUNC AS Dif FROM CTE C1 JOIN CTE C2 ON (c1.Adate - 1) = C2.Adate)

SELECT CD.Adate FROM CTE_DIF CD WHERE CD.Dif = 1 AND CD.Adate - (SELECT MIN(CD2.ADate) FROM CTE_DID CD2 WHERE CD.ADate < CD2.ADate AND CD2.DIf = -1) = -30



that should then select any run of exactly 30

That's something I saw in a SQL book that they referred to as  'The Islands Problem'

this may help, I don't know

put some corrections in there

I need ranges that are AT LEAST 30 days long. Shouldn't be much of a problem to adapt, but I can't quite see how to apply this to my problem, so it checks for valid ranges for each FK_ID_intEmpresa,FK_ID_intFuncionario and then presents the grouped results as explained above. I suppose you could adapt this so you'd include the IDs in the CTE, but how to present them? That, to me, is the biggest problem.
Using the calendar table (field name is CalendarDate, btw), I can see any running 30 days or over range. But each range may have multiple FK_ID_intTipoTrabalho, which needs to be returned.

I'm confused by '..regardless of FK_ID_intTipoTrabalho'

so you want to find any run of 30 days or more, for all FK_ID_intTipoTrabalho combined, but then in that run, you want all of the FK_ID_intTipoTrabalho that are in it?

so once you've made the CTE with
0
0
1
1
.
.
.
1
1
1
1
0

you can work out the dif's between successive days

then where you have a 1 (a run starts), you can find the next -1 (a run ends) and check if it is over 30.  If it is over the 30, then you have a start date and an end date of the run, so you can find and IDs that are in that run

Yes. But my problem is also grouping them, as explained above. That is, if I have, for a specific ID COLUMNS (FK_ID_intTipoTrabalho, datHoraEntradaTrabalho, datHoraSaidaTrabalho)
A  2012-01-01  2012-01-10
B  2012-01-11  2012-01-20
B  2012-01-21  2012-02-10
A  2012-02-11  2012-02-25
A  2012-02-26  2012-02-29

I can identify the run as starting at 2012-01-01 and ending at 2012-02-29. But what I want returned is:
A  2012-01-01  2012-01-10  10
B  2012-01-11  2012-02-10  31
A  2012-02-11  2012-02-29  19

once you have a start or end date for each run over 30, you should be able to query each start and end time appearing between those dates, using a further CTE and exists.

I don't think you're quite understanding. Whether I use your solution or whether I use an inner join with Calendar and check for counts of 30+ where not exists any calendardate in between, I can get the results. My main issue is getting the results grouped as in my last post. To get those 5 rows into those 3. I have many cases where there are entries of 1 day each running for a period of over 40 days. I don't want 40 rows returned. I want a single one if it's the same FK_ID_intTipoTrabalho with the full range, or break it down, not having more than one in a row.

so you can find
code   start              end
A      2012-01-01  2012-01-10
B      2012-01-11  2012-01-20
B      2012-01-21  2012-02-10
A      2012-02-11  2012-02-25
A       2012-02-26  2012-02-29

call it CTX

then you do

SELECT DQ.code, Min(DQ.Start), Max(DQ.end) FROM (SELECT     code, start,end,
   row_number() over(order by start) - row_number() over(partition by code order by start)  AS X FROM         CTEX
) DQ
GROUP BY DQ.Code, DQ.X

[edited this]

if you make a simple table

code   start              end
A      2012-01-01  2012-01-10
B      2012-01-11  2012-01-20
B      2012-01-21  2012-02-10
A      2012-02-11  2012-02-25
A       2012-02-26  2012-02-29

then run the

SELECT     code, start,end,
   row_number() over(order by start) - row_number() over(partition by code order by start)  AS X FROM         CTEX

you should see how it makes islands have unique code and X for each island

here
test.PNG

although you need to order by date at the end, the islands are there

The sample data would be vastly more useful as either INSERT statements or a spreadsheet -- as it is, I still have to do a lot of formatting just to get sample data loaded into a table for testing :-( .

Sorry. I simply did a select and copy/pasted. I'll make it into an insert tomorrow when I get back to work.

While I try to get the queries going, here is the sample data as inserts:

INSERT INTO tblTrabalho VALUES (41,824,'3A1F0A86-34E5-4BB8-98A3-11DA752484E3','20120910','20120924')
INSERT INTO tblTrabalho VALUES (41,636,'18187308-A0CD-4A06-B5D1-1E7CF2911120','20110704','20110730')
INSERT INTO tblTrabalho VALUES (41,646,'18187308-A0CD-4A06-B5D1-1E7CF2911120','20111105','20111130')
INSERT INTO tblTrabalho VALUES (41,542,'5DC1CD6D-9F7D-4473-9D3C-6BCEFF3FEF0C','20111205','20111205')
INSERT INTO tblTrabalho VALUES (41,542,'5DC1CD6D-9F7D-4473-9D3C-6BCEFF3FEF0C','20120123','20120123')
INSERT INTO tblTrabalho VALUES (41,542,'5DC1CD6D-9F7D-4473-9D3C-6BCEFF3FEF0C','20120428','20120429')
INSERT INTO tblTrabalho VALUES (41,609,'5DC1CD6D-9F7D-4473-9D3C-6BCEFF3FEF0C','20111121','20111121')
INSERT INTO tblTrabalho VALUES (41,609,'5DC1CD6D-9F7D-4473-9D3C-6BCEFF3FEF0C','20111128','20111128')
INSERT INTO tblTrabalho VALUES (41,625,'5DC1CD6D-9F7D-4473-9D3C-6BCEFF3FEF0C','20111209','20111209')
INSERT INTO tblTrabalho VALUES (41,625,'5DC1CD6D-9F7D-4473-9D3C-6BCEFF3FEF0C','20120105','20120105')
INSERT INTO tblTrabalho VALUES (41,646,'5DC1CD6D-9F7D-4473-9D3C-6BCEFF3FEF0C','20111207','20111207')
INSERT INTO tblTrabalho VALUES (41,646,'5DC1CD6D-9F7D-4473-9D3C-6BCEFF3FEF0C','20111216','20111216')
INSERT INTO tblTrabalho VALUES (41,646,'5DC1CD6D-9F7D-4473-9D3C-6BCEFF3FEF0C','20111223','20111223')
INSERT INTO tblTrabalho VALUES (41,646,'5DC1CD6D-9F7D-4473-9D3C-6BCEFF3FEF0C','20111229','20111229')
INSERT INTO tblTrabalho VALUES (41,646,'5DC1CD6D-9F7D-4473-9D3C-6BCEFF3FEF0C','20120105','20120105')
INSERT INTO tblTrabalho VALUES (41,646,'5DC1CD6D-9F7D-4473-9D3C-6BCEFF3FEF0C','20120128','20120128')
INSERT INTO tblTrabalho VALUES (41,879,'5DC1CD6D-9F7D-4473-9D3C-6BCEFF3FEF0C','20120126','20120126')
INSERT INTO tblTrabalho VALUES (41,903,'5DC1CD6D-9F7D-4473-9D3C-6BCEFF3FEF0C','20120124','20120124')
INSERT INTO tblTrabalho VALUES (41,999,'5DC1CD6D-9F7D-4473-9D3C-6BCEFF3FEF0C','20120116','20120116')
INSERT INTO tblTrabalho VALUES (41,1060,'5DC1CD6D-9F7D-4473-9D3C-6BCEFF3FEF0C','20111126','20111126')
INSERT INTO tblTrabalho VALUES (41,1060,'5DC1CD6D-9F7D-4473-9D3C-6BCEFF3FEF0C','20111203','20111203')
INSERT INTO tblTrabalho VALUES (41,1060,'5DC1CD6D-9F7D-4473-9D3C-6BCEFF3FEF0C','20111210','20111210')
INSERT INTO tblTrabalho VALUES (41,1060,'5DC1CD6D-9F7D-4473-9D3C-6BCEFF3FEF0C','20111217','20111217')
INSERT INTO tblTrabalho VALUES (41,1169,'5DC1CD6D-9F7D-4473-9D3C-6BCEFF3FEF0C','20111221','20111221')
INSERT INTO tblTrabalho VALUES (41,1260,'5DC1CD6D-9F7D-4473-9D3C-6BCEFF3FEF0C','20111121','20111121')
INSERT INTO tblTrabalho VALUES (41,1260,'5DC1CD6D-9F7D-4473-9D3C-6BCEFF3FEF0C','20111203','20111203')
INSERT INTO tblTrabalho VALUES (41,1260,'5DC1CD6D-9F7D-4473-9D3C-6BCEFF3FEF0C','20111231','20111231')
INSERT INTO tblTrabalho VALUES (41,1260,'5DC1CD6D-9F7D-4473-9D3C-6BCEFF3FEF0C','20120125','20120125')
INSERT INTO tblTrabalho VALUES (41,1265,'5DC1CD6D-9F7D-4473-9D3C-6BCEFF3FEF0C','20111231','20111231')
INSERT INTO tblTrabalho VALUES (41,1275,'5DC1CD6D-9F7D-4473-9D3C-6BCEFF3FEF0C','20111121','20111121')
INSERT INTO tblTrabalho VALUES (41,1275,'5DC1CD6D-9F7D-4473-9D3C-6BCEFF3FEF0C','20111122','20111122')
INSERT INTO tblTrabalho VALUES (41,1275,'5DC1CD6D-9F7D-4473-9D3C-6BCEFF3FEF0C','20111219','20111219')
INSERT INTO tblTrabalho VALUES (41,1275,'5DC1CD6D-9F7D-4473-9D3C-6BCEFF3FEF0C','20111227','20111227')
INSERT INTO tblTrabalho VALUES (41,1275,'5DC1CD6D-9F7D-4473-9D3C-6BCEFF3FEF0C','20120103','20120103')
INSERT INTO tblTrabalho VALUES (41,1275,'5DC1CD6D-9F7D-4473-9D3C-6BCEFF3FEF0C','20120124','20120124')
INSERT INTO tblTrabalho VALUES (41,1275,'5DC1CD6D-9F7D-4473-9D3C-6BCEFF3FEF0C','20120129','20120129')
INSERT INTO tblTrabalho VALUES (41,1282,'5DC1CD6D-9F7D-4473-9D3C-6BCEFF3FEF0C','20120127','20120127')
INSERT INTO tblTrabalho VALUES (41,1282,'5DC1CD6D-9F7D-4473-9D3C-6BCEFF3FEF0C','20120128','20120128')
INSERT INTO tblTrabalho VALUES (41,1311,'5DC1CD6D-9F7D-4473-9D3C-6BCEFF3FEF0C','20111212','20111212')
INSERT INTO tblTrabalho VALUES (41,1314,'5DC1CD6D-9F7D-4473-9D3C-6BCEFF3FEF0C','20111203','20111203')
INSERT INTO tblTrabalho VALUES (41,1314,'5DC1CD6D-9F7D-4473-9D3C-6BCEFF3FEF0C','20111223','20111223')
INSERT INTO tblTrabalho VALUES (41,1322,'5DC1CD6D-9F7D-4473-9D3C-6BCEFF3FEF0C','20120103','20120103')
INSERT INTO tblTrabalho VALUES (41,1322,'5DC1CD6D-9F7D-4473-9D3C-6BCEFF3FEF0C','20120107','20120107')
INSERT INTO tblTrabalho VALUES (41,1322,'5DC1CD6D-9F7D-4473-9D3C-6BCEFF3FEF0C','20120108','20120108')
INSERT INTO tblTrabalho VALUES (41,1327,'5DC1CD6D-9F7D-4473-9D3C-6BCEFF3FEF0C','20111121','20111121')
INSERT INTO tblTrabalho VALUES (41,1327,'5DC1CD6D-9F7D-4473-9D3C-6BCEFF3FEF0C','20111202','20111202')
INSERT INTO tblTrabalho VALUES (41,1327,'5DC1CD6D-9F7D-4473-9D3C-6BCEFF3FEF0C','20111227','20111227')
INSERT INTO tblTrabalho VALUES (41,1327,'5DC1CD6D-9F7D-4473-9D3C-6BCEFF3FEF0C','20120104','20120104')
INSERT INTO tblTrabalho VALUES (41,1362,'5DC1CD6D-9F7D-4473-9D3C-6BCEFF3FEF0C','20120116','20120116')
INSERT INTO tblTrabalho VALUES (41,1363,'5DC1CD6D-9F7D-4473-9D3C-6BCEFF3FEF0C','20120102','20120102')
INSERT INTO tblTrabalho VALUES (41,1367,'5DC1CD6D-9F7D-4473-9D3C-6BCEFF3FEF0C','20111215','20111215')
INSERT INTO tblTrabalho VALUES (41,1374,'5DC1CD6D-9F7D-4473-9D3C-6BCEFF3FEF0C','20120116','20120116')
INSERT INTO tblTrabalho VALUES (41,1374,'5DC1CD6D-9F7D-4473-9D3C-6BCEFF3FEF0C','20120123','20120123')
INSERT INTO tblTrabalho VALUES (41,1381,'5DC1CD6D-9F7D-4473-9D3C-6BCEFF3FEF0C','20111212','20111212')
INSERT INTO tblTrabalho VALUES (41,1381,'5DC1CD6D-9F7D-4473-9D3C-6BCEFF3FEF0C','20111216','20111216')
INSERT INTO tblTrabalho VALUES (41,1381,'5DC1CD6D-9F7D-4473-9D3C-6BCEFF3FEF0C','20111229','20111229')
INSERT INTO tblTrabalho VALUES (41,1382,'5DC1CD6D-9F7D-4473-9D3C-6BCEFF3FEF0C','20111213','20111213')
INSERT INTO tblTrabalho VALUES (41,1382,'5DC1CD6D-9F7D-4473-9D3C-6BCEFF3FEF0C','20120124','20120124')
INSERT INTO tblTrabalho VALUES (41,1385,'5DC1CD6D-9F7D-4473-9D3C-6BCEFF3FEF0C','20111130','20111130')
INSERT INTO tblTrabalho VALUES (41,1385,'5DC1CD6D-9F7D-4473-9D3C-6BCEFF3FEF0C','20120124','20120124')
INSERT INTO tblTrabalho VALUES (41,275,'B4FB2460-330E-4C62-B7DF-73710F85D462','20111001','20111030')
INSERT INTO tblTrabalho VALUES (41,275,'B4FB2460-330E-4C62-B7DF-73710F85D462','20111031','20111129')
INSERT INTO tblTrabalho VALUES (41,275,'B4FB2460-330E-4C62-B7DF-73710F85D462','20111130','20111229')
INSERT INTO tblTrabalho VALUES (41,275,'B4FB2460-330E-4C62-B7DF-73710F85D462','20111230','20120128')
INSERT INTO tblTrabalho VALUES (41,275,'B4FB2460-330E-4C62-B7DF-73710F85D462','20120129','20120227')
INSERT INTO tblTrabalho VALUES (41,294,'B4FB2460-330E-4C62-B7DF-73710F85D462','20111201','20111230')
INSERT INTO tblTrabalho VALUES (41,294,'B4FB2460-330E-4C62-B7DF-73710F85D462','20111231','20120129')
INSERT INTO tblTrabalho VALUES (41,294,'B4FB2460-330E-4C62-B7DF-73710F85D462','20120130','20120229')
INSERT INTO tblTrabalho VALUES (41,294,'B4FB2460-330E-4C62-B7DF-73710F85D462','20120301','20120329')
INSERT INTO tblTrabalho VALUES (41,919,'B4FB2460-330E-4C62-B7DF-73710F85D462','20120727','20120729')
INSERT INTO tblTrabalho VALUES (41,919,'B4FB2460-330E-4C62-B7DF-73710F85D462','20120730','20120829')
INSERT INTO tblTrabalho VALUES (41,919,'B4FB2460-330E-4C62-B7DF-73710F85D462','20120830','20120929')
INSERT INTO tblTrabalho VALUES (41,919,'B4FB2460-330E-4C62-B7DF-73710F85D462','20120930','20121029')
INSERT INTO tblTrabalho VALUES (41,919,'B4FB2460-330E-4C62-B7DF-73710F85D462','20121030','20121126')
INSERT INTO tblTrabalho VALUES (41,1055,'B4FB2460-330E-4C62-B7DF-73710F85D462','20101218','20101225')
INSERT INTO tblTrabalho VALUES (41,1055,'B4FB2460-330E-4C62-B7DF-73710F85D462','20110104','20110105')
INSERT INTO tblTrabalho VALUES (41,1055,'B4FB2460-330E-4C62-B7DF-73710F85D462','20110107','20110107')
INSERT INTO tblTrabalho VALUES (41,1055,'B4FB2460-330E-4C62-B7DF-73710F85D462','20110110','20110110')
INSERT INTO tblTrabalho VALUES (41,1055,'B4FB2460-330E-4C62-B7DF-73710F85D462','20110118','20110118')
INSERT INTO tblTrabalho VALUES (41,1060,'B4FB2460-330E-4C62-B7DF-73710F85D462','20101229','20101231')
INSERT INTO tblTrabalho VALUES (41,1060,'B4FB2460-330E-4C62-B7DF-73710F85D462','20110101','20110111')
INSERT INTO tblTrabalho VALUES (41,1214,'B4FB2460-330E-4C62-B7DF-73710F85D462','20100329','20100402')
INSERT INTO tblTrabalho VALUES (41,1229,'B4FB2460-330E-4C62-B7DF-73710F85D462','20120920','20120930')
INSERT INTO tblTrabalho VALUES (41,1229,'B4FB2460-330E-4C62-B7DF-73710F85D462','20121001','20121003')
INSERT INTO tblTrabalho VALUES (41,1229,'B4FB2460-330E-4C62-B7DF-73710F85D462','20121105','20121111')
INSERT INTO tblTrabalho VALUES (41,1229,'B4FB2460-330E-4C62-B7DF-73710F85D462','20121119','20121125')
INSERT INTO tblTrabalho VALUES (41,1240,'B4FB2460-330E-4C62-B7DF-73710F85D462','20120524','20120525')
INSERT INTO tblTrabalho VALUES (41,1240,'B4FB2460-330E-4C62-B7DF-73710F85D462','20120528','20120530')
INSERT INTO tblTrabalho VALUES (41,1375,'B4FB2460-330E-4C62-B7DF-73710F85D462','20120329','20120330')
INSERT INTO tblTrabalho VALUES (41,1375,'B4FB2460-330E-4C62-B7DF-73710F85D462','20120406','20120418')
INSERT INTO tblTrabalho VALUES (41,1375,'B4FB2460-330E-4C62-B7DF-73710F85D462','20120419','20120518')
INSERT INTO tblTrabalho VALUES (41,1375,'B4FB2460-330E-4C62-B7DF-73710F85D462','20120519','20120617')
INSERT INTO tblTrabalho VALUES (41,1375,'B4FB2460-330E-4C62-B7DF-73710F85D462','20120618','20120717')
INSERT INTO tblTrabalho VALUES (41,1375,'B4FB2460-330E-4C62-B7DF-73710F85D462','20120718','20120816')
INSERT INTO tblTrabalho VALUES (41,1375,'B4FB2460-330E-4C62-B7DF-73710F85D462','20120817','20120901')
INSERT INTO tblTrabalho VALUES (41,1375,'9B36C78F-CAAF-44FB-9E41-7C10994E5C39','20121015','20121015')
INSERT INTO tblTrabalho VALUES (41,1375,'9B36C78F-CAAF-44FB-9E41-7C10994E5C39','20121016','20121016')
INSERT INTO tblTrabalho VALUES (41,1375,'9B36C78F-CAAF-44FB-9E41-7C10994E5C39','20121018','20121018')
INSERT INTO tblTrabalho VALUES (41,1375,'9B36C78F-CAAF-44FB-9E41-7C10994E5C39','20121020','20121020')
INSERT INTO tblTrabalho VALUES (41,1375,'9B36C78F-CAAF-44FB-9E41-7C10994E5C39','20121022','20121022')
INSERT INTO tblTrabalho VALUES (41,1375,'9B36C78F-CAAF-44FB-9E41-7C10994E5C39','20121023','20121023')
INSERT INTO tblTrabalho VALUES (41,1375,'9B36C78F-CAAF-44FB-9E41-7C10994E5C39','20121025','20121025')
INSERT INTO tblTrabalho VALUES (41,1375,'9B36C78F-CAAF-44FB-9E41-7C10994E5C39','20121026','20121026')
INSERT INTO tblTrabalho VALUES (41,1375,'9B36C78F-CAAF-44FB-9E41-7C10994E5C39','20121029','20121029')
INSERT INTO tblTrabalho VALUES (41,1375,'9B36C78F-CAAF-44FB-9E41-7C10994E5C39','20121030','20121030')
INSERT INTO tblTrabalho VALUES (41,1375,'9B36C78F-CAAF-44FB-9E41-7C10994E5C39','20121101','20121101')
INSERT INTO tblTrabalho VALUES (41,1375,'9B36C78F-CAAF-44FB-9E41-7C10994E5C39','20121102','20121102')
INSERT INTO tblTrabalho VALUES (41,1375,'9B36C78F-CAAF-44FB-9E41-7C10994E5C39','20121103','20121103')
INSERT INTO tblTrabalho VALUES (41,1375,'9B36C78F-CAAF-44FB-9E41-7C10994E5C39','20121105','20121105')
INSERT INTO tblTrabalho VALUES (41,1375,'9B36C78F-CAAF-44FB-9E41-7C10994E5C39','20121106','20121106')
INSERT INTO tblTrabalho VALUES (41,1375,'9B36C78F-CAAF-44FB-9E41-7C10994E5C39','20121108','20121108')
INSERT INTO tblTrabalho VALUES (41,1375,'9B36C78F-CAAF-44FB-9E41-7C10994E5C39','20121109','20121109')
INSERT INTO tblTrabalho VALUES (41,1375,'9B36C78F-CAAF-44FB-9E41-7C10994E5C39','20121110','20121110')
INSERT INTO tblTrabalho VALUES (41,1375,'9B36C78F-CAAF-44FB-9E41-7C10994E5C39','20121112','20121112')
INSERT INTO tblTrabalho VALUES (41,1375,'9B36C78F-CAAF-44FB-9E41-7C10994E5C39','20121113','20121113')
INSERT INTO tblTrabalho VALUES (41,1375,'9B36C78F-CAAF-44FB-9E41-7C10994E5C39','20121114','20121114')
INSERT INTO tblTrabalho VALUES (41,1375,'9B36C78F-CAAF-44FB-9E41-7C10994E5C39','20121115','20121115')
INSERT INTO tblTrabalho VALUES (41,1375,'9B36C78F-CAAF-44FB-9E41-7C10994E5C39','20121116','20121116')
INSERT INTO tblTrabalho VALUES (41,1375,'9B36C78F-CAAF-44FB-9E41-7C10994E5C39','20121119','20121119')
INSERT INTO tblTrabalho VALUES (41,1375,'9B36C78F-CAAF-44FB-9E41-7C10994E5C39','20121120','20121120')
INSERT INTO tblTrabalho VALUES (41,177,'0FC911AD-29E7-4F15-A2A5-AD4D629F30FF','20101109','20101112')
INSERT INTO tblTrabalho VALUES (41,177,'0FC911AD-29E7-4F15-A2A5-AD4D629F30FF','20110216','20110522')
INSERT INTO tblTrabalho VALUES (41,177,'0FC911AD-29E7-4F15-A2A5-AD4D629F30FF','20120912','20120914')
INSERT INTO tblTrabalho VALUES (41,177,'0FC911AD-29E7-4F15-A2A5-AD4D629F30FF','20120915','20120918')
INSERT INTO tblTrabalho VALUES (41,275,'0FC911AD-29E7-4F15-A2A5-AD4D629F30FF','20110831','20110930')
INSERT INTO tblTrabalho VALUES (41,542,'0FC911AD-29E7-4F15-A2A5-AD4D629F30FF','20100202','20100221')
INSERT INTO tblTrabalho VALUES (41,542,'0FC911AD-29E7-4F15-A2A5-AD4D629F30FF','20100707','20100713')
INSERT INTO tblTrabalho VALUES (41,542,'0FC911AD-29E7-4F15-A2A5-AD4D629F30FF','20101018','20101126')
INSERT INTO tblTrabalho VALUES (41,609,'0FC911AD-29E7-4F15-A2A5-AD4D629F30FF','20100918','20100920')
INSERT INTO tblTrabalho VALUES (41,609,'0FC911AD-29E7-4F15-A2A5-AD4D629F30FF','20101015','20101031')
INSERT INTO tblTrabalho VALUES (41,609,'0FC911AD-29E7-4F15-A2A5-AD4D629F30FF','20110101','20110103')
INSERT INTO tblTrabalho VALUES (41,609,'0FC911AD-29E7-4F15-A2A5-AD4D629F30FF','20110208','20110215')
INSERT INTO tblTrabalho VALUES (41,609,'0FC911AD-29E7-4F15-A2A5-AD4D629F30FF','20110725','20110731')
INSERT INTO tblTrabalho VALUES (41,636,'0FC911AD-29E7-4F15-A2A5-AD4D629F30FF','20110412','20110427')
INSERT INTO tblTrabalho VALUES (41,646,'0FC911AD-29E7-4F15-A2A5-AD4D629F30FF','20100114','20100125')
INSERT INTO tblTrabalho VALUES (41,646,'0FC911AD-29E7-4F15-A2A5-AD4D629F30FF','20100319','20100323')
INSERT INTO tblTrabalho VALUES (41,646,'0FC911AD-29E7-4F15-A2A5-AD4D629F30FF','20100508','20100516')
INSERT INTO tblTrabalho VALUES (41,646,'0FC911AD-29E7-4F15-A2A5-AD4D629F30FF','20110615','20110703')
INSERT INTO tblTrabalho VALUES (41,646,'0FC911AD-29E7-4F15-A2A5-AD4D629F30FF','20120217','20120222')
INSERT INTO tblTrabalho VALUES (41,646,'0FC911AD-29E7-4F15-A2A5-AD4D629F30FF','20120223','20120226')
INSERT INTO tblTrabalho VALUES (41,879,'0FC911AD-29E7-4F15-A2A5-AD4D629F30FF','20120127','20120129')
INSERT INTO tblTrabalho VALUES (41,903,'0FC911AD-29E7-4F15-A2A5-AD4D629F30FF','20100906','20100913')
INSERT INTO tblTrabalho VALUES (41,903,'0FC911AD-29E7-4F15-A2A5-AD4D629F30FF','20111230','20120110')
INSERT INTO tblTrabalho VALUES (41,919,'0FC911AD-29E7-4F15-A2A5-AD4D629F30FF','20101028','20101107')
INSERT INTO tblTrabalho VALUES (41,919,'0FC911AD-29E7-4F15-A2A5-AD4D629F30FF','20120618','20120629')
INSERT INTO tblTrabalho VALUES (41,919,'0FC911AD-29E7-4F15-A2A5-AD4D629F30FF','20120630','20120726')
INSERT INTO tblTrabalho VALUES (41,926,'0FC911AD-29E7-4F15-A2A5-AD4D629F30FF','20100203','20100210')
INSERT INTO tblTrabalho VALUES (41,939,'0FC911AD-29E7-4F15-A2A5-AD4D629F30FF','20110623','20110630')
INSERT INTO tblTrabalho VALUES (41,939,'0FC911AD-29E7-4F15-A2A5-AD4D629F30FF','20120308','20120313')
INSERT INTO tblTrabalho VALUES (41,939,'0FC911AD-29E7-4F15-A2A5-AD4D629F30FF','20120817','20120819')
INSERT INTO tblTrabalho VALUES (41,1047,'0FC911AD-29E7-4F15-A2A5-AD4D629F30FF','20110301','20110308')
INSERT INTO tblTrabalho VALUES (41,1071,'0FC911AD-29E7-4F15-A2A5-AD4D629F30FF','20100610','20100618')
INSERT INTO tblTrabalho VALUES (41,1071,'0FC911AD-29E7-4F15-A2A5-AD4D629F30FF','20111006','20111027')
INSERT INTO tblTrabalho VALUES (41,1103,'0FC911AD-29E7-4F15-A2A5-AD4D629F30FF','20101110','20101121')
INSERT INTO tblTrabalho VALUES (41,1103,'0FC911AD-29E7-4F15-A2A5-AD4D629F30FF','20110224','20110308')
INSERT INTO tblTrabalho VALUES (41,1103,'0FC911AD-29E7-4F15-A2A5-AD4D629F30FF','20110620','20110624')
INSERT INTO tblTrabalho VALUES (41,1103,'0FC911AD-29E7-4F15-A2A5-AD4D629F30FF','20120208','20120219')
INSERT INTO tblTrabalho VALUES (41,1103,'0FC911AD-29E7-4F15-A2A5-AD4D629F30FF','20120220','20120224')
INSERT INTO tblTrabalho VALUES (41,1229,'0FC911AD-29E7-4F15-A2A5-AD4D629F30FF','20100101','20100101')
INSERT INTO tblTrabalho VALUES (41,1261,'0FC911AD-29E7-4F15-A2A5-AD4D629F30FF','20100708','20100711')
INSERT INTO tblTrabalho VALUES (41,1261,'0FC911AD-29E7-4F15-A2A5-AD4D629F30FF','20110126','20110131')
INSERT INTO tblTrabalho VALUES (41,1261,'0FC911AD-29E7-4F15-A2A5-AD4D629F30FF','20110205','20110211')
INSERT INTO tblTrabalho VALUES (41,1261,'0FC911AD-29E7-4F15-A2A5-AD4D629F30FF','20110215','20110220')
INSERT INTO tblTrabalho VALUES (41,1264,'0FC911AD-29E7-4F15-A2A5-AD4D629F30FF','20100729','20100806')
INSERT INTO tblTrabalho VALUES (41,1265,'0FC911AD-29E7-4F15-A2A5-AD4D629F30FF','20100526','20100606')
INSERT INTO tblTrabalho VALUES (41,1265,'0FC911AD-29E7-4F15-A2A5-AD4D629F30FF','20120131','20120207')
INSERT INTO tblTrabalho VALUES (41,1269,'0FC911AD-29E7-4F15-A2A5-AD4D629F30FF','20100405','20100409')
INSERT INTO tblTrabalho VALUES (41,1269,'0FC911AD-29E7-4F15-A2A5-AD4D629F30FF','20100729','20100809')
INSERT INTO tblTrabalho VALUES (41,1275,'0FC911AD-29E7-4F15-A2A5-AD4D629F30FF','20120525','20120531')
INSERT INTO tblTrabalho VALUES (41,1276,'0FC911AD-29E7-4F15-A2A5-AD4D629F30FF','20101018','20101018')
INSERT INTO tblTrabalho VALUES (41,1276,'0FC911AD-29E7-4F15-A2A5-AD4D629F30FF','20101220','20101224')
INSERT INTO tblTrabalho VALUES (41,1282,'0FC911AD-29E7-4F15-A2A5-AD4D629F30FF','20120924','20120928')
INSERT INTO tblTrabalho VALUES (41,1287,'0FC911AD-29E7-4F15-A2A5-AD4D629F30FF','20101106','20101112')
INSERT INTO tblTrabalho VALUES (41,1287,'0FC911AD-29E7-4F15-A2A5-AD4D629F30FF','20101210','20101214')
INSERT INTO tblTrabalho VALUES (41,1287,'0FC911AD-29E7-4F15-A2A5-AD4D629F30FF','20110103','20110110')
INSERT INTO tblTrabalho VALUES (41,1303,'0FC911AD-29E7-4F15-A2A5-AD4D629F30FF','20100704','20100708')
INSERT INTO tblTrabalho VALUES (41,1311,'0FC911AD-29E7-4F15-A2A5-AD4D629F30FF','20120320','20120321')
INSERT INTO tblTrabalho VALUES (41,1314,'0FC911AD-29E7-4F15-A2A5-AD4D629F30FF','20110801','20110812')
INSERT INTO tblTrabalho VALUES (41,1320,'0FC911AD-29E7-4F15-A2A5-AD4D629F30FF','20110216','20110227')
INSERT INTO tblTrabalho VALUES (41,1326,'0FC911AD-29E7-4F15-A2A5-AD4D629F30FF','20110320','20110325')
INSERT INTO tblTrabalho VALUES (41,1327,'0FC911AD-29E7-4F15-A2A5-AD4D629F30FF','20110919','20110923')
INSERT INTO tblTrabalho VALUES (41,1332,'0FC911AD-29E7-4F15-A2A5-AD4D629F30FF','20101217','20101219')
INSERT INTO tblTrabalho VALUES (41,1341,'0FC911AD-29E7-4F15-A2A5-AD4D629F30FF','20100913','20100918')
INSERT INTO tblTrabalho VALUES (41,1350,'0FC911AD-29E7-4F15-A2A5-AD4D629F30FF','20110120','20110125')
INSERT INTO tblTrabalho VALUES (41,1351,'0FC911AD-29E7-4F15-A2A5-AD4D629F30FF','20101110','20101207')
INSERT INTO tblTrabalho VALUES (41,1355,'0FC911AD-29E7-4F15-A2A5-AD4D629F30FF','20110404','20110409')
INSERT INTO tblTrabalho VALUES (41,1356,'0FC911AD-29E7-4F15-A2A5-AD4D629F30FF','20110501','20110512')
INSERT INTO tblTrabalho VALUES (41,1356,'0FC911AD-29E7-4F15-A2A5-AD4D629F30FF','20111001','20111005')
INSERT INTO tblTrabalho VALUES (41,1357,'0FC911AD-29E7-4F15-A2A5-AD4D629F30FF','20110227','20110228')
INSERT INTO tblTrabalho VALUES (41,1357,'0FC911AD-29E7-4F15-A2A5-AD4D629F30FF','20110324','20110330')
INSERT INTO tblTrabalho VALUES (41,1357,'0FC911AD-29E7-4F15-A2A5-AD4D629F30FF','20110416','20110422')
INSERT INTO tblTrabalho VALUES (41,1375,'0FC911AD-29E7-4F15-A2A5-AD4D629F30FF','20110426','20110501')
INSERT INTO tblTrabalho VALUES (41,1375,'0FC911AD-29E7-4F15-A2A5-AD4D629F30FF','20111121','20111219')
INSERT INTO tblTrabalho VALUES (41,1375,'0FC911AD-29E7-4F15-A2A5-AD4D629F30FF','20111220','20120118')
INSERT INTO tblTrabalho VALUES (41,1375,'0FC911AD-29E7-4F15-A2A5-AD4D629F30FF','20120119','20120120')
INSERT INTO tblTrabalho VALUES (41,1375,'0FC911AD-29E7-4F15-A2A5-AD4D629F30FF','20120121','20120209')
INSERT INTO tblTrabalho VALUES (41,1375,'0FC911AD-29E7-4F15-A2A5-AD4D629F30FF','20120210','20120218')
INSERT INTO tblTrabalho VALUES (41,1375,'0FC911AD-29E7-4F15-A2A5-AD4D629F30FF','20120219','20120310')
INSERT INTO tblTrabalho VALUES (41,1375,'0FC911AD-29E7-4F15-A2A5-AD4D629F30FF','20120311','20120319')
INSERT INTO tblTrabalho VALUES (41,1375,'0FC911AD-29E7-4F15-A2A5-AD4D629F30FF','20120320','20120328')
INSERT INTO tblTrabalho VALUES (41,1382,'0FC911AD-29E7-4F15-A2A5-AD4D629F30FF','20120518','20120525')
INSERT INTO tblTrabalho VALUES (41,1382,'0FC911AD-29E7-4F15-A2A5-AD4D629F30FF','20120526','20120527')
INSERT INTO tblTrabalho VALUES (41,1382,'0FC911AD-29E7-4F15-A2A5-AD4D629F30FF','20120711','20120718')
INSERT INTO tblTrabalho VALUES (41,1382,'0FC911AD-29E7-4F15-A2A5-AD4D629F30FF','20120817','20120819')
INSERT INTO tblTrabalho VALUES (41,1387,'0FC911AD-29E7-4F15-A2A5-AD4D629F30FF','20110819','20110821')
INSERT INTO tblTrabalho VALUES (41,1391,'0FC911AD-29E7-4F15-A2A5-AD4D629F30FF','20110820','20110827')
INSERT INTO tblTrabalho VALUES (41,1322,'C3B87B60-0205-43D2-9356-ADE88304A9C4','20120106','20120106')
INSERT INTO tblTrabalho VALUES (41,542,'8E5E8121-5CC7-42BD-A57F-B20DDD334B24','20100222','20100310')
INSERT INTO tblTrabalho VALUES (41,542,'8E5E8121-5CC7-42BD-A57F-B20DDD334B24','20101222','20101231')
INSERT INTO tblTrabalho VALUES (41,542,'8E5E8121-5CC7-42BD-A57F-B20DDD334B24','20110101','20110406')
INSERT INTO tblTrabalho VALUES (41,1311,'8E5E8121-5CC7-42BD-A57F-B20DDD334B24','20110712','20110718')
INSERT INTO tblTrabalho VALUES (41,1330,'8E5E8121-5CC7-42BD-A57F-B20DDD334B24','20110401','20110406')
INSERT INTO tblTrabalho VALUES (41,1351,'8E5E8121-5CC7-42BD-A57F-B20DDD334B24','20101208','20101221')
INSERT INTO tblTrabalho VALUES (41,1375,'7A375D2F-5471-4446-8E00-B32B7EAC9839','20120401','20120405')
INSERT INTO tblTrabalho VALUES (41,903,'C5DD9F42-300B-45AD-AFCC-B9815D1D7AEF','20110309','20110311')
INSERT INTO tblTrabalho VALUES (41,903,'C5DD9F42-300B-45AD-AFCC-B9815D1D7AEF','20120415','20120417')
INSERT INTO tblTrabalho VALUES (41,1375,'C5DD9F42-300B-45AD-AFCC-B9815D1D7AEF','20110301','20110303')

Select allOpen in new window

It seems I'm failing to get all the values back. I used this query:

WITH CTE AS (SELECT CONVERT(char(10),CalendarDate,105) Data,
					CalendarDate,
					FK_ID_intEmpresa Emp,
					FK_ID_intFuncionario Num,
					strTipoTrabalho Tipo,
					CONVERT(char(10),datHoraEntradaTrabalho,105) Ent,
					CONVERT(char(10),datHoraSaidaTrabalho,105) Sai
			FROM Calendar
			INNER JOIN tblTrabalho
			ON CalendarDate BETWEEN datHoraEntradaTrabalho AND datHoraSaidaTrabalho
			INNER JOIN tblTiposTrabalho
			ON ID_intTipoTrabalho=FK_ID_intTipoTrabalho
			WHERE strCategoria IN ('I','A')
				AND strTipoTrabalho NOT LIKE '%Férias%'
				AND FK_ID_intEmpresa=41
				AND FK_ID_intFuncionario<=1391)

SELECT *
FROM CTE c1
INNER JOIN CTE c2
ON c1.Emp=c2.Emp
	AND c1.Num=c2.Num
	AND c2.CalendarDate=(SELECT MIN(CalendarDate) FROM CTE c4 WHERE c1.Emp=c4.Emp AND c1.Num=c4.Num AND c4.CalendarDate>c1.CalendarDate)
WHERE NOT EXISTS (SELECT 'a'
				FROM Calendar c3
				WHERE c1.CalendarDate<c3.CalendarDate
					AND c3.CalendarDate<c2.CalendarDate)

Select allOpen in new window

But it took 2 minutes to get the results (1647 rows) and I still have to identify 30 day islands. Not sure how I'll do this when the same FK_ID_intEmpresa,FK_ID_intFuncionario can have multiple islands and only a few will be 30+ days.

I have also failed to adapt the first code to this. I can see how easily it would work for one employee at a time, but the problem is that, even though there are no overlaps for each FK_ID_intEmpresa,FK_ID_intFuncionario, there are many for each FK_ID_intEmpresa. A single day can have quite a few FK_ID_intFuncionario which have to be taken into account. I would need to generate a calendar for each employee and that would be too slow, even if I were to limit the years to, for example, 2010-2013.

I've improved the query. It now takes under a second to run and returns one row per day (the previous didn't return the last day). Still have a problem with the 30 day ranges, though.

WITH CTE AS (SELECT CONVERT(char(10),CalendarDate,105) Data,
					CalendarDate,
					FK_ID_intEmpresa Emp,
					FK_ID_intFuncionario Num,
					strTipoTrabalho Tipo,
					CONVERT(char(10),datHoraEntradaTrabalho,105) Ent,
					CONVERT(char(10),datHoraSaidaTrabalho,105) Sai
			FROM Calendar
			INNER JOIN tblTrabalho
			ON CalendarDate BETWEEN datHoraEntradaTrabalho AND datHoraSaidaTrabalho
			INNER JOIN tblTiposTrabalho
			ON ID_intTipoTrabalho=FK_ID_intTipoTrabalho
			WHERE strCategoria IN ('I','A')
				AND strTipoTrabalho NOT LIKE '%Férias%'
				AND FK_ID_intEmpresa=41
				AND FK_ID_intFuncionario<=1391)

SELECT *
FROM CTE c1
WHERE EXISTS (SELECT 'a'
			FROM CTE c2
			WHERE ABS(DATEDIFF(day,c1.CalendarDate,c2.CalendarDate))=1
				AND c1.Emp=c2.Emp
				AND c1.Num=c2.Num)
ORDER BY 3,4,6,2

Select allOpen in new window

Ok, combining my query with the above suggestions, I've managed to get it working.

DECLARE @Emp int
SET @Emp=41;

WITH CTE AS (SELECT CalendarDate,
					FK_ID_intEmpresa Emp,
					FK_ID_intFuncionario Num,
					strTipoTrabalho Tipo
			FROM Calendar
			INNER JOIN tblTrabalho
			ON CalendarDate BETWEEN datHoraEntradaTrabalho AND datHoraSaidaTrabalho
			INNER JOIN tblTiposTrabalho
			ON ID_intTipoTrabalho=FK_ID_intTipoTrabalho
			WHERE strCategoria IN ('I','A')
				AND strTipoTrabalho NOT LIKE '%Férias%'
				AND FK_ID_intEmpresa=@Emp),

	CT2 AS (SELECT c1.*,
				CASE WHEN NOT EXISTS (SELECT 'a'
									FROM CTE c3
									WHERE c1.Emp=c3.Emp
										AND c1.Num=c3.Num
										AND c1.CalendarDate=DATEADD(day,-1,c3.CalendarDate)) THEN -1
					WHEN NOT EXISTS (SELECT 'a'
									FROM CTE c4
									WHERE c1.Emp=c4.Emp
										AND c1.Num=c4.Num
										AND c1.CalendarDate=DATEADD(day,1,c4.CalendarDate)) THEN 1
					ELSE 0 END ColCheck
			FROM CTE c1
			WHERE EXISTS (SELECT 'a'
						FROM CTE c2
						WHERE ABS(DATEDIFF(day,c1.CalendarDate,c2.CalendarDate))=1
							AND c1.Emp=c2.Emp
							AND c1.Num=c2.Num)),

	CT3 AS (SELECT c1.CalendarDate StartDate,
					c1.Emp,
					c1.Num,
					(SELECT MIN(c2.CalendarDate)
						FROM CT2 c2
						WHERE c2.ColCheck=-1
							AND c1.CalendarDate<c2.CalendarDate
							AND c1.Emp=c2.Emp
							AND c1.Num=c2.Num) EndDate
			FROM (SELECT *
				FROM CT2
				WHERE ColCheck=1) c1
			WHERE DATEDIFF(day,c1.CalendarDate,(SELECT MIN(c2.CalendarDate)
												FROM CT2 c2
												WHERE c2.ColCheck=-1
													AND c1.CalendarDate<c2.CalendarDate
													AND c1.Emp=c2.Emp
													AND c1.Num=c2.Num))+1>=30),

	CTF AS (SELECT FK_ID_intEmpresa Emp,
					FK_ID_intFuncionario Num,
					strTipoTrabalho Tipo,
					datHoraEntradaTrabalho Ent,
					datHoraSaidaTrabalho Sai,
					ROW_NUMBER() OVER (PARTITION BY FK_ID_intEmpresa,FK_ID_intFuncionario
										ORDER BY datHoraEntradaTrabalho)-
						ROW_NUMBER() OVER (PARTITION BY FK_ID_intEmpresa,FK_ID_intFuncionario,strTipoTrabalho
										ORDER BY datHoraEntradaTrabalho) X
			FROM tblTrabalho
			INNER JOIN tblTiposTrabalho
			ON ID_intTipoTrabalho=FK_ID_intTipoTrabalho
			INNER JOIN CT3
			ON FK_ID_intEmpresa=Emp
				AND FK_ID_intFuncionario=Num
				AND datHoraEntradaTrabalho BETWEEN StartDate AND EndDate)

SELECT Emp,
		Num,
		Tipo,
		MIN(Ent) Ent,
		MAX(Sai) Sai,
		DATEDIFF(day,MIN(Ent),MAX(Sai))+1 Dif
FROM CTF
GROUP BY Emp,
		Num,
		Tipo,
		X

ORDER BY 1,2,4

Select allOpen in new window

However, this is quite slow, taking 2 minutes to run on the sample data provided and over 6 minutes without the FK_ID_intFuncionario filter in the initial query.

I'm sure there's a more efficient way to do this.

I'm attaching an excel file with the results for each CTE, in case they can help.

EDIT: Corrected the file (there was a step missing)
Temp.xlsx

if it's taking 6 minutes on a few rows then I'd consider using a cursor to scroll through it in date order.

________________

Yeas I think you could use a cursor, calculate the start and end date of your dataset, iterate through your dates table from start to end date, finding the dates in your data table where sequences start and end, and drop those dates into a temp table, then select start and end of over 30 days in your temp table and iterate with another cursor to determine the details of the codes runs in the 30 days, then out put those to another temp table, then query the final temp table.

I really have no idea how to use a cursor. I'm an experienced programmer, but only so-so on T-SQL. If I understand you correctly, you're saying I should replace CTE or CT2 with a cursor and work with that one?

I have a feeling that there's a more effective way of doing this. I'm going about in a loop (turn ranges into rows, group them, then go back to the original table to get the ranges and group them again). I think there's a better way to do this, but I seem to be stuck in this logic.

I would LOVE to get involved in this one, but wont be able to until later today (after "work" which I am about to head out to do).

So, if still here when I return, then will sink my teeth into it :)

So, days need to be consecutive groups of 30 (or more) days shown more like a calendar for a given FK_ID_intEmpresa,FK_ID_intFuncionario combo in claendar date sequence by FK_ID_intTipoTrabalho

I read above (somewhere) that the sequence needs to be preserved. Does that mean we cannot sort into date sequence for FK_ID_intEmpresa,FK_ID_intFuncionario ?

The sequence for each FK_ID_intEmpresa,FK_ID_intFuncionario is what must be preserved, no matter what FK_ID_intTipoTrabalho it has (or strTipoTrabalho on the linked version of the query). That means that a 30+ day period may contain many records of the same FK_ID_intTipoTrabalho, which have to be grouped, but they may also have some different ones in between, and these have to be listed as well.

If you check the last query I posted and the excel I've attached, you'll see the results for each step, as well as the final result. What I want, basically, is to turn the first sheet (CTE) into the last one (CTF Final).

My query already works. It just takes too long to do so. I'm sure there's a better way to do this, or to improve on my query.

To encourage more input, do the following:


Code | fromDate | toDate | noOfDaysInThisRecord


Code | fromDate | toDate |noOfDaysInThisRecord

Actually, I had already done that. I posted data in the OQ and later I posted the same data as INSERT statements. Afterwards, when I got the query working, I posted the data in the excel file.
I'm attaching a new excel file with the same steps but for a larger set of data. I'm also including the original table without linking to Calendar table, in case a solution arises which doesn't use it.
If you want to work with smaller subsets, just pick a couple results from the last sheet and filter those in the previous sheets.

Basically, given the data in the first sheet, I need the last one returned.
This is the query I used:

--DECLARE @Emp int,@iAno int
--SET @Emp=41
--SET @iAno=2012;

WITH CTE AS (SELECT CalendarDate,
					FK_ID_intEmpresa Emp,
					FK_ID_intFuncionario Num,
					strTipoTrabalho Tipo
			FROM Calendar
			INNER JOIN tblTrabalho
			ON CalendarDate BETWEEN datHoraEntradaTrabalho AND datHoraSaidaTrabalho
			INNER JOIN tblTiposTrabalho
			ON ID_intTipoTrabalho=FK_ID_intTipoTrabalho
			WHERE strCategoria IN ('I','A','F')
				AND strLegenda IN ('AF','BAT','BD','D','LSV','SD','SM')
				AND CONVERT(char(5),datHoraEntradaTrabalho,108)='00:00'
				AND CONVERT(char(5),datHoraSaidaTrabalho,108)='00:00'
				AND FK_ID_intEmpresa IN (41,42,43,63,75,104)),
				--AND CalendarYear=@iAno),

	CT2 AS (SELECT c1.*,
				CASE WHEN NOT EXISTS (SELECT 'a'
									FROM CTE c3
									WHERE c1.Emp=c3.Emp
										AND c1.Num=c3.Num
										AND c1.CalendarDate=DATEADD(day,-1,c3.CalendarDate)) THEN -1
					WHEN NOT EXISTS (SELECT 'a'
									FROM CTE c4
									WHERE c1.Emp=c4.Emp
										AND c1.Num=c4.Num
										AND c1.CalendarDate=DATEADD(day,1,c4.CalendarDate)) THEN 1
					ELSE 0 END ColCheck
			FROM CTE c1
			WHERE EXISTS (SELECT 'a'
						FROM CTE c2
						WHERE ABS(DATEDIFF(day,c1.CalendarDate,c2.CalendarDate))=1
							AND c1.Emp=c2.Emp
							AND c1.Num=c2.Num)),

	CT3 AS (SELECT c1.CalendarDate StartDate,
					c1.Emp,
					c1.Num,
					(SELECT MIN(c2.CalendarDate)
						FROM CT2 c2
						WHERE c2.ColCheck=-1
							AND c1.CalendarDate<c2.CalendarDate
							AND c1.Emp=c2.Emp
							AND c1.Num=c2.Num) EndDate
			FROM CT2 c1
			WHERE ColCheck=1
				AND DATEDIFF(day,c1.CalendarDate,(SELECT MIN(c2.CalendarDate)
												FROM CT2 c2
												WHERE c2.ColCheck=-1
													AND c1.CalendarDate<c2.CalendarDate
													AND c1.Emp=c2.Emp
													AND c1.Num=c2.Num))+1>=30),

	CTF AS (SELECT FK_ID_intEmpresa Emp,
					FK_ID_intFuncionario Num,
					strTipoTrabalho Tipo,
					datHoraEntradaTrabalho Ent,
					datHoraSaidaTrabalho Sai,
					ROW_NUMBER() OVER (PARTITION BY FK_ID_intEmpresa,FK_ID_intFuncionario
										ORDER BY datHoraEntradaTrabalho)-
						ROW_NUMBER() OVER (PARTITION BY FK_ID_intEmpresa,FK_ID_intFuncionario,strTipoTrabalho
										ORDER BY datHoraEntradaTrabalho) X
			FROM tblTrabalho
			INNER JOIN tblTiposTrabalho
			ON ID_intTipoTrabalho=FK_ID_intTipoTrabalho
			INNER JOIN CT3
			ON FK_ID_intEmpresa=Emp
				AND FK_ID_intFuncionario=Num
				AND datHoraEntradaTrabalho BETWEEN StartDate AND EndDate)

SELECT Emp,
		Num,
		Tipo,
		CONVERT(char(10),MIN(Ent),105) Ent,
		CONVERT(char(10),MAX(Sai),105) Sai,
		DATEDIFF(day,MIN(Ent),MAX(Sai))+1 Dif
FROM CTF
GROUP BY Emp,
		Num,
		Tipo,
		X

ORDER BY Emp,
		Num,
		MIN(Ent)

Select allOpen in new window

This takes 26:32 to run, which is too much, obviously. There has to be a way of either speeding this query, or of getting the same results using a different approach that is faster.

Any other data you need, let me know.

EDIT: Added the file, which I forgot to attach
Temp.xlsx

I received an invitation to help in this question.
So don't expect me to go through all the previous comments. Please just reply to my previous comment as requested. This helps me to understand the problem.

Sure. But as I said, you can use the above file and just select a few solutions. Either way, I'll post a few here:
Original:

FK_ID_intEmpresa	FK_ID_intFuncionario	strTipoTrabalho	datHoraEntradaTrabalho	datHoraSaidaTrabalho
41	542	Baixa Por Doença	02-02-2010	21-02-2010
41	542	Baixa Por Acidente de Trabalho	22-02-2010	10-03-2010
41	542	Baixa Por Doença	07-07-2010	13-07-2010
41	542	Baixa Por Doença	18-10-2010	26-11-2010
41	542	Baixa Por Acidente de Trabalho	22-12-2010	31-12-2010
41	542	Baixa Por Acidente de Trabalho	01-01-2011	06-04-2011
41	542	Dispensa	05-12-2011	05-12-2011
41	542	Dispensa	23-01-2012	23-01-2012
41	542	Dispensa	28-04-2012	29-04-2012
42	589	Baixa Por Acidente de Trabalho	11-05-2010	26-05-2010
42	589	Baixa Por Acidente de Trabalho	27-05-2010	04-06-2010
42	589	Baixa Por Doença	10-05-2011	20-05-2011
42	589	Baixa Por Doença	10-08-2011	07-09-2011
42	589	Baixa Por Doença	20-12-2011	22-12-2011
42	589	Baixa Por Acidente de Trabalho	22-02-2012	16-03-2012
42	589	Baixa Por Acidente de Trabalho	17-03-2012	02-04-2012
42	589	Baixa Por Acidente de Trabalho	16-04-2012	28-04-2012
42	589	Baixa Por Acidente de Trabalho	29-04-2012	30-04-2012
42	589	Baixa Por Acidente de Trabalho	01-05-2012	07-05-2012
42	589	Baixa Por Acidente de Trabalho	08-05-2012	14-05-2012
42	589	Baixa Por Acidente de Trabalho	15-05-2012	28-05-2012
42	589	Baixa Por Acidente de Trabalho	29-05-2012	27-06-2012
42	589	Baixa Por Acidente de Trabalho	28-06-2012	28-06-2012
42	589	Baixa Por Doença	29-06-2012	05-07-2012
42	589	Baixa Por Doença	06-07-2012	16-07-2012
42	589	Baixa Por Acidente de Trabalho	17-07-2012	23-07-2012
42	589	Baixa Por Acidente de Trabalho	24-07-2012	10-08-2012
42	589	Dispensa	12-08-2012	12-08-2012
42	589	Baixa Por Acidente de Trabalho	28-08-2012	10-09-2012
42	589	Baixa Por Acidente de Trabalho	11-09-2012	25-09-2012
42	589	Baixa Por Acidente de Trabalho	26-09-2012	25-10-2012
42	589	Baixa Por Acidente de Trabalho	26-10-2012	23-11-2012

Select allOpen in new window

Expected results:

Emp	Num	Tipo	Ent	Sai	Dif
41	542	Baixa Por Doença	02-02-2010	21-02-2010	20
41	542	Baixa Por Acidente de Trabalho	22-02-2010	10-03-2010	17
41	542	Baixa Por Doença	18-10-2010	26-11-2010	40
41	542	Baixa Por Acidente de Trabalho	22-12-2010	06-04-2011	106
42	589	Baixa Por Acidente de Trabalho	22-02-2012	28-06-2012	128
42	589	Baixa Por Doença	29-06-2012	16-07-2012	18
42	589	Baixa Por Acidente de Trabalho	17-07-2012	23-11-2012	130

Select allOpen in new window

It's more than 10 lines, but they relate to just two separate cases which cover most of the problems this question covers.

For not to drag in more comments, please supply the info for me to understand the problem.

code | other | fromDate | toDate | diff
41      x      02-02-2010      21-02-2010        fill
41      y      22-02-2010      10-03-2010      fill
41      x      07-07-2010      13-07-2010      fill
41      y      18-10-2010      26-11-2010      fill
42      x      02-02-2010      21-02-2010      fill
42      y      22-02-2010      10-03-2010      fill
42      x      07-07-2010      13-07-2010      fill
42      y      18-10-2010      26-11-2010      fill

Modify the above data and list down the expected output.

I don't think you quite understand. FK_ID_intEmpresa and FK_ID_intFuncionario are both part of the ID. Thus, 41/542 is one ID, 42/589 is another ID. Each of the columns can have multiple values, but only one pair is allowed, as a normal composite ID key.
Now, for each ID, you can have multiple codes (strTipoTrabalho) which was what was posted above.

The data you posted was from the expected results which already include everything, including the number of days.

I'll post the sample data with the date difference.

41	542	Baixa Por Doença	02-02-2010	21-02-2010	20
41	542	Baixa Por Acidente de Trabalho	22-02-2010	10-03-2010	17
41	542	Baixa Por Doença	07-07-2010	13-07-2010	7
41	542	Baixa Por Doença	18-10-2010	26-11-2010	40
41	542	Baixa Por Acidente de Trabalho	22-12-2010	31-12-2010	10
41	542	Baixa Por Acidente de Trabalho	01-01-2011	06-04-2011	96
41	542	Dispensa	05-12-2011	05-12-2011	1
41	542	Dispensa	23-01-2012	23-01-2012	1
41	542	Dispensa	28-04-2012	29-04-2012	2
42	589	Baixa Por Acidente de Trabalho	11-05-2010	26-05-2010	16
42	589	Baixa Por Acidente de Trabalho	27-05-2010	04-06-2010	9
42	589	Baixa Por Doença	10-05-2011	20-05-2011	11
42	589	Baixa Por Doença	10-08-2011	07-09-2011	29
42	589	Baixa Por Doença	20-12-2011	22-12-2011	3
42	589	Baixa Por Acidente de Trabalho	22-02-2012	16-03-2012	24
42	589	Baixa Por Acidente de Trabalho	17-03-2012	02-04-2012	17
42	589	Baixa Por Acidente de Trabalho	16-04-2012	28-04-2012	13
42	589	Baixa Por Acidente de Trabalho	29-04-2012	30-04-2012	2
42	589	Baixa Por Acidente de Trabalho	01-05-2012	07-05-2012	7
42	589	Baixa Por Acidente de Trabalho	08-05-2012	14-05-2012	7
42	589	Baixa Por Acidente de Trabalho	15-05-2012	28-05-2012	14
42	589	Baixa Por Acidente de Trabalho	29-05-2012	27-06-2012	30
42	589	Baixa Por Acidente de Trabalho	28-06-2012	28-06-2012	1
42	589	Baixa Por Doença	29-06-2012	05-07-2012	7
42	589	Baixa Por Doença	06-07-2012	16-07-2012	11
42	589	Baixa Por Acidente de Trabalho	17-07-2012	23-07-2012	7
42	589	Baixa Por Acidente de Trabalho	24-07-2012	10-08-2012	18
42	589	Dispensa	12-08-2012	12-08-2012	1
42	589	Baixa Por Acidente de Trabalho	28-08-2012	10-09-2012	14
42	589	Baixa Por Acidente de Trabalho	11-09-2012	25-09-2012	15
42	589	Baixa Por Acidente de Trabalho	26-09-2012	25-10-2012	30
42	589	Baixa Por Acidente de Trabalho	26-10-2012	23-11-2012	29

Select allOpen in new window

Now, remember that the first two columns are part of the ID, then comes the type (or code), then from and to dates, then diff.

Sorry, I can't understand the problem with such info.
If you need my help please complete the input data as I suggested in previous comment, and list the expected output.
There are many open questions and cannot be involved in the actual design of each case.
Instead of putting a long field name for example "datHoraEntradaTrabalho" , you may just call it x in , and once satisfied with a solution rename to required names.

The names aren't important. I can't fill the data you suggested, because the data you suggested already IS the desired output.

To be honest, if you want to understand the problem, the best way is to download the excel file, pick a couple results from the last sheet and filter those results in all the previous sheets. The first sheet contains the original data, the other sheets contain the steps for each CTE in my query, so you can skip those if you like. You can rename all the fields to whatever you want and manipulate the data to whatever you want.
After all, I created the excel file to help other ee experts to understand both the problem and my query.

However, if you still find all those rows too complicated, I'll break it down for just a single result:
code1  code2   other                                               from                to                     diff
41       542       Baixa Por Doença                            02-02-2010      21-02-2010      20
41       542       Baixa Por Acidente de Trabalho      22-02-2010      10-03-2010      17
41       542       Baixa Por Doença                            07-07-2010      13-07-2010      7
41       542       Baixa Por Doença                            18-10-2010      26-11-2010      40
41       542       Baixa Por Acidente de Trabalho      22-12-2010      31-12-2010      10
41       542       Baixa Por Acidente de Trabalho      01-01-2011      06-04-2011      96
41       542       Dispensa                                         05-12-2011      05-12-2011      1
41       542       Dispensa                                         23-01-2012      23-01-2012      1
41       542       Dispensa                                         28-04-2012      29-04-2012      2

expected:
code1  code2   other                                               from                to                     diff
41        542      Baixa Por Doença                            02-02-2010      21-02-2010      20
41        542      Baixa Por Acidente de Trabalho      22-02-2010      10-03-2010      17
41        542      Baixa Por Doença                            18-10-2010      26-11-2010      40
41        542      Baixa Por Acidente de Trabalho      22-12-2010      06-04-2011      106

The problem in itself is really simple. For each code1/code2 IDs, identify consecutive periods of 30 days (not 1 month) or more and then present such data in a compacted way, as seen by the last line of the result.

OK, sorry about the longer than expected absence...

Was really good fun, enjoyed and found a couple of errors with either expected results, or, maybe stuffed up the inserts, or, simply got it wrong (but have worked through every single example and can explain).

So, the procedure below goes through every consecutive date by Empresa and Funcionario then getting the results for those periods broken out by individual (groups) of TipoTrabalho HoraEntrada HoraSaida.

There are a couple of additional columns at the end to reflect the period it belongs to ( to help with disgnosis) and can be removed.

We use recursive CTE's to carry forward the apprioriate end or start date.

To help make that happen, we first extracct the required data into a temp table and index it to make sure performance will be reasonable.

As the stored procedure, used Number of Days as a parameter - so - if you want 45 days or 60 (or whatever) simply change the days parameter.

Could also add more params to help minimise the select into the temp table (at the moment, just Empressa)

if object_id('usp_tblTrabalho_periods') is not null drop procedure usp_tblTrabalho_periods
go

create procedure usp_tblTrabalho_periods (@Empresa int, @days int = 30)
AS
Begin

  if object_id('tempdb..#tmp_tblTrabalho','U') is not null drop table #tmp_tblTrabalho
  ;

  select row_number() over (partition by FK_ID_intEmpresa,FK_ID_intFuncionario order by datHoraEntradaTrabalho desc) as DRN
         , row_number() over (partition by FK_ID_intEmpresa,FK_ID_intFuncionario order by datHoraEntradaTrabalho ASC) as ARN
         , * into #tmp_tblTrabalho 
  from tblTrabalho
  where FK_ID_intEmpresa = @empresa
  ;

  create index idx_tmp_tblTrabalho_drn on #tmp_tblTrabalho (drn,FK_ID_intEmpresa,FK_ID_intFuncionario)
  ;

  create index idx_tmp_tblTrabalho_arn on #tmp_tblTrabalho (Arn,FK_ID_intEmpresa,FK_ID_intFuncionario)
  ;

  ; with cte_period_ends as
  ( select drn, arn, FK_ID_intEmpresa,FK_ID_intFuncionario,FK_ID_intTipoTrabalho,datHoraEntradaTrabalho,datHoraSaidaTrabalho, datHoraSaidaTrabalho as nextend, datHoraEntradaTrabalho as priorstart
    from  #tmp_tblTrabalho 
    where DRN = 1
    union all
    select T.DRN, T.ARN, E.FK_ID_intEmpresa,T.FK_ID_intFuncionario,T.FK_ID_intTipoTrabalho,T.datHoraEntradaTrabalho,T.datHoraSaidaTrabalho, 
           case when datediff(day,T.datHoraSaidaTrabalho,E.datHoraEntradaTrabalho) < 2 then E.nextend else T.datHoraSaidaTrabalho end, T.datHoraEntradaTrabalho 
    from  cte_period_ends E
    inner join #tmp_tblTrabalho T on T.FK_ID_intEmpresa = E.FK_ID_intEmpresa and T.FK_ID_intFuncionario = E.FK_ID_intFuncionario and T.DRN = E.DRN + 1
  )
  , cte_period_commence as
  ( select DRN, ARN, FK_ID_intEmpresa,FK_ID_intFuncionario,FK_ID_intTipoTrabalho,datHoraEntradaTrabalho,datHoraSaidaTrabalho, nextend, priorstart
    from  cte_period_ends 
    where ARN = 1
    union all
    select E.DRN, E.ARN, C.FK_ID_intEmpresa,E.FK_ID_intFuncionario,E.FK_ID_intTipoTrabalho,E.datHoraEntradaTrabalho,E.datHoraSaidaTrabalho, E.nextend,
           case when datediff(day,c.datHoraSaidaTrabalho,E.datHoraEntradaTrabalho) < 2 then C.priorstart else E.datHoraEntradaTrabalho end
    from  cte_period_commence C
    inner join cte_period_ends E on E.FK_ID_intEmpresa = c.FK_ID_intEmpresa and E.FK_ID_intFuncionario = c.FK_ID_intFuncionario and E.ARN = C.ARN + 1
  )
  select FK_ID_intEmpresa,FK_ID_intFuncionario,FK_ID_intTipoTrabalho,min(datHoraEntradaTrabalho) as datHoraEntradaTrabalho,max(datHoraSaidaTrabalho) as datHoraSaidaTrabalho
         , datediff(day,min(datHoraEntradaTrabalho),max(datHoraSaidaTrabalho)) + 1 as Total
         , convert(char(10), priorstart,120) + '  to  ' + convert(char(10), nextend,120) as Empresa_Funcionario_Period_Group    -- remove later
         , datediff(day,priorstart,nextend) + 1 as Total_For_Period_Group                                                     -- remove later
  from cte_period_commence
  where datediff(day,priorstart,nextend) >= @days
  group by FK_ID_intEmpresa,FK_ID_intFuncionario,FK_ID_intTipoTrabalho,priorstart,nextend
  order by 1,2,4,5,3
  ;

  if object_id('tempdb..#tmp_tblTrabalho','U') is not null drop table #tmp_tblTrabalho
  ;
END
GO

-- now run it :)

exec usp_tblTrabalho_periods 41,30

Select allOpen in new window

Now, I got an extra 542 and two extra 1375 and looking back, while the individual TipoTrabalho doesnt qulify, it does fall withn consecutive dates for the ID's

Oh, BTW, my examples and results reflect the original as posted in the question, not the follow up stuff. I will try some of the other examples in the meantime.

There is no need to check my queries. Your query does everything right and takes 0 seconds. I had improved my query to 29 seconds, but some results weren't correct, apparently. Namely, if there were two different periods of the same type (I don't think the original sample had this case) it would present them as one. That is, your query shows 16-03-2012 to 17-04-2012 and 14-06-2012 to 26-10-2012, whereas mine would show 16-03-2012 to 26-10-2012, presumably because they are the same type. I wouldn't have noticed it either, if I weren't comparing both results.
Anyway, your query does what was requested and is really fast, so thanks.

Very pleased to have been able to help. I did enjoy it, so thank you for allowing us to help.

Cheers,
Mark Wills

Actually, it isn't quite right yet. Given this data:

FK_ID_intEmpresa	FK_ID_intFuncionario	FK_ID_intTipoTrabalho	datHoraEntradaTrabalho	datHoraSaidaTrabalho
42	589	Baixa Por Acidente de Trabalho	2012-02-22 00:00:00	2012-03-16 00:00:00
42	589	Baixa Por Acidente de Trabalho	2012-03-17 00:00:00	2012-04-02 00:00:00
42	589	Baixa Por Acidente de Trabalho	2012-04-16 00:00:00	2012-04-28 00:00:00
42	589	Baixa Por Acidente de Trabalho	2012-04-29 00:00:00	2012-04-30 00:00:00
42	589	Baixa Por Acidente de Trabalho	2012-05-01 00:00:00	2012-05-07 00:00:00
42	589	Baixa Por Acidente de Trabalho	2012-05-08 00:00:00	2012-05-14 00:00:00
42	589	Baixa Por Acidente de Trabalho	2012-05-15 00:00:00	2012-05-28 00:00:00
42	589	Baixa Por Acidente de Trabalho	2012-05-29 00:00:00	2012-06-27 00:00:00
42	589	Baixa Por Acidente de Trabalho	2012-06-28 00:00:00	2012-06-28 00:00:00
42	589	Baixa Por Doença	2012-06-29 00:00:00	2012-07-05 00:00:00
42	589	Baixa Por Doença	2012-07-06 00:00:00	2012-07-16 00:00:00
42	589	Baixa Por Acidente de Trabalho	2012-07-17 00:00:00	2012-07-23 00:00:00
42	589	Baixa Por Acidente de Trabalho	2012-07-24 00:00:00	2012-08-10 00:00:00
42	589	Baixa Por Acidente de Trabalho	2012-08-28 00:00:00	2012-09-10 00:00:00
42	589	Baixa Por Acidente de Trabalho	2012-09-11 00:00:00	2012-09-25 00:00:00
42	589	Baixa Por Acidente de Trabalho	2012-09-26 00:00:00	2012-10-25 00:00:00
42	589	Baixa Por Acidente de Trabalho	2012-10-26 00:00:00	2012-11-23 00:00:00

Select allOpen in new window

Your code returns (I've changed the column aliases for the output):

Emp	Num	Tipo	Ent	Sai	Dias	Periodo	DiasTotal
42	589	Baixa Por Acidente de Trabalho	22-02-2012	02-04-2012	41	2012-02-22 to 2012-04-02	41
42	589	Baixa Por Acidente de Trabalho	16-04-2012	10-08-2012	117	2012-04-16 to 2012-08-10	117
42	589	Baixa Por Doença	29-06-2012	16-07-2012	18	2012-04-16 to 2012-08-10	117
42	589	Baixa Por Acidente de Trabalho	28-08-2012	23-11-2012	88	2012-08-28 to 2012-11-23	88

Select allOpen in new window

And it should return:

Emp	Num	Tipo	Ent	Sai	Dias	Periodo	DiasTotal
42	589	Baixa Por Acidente de Trabalho	22-02-2012	02-04-2012	41	2012-02-22 to 2012-04-02	41
42	589	Baixa Por Acidente de Trabalho	16-04-2012	28-06-2012	74	2012-04-16 to 2012-08-10	117
42	589	Baixa Por Doença	29-06-2012	16-07-2012	18	2012-04-16 to 2012-08-10	117
42	589	Baixa Por Acidente de Trabalho	17-07-2012	10-08-2012	25	2012-04-16 to 2012-08-10	117
42	589	Baixa Por Acidente de Trabalho	28-08-2012	23-11-2012	88	2012-08-28 to 2012-11-23	88

Select allOpen in new window

Checking the output from CTE_Period_Commence, I believe some sort of ROW_NUMBER() will have to be added, although I'm not sure how to adjust it. This is the output for this case:

DRN	ARN	FK_ID_intEmpresa	FK_ID_intFuncionario	FK_ID_intTipoTrabalho	datHoraEntradaTrabalho	datHoraSaidaTrabalho	NextEnd	PriorStart
16	3	42	589	Baixa Por Acidente de Trabalho	2012-04-16 00:00:00	2012-04-28 00:00:00	2012-08-10 00:00:00	2012-04-16 00:00:00
15	4	42	589	Baixa Por Acidente de Trabalho	2012-04-29 00:00:00	2012-04-30 00:00:00	2012-08-10 00:00:00	2012-04-16 00:00:00
14	5	42	589	Baixa Por Acidente de Trabalho	2012-05-01 00:00:00	2012-05-07 00:00:00	2012-08-10 00:00:00	2012-04-16 00:00:00
13	6	42	589	Baixa Por Acidente de Trabalho	2012-05-08 00:00:00	2012-05-14 00:00:00	2012-08-10 00:00:00	2012-04-16 00:00:00
12	7	42	589	Baixa Por Acidente de Trabalho	2012-05-15 00:00:00	2012-05-28 00:00:00	2012-08-10 00:00:00	2012-04-16 00:00:00
11	8	42	589	Baixa Por Acidente de Trabalho	2012-05-29 00:00:00	2012-06-27 00:00:00	2012-08-10 00:00:00	2012-04-16 00:00:00
10	9	42	589	Baixa Por Acidente de Trabalho	2012-06-28 00:00:00	2012-06-28 00:00:00	2012-08-10 00:00:00	2012-04-16 00:00:00
9	10	42	589	Baixa Por Doença	2012-06-29 00:00:00	2012-07-05 00:00:00	2012-08-10 00:00:00	2012-04-16 00:00:00
8	11	42	589	Baixa Por Doença	2012-07-06 00:00:00	2012-07-16 00:00:00	2012-08-10 00:00:00	2012-04-16 00:00:00
7	12	42	589	Baixa Por Acidente de Trabalho	2012-07-17 00:00:00	2012-07-23 00:00:00	2012-08-10 00:00:00	2012-04-16 00:00:00
6	13	42	589	Baixa Por Acidente de Trabalho	2012-07-24 00:00:00	2012-08-10 00:00:00	2012-08-10 00:00:00	2012-04-16 00:00:00

Select allOpen in new window

It would need another column which would have the value (for example) 1 for the first rows for "Baixa Por Acidente de Trabalho", 2 for the ones for "Baixa Por Doença" and 3 for the last ones.
I've tried a few ROW_NUMBER() variations, but I couldn't find one that would help me fix this.

Yeah, it is an interesting relationship when they belong to the same "period group" but interrupted by a different Funcionario.

Will need to come up with some kind of rule to describe that data relationship / scenario to force a "break" in the group by part of the query.

I am thinking that we simply start with a 1 in a new column group_break and as we traverse (in the recursive part) we simply add 1 to group_break when Funcionario changes.

Downside of that is we need to check the aggregated days regardless of that group_break.

Which we can do using windows function e.g. sum() over ()

But I will have a play with that scenario and find a way.

So, first thing I'll do is add that relationship / scenario into the original test data as 589.

I've been playing with different row_number scenarios. One that seems most promising is:
ROW_NUMBER() OVER (PARTITION BY FK_ID_intEmpresa,FK_ID_intFuncionario ORDER BY datHoraEntradaTrabalho) - ROW_NUMBER() OVER (PARTITION BY FK_ID_intEmpresa,FK_ID_intFuncionario,FK_ID_intTipoTrabalho,NextEnd ORDER BY datHoraEntradaTrabalho)

This was inspired by one of the earlier posts by deighton. I would have to test more, but it seems that this would generate mostly unique values.

>> Yeah, it is an interesting relationship when they belong to the same "period group" but interrupted by a different Funcionario.

Not Funcionario. That is part of the composite ID. intTipoTrabalho is the one that interrupts the range. The examples I posted last are regarding a single FK_ID_intEmpresa/FK_ID_intFuncionario id.

Yep - my mistake - knew it was TipoTrabalho :)

And dont think row_number() is going to cut it. Something easier ;)

I'll be back  (spoken in best Arnold Schwarzenager impersonation).

Yeah, I can't even find any reliable combination of row_number() that will be unique enough to group by.

I'm leaving work right now, but I'll test that tomorrow. Thanks for helping out, even though the points are already awarded.

No problems, I am here for the long haul if needed....

At first glance, this seems to work. However, I'm going to look into a few more special cases before awarding the answer this time.

One small thing: How would this behave if the table had overlapping periods? On a different table (same structure) there are many ranges that overlap, like:
Code1 01-01-2012 to 31-01-2012
Code1 01-01-2012 to 29-02-2012
Code1 01-02-2012 to 31-05-2012
Code2 01-03-2012 to 31-03-2012
Code1 01-04-2012 to 30-06-2012

This is for a single Funcionario (for this table, Empresa is moot because there is only one, even though the field is still there).
Hopefully, the best expected output would be something like we've arranged except that it will repeat periods with different TipoTrabalho:
Code1 01-01-2012 to 30-06-2012
Code2 01-03-2012 to 31-03-2012

If (like it happens a lot) there are only partial overlaps, then it would show like:
Code1  01-01-2012 to 14-03-2012
Code2  01-03-2012 to 31-03-2012
Code1  30-03-2012 to 30-06-2012

One thing I didn't understand was why you would change the select. After all, it had no practical value. The way it was returned the same results, as long as you add the Group_Break to the GROUP BY list.

One small correction as well: You checking for datediff>=30, but you have to check for datediff+1>=30. I've spotted that quite early, but it wasn't really important, so I didn't bother to mention. But I almost made that mistake again when I pasted your code. :)

Well, a couple of reasons...

The thought was to minimise those rows that we want to group by.

Was using that when debugging the code to be able to simply comment out the outside query.

Yeah, and know about that + 1 (did it in the datediff above) and had intended to simply sub 1 from the parameter, and never got around to it. Could also simply remove the "=" sign from ">=". Preferably do not do date arithmetic like that with functions and arithmetic when it is so much easier to avoid, just that I forgot :)

Trouble with overlapping dates is the count - do we count different calendar days, or accumulate the differences between dates (yeah, and kinda know it is the latter, just trying to avoid that thought for a moment).

We do have recursive queries so should be easy enough to calculate an extra load, just a bit concerned about the aggregation of those differences (maybe), but havent really spent any time thinking about it (just off the top of my head comments).

Isn't
FROM Table WHERE Condition
the same as
FROM (SELECT * FROM Table WHERE Condition)?

I had the impression that the query optimizer (or whatever the name is) read the code in advance and ran both the same way.
In this specific case, there is no difference in performance, so either solution is good.

Without going through all cases one by one, this seems to be working perfectly. I've tested on a few of the more complicated cases and this is working fine.

As for the overlapping table, I've ran a preliminary test and it seems to work properly, at first glance. I'll do further tests later, but this is not the original question as posted, so I'm going to award the answer. If I require changes, I'll open a new related question for it (though I have yet to know how to do that. Previous efforts to create a related question have failed).

Thanks a lot for your help. Now that the solution works, I'll try to understand how the query itself works, because I love learning this kind of things. :)

Yep, the query optimizer does do a good job, and it isnt really optimizing the code as such, it is really choosing the best query plan to use. The way the code is written can suggest different query plans (along with any query hints). It is a "cost based" optimizer and will not try every possible combination, so, we can help identify "costs".

But having said that, it often seems that the optimizer has a mind of its own basing its decisions on various stats, cached plans etc...

In this case you are likely to be correct - it makes no difference (other than the way I was writing it and wanting to easily debug by simply commenting out a few lines here and there, along with a potential aggregation within the subquery).

But that might not always be the case where a subquery can simply be incorporated into an outer query and the optimizer behaves the same.

If when reading the code, it logically gives no real advantage to have a subquery, then it is likely that the optimizer will react the same way in terms of query plan. In our case the "WHERE" predicate will probably result in a table scan (well, table spools and concatenations which happens with recursive queries), so doesnt make much difference.

But if I had "join", and more "where" and calculated columns, or differing groups bys, or other predicates (like "having"), then it doesnt always hold true that : "FROM Table WHERE Condition" is same as "FROM (SELECT * FROM Table WHERE Condition)" because of what is happening next with joins, evaluating predicates, or the final select list with respect to any more "where" conditions...

(but again, in this specific case it doesnt really make any difference)

What will always be true is (functionally) if you can comment out lines and still preserve the query, then it probably should be left with commented out lines. So, the query in question, could have been better written as you have suggested :

  select FK_ID_intEmpresa,FK_ID_intFuncionario,FK_ID_intTipoTrabalho,min(datHoraEntradaTrabalho) as datHoraEntradaTrabalho,max(datHoraSaidaTrabalho) as datHoraSaidaTrabalho
         , datediff(day,min(datHoraEntradaTrabalho),max(datHoraSaidaTrabalho)) + 1 as Total
--  from  (
--          select * 
          from cte_period_commence
          where datediff(day,priorstart,nextend) > @days
--         ) P
  group by FK_ID_intEmpresa,FK_ID_intFuncionario,group_break,FK_ID_intTipoTrabalho,priorstart,nextend
  order by 1,2,4,5,3

Select allOpen in new window

I have no doubts whatsoever that the query should work with overlapping dates - the datediff to check for contiguous days is simply check for < 2 so includes overlapping dates (which would go negative).

If you ever want the total days consumed within the period though (within priorstart and nextend), then that is when it currently only considers a datediff and that might not yield the correct results.

So, I did have considerable concern as to what we needed to measure - if contiguous days (as written) or consumed days.

But you will also notice that we removed those two "checking" total columns from the final result so that they no longer report potential conflicts from any overlapping dates. And there was a deliberate reason to exclude them from the last tests.

And time to come clean to explain why they were removed (and the invention of the "P" subquery)....

I did have some overlapping dates in my test, so was OK with the concept, but having tested with overlapping dates is also the reason why I removed those columns and hence my comment above "We do have recursive queries so should be easy enough to calculate an extra load, just a bit concerned about the aggregation of those differences..."

 The concern comes in in a case where (showing datediff, not inclusive days the "plus 1") :

2012-01-01 to 2012-01-21  = 20 days
2012-01-02 to 2012-01-22  = 20 days

19 of those days are overlapping, but, looking at the "range" of days

2012-01-01 to 2021-01-22  = 21 days

And will not be reported because the range of different (contiguous) days is only 21.

So, we need to consider the extra "load" on days by including the (aggregation) of those overlapping days - the extra 19 days added to our range of 21 to give us the real total of 40 days being actually consumed (rather than 21 days being the measure of contiguous days).

That subquery was heading off into the wild blue yonder doing some of that calculating "load", just a slightly different basis for aggregation so we could sum() in that load to our date range... That different aggregation meant we needed to subquery to find the qualifying entries before we could start doing the reporting of them given the different basis of the reporting "group by".

Oh, for for overlapping dates, the ARN and DRN row_number() needs to change just a little to make sure we get those row_numbers() into correct descending and ascending sequence.

Right now they just consider (an assumed unique) datHoraEntradaTrabalho for sequencing of dates.

As for the syntax, thanks. I learned a lot with that.

As for the dates, your example shouldn't return anything. I really only want contiguous days, not aggregated.

I'm not sure if the query does need changes. It was a cursory glance, but the results seemed to be correct. I can't be sure until I examine them more closely, but that will only happen on monday. I'll let you know how it goes.

Ok, I noticed a small "bug". Not too important, but I'm not sure why it happens. Given this data:

FK_ID_intEmpresa	FK_ID_intFuncionario	FK_ID_intTipoTrabalho	datHoraEntradaTrabalho	datHoraSaidaTrabalho
32	14886	Lic. Parental	2012-08-16 00:00:00	2012-09-15 00:00:00
32	14886	Lic. Parental	2012-09-16 00:00:00	2012-10-15 00:00:00
32	14886	Doença	2011-07-26 00:00:00	2011-08-15 00:00:00
32	14886	Doença	2012-02-05 00:00:00	2012-02-11 00:00:00
32	14886	Doença	2012-02-16 00:00:00	2012-02-27 00:00:00
32	14886	Doença	2012-03-07 00:00:00	2012-03-15 00:00:00
32	14886	Doença	2012-03-16 00:00:00	2012-04-15 00:00:00
32	14886	Doença	2012-04-16 00:00:00	2012-05-15 00:00:00
32	14886	Doença	2012-05-16 00:00:00	2012-06-07 00:00:00
32	14886	Lic. Parental	2012-06-08 00:00:00	2012-06-15 00:00:00
32	14886	Lic. Parental	2012-06-08 00:00:00	2012-07-15 00:00:00
32	14886	Lic. Parental	2012-07-16 00:00:00	2012-08-15 00:00:00

Select allOpen in new window

The query returns:

14886	Doença	07-03-2012	07-06-2012	93	07-03-2012 to 15-10-2012	223
14886	Lic. Parental	08-06-2012	15-07-2012	38	07-03-2012 to 15-10-2012	223
14886	Lic. Parental	16-07-2012	15-10-2012	92	16-07-2012 to 15-10-2012	92

Select allOpen in new window

Not sure why it's returning the last line, especially why it's with a different range. It's something that you can easily identify when looking at it, but it could be complicated for any code to identify and disregard.

This one seems more strange:

FK_ID_intEmpresa	FK_ID_intFuncionario	FK_ID_intTipoTrabalho	datHoraEntradaTrabalho	datHoraSaidaTrabalho
32	14143	Doença	2012-09-13 00:00:00	2012-10-18 00:00:00
32	14143	Parto	2012-09-01 00:00:00	2012-09-14 00:00:00
32	14143	Parto	2012-08-01 00:00:00	2012-08-31 00:00:00
32	14143	Doença	2010-09-21 00:00:00	2010-10-15 00:00:00
32	14143	Doença	2010-10-16 00:00:00	2010-12-31 00:00:00
32	14143	Doença	2011-07-05 00:00:00	2011-07-17 00:00:00
32	14143	Doença	2011-07-05 00:00:00	2011-07-31 00:00:00
32	14143	Doença	2011-12-26 00:00:00	2012-01-18 00:00:00
32	14143	Doença	2012-01-01 00:00:00	2012-01-31 00:00:00
32	14143	Doença	2012-01-24 00:00:00	2012-02-12 00:00:00
32	14143	Doença	2012-02-13 00:00:00	2012-02-23 00:00:00
32	14143	Doença	2012-03-24 00:00:00	2012-03-12 00:00:00
32	14143	Doença	2012-02-24 00:00:00	2012-03-12 00:00:00
32	14143	Doença	2012-03-13 00:00:00	2012-04-11 00:00:00
32	14143	Doença	2012-04-01 00:00:00	2012-04-17 00:00:00
32	14143	Parto	2012-04-18 00:00:00	2012-04-30 00:00:00
32	14143	Parto	2012-05-01 00:00:00	2012-05-31 00:00:00
32	14143	Parto	2012-06-01 00:00:00	2012-06-30 00:00:00
32	14143	Parto	2012-07-01 00:00:00	2012-07-31 00:00:00

Select allOpen in new window

results:

14143	Doença	01-01-2012	11-04-2012	102	01-01-2012 to 12-03-2012	72
14143	Doença	01-04-2012	17-04-2012	17	01-04-2012 to 18-10-2012	201
14143	Parto	18-04-2012	14-09-2012	150	01-04-2012 to 18-10-2012	201
14143	Doença	13-09-2012	18-10-2012	36	01-04-2012 to 18-10-2012	201

Select allOpen in new window

First and second lines should be part of the same. However, I detected that one record had a start date after the end date. When I removed it, it returned the desired results. If that is the only problem, then nothing need be done. I just need to change the query that fills the table to filter these invalid records.

>> one record had a start date after the end date

Yeah, think I assume that they will be all OK, so the sequences are probably mucked up and definitely not being checked for.

I will test both the above two scenarios and see what I can see...

As I said, if it was only that, then don't worry about it. I have set the query to ignore these when populating the table. They shouldn't exist in the first place anyway.

One other small thing I noticed:

13053	Assist. Familia	01-01-2012	24-02-2012	55	01-01-2012 to 20-11-2012	325
13053	Doença	25-02-2012	25-03-2012	30	01-01-2012 to 20-11-2012	325
13053	Assist. Familia	26-03-2012	24-04-2012	30	01-01-2012 to 20-11-2012	325
13053	Doença	26-03-2012	24-04-2012	30	01-01-2012 to 20-11-2012	325
13053	Assist. Familia	25-04-2012	20-11-2012	210	01-01-2012 to 20-11-2012	325

Select allOpen in new window

The 3rd and 5th line should be grouped. This is probably because of the order and because both the 3rd and 4th lines start on the same date. Don't know if this is fixable, but if not, it's not too important, because the full period and total count are correct.

The first case (14886), now returns:

14886	Doença	07-03-2012	07-06-2012	93	07-03-2012 to 15-06-2012	101
14886	Lic. Parental	08-06-2012	15-07-2012	38	07-03-2012 to 15-06-2012	101
14886	Lic. Parental	16-07-2012	15-10-2012	92	07-03-2012 to 15-10-2012	223

Select allOpen in new window

No change was made, so I have no idea why it did this. Still not correct though.

One thing that should be noted is that I'm restricting records to 2012. If a record starts before 2012, I simply insert it as 01-01-2012. Likewise for after 2012 (31-12-2012). Shouldn't make much difference, but I thought you should know.

Yeah, that one could get interesting... Same dates... and all of it fits within the same "group" period.

If this is too much trouble, just let it go. The way it is now is good enough. I just like it to be as best as it possibly can, especially if I intend to automatize things in the future. But this is already great the way it is, especially because I had nothing like this before.

Yet another strange case, probably because of all the rows that start with the same date. In this case, it's the total period that is incorrect:

FK_ID_intEmpresa	FK_ID_intFuncionario	FK_ID_intTipoTrabalho	datHoraEntradaTrabalho	datHoraSaidaTrabalho
32	9045	Doença	2012-01-01 00:00:00	2012-01-04 00:00:00
32	9045	Parto	2012-01-05 00:00:00	2012-02-29 00:00:00
32	9045	Lic. Parental	2012-01-05 00:00:00	2012-06-02 00:00:00
32	9045	Parto	2012-01-05 00:00:00	2012-01-31 00:00:00
32	9045	Parto	2012-02-01 00:00:00	2012-03-31 00:00:00
32	9045	Parto	2012-03-15 00:00:00	2012-04-16 00:00:00

Select allOpen in new window

results:

9045	Parto	05-01-2012	29-02-2012	56	01-01-2012 to 16-04-2012	107
9045	Lic. Parental	05-01-2012	02-06-2012	150	01-01-2012 to 16-04-2012	107
9045	Parto	01-02-2012	16-04-2012	76	01-01-2012 to 16-04-2012	107

Select allOpen in new window

Sorry, it actually results:

9045	Doença	01-01-2012	04-01-2012	4	01-01-2012 to 16-04-2012	107
9045	Parto	05-01-2012	29-02-2012	56	01-01-2012 to 16-04-2012	107
9045	Lic. Parental	05-01-2012	02-06-2012	150	01-01-2012 to 16-04-2012	107
9045	Parto	01-02-2012	16-04-2012	76	01-01-2012 to 16-04-2012	107

Select allOpen in new window

The first line wasn't pasted the first time.

This last one is actually important. I mean, as long as the total period is correct, the exact way each individual one is presented isn't as important. But the total period must be assured to be correct. If you want, I'll open a new question, as this is a new problem not completely related to OQ.

Maybe... Can get back to checking code in a few hours. Can you wait a little before opening a new question ?

Seems to be with overlapping dates and have a few ideas (remember the ARN and DRN classification only used start date and expected them to be unique per row).

I'll post back here soonish...

I'm not in any hurry. This particular case will only be useful in a couple months, most likely.

Well, did you advance anything or should I open a new question?

You will never guess what I am in the middle of :)

:)
As I said, there is no rush. I just thought that, if it's giving you much trouble, I might open another question. After all, it's a different solution, so you should be awarded more points.

OK, with that last one, I am pretty sure it is the row_number() function - needs to take into account the end and start dates to make sure of the sequences.

But it is probably unfair of me to excercise your patience for so very long.

So, open up the new question - there are a lot of brilliant experts in SQL and I am sure that someone (hopefully me) will be able to assist you without you having to wait all this time just on me...

declare @Empresa int = 32, @days int = 30

  if object_id('tempdb..#tmp_tblTrabalho','U') is not null drop table #tmp_tblTrabalho
  ;

  select row_number() over (partition by FK_ID_intEmpresa,FK_ID_intFuncionario order by datHoraSaidaTrabalho DESC, datHoraEntradaTrabalho DESC) as DRN
         , row_number() over (partition by FK_ID_intEmpresa,FK_ID_intFuncionario order by datHoraEntradaTrabalho ASC,datHoraEntradaTrabalho ASC) as ARN
         , * into #tmp_tblTrabalho 
  from tblTrabalho
  where FK_ID_intEmpresa = @empresa
  ;

  create index idx_tmp_tblTrabalho_drn on #tmp_tblTrabalho (drn,FK_ID_intEmpresa,FK_ID_intFuncionario)
  ;

  create index idx_tmp_tblTrabalho_arn on #tmp_tblTrabalho (Arn,FK_ID_intEmpresa,FK_ID_intFuncionario)
  ;

  ; with cte_period_ends as
  ( select drn, arn, FK_ID_intEmpresa,FK_ID_intFuncionario,FK_ID_intTipoTrabalho,datHoraEntradaTrabalho,datHoraSaidaTrabalho, datHoraSaidaTrabalho as nextend, datHoraEntradaTrabalho as priorstart
    from  #tmp_tblTrabalho 
    where DRN = 1
    union all
    select T.DRN, T.ARN, E.FK_ID_intEmpresa,T.FK_ID_intFuncionario,T.FK_ID_intTipoTrabalho,T.datHoraEntradaTrabalho,T.datHoraSaidaTrabalho, 
           case when datediff(day,T.datHoraSaidaTrabalho,E.datHoraEntradaTrabalho) < 2 then E.nextend else T.datHoraSaidaTrabalho end, T.datHoraEntradaTrabalho 
    from  cte_period_ends E
    inner join #tmp_tblTrabalho T on T.FK_ID_intEmpresa = E.FK_ID_intEmpresa and T.FK_ID_intFuncionario = E.FK_ID_intFuncionario and T.DRN = E.DRN + 1
  )
  , cte_period_commence as
  ( select DRN, ARN, FK_ID_intEmpresa,FK_ID_intFuncionario,FK_ID_intTipoTrabalho,datHoraEntradaTrabalho,datHoraSaidaTrabalho, nextend, priorstart, 1 as group_break
    from  cte_period_ends 
    where ARN = 1
    union all
    select E.DRN, E.ARN, C.FK_ID_intEmpresa,E.FK_ID_intFuncionario,E.FK_ID_intTipoTrabalho,E.datHoraEntradaTrabalho,E.datHoraSaidaTrabalho, E.nextend,
           case when datediff(day,c.datHoraSaidaTrabalho,E.datHoraEntradaTrabalho) < 2 then C.priorstart else E.datHoraEntradaTrabalho end,
           case when C.FK_ID_intTipoTrabalho = E.FK_ID_intTipoTrabalho then c.group_break else c.group_break + 1 end 
    from  cte_period_commence C
    inner join cte_period_ends E on E.FK_ID_intEmpresa = c.FK_ID_intEmpresa and E.FK_ID_intFuncionario = c.FK_ID_intFuncionario and E.ARN = C.ARN + 1
  )
  select FK_ID_intEmpresa,FK_ID_intFuncionario,FK_ID_intTipoTrabalho,min(datHoraEntradaTrabalho) as datHoraEntradaTrabalho,max(datHoraSaidaTrabalho) as datHoraSaidaTrabalho
         , datediff(day,min(datHoraEntradaTrabalho),max(datHoraSaidaTrabalho)) + 1 as Total
  from  (
          select * 
          from cte_period_commence
          where datediff(day,priorstart,nextend) >= @days
     ) P
  group by FK_ID_intEmpresa,FK_ID_intFuncionario,group_break,FK_ID_intTipoTrabalho,priorstart,nextend
  order by 1,2,4,5,3
  ;

  if object_id('tempdb..#tmp_tblTrabalho','U') is not null drop table #tmp_tblTrabalho
  ;

Select allOpen in new window

Right...

Made changes to the above, so ignore that.

Those overlapping dates are really a pain...

For "Lic. Parental" we have 150 days but a fair portion of those overlap with others... for example, Parto is wholly contained within "Lic Parental" dates, but because they are different TipoTrabalho then they show...

Anyway, check out the code below and please let me know any errors and the expected outcome (along with the input / raw as you have done above).

declare @Empresa int = 32, @days int = 30

  if object_id('tempdb..#tmp_tblTrabalho','U') is not null drop table #tmp_tblTrabalho
  ;

  select row_number() over (partition by FK_ID_intEmpresa,FK_ID_intFuncionario order by datHoraSaidaTrabalho DESC, datHoraEntradaTrabalho DESC) as DRN
       , row_number() over (partition by FK_ID_intEmpresa,FK_ID_intFuncionario order by datHoraEntradaTrabalho ASC, datHoraSaidaTrabalho ASC) as ARN
       , * into #tmp_tblTrabalho 
  from tblTrabalho T
  where FK_ID_intEmpresa = @empresa
  and datHoraSaidaTrabalho > datHoraEntradaTrabalho              -- NEW CODE to exlcude erroneous dates
  and not exists (select NULL from  tblTrabalho T1               -- NEW CODE to exclude wholly contained dupe of Empressa+Funcionario+TipoTrabalho
                              where T.FK_ID_intEmpresa = T1.FK_ID_intEmpresa 
                              and   T.FK_ID_intFuncionario = T1.FK_ID_intFuncionario  
                              and   T.FK_ID_intTipoTrabalho = T1.FK_ID_intTipoTrabalho
                              and ((T.datHoraEntradaTrabalho >= T1.datHoraEntradaTrabalho and T.datHoraSaidaTrabalho < T1.datHoraSaidaTrabalho)
                              or   (T.datHoraEntradaTrabalho > T1.datHoraEntradaTrabalho and T.datHoraSaidaTrabalho <= T1.datHoraSaidaTrabalho))
                 );
				   
  select * from #tmp_tblTrabalho order by 2,1
  select * from #tmp_tblTrabalho order by 1,2


  create index idx_tmp_tblTrabalho_drn on #tmp_tblTrabalho (drn,FK_ID_intEmpresa,FK_ID_intFuncionario)
  ;

  create index idx_tmp_tblTrabalho_arn on #tmp_tblTrabalho (Arn,FK_ID_intEmpresa,FK_ID_intFuncionario)
  ;
  
  ; with cte_period_ends as
  ( select drn, arn, FK_ID_intEmpresa,FK_ID_intFuncionario,FK_ID_intTipoTrabalho,datHoraEntradaTrabalho,datHoraSaidaTrabalho, datHoraSaidaTrabalho as nextend, datHoraEntradaTrabalho as priorstart
    from  #tmp_tblTrabalho 
    where DRN = 1
    union all
    select T.DRN, T.ARN, E.FK_ID_intEmpresa,T.FK_ID_intFuncionario,T.FK_ID_intTipoTrabalho,T.datHoraEntradaTrabalho,T.datHoraSaidaTrabalho, 
           case when datediff(day,T.datHoraSaidaTrabalho,E.datHoraEntradaTrabalho) < 2 then E.nextend else T.datHoraSaidaTrabalho end, T.datHoraEntradaTrabalho 
    from  cte_period_ends E
    inner join #tmp_tblTrabalho T on T.FK_ID_intEmpresa = E.FK_ID_intEmpresa and T.FK_ID_intFuncionario = E.FK_ID_intFuncionario and T.DRN = E.DRN + 1
  )
  , cte_period_commence as
  ( select DRN, ARN, FK_ID_intEmpresa,FK_ID_intFuncionario,FK_ID_intTipoTrabalho,datHoraEntradaTrabalho,datHoraSaidaTrabalho, nextend, priorstart, 1 as group_break
    from  cte_period_ends 
    where ARN = 1
    union all
    select E.DRN, E.ARN, C.FK_ID_intEmpresa,E.FK_ID_intFuncionario,E.FK_ID_intTipoTrabalho,E.datHoraEntradaTrabalho,E.datHoraSaidaTrabalho, E.nextend,
           case when datediff(day,c.datHoraSaidaTrabalho,E.datHoraEntradaTrabalho) < 2 then C.priorstart else E.datHoraEntradaTrabalho end,
           case when C.FK_ID_intTipoTrabalho = E.FK_ID_intTipoTrabalho then c.group_break else c.group_break + 1 end 
    from  cte_period_commence C
    inner join cte_period_ends E on E.FK_ID_intEmpresa = c.FK_ID_intEmpresa and E.FK_ID_intFuncionario = c.FK_ID_intFuncionario and E.ARN = C.ARN + 1
  )

 --         select * 
 --         from cte_period_commence
 --         where datediff(day,priorstart,nextend) >= @days


  select FK_ID_intEmpresa,FK_ID_intFuncionario,FK_ID_intTipoTrabalho,min(datHoraEntradaTrabalho) as datHoraEntradaTrabalho,max(datHoraSaidaTrabalho) as datHoraSaidaTrabalho
         , datediff(day,min(datHoraEntradaTrabalho),max(datHoraSaidaTrabalho)) + 1 as Total_TipoTrabalho_Days
         , priorstart as Empressa_Funcionario_Period_Start, nextend as Empressa_Funcionario_Period_End, datediff(day, priorstart, nextend) + 1 as Total_Period_Days
  from cte_period_commence
  where datediff(day,priorstart,nextend) >= @days
  group by FK_ID_intEmpresa,FK_ID_intFuncionario,group_break,FK_ID_intTipoTrabalho,priorstart,nextend
  order by 1,2,4,5,3
  ;

  if object_id('tempdb..#tmp_tblTrabalho','U') is not null drop table #tmp_tblTrabalho
  ;

Select allOpen in new window

I won't really need this for at least another month. It might have some other uses, but those aren't important right now. So if you want a shot at it, I don't mind waiting.

Regarding your code above:
1- ARN has the same field twice. Which position is datHoraSaidaTrabalho supposed to be in?
2- DRN orders datHoraSaidaTrabalho before datHoraEntradaTrabalho. Is this how it should be? If so, why? Wouldn't you want to order by in before ordering by out?

Using
ORDER BY datHoraEntradaTrabalho ASC,datHoraSaidaTrabalho ASC) ARN
the total period isn't quite correct. Nor does it fully group the results. For example, given these values:

32	6853	Doença	2012-06-21 00:00:00	2012-07-19 00:00:00
32	6853	Doença	2012-07-20 00:00:00	2012-08-19 00:00:00
32	6853	Doença	2012-08-20 00:00:00	2012-09-27 00:00:00
32	6853	Parto	2012-08-28 00:00:00	2012-09-15 00:00:00
32	6853	Parto	2012-09-16 00:00:00	2012-10-15 00:00:00
32	6853	Parto	2012-10-16 00:00:00	2012-11-15 00:00:00
32	6853	Parto	2012-11-16 00:00:00	2012-12-15 00:00:00

Select allOpen in new window

It returns:

6853	Doença	21-06-2012	19-08-2012	60	21-06-2012 to 19-08-2012	60
6853	Doença	20-08-2012	27-09-2012	39	21-06-2012 to 15-12-2012	178
6853	Parto	28-08-2012	15-12-2012	110	21-06-2012 to 15-12-2012	178

Select allOpen in new window

Nah, you didnt use the latest code....

But DRN is meant to be picking up the "last" date in a sequence. ARN then goes down the First date in a sequence. If they are adjoing dates then we carry over the "last" (ie the biggest in DRN sequence) or carry over the "first" (ie the smallest in ARN sequence).

We cant simply do one sequence - we have to go both directions.

Last post was made before I saw the new code. However, they return the same for this situation. Not sure why it's not grouping the total period. Probably for the same reason it's not grouping Doença as well.

You don't need to worry about inverted dates. Those are already filtered beforehand and cannot exist in the table anymore.

The latest data sample wont group because of a date being wholly contained within another - so - the sequence is thrown out...

Doença    2012-08-20 00:00:00   2012-09-27 00:00:00
Parto       2012-08-28 00:00:00   2012-09-15 00:00:00

Here parto is wholly contained, so either which way it checks, it stumbles across something "out of sequence" in essence.

So, with this approach you can't get the ranges right for these cases? Does it require a different approach (in which case I open a new question) or just some fiddling (in which case I wait a bit more)?

Well, no, not with the need to currently check for contiguous days, but then break up by TipoTrabalho - because while the dates overlap, they are different TipoTrabalho...

We could simply check contiguous days, then report any TipoTrabalho that falls with those days. At the moment it is kind of doing both at the same time, and while we might want sequence to follow a specific TipoTrabalho in some cases, we still need to follow dates by empresa and funcionario.

I would be inclined to report contiguous periods (just by empresa and funcionario ignoring TipoTrabalho), then from those dates, simply report the number of days that each TipoTrabahlo consumes (without trying to link up consecutive dates for the TipoTrabalho), or, show the details of each row that qualifies within the period as a detail item sorted into TipoTrabalho and then date sequence. The challenge we have is that the 30 days might be made up from different TipoTrabalho's which is what we are currently doing...

Maybe we can come up with some "pre-canned" scenarios and show what is required from each scenario with overlapping dates, contained dates, for multiple TipoTrabalho's.

And yes, think the scenario has changed with the overlapping periods to warrant a new question. The original scenario without overlapping dates is pretty much handled, and it might take on a new approach (will probably need to) for the new scenarios.

I will open a new question tomorrow. It's only fair that you get credited (or have a shot at it) for the extra work. Thanks for your effort, even though the question was already closed. I really appreciate your help.

Question opened here:
https://www.experts-exchange.com/questions/27983690/T-SQL-to-group-contiguous-overlapping-dates.html