GO
     CREATE TABLE [HeadCount_Ver01].[dbo].DepartmentSlotsLog(
    DepartmentID                    INTEGER                         NOT NULL,
    SlotID                          INTEGER                         NOT NULL,
    EmployeeID                      INTEGER                             NULL,
    ResourceManagerID               INTEGER                             NULL,
    ServiceManagerID                INTEGER                             NULL,
    LogDate                         DATETIME                        NOT NULL

    PRIMARY KEY(DepartmentID, SlotID, LogDate)
);
GO

CREATE VIEW VResourceManagerLogData

AS
SELECT
    D.DepartmentID                          AS DepartmentID,
    DSL.SlotID                              AS SlotID,
    D.DepartmentName                        AS DepartmentName,
    DSL.ResourceManagerID                   AS ResourceManagerID,
    E.LastName + ', ' + E.FirstName         AS EmployeeFullName,
    E.EmployeeRole                          AS EmployeeRole,
    E.EmployeeLevel                         AS EmployeeRoleLevel,
    CONVERT(VARCHAR(50), DSL.LogDate, 107)  AS RecordDate

FROM

    Departments         AS  D
        LEFT JOIN DepartmentSlotsLog        AS  DSL
            ON D.DepartmentID = DSL.DepartmentID

        LEFT JOIN Employees         AS  E
            ON DSL.ResourceManagerID = E.EmployeeID

CREATE VIEW VServiceManagerLogData

AS
SELECT
    D.DepartmentID                          AS DepartmentID,
    DSL.SlotID                              AS SlotID,
    D.DepartmentName                        AS DepartmentName,
    DSL.ServiceManagerID                    AS ServiceManagerID,
    E.LastName + ', ' + E.FirstName         AS EmployeeFullName,
    E.EmployeeRole                          AS EmployeeRole,
    E.EmployeeLevel                         AS EmployeeRoleLevel,
    CONVERT(VARCHAR(50), DSL.LogDate, 107)  AS RecordDate


FROM

    Departments         AS  D
        LEFT JOIN DepartmentSlotsLog        AS  DSL
            ON D.DepartmentID = DSL.DepartmentID

        LEFT JOIN Employees         AS  E
            ON DSL.ServiceManagerID = E.EmployeeID

CREATE VIEW VMasterLogData

AS
SELECT
    VESLD.DepartmentID                  AS DepartmentID,
    VESLD.DepartmentName                AS DepartmentName,
    VESLD.SlotID                        AS SlotID,
    VESLD.EmployeeID                    AS EmployeeID,
    VESLD.EmployeeFullName              AS EmployeeFullName,
    VESLD.EmployeeRole                  AS EmployeeRole,
    VESLD.EmployeeRoleLevel             AS EmployeeRoleLevel,
    VESLD.ResourceManagerID             AS RMID,
    VRMLD.EmployeeFullName              AS RMFullName,
    VESLD.ServiceManagerID              AS SMID,
    VSMLD.EmployeeFullName              AS SMFullName,
    VSMLD.RecordDate                    AS RecordDate

FROM

    VEmployeeSlotsLogData  AS VESLD
        LEFT JOIN VResourceManagerLogData   AS VRMLD
        ON VESLD.ResourceManagerID = VRMLD.ResourceManagerID
       AND VESLD.SlotID            = VRMLD.SlotID

        LEFT JOIN VServiceManagerLogData    AS VSMLD
        ON VESLD.ServiceManagerID = VSMLD.ServiceManagerID
       AND VESLD.SlotID           = VSMLD.SlotID