Group by contiguous dates in MS Access

Question

I have a table in our database keeps track of all our teams, their name, their lead and to which team (and manager) they are hierarchically subordinate. There will be a row for each day/team combination.

DAY         | TEAM    | MANAGER | PARENT_TEAM | PARENT_MANAGER
01/01/2017  | TeamRed | John    | GroupWendy  | Wendy
...
05/01/2017  | TeamRed | John    | GroupKarl   | Karl
...
10/05/2017  | TeamRed | John    | GroupWendy  | Wendy

Now, my task is to group this data to create a "historical" report. Something like this:

TEAM    | MANAGER | PARENT_TEAM | PARENT_MANAGER | VALID_FROM | VALID_TO
TeamRed | John    | GroupWendy  | Wendy          | 01/01/2017 | 04/01/2017
TeamRed | John    | GroupKarl   | Karl           | 05/01/2017 | 10/05/2017
TeamRed | John    | GroupWendy  | Wendy          | 11/05/2017 | 31/12/2099

Normally I would proceed by grouping by the string fields and setting the dates to min and max. The catch that I cannot overcome is that a team can "jump" back and forth from a parent team, and I hope the example reflects this.

using

SELECT team, manager, parent_team, parent_manager, MIN(day) AS valid_from, MAX(day) AS valid_to
FROM tblMock
GROUP BY team, manager, parent_team, parent_manager;

would not produce the result above (desired) but rather

TEAM    | MANAGER | PARENT_TEAM | PARENT_MANAGER | VALID_FROM | VALID_TO
TeamRed | John    | GroupWendy  | Wendy          | 01/01/2017 | 31/12/2099
TeamRed | John    | GroupKarl   | Karl           | 05/01/2017 | 10/05/2017

Thank you to whomever might help and final disclaimer, hope this was not asked before, but the search I performed did not really help. Cheers, Igor

Answer 1

I tried to do something, but what I did is not really a simple query. I have not MSACCESS now, but I tried it on MSSQL,trying to use only commands and function available in MSACCESS. It's possibile query can be simplified. It's possibile you can do something simpler using VBA functions (I tried to use SQL only).

Pls, let me know.

SELECT F.TEAM, F.MANAGER, F.PARENT_TEAM, F.PARENT_MANAGER, MAX(DATE_START) AS DATE_START, DATE_TO
 FROM 
(
  SELECT E.TEAM, E.MANAGER, E.PARENT_TEAM, E.parent_manager, E.day AS DATE_TO       
            FROM 
            (SELECT D.*, CASE WHEN DAY=DATEADD(dd,-1,SUCC)  THEN 1 WHEN DAY=DATEADD(dd,1,PREC) THEN 2 ELSE 0 END AS GR 
            FROM 
            (SELECT A.TEAM, A.MANAGER, A.PARENT_TEAM, A.PARENT_MANAGER, A.day
                    , (SELECT MIN(DAY) AS DAY_SUCC FROM TM B WHERE A.TEAM=B.TEAM AND A.MANAGER=B.MANAGER AND A.PARENT_TEAM=B.PARENT_TEAM AND A.parent_manager=B.parent_manager AND B.day>A.day ) AS SUCC
                    , (SELECT MAX(DAY) AS DAY_PREC FROM TM B WHERE A.TEAM=B.TEAM AND A.MANAGER=B.MANAGER AND A.PARENT_TEAM=B.PARENT_TEAM AND A.parent_manager=B.parent_manager AND B.day<A.day )  AS PREC                   
                  FROM TM A
            ) D ) E WHERE E.GR=2 OR E.GR=0
    ) F 
INNER JOIN (
SELECT E.TEAM, E.MANAGER, E.PARENT_TEAM, E.parent_manager, E.day AS DATE_START 
            FROM 
            (SELECT D.*, CASE WHEN DAY=DATEADD(dd,-1,SUCC)  THEN 1 WHEN DAY=DATEADD(dd,1,PREC) THEN 2 ELSE 0 END AS GR 
            FROM 
            (SELECT A.TEAM, A.MANAGER, A.PARENT_TEAM, A.PARENT_MANAGER, A.day
                    , (SELECT MIN(DAY) AS DAY_SUCC FROM TM B WHERE A.TEAM=B.TEAM AND A.MANAGER=B.MANAGER AND A.PARENT_TEAM=B.PARENT_TEAM AND A.parent_manager=B.parent_manager AND B.day>A.day ) AS SUCC
                    , (SELECT MAX(DAY) AS DAY_PREC FROM TM B WHERE A.TEAM=B.TEAM AND A.MANAGER=B.MANAGER AND A.PARENT_TEAM=B.PARENT_TEAM AND A.parent_manager=B.parent_manager AND B.day<A.day )  AS PREC
                    , CASE WHEN (SELECT MAX(DAY) AS DAY_PREC FROM TM B WHERE A.TEAM=B.TEAM AND A.MANAGER=B.MANAGER AND A.PARENT_TEAM=B.PARENT_TEAM AND A.parent_manager=B.parent_manager AND B.day<A.day ) = DATEADD(dd,-1,A.DAY) THEN (SELECT MAX(DAY) AS DAY_PREC FROM TM B WHERE A.TEAM=B.TEAM AND A.MANAGER=B.MANAGER AND A.PARENT_TEAM=B.PARENT_TEAM AND A.parent_manager=B.parent_manager AND B.day<A.day )  ELSE NULL END  AS PREC2                   
                  FROM TM A
            ) D ) E WHERE E.PREC2 IS NULL
) G ON F.TEAM=G.TEAM AND F.MANAGER=G.MANAGER AND F.PARENT_TEAM=G.PARENT_TEAM AND F.parent_manager=G.parent_manager AND G.DATE_START<=F.DATE_TO  
  GROUP BY F.TEAM, F.MANAGER, F.PARENT_TEAM, F.PARENT_MANAGER, DATE_TO
  ORDER BY 5,4;

Sample data (I added some rows at yours):

INSERT INTO TM VALUES ('2017-01-01','TeamRed','John','GroupWendy','Wendy');
INSERT INTO TM VALUES ('2017-01-02','TeamRed','John','GroupWendy','Wendy');
INSERT INTO TM VALUES ('2017-01-03','TeamRed','John','GroupWendy','Wendy');
INSERT INTO TM VALUES ('2017-01-04','TeamRed','John','GroupWendy','Wendy');    
INSERT INTO TM VALUES ('2017-01-02','TeamRed','John','GroupKarl','Karl');    
INSERT INTO TM VALUES ('2017-01-05','TeamRed','John','GroupKarl','Karl');
INSERT INTO TM VALUES ('2017-01-06','TeamRed','John','GroupKarl','Karl');
INSERT INTO TM VALUES ('2017-01-07','TeamRed','John','GroupKarl','Karl');
INSERT INTO TM VALUES ('2017-01-08','TeamRed','John','GroupKarl','Karl');
INSERT INTO TM VALUES ('2017-05-11','TeamRed','John','GroupWendy','Wendy');
INSERT INTO TM VALUES ('2017-05-12','TeamRed','John','GroupWendy','Wendy');
INSERT INTO TM VALUES ('2017-06-14','TeamRed','John','GroupKarl','Karl');

Output:

TEAM    MANAGER PARENT_TEAM PARENT_MANAGER  DATE_START  DATE_TO
TeamRed John    GroupWendy  Wendy       2017-01-01  2017-01-04
TeamRed John    GroupKarl   Karl        2017-01-02  2017-01-02
TeamRed John    GroupKarl   Karl        2017-01-05  2017-01-08
TeamRed John    GroupWendy  Wendy       2017-05-11  2017-05-12
TeamRed John    GroupKarl   Karl        2017-06-14  2017-06-14

Answer 2

Try the following query (unfortunately, this is some complex SQL):

SELECT t.Team, t.Manager, t.parent_team, 
(
    SELECT Min(s.Day) FROM s WHERE s.Day > t.MaxPreviousTeam OR t.MaxPreviousTeam Is Null
) As VALID_FROM, t.VALID_TO
FROM (
    SELECT f.Team, f.Manager, f.parent_team, Max(f.Day) As VALID_TO, 
    (
        SELECT Max(Day) FROM tblMock fs WHERE fs.Team <> t.Team AND Day < VALID_TO
    ) As MaxPreviousTeam 
    FROM tblMock f
    GROUP BY f.team, f.manager, f.parent_team, f.parent_manager
) AS t

I can explain some of the steps when needed. Essentially, I first select all columns you need except VALID_FROM, and instead, calculate the highest value lower than VALID_TO where the team is not the same. Then, I grab all those values, and the lowest value that is higher than that value I just calculated.