How to: Generate TZ Group Roster Report as on roster published date

Owned by Dipayan Dey (Deactivated)
Last updated: 03/05/2019
4 min read

TZ generally wants to capture the planned group roster report (at published date) for the month ahead and then later at the end of the roster period captures the actual group roster report for their internal reporting to capture the % of duty changes in a roster period for the cabin crews

Currently, Merlot does not have the planned group roster report to be pulled based on the department and ranks separately from the UI. Due to the merger of TZ and TR, they have a mixed fleet of 320s and 787s. The cabin crew team has been qualified to fly on both these type of Aircraft. So, a new department for Dual Fleet (DF) was created to segregate those crews with the pure 787 and 320 crew qualified. Whenever the group roster report is requested, it is required to generate the extracts based on separate ranks and departments. Since there is no planned group roster report available in the system a workaround has to be done to generate the extracts using department filter.

P.S - TZ has even split the Dual Fleet Department into 2 departments DF A and DF B and are moving the crews accordingly.


Step-by-step guide

  1. Log in to the SQL database with admin access as you need to execute the stored procedure.
  2. Go to the Stored Procedure and take a back up of the original GetEmployeeGroupRosterReportDetails.proc
  3. Run the below scripts to check if there are crews on separate departments or all in Dual Fleet department during that particular roster period

    1. Script for checking Dual Fleet Cabin crew

      SELECT DISTINCT
    E.Id,
    E.EmpCode,
    EB.BaseId,
    R.Id,
    R.Code,
    R.Name,
    Port.Code
FROM
    dbo.Employee E
    INNER JOIN dbo.EmployeeBase EB ON EB.EmployeeId = E.Id
    INNER JOIN dbo.EmployeeRating ER ON ER.EmployeeId = E.Id
    INNER JOIN dbo.[Rank] R ON R.Id = ER.RankId
    INNER JOIN dbo.Port ON Port.Id = EB.BaseId
    INNER JOIN dbo.EmployeeDepartment ED ON ED.EmployeeId = E.Id
    INNER JOIN dbo.DepartmentOperator DO ON DO.DepartmentId = ED.DepartmentId
    LEFT JOIN dbo.EquipmentGroup EG ON EG.Id = ER.EquipmentGroupId
    LEFT JOIN dbo.EquipmentType ET ON ET.EquipmentGroupId = EG.Id
WHERE
    E.Active = 1 AND ED.DepartmentId = 26
    AND EB.[From] <= '2019-03-31 15:59' AND '2019-02-28 16:00' <= EB.[To]
    AND ER.[From] <= '2019-03-31 15:59' AND '2019-02-28 16:00' <= ER.[To]
    AND ED.[ActiveFrom] <= '2019-03-31 15:59' AND '2019-02-28 16:00' <= ED.[ActiveTo]

    2. Script for checking Pure 787 Cabin crew

      SELECT DISTINCT
    E.Id,
    E.EmpCode,
    EB.BaseId,
    R.Id,
    R.Code,
    R.Name,
    Port.Code
FROM
    dbo.Employee E
    INNER JOIN dbo.EmployeeBase EB ON EB.EmployeeId = E.Id
    INNER JOIN dbo.EmployeeRating ER ON ER.EmployeeId = E.Id
    INNER JOIN dbo.[Rank] R ON R.Id = ER.RankId
    INNER JOIN dbo.Port ON Port.Id = EB.BaseId
    INNER JOIN dbo.EmployeeDepartment ED ON ED.EmployeeId = E.Id
    INNER JOIN dbo.DepartmentOperator DO ON DO.DepartmentId = ED.DepartmentId
    LEFT JOIN dbo.EquipmentGroup EG ON EG.Id = ER.EquipmentGroupId
    LEFT JOIN dbo.EquipmentType ET ON ET.EquipmentGroupId = EG.Id
WHERE
    E.Active = 1 AND ED.DepartmentId = 3
    AND EB.[From] <= '2019-03-31 15:59' AND '2019-02-28 16:00' <= EB.[To]
    AND ER.[From] <= '2019-03-31 15:59' AND '2019-02-28 16:00' <= ER.[To]
    AND ED.[ActiveFrom] <= '2019-03-31 15:59' AND '2019-02-28 16:00' <= ED.[ActiveTo]

    3. Script for checking Pure 320 Cabin crew

      SELECT DISTINCT
    E.Id,
    E.EmpCode,
    EB.BaseId,
    R.Id,
    R.Code,
    R.Name,
    Port.Code
FROM
    dbo.Employee E
    INNER JOIN dbo.EmployeeBase EB ON EB.EmployeeId = E.Id
    INNER JOIN dbo.EmployeeRating ER ON ER.EmployeeId = E.Id
    INNER JOIN dbo.[Rank] R ON R.Id = ER.RankId
    INNER JOIN dbo.Port ON Port.Id = EB.BaseId
    INNER JOIN dbo.EmployeeDepartment ED ON ED.EmployeeId = E.Id
    INNER JOIN dbo.DepartmentOperator DO ON DO.DepartmentId = ED.DepartmentId
    LEFT JOIN dbo.EquipmentGroup EG ON EG.Id = ER.EquipmentGroupId
    LEFT JOIN dbo.EquipmentType ET ON ET.EquipmentGroupId = EG.Id
WHERE
    E.Active = 1 AND ED.DepartmentId = 21000014
    AND EB.[From] <= '2019-03-31 15:59' AND '2019-02-28 16:00' <= EB.[To]
    AND ER.[From] <= '2019-03-31 15:59' AND '2019-02-28 16:00' <= ER.[To]
    AND ED.[ActiveFrom] <= '2019-03-31 15:59' AND '2019-02-28 16:00' <= ED.[ActiveTo]

  4. Run the query against the database by updating the ED.DepartmentId depending on which department the data is available for cabin crews. 

    - GetEmployeeGroupRosterDetailsAtPublish.proc.sql

    DepartmentIdDepartment Name
    3TR Cabin Crew B787
    26TR Cabin Crew DF
    21000014TR Cabin Crew DF A
    21000020TR Cabin Crew DF B
    ...............
INSERT INTO #EmployeeList
SELECT DISTINCT
    E.Id,
    E.EmpCode,
    CASE WHEN @ShowEmployeeName = 1 THEN E.Name4 + ', ' + E.Name1 ELSE '' END,
    EB.BaseId,
    R.Id,
    R.Code,
    R.Name,
    Port.Code
FROM
    dbo.Employee E
    INNER JOIN dbo.EmployeeBase EB ON EB.EmployeeId = E.Id
    INNER JOIN dbo.EmployeeRating ER ON ER.EmployeeId = E.Id
    INNER JOIN dbo.[Rank] R ON R.Id = ER.RankId
    INNER JOIN dbo.Port ON Port.Id = EB.BaseId
    INNER JOIN dbo.EmployeeDepartment ED ON ED.EmployeeId = E.Id
    INNER JOIN dbo.DepartmentOperator DO ON DO.DepartmentId = ED.DepartmentId
    LEFT JOIN dbo.EquipmentGroup EG ON EG.Id = ER.EquipmentGroupId
    LEFT JOIN dbo.EquipmentType ET ON ET.EquipmentGroupId = EG.Id
WHERE
    E.Active = 1  --AND ED.DepartmentId = 26
    AND EB.[From] <= @EndDate AND @StartDate <= EB.[To]
    AND ER.[From] <= @EndDate AND @StartDate <= ER.[To]
......................
  5. Generate the report for CC, CIC, CL using MS Report Builder 
  6. Steps to run the MS Report Builder is detailed here - Generate Group Roster report using Report Builder


Make sure to rollback the script once you have generated else it will affect TR Flight Crew Reports and Actualised Group Roster Reports