-- Contador de una fase
SELECT p.name, p.title phase , numTotal, numActive, numEnded, numDelayed 
FROM us_timeline_phase p 
LEFT JOIN (
	SELECT _d.name, _d.title phase, count(tla.id) AS numTotal 
    FROM us_timeline_timeline_activity tla 
    LEFT JOIN us_timeline_timeline tl ON tl.id = tla.timeline_id 
    LEFT JOIN us_timeline_timeline_phase tlp ON tlp.timeline_id = tl.id 
    LEFT JOIN us_timeline_phase _d ON _d.id = tlp.phase_id 
    WHERE _d.name = 'phase_1' 
    AND tla.start_date 
    BETWEEN tlp.start_date AND tlp.end_date 
    GROUP BY _d.id 
    ) numTotal ON numTotal.phase = p.name 
LEFT JOIN (
	SELECT _d1.name, _d1.title phase, count(tla.id) AS numActive 
    FROM us_timeline_timeline_activity tla 
    LEFT JOIN us_timeline_timeline tl ON tl.id = tla.timeline_id 
    LEFT JOIN us_timeline_timeline_phase tlp ON tlp.timeline_id = tl.id 
    LEFT JOIN us_timeline_phase _d1 ON _d1.id = tlp.phase_id 
    WHERE _d1.name = 'phase_1' 
    AND tla.start_date BETWEEN tlp.start_date AND tlp.end_date 
    AND tla.status = 1 
    GROUP BY _d1.id
) numActive ON numActive.name = p.name 
LEFT JOIN (SELECT _d2.name, _d2.title phase, count(tla.id) AS numEnded FROM us_timeline_timeline_activity tla LEFT JOIN us_timeline_timeline tl ON tl.id = tla.timeline_id LEFT JOIN us_timeline_timeline_phase tlp ON tlp.timeline_id = tl.id LEFT JOIN us_timeline_phase _d2 ON _d2.id = tlp.phase_id WHERE _d2.name = 'phase_1' AND tla.start_date BETWEEN tlp.start_date AND tlp.end_date AND tla.status = 2 GROUP BY _d2.id ) numEnded ON numEnded.name = p.name LEFT JOIN (SELECT _d4.name, _d4.title phase, count(tla.id) AS numDelayed FROM us_timeline_timeline_activity tla LEFT JOIN us_timeline_timeline tl ON tl.id = tla.timeline_id LEFT JOIN us_timeline_timeline_phase tlp ON tlp.timeline_id = tl.id LEFT JOIN us_timeline_phase _d4 ON _d4.id = tlp.phase_id WHERE _d4.name = 'phase_1' AND tla.start_date BETWEEN tlp.start_date AND tlp.end_date AND tla.status = 4 GROUP BY _d4.id ) numDelayed ON numDelayed.name = p.name GROUP BY p.name;

-- contador de phase_2

	SELECT phase_2_.name, phase_2_.title phase, count(tla.id) AS numTotal
	FROM us_timeline_timeline_activity tla
	LEFT JOIN us_timeline_timeline tl ON tl.id = tla.timeline_id
	LEFT JOIN us_timeline_timeline_phase tlp ON tlp.timeline_id = tl.id
	LEFT JOIN us_timeline_phase phase_2_ ON phase_2_.id = tlp.phase_id
	WHERE phase_2_.name = 'phase_2'
	AND tla.start_date BETWEEN tlp.start_date AND tlp.end_date
	GROUP BY phase_2_.id;
                
-- contador de un estado
SELECT _d.name, _d.title phase, count(tla.id) AS numTotal 
    FROM us_timeline_timeline_activity tla 
    LEFT JOIN us_timeline_timeline tl ON tl.id = tla.timeline_id 
    LEFT JOIN us_timeline_timeline_phase tlp ON tlp.timeline_id = tl.id 
    LEFT JOIN us_timeline_phase _d ON _d.id = tlp.phase_id 
    WHERE _d.name = 'phase_1' 
    AND tla.start_date 
    BETWEEN tlp.start_date AND tlp.end_date 
    GROUP BY _d.id ;
    
    
-- ---------------------------------------
-- total
-- ---------------------------------------
-- by MULTISELECTS
SELECT p.name, p.title phase, 
(SELECT count(tla.id) numTotal FROM us_timeline_timeline_activity tla LEFT JOIN us_timeline_timeline tl ON tl.id = tla.timeline_id LEFT JOIN us_timeline_timeline_phase tlp ON tlp.timeline_id = tl.id LEFT JOIN us_timeline_phase _p ON _p.id = tlp.phase_id WHERE _p.id = p.id AND tla.start_date BETWEEN tlp.start_date AND tlp.end_date GROUP BY _p.id ) numTotal,
(SELECT count(tla.id) FROM us_timeline_timeline_activity tla LEFT JOIN us_timeline_timeline tl ON tl.id = tla.timeline_id LEFT JOIN us_timeline_timeline_phase tlp ON tlp.timeline_id = tl.id LEFT JOIN us_timeline_phase _p1 ON _p1.id = tlp.phase_id WHERE _p1.id = p.id AND tla.start_date BETWEEN tlp.start_date AND tlp.end_date AND tla.status = 1 GROUP BY _p1.id ) numActive,
(SELECT count(tla.id) numEnded FROM us_timeline_timeline_activity tla LEFT JOIN us_timeline_timeline tl ON tl.id = tla.timeline_id LEFT JOIN us_timeline_timeline_phase tlp ON tlp.timeline_id = tl.id LEFT JOIN us_timeline_phase _p2 ON _p2.id = tlp.phase_id WHERE _p2.id = p.id AND tla.start_date BETWEEN tlp.start_date AND tlp.end_date AND tla.status = 2 GROUP BY _p2.id ) numEnded, 
(SELECT count(tla.id) FROM us_timeline_timeline_activity tla LEFT JOIN us_timeline_timeline tl ON tl.id = tla.timeline_id LEFT JOIN us_timeline_timeline_phase tlp ON tlp.timeline_id = tl.id LEFT JOIN us_timeline_phase _p4 ON _p4.id = tlp.phase_id WHERE _p4.id = p.id AND tla.start_date BETWEEN tlp.start_date AND tlp.end_date AND tla.status = 4 GROUP BY _p4.id ) numDelayed, 
(numEnded / numTotal * 100) compliancePercent 
FROM us_timeline_phase p;

-- by JOINS
( SELECT p.name, p.title phase , numTotal,  numActive, numEnded, numDelayed
                FROM us_timeline_phase p

                LEFT JOIN (SELECT phase_1_.name, phase_1_.title phase, count(tla.id) AS numTotal
                FROM us_timeline_timeline_activity tla
                LEFT JOIN us_timeline_timeline tl ON tl.id = tla.timeline_id
                LEFT JOIN us_timeline_timeline_phase tlp ON tlp.timeline_id = tl.id
                LEFT JOIN us_timeline_phase phase_1_ ON phase_1_.id = tlp.phase_id
                WHERE phase_1_.name = 'phase_1'
                AND tla.start_date BETWEEN tlp.start_date AND tlp.end_date

                GROUP BY phase_1_.id
                ) numTotal ON numTotal.name = p.name
                LEFT JOIN (SELECT phase_1_1.name, phase_1_1.title phase, count(tla.id) AS numActive
                FROM us_timeline_timeline_activity tla
                LEFT JOIN us_timeline_timeline tl ON tl.id = tla.timeline_id
                LEFT JOIN us_timeline_timeline_phase tlp ON tlp.timeline_id = tl.id
                LEFT JOIN us_timeline_phase phase_1_1 ON phase_1_1.id = tlp.phase_id
                WHERE phase_1_1.name = 'phase_1'
                AND tla.start_date BETWEEN tlp.start_date AND tlp.end_date

                 AND tla.status = 1 GROUP BY phase_1_1.id
                ) numActive ON numActive.name = p.name
                LEFT JOIN (SELECT phase_1_2.name, phase_1_2.title phase, count(tla.id) AS numEnded
                FROM us_timeline_timeline_activity tla
                LEFT JOIN us_timeline_timeline tl ON tl.id = tla.timeline_id
                LEFT JOIN us_timeline_timeline_phase tlp ON tlp.timeline_id = tl.id
                LEFT JOIN us_timeline_phase phase_1_2 ON phase_1_2.id = tlp.phase_id
                WHERE phase_1_2.name = 'phase_1'
                AND tla.start_date BETWEEN tlp.start_date AND tlp.end_date

                 AND tla.status = 2 GROUP BY phase_1_2.id
                ) numEnded ON numEnded.name = p.name
                LEFT JOIN (SELECT phase_1_4.name, phase_1_4.title phase, count(tla.id) AS numDelayed
                FROM us_timeline_timeline_activity tla
                LEFT JOIN us_timeline_timeline tl ON tl.id = tla.timeline_id
                LEFT JOIN us_timeline_timeline_phase tlp ON tlp.timeline_id = tl.id
                LEFT JOIN us_timeline_phase phase_1_4 ON phase_1_4.id = tlp.phase_id
                WHERE phase_1_4.name = 'phase_1'
                AND tla.start_date BETWEEN tlp.start_date AND tlp.end_date

                 AND tla.status = 4 GROUP BY phase_1_4.id
                ) numDelayed ON numDelayed.name = p.name

                WHERE p.name = 'phase_1'

                GROUP BY p.name
                ) UNION ( SELECT p.name, p.title phase , numTotal,  numActive, numEnded, numDelayed
                FROM us_timeline_phase p

                LEFT JOIN (SELECT phase_2_.name, phase_2_.title phase, count(tla.id) AS numTotal
                FROM us_timeline_timeline_activity tla
                LEFT JOIN us_timeline_timeline tl ON tl.id = tla.timeline_id
                LEFT JOIN us_timeline_timeline_phase tlp ON tlp.timeline_id = tl.id
                LEFT JOIN us_timeline_phase phase_2_ ON phase_2_.id = tlp.phase_id
                WHERE phase_2_.name = 'phase_2'
                AND tla.start_date BETWEEN tlp.start_date AND tlp.end_date

                GROUP BY phase_2_.id
                ) numTotal ON numTotal.name = p.name
                LEFT JOIN (SELECT phase_2_1.name, phase_2_1.title phase, count(tla.id) AS numActive
                FROM us_timeline_timeline_activity tla
                LEFT JOIN us_timeline_timeline tl ON tl.id = tla.timeline_id
                LEFT JOIN us_timeline_timeline_phase tlp ON tlp.timeline_id = tl.id
                LEFT JOIN us_timeline_phase phase_2_1 ON phase_2_1.id = tlp.phase_id
                WHERE phase_2_1.name = 'phase_2'
                AND tla.start_date BETWEEN tlp.start_date AND tlp.end_date

                 AND tla.status = 1 GROUP BY phase_2_1.id
                ) numActive ON numActive.name = p.name
                LEFT JOIN (SELECT phase_2_2.name, phase_2_2.title phase, count(tla.id) AS numEnded
                FROM us_timeline_timeline_activity tla
                LEFT JOIN us_timeline_timeline tl ON tl.id = tla.timeline_id
                LEFT JOIN us_timeline_timeline_phase tlp ON tlp.timeline_id = tl.id
                LEFT JOIN us_timeline_phase phase_2_2 ON phase_2_2.id = tlp.phase_id
                WHERE phase_2_2.name = 'phase_2'
                AND tla.start_date BETWEEN tlp.start_date AND tlp.end_date

                 AND tla.status = 2 GROUP BY phase_2_2.id
                ) numEnded ON numEnded.name = p.name
                LEFT JOIN (SELECT phase_2_4.name, phase_2_4.title phase, count(tla.id) AS numDelayed
                FROM us_timeline_timeline_activity tla
                LEFT JOIN us_timeline_timeline tl ON tl.id = tla.timeline_id
                LEFT JOIN us_timeline_timeline_phase tlp ON tlp.timeline_id = tl.id
                LEFT JOIN us_timeline_phase phase_2_4 ON phase_2_4.id = tlp.phase_id
                WHERE phase_2_4.name = 'phase_2'
                AND tla.start_date BETWEEN tlp.start_date AND tlp.end_date

                 AND tla.status = 4 GROUP BY phase_2_4.id
                ) numDelayed ON numDelayed.name = p.name

                WHERE p.name = 'phase_2'

                GROUP BY p.name
                ) UNION ( SELECT p.name, p.title phase , numTotal,  numActive, numEnded, numDelayed
                FROM us_timeline_phase p

                LEFT JOIN (SELECT phase_3_.name, phase_3_.title phase, count(tla.id) AS numTotal
                FROM us_timeline_timeline_activity tla
                LEFT JOIN us_timeline_timeline tl ON tl.id = tla.timeline_id
                LEFT JOIN us_timeline_timeline_phase tlp ON tlp.timeline_id = tl.id
                LEFT JOIN us_timeline_phase phase_3_ ON phase_3_.id = tlp.phase_id
                WHERE phase_3_.name = 'phase_3'
                AND tla.start_date BETWEEN tlp.start_date AND tlp.end_date

                GROUP BY phase_3_.id
                ) numTotal ON numTotal.name = p.name
                LEFT JOIN (SELECT phase_3_1.name, phase_3_1.title phase, count(tla.id) AS numActive
                FROM us_timeline_timeline_activity tla
                LEFT JOIN us_timeline_timeline tl ON tl.id = tla.timeline_id
                LEFT JOIN us_timeline_timeline_phase tlp ON tlp.timeline_id = tl.id
                LEFT JOIN us_timeline_phase phase_3_1 ON phase_3_1.id = tlp.phase_id
                WHERE phase_3_1.name = 'phase_3'
                AND tla.start_date BETWEEN tlp.start_date AND tlp.end_date

                 AND tla.status = 1 GROUP BY phase_3_1.id
                ) numActive ON numActive.name = p.name
                LEFT JOIN (SELECT phase_3_2.name, phase_3_2.title phase, count(tla.id) AS numEnded
                FROM us_timeline_timeline_activity tla
                LEFT JOIN us_timeline_timeline tl ON tl.id = tla.timeline_id
                LEFT JOIN us_timeline_timeline_phase tlp ON tlp.timeline_id = tl.id
                LEFT JOIN us_timeline_phase phase_3_2 ON phase_3_2.id = tlp.phase_id
                WHERE phase_3_2.name = 'phase_3'
                AND tla.start_date BETWEEN tlp.start_date AND tlp.end_date

                 AND tla.status = 2 GROUP BY phase_3_2.id
                ) numEnded ON numEnded.name = p.name
                LEFT JOIN (SELECT phase_3_4.name, phase_3_4.title phase, count(tla.id) AS numDelayed
                FROM us_timeline_timeline_activity tla
                LEFT JOIN us_timeline_timeline tl ON tl.id = tla.timeline_id
                LEFT JOIN us_timeline_timeline_phase tlp ON tlp.timeline_id = tl.id
                LEFT JOIN us_timeline_phase phase_3_4 ON phase_3_4.id = tlp.phase_id
                WHERE phase_3_4.name = 'phase_3'
                AND tla.start_date BETWEEN tlp.start_date AND tlp.end_date

                 AND tla.status = 4 GROUP BY phase_3_4.id
                ) numDelayed ON numDelayed.name = p.name

                WHERE p.name = 'phase_3'

                GROUP BY p.name
                ) UNION ( SELECT p.name, p.title phase , numTotal,  numActive, numEnded, numDelayed
                FROM us_timeline_phase p

                LEFT JOIN (SELECT phase_4_.name, phase_4_.title phase, count(tla.id) AS numTotal
                FROM us_timeline_timeline_activity tla
                LEFT JOIN us_timeline_timeline tl ON tl.id = tla.timeline_id
                LEFT JOIN us_timeline_timeline_phase tlp ON tlp.timeline_id = tl.id
                LEFT JOIN us_timeline_phase phase_4_ ON phase_4_.id = tlp.phase_id
                WHERE phase_4_.name = 'phase_4'
                AND tla.start_date BETWEEN tlp.start_date AND tlp.end_date

                GROUP BY phase_4_.id
                ) numTotal ON numTotal.name = p.name
                LEFT JOIN (SELECT phase_4_1.name, phase_4_1.title phase, count(tla.id) AS numActive
                FROM us_timeline_timeline_activity tla
                LEFT JOIN us_timeline_timeline tl ON tl.id = tla.timeline_id
                LEFT JOIN us_timeline_timeline_phase tlp ON tlp.timeline_id = tl.id
                LEFT JOIN us_timeline_phase phase_4_1 ON phase_4_1.id = tlp.phase_id
                WHERE phase_4_1.name = 'phase_4'
                AND tla.start_date BETWEEN tlp.start_date AND tlp.end_date

                 AND tla.status = 1 GROUP BY phase_4_1.id
                ) numActive ON numActive.name = p.name
                LEFT JOIN (SELECT phase_4_2.name, phase_4_2.title phase, count(tla.id) AS numEnded
                FROM us_timeline_timeline_activity tla
                LEFT JOIN us_timeline_timeline tl ON tl.id = tla.timeline_id
                LEFT JOIN us_timeline_timeline_phase tlp ON tlp.timeline_id = tl.id
                LEFT JOIN us_timeline_phase phase_4_2 ON phase_4_2.id = tlp.phase_id
                WHERE phase_4_2.name = 'phase_4'
                AND tla.start_date BETWEEN tlp.start_date AND tlp.end_date

                 AND tla.status = 2 GROUP BY phase_4_2.id
                ) numEnded ON numEnded.name = p.name
                LEFT JOIN (SELECT phase_4_4.name, phase_4_4.title phase, count(tla.id) AS numDelayed
                FROM us_timeline_timeline_activity tla
                LEFT JOIN us_timeline_timeline tl ON tl.id = tla.timeline_id
                LEFT JOIN us_timeline_timeline_phase tlp ON tlp.timeline_id = tl.id
                LEFT JOIN us_timeline_phase phase_4_4 ON phase_4_4.id = tlp.phase_id
                WHERE phase_4_4.name = 'phase_4'
                AND tla.start_date BETWEEN tlp.start_date AND tlp.end_date

                 AND tla.status = 4 GROUP BY phase_4_4.id
                ) numDelayed ON numDelayed.name = p.name

                WHERE p.name = 'phase_4'

                GROUP BY p.name
                ) UNION ( SELECT p.name, p.title phase , numTotal,  numActive, numEnded, numDelayed
                FROM us_timeline_phase p

                LEFT JOIN (SELECT phase_5_.name, phase_5_.title phase, count(tla.id) AS numTotal
                FROM us_timeline_timeline_activity tla
                LEFT JOIN us_timeline_timeline tl ON tl.id = tla.timeline_id
                LEFT JOIN us_timeline_timeline_phase tlp ON tlp.timeline_id = tl.id
                LEFT JOIN us_timeline_phase phase_5_ ON phase_5_.id = tlp.phase_id
                WHERE phase_5_.name = 'phase_5'
                AND tla.start_date BETWEEN tlp.start_date AND tlp.end_date

                GROUP BY phase_5_.id
                ) numTotal ON numTotal.name = p.name
                LEFT JOIN (SELECT phase_5_1.name, phase_5_1.title phase, count(tla.id) AS numActive
                FROM us_timeline_timeline_activity tla
                LEFT JOIN us_timeline_timeline tl ON tl.id = tla.timeline_id
                LEFT JOIN us_timeline_timeline_phase tlp ON tlp.timeline_id = tl.id
                LEFT JOIN us_timeline_phase phase_5_1 ON phase_5_1.id = tlp.phase_id
                WHERE phase_5_1.name = 'phase_5'
                AND tla.start_date BETWEEN tlp.start_date AND tlp.end_date

                 AND tla.status = 1 GROUP BY phase_5_1.id
                ) numActive ON numActive.name = p.name
                LEFT JOIN (SELECT phase_5_2.name, phase_5_2.title phase, count(tla.id) AS numEnded
                FROM us_timeline_timeline_activity tla
                LEFT JOIN us_timeline_timeline tl ON tl.id = tla.timeline_id
                LEFT JOIN us_timeline_timeline_phase tlp ON tlp.timeline_id = tl.id
                LEFT JOIN us_timeline_phase phase_5_2 ON phase_5_2.id = tlp.phase_id
                WHERE phase_5_2.name = 'phase_5'
                AND tla.start_date BETWEEN tlp.start_date AND tlp.end_date

                 AND tla.status = 2 GROUP BY phase_5_2.id
                ) numEnded ON numEnded.name = p.name
                LEFT JOIN (SELECT phase_5_4.name, phase_5_4.title phase, count(tla.id) AS numDelayed
                FROM us_timeline_timeline_activity tla
                LEFT JOIN us_timeline_timeline tl ON tl.id = tla.timeline_id
                LEFT JOIN us_timeline_timeline_phase tlp ON tlp.timeline_id = tl.id
                LEFT JOIN us_timeline_phase phase_5_4 ON phase_5_4.id = tlp.phase_id
                WHERE phase_5_4.name = 'phase_5'
                AND tla.start_date BETWEEN tlp.start_date AND tlp.end_date

                 AND tla.status = 4 GROUP BY phase_5_4.id
                ) numDelayed ON numDelayed.name = p.name

                WHERE p.name = 'phase_5'

                GROUP BY p.name
                ) UNION ( SELECT p.name, p.title phase , numTotal,  numActive, numEnded, numDelayed
                FROM us_timeline_phase p

                LEFT JOIN (SELECT phase_6_.name, phase_6_.title phase, count(tla.id) AS numTotal
                FROM us_timeline_timeline_activity tla
                LEFT JOIN us_timeline_timeline tl ON tl.id = tla.timeline_id
                LEFT JOIN us_timeline_timeline_phase tlp ON tlp.timeline_id = tl.id
                LEFT JOIN us_timeline_phase phase_6_ ON phase_6_.id = tlp.phase_id
                WHERE phase_6_.name = 'phase_6'
                AND tla.start_date BETWEEN tlp.start_date AND tlp.end_date

                GROUP BY phase_6_.id
                ) numTotal ON numTotal.name = p.name
                LEFT JOIN (SELECT phase_6_1.name, phase_6_1.title phase, count(tla.id) AS numActive
                FROM us_timeline_timeline_activity tla
                LEFT JOIN us_timeline_timeline tl ON tl.id = tla.timeline_id
                LEFT JOIN us_timeline_timeline_phase tlp ON tlp.timeline_id = tl.id
                LEFT JOIN us_timeline_phase phase_6_1 ON phase_6_1.id = tlp.phase_id
                WHERE phase_6_1.name = 'phase_6'
                AND tla.start_date BETWEEN tlp.start_date AND tlp.end_date

                 AND tla.status = 1 GROUP BY phase_6_1.id
                ) numActive ON numActive.name = p.name
                LEFT JOIN (SELECT phase_6_2.name, phase_6_2.title phase, count(tla.id) AS numEnded
                FROM us_timeline_timeline_activity tla
                LEFT JOIN us_timeline_timeline tl ON tl.id = tla.timeline_id
                LEFT JOIN us_timeline_timeline_phase tlp ON tlp.timeline_id = tl.id
                LEFT JOIN us_timeline_phase phase_6_2 ON phase_6_2.id = tlp.phase_id
                WHERE phase_6_2.name = 'phase_6'
                AND tla.start_date BETWEEN tlp.start_date AND tlp.end_date

                 AND tla.status = 2 GROUP BY phase_6_2.id
                ) numEnded ON numEnded.name = p.name
                LEFT JOIN (SELECT phase_6_4.name, phase_6_4.title phase, count(tla.id) AS numDelayed
                FROM us_timeline_timeline_activity tla
                LEFT JOIN us_timeline_timeline tl ON tl.id = tla.timeline_id
                LEFT JOIN us_timeline_timeline_phase tlp ON tlp.timeline_id = tl.id
                LEFT JOIN us_timeline_phase phase_6_4 ON phase_6_4.id = tlp.phase_id
                WHERE phase_6_4.name = 'phase_6'
                AND tla.start_date BETWEEN tlp.start_date AND tlp.end_date

                 AND tla.status = 4 GROUP BY phase_6_4.id
                ) numDelayed ON numDelayed.name = p.name

                WHERE p.name = 'phase_6'

                GROUP BY p.name
                ) UNION ( SELECT p.name, p.title phase , numTotal,  numActive, numEnded, numDelayed
                FROM us_timeline_phase p

                LEFT JOIN (SELECT phase_7_.name, phase_7_.title phase, count(tla.id) AS numTotal
                FROM us_timeline_timeline_activity tla
                LEFT JOIN us_timeline_timeline tl ON tl.id = tla.timeline_id
                LEFT JOIN us_timeline_timeline_phase tlp ON tlp.timeline_id = tl.id
                LEFT JOIN us_timeline_phase phase_7_ ON phase_7_.id = tlp.phase_id
                WHERE phase_7_.name = 'phase_7'
                AND tla.start_date BETWEEN tlp.start_date AND tlp.end_date

                GROUP BY phase_7_.id
                ) numTotal ON numTotal.name = p.name
                LEFT JOIN (SELECT phase_7_1.name, phase_7_1.title phase, count(tla.id) AS numActive
                FROM us_timeline_timeline_activity tla
                LEFT JOIN us_timeline_timeline tl ON tl.id = tla.timeline_id
                LEFT JOIN us_timeline_timeline_phase tlp ON tlp.timeline_id = tl.id
                LEFT JOIN us_timeline_phase phase_7_1 ON phase_7_1.id = tlp.phase_id
                WHERE phase_7_1.name = 'phase_7'
                AND tla.start_date BETWEEN tlp.start_date AND tlp.end_date

                 AND tla.status = 1 GROUP BY phase_7_1.id
                ) numActive ON numActive.name = p.name
                LEFT JOIN (SELECT phase_7_2.name, phase_7_2.title phase, count(tla.id) AS numEnded
                FROM us_timeline_timeline_activity tla
                LEFT JOIN us_timeline_timeline tl ON tl.id = tla.timeline_id
                LEFT JOIN us_timeline_timeline_phase tlp ON tlp.timeline_id = tl.id
                LEFT JOIN us_timeline_phase phase_7_2 ON phase_7_2.id = tlp.phase_id
                WHERE phase_7_2.name = 'phase_7'
                AND tla.start_date BETWEEN tlp.start_date AND tlp.end_date

                 AND tla.status = 2 GROUP BY phase_7_2.id
                ) numEnded ON numEnded.name = p.name
                LEFT JOIN (SELECT phase_7_4.name, phase_7_4.title phase, count(tla.id) AS numDelayed
                FROM us_timeline_timeline_activity tla
                LEFT JOIN us_timeline_timeline tl ON tl.id = tla.timeline_id
                LEFT JOIN us_timeline_timeline_phase tlp ON tlp.timeline_id = tl.id
                LEFT JOIN us_timeline_phase phase_7_4 ON phase_7_4.id = tlp.phase_id
                WHERE phase_7_4.name = 'phase_7'
                AND tla.start_date BETWEEN tlp.start_date AND tlp.end_date

                 AND tla.status = 4 GROUP BY phase_7_4.id
                ) numDelayed ON numDelayed.name = p.name

                WHERE p.name = 'phase_7'

                GROUP BY p.name
                );

    
-- ------------------------------------
-- group by direction
-- -----------------------------------    

  -- by direction
  
  SELECT 
   u20_.firstname,
  u0_.name AS name0, 
  u0_.title AS title1,
  (
    SELECT 
      count(u1_.id) AS dctrn__1 
    FROM 
      us_timeline_timeline_activity u1_ 
      LEFT JOIN us_timeline_timeline u2_ ON u1_.timeline_id = u2_.id 
      LEFT JOIN us_timeline_timeline_phase u3_ ON u2_.id = u3_.timeline_id 
      LEFT JOIN us_timeline_phase u4_ ON u3_.phase_id = u4_.id 
    WHERE 
      u4_.id = u0_.id 
      AND u1_.start_date BETWEEN u3_.start_date 
      AND u3_.end_date 
    GROUP BY 
      u4_.id
  ) AS sclr2, 
  (
    SELECT 
      count(u5_.id) AS dctrn__2 
    FROM 
      us_timeline_timeline_activity u5_ 
      LEFT JOIN us_timeline_timeline u6_ ON u5_.timeline_id = u6_.id 
      LEFT JOIN us_timeline_timeline_phase u7_ ON u6_.id = u7_.timeline_id 
      LEFT JOIN us_timeline_phase u8_ ON u7_.phase_id = u8_.id 
    WHERE 
      u8_.id = u0_.id 
      AND u5_.start_date BETWEEN u7_.start_date 
      AND u7_.end_date 
      AND u5_.status = 1 
    GROUP BY 
      u8_.id
  ) AS sclr3, 
  (
    SELECT 
      count(u9_.id) AS dctrn__3 
    FROM 
      us_timeline_timeline_activity u9_ 
      LEFT JOIN us_timeline_timeline u10_ ON u9_.timeline_id = u10_.id 
      LEFT JOIN us_timeline_timeline_phase u11_ ON u10_.id = u11_.timeline_id 
      LEFT JOIN us_timeline_phase u12_ ON u11_.phase_id = u12_.id 
    WHERE 
      u12_.id = u0_.id 
      AND u9_.start_date BETWEEN u11_.start_date 
      AND u11_.end_date 
      AND u9_.status = 2 
    GROUP BY 
      u12_.id
  ) AS sclr4, 
  (
    SELECT 
      count(u13_.id) AS dctrn__4 
    FROM 
      us_timeline_timeline_activity u13_ 
      LEFT JOIN us_timeline_timeline u14_ ON u13_.timeline_id = u14_.id 
      LEFT JOIN us_timeline_timeline_phase u15_ ON u14_.id = u15_.timeline_id 
      LEFT JOIN us_timeline_phase u16_ ON u15_.phase_id = u16_.id 
    WHERE 
      u16_.id = u0_.id 
      AND u13_.start_date BETWEEN u15_.start_date 
      AND u15_.end_date 
      AND u13_.status = 4 
    GROUP BY 
      u16_.id
  ) AS sclr5, 
  
  u17_.name AS name6 
FROM   
  us_timeline_timeline u19_
  LEFT JOIN us_user_user u20_ ON u19_.user_id = u20_.id 
  LEFT JOIN us_user_direction u17_ ON u20_.direction_id = u17_.id 
  LEFT JOIN us_timeline_timeline_phase u18_ ON u19_.id = u18_.timeline_id 
  LEFT JOIN us_timeline_phase u0_ ON u18_.phase_id = u0_.id 
  
WHERE u0_.name IS NOT NULL
 GROUP BY 
	 -- firstname
     name6
     , u0_.name
ORDER BY 
   -- u20_.firstname,
  name6 ASC, 
  u0_.name
  -- title1 ASC
  ;
  
  
  
  
  
  
  
  
SELECT 
  distinct(u0_.name) AS name0, 
  u0_.title AS title1, 
  (
    SELECT 
      count(u1_.id) AS dctrn__1 
    FROM 
      us_timeline_timeline_activity u1_ 
      LEFT JOIN us_timeline_timeline u2_ ON u1_.timeline_id = u2_.id 
      LEFT JOIN us_timeline_timeline_phase u3_ ON u2_.id = u3_.timeline_id 
      LEFT JOIN us_timeline_phase u4_ ON u3_.phase_id = u4_.id 
    WHERE 
      u4_.id = u0_.id 
      AND u1_.start_date BETWEEN u3_.start_date 
      AND u3_.end_date 
    GROUP BY 
      u4_.id
  ) AS sclr2, 
  (
    SELECT 
      count(u5_.id) AS dctrn__2 
    FROM 
      us_timeline_timeline_activity u5_ 
      LEFT JOIN us_timeline_timeline u6_ ON u5_.timeline_id = u6_.id 
      LEFT JOIN us_timeline_timeline_phase u7_ ON u6_.id = u7_.timeline_id 
      LEFT JOIN us_timeline_phase u8_ ON u7_.phase_id = u8_.id 
    WHERE 
      u8_.id = u0_.id 
      AND u5_.start_date BETWEEN u7_.start_date 
      AND u7_.end_date 
      AND u5_.status = 1 
    GROUP BY 
      u8_.id
  ) AS sclr3, 
  (
    SELECT 
      count(u9_.id) AS dctrn__3 
    FROM 
      us_timeline_timeline_activity u9_ 
      LEFT JOIN us_timeline_timeline u10_ ON u9_.timeline_id = u10_.id 
      LEFT JOIN us_timeline_timeline_phase u11_ ON u10_.id = u11_.timeline_id 
      LEFT JOIN us_timeline_phase u12_ ON u11_.phase_id = u12_.id 
    WHERE 
      u12_.id = u0_.id 
      AND u9_.start_date BETWEEN u11_.start_date 
      AND u11_.end_date 
      AND u9_.status = 2 
    GROUP BY 
      u12_.id
  ) AS sclr4, 
  (
    SELECT 
      count(u13_.id) AS dctrn__4 
    FROM 
      us_timeline_timeline_activity u13_ 
      LEFT JOIN us_timeline_timeline u14_ ON u13_.timeline_id = u14_.id 
      LEFT JOIN us_timeline_timeline_phase u15_ ON u14_.id = u15_.timeline_id 
      LEFT JOIN us_timeline_phase u16_ ON u15_.phase_id = u16_.id 
    WHERE 
      u16_.id = u0_.id 
      AND u13_.start_date BETWEEN u15_.start_date 
      AND u15_.end_date 
      AND u13_.status = 4 
    GROUP BY 
      u16_.id
  ) AS sclr5, 
  u17_.name AS name6 
FROM 
  us_timeline_phase u0_ 
  LEFT JOIN us_timeline_timeline_phase u18_ ON u0_.id = u18_.timeline_id 
  LEFT JOIN us_timeline_timeline u19_ ON u18_.timeline_id = u19_.id 
  LEFT JOIN us_user_user u20_ ON u19_.user_id = u20_.id 
  LEFT JOIN us_user_direction u17_ ON u20_.direction_id = u17_.id 
ORDER BY 
  name6 ASC, 
  title1 ASC
  ;
        