none
Database Design & Query for RosterManagement including Shift Management RRS feed

  • Question

  • Hi Guys, I am an Engineering Student doing a Dashboard application for my internship company I have 0 knowledge SQL and database and I need to complete this in a 3 Weeks time. That allowed employees to apply for leave due to training and other reason, also allowing the supervisors to assign Overtimes to other employees to cover those who are on leave. Here is roughly my idea, as I am not sure what is the right way of doing it.

    So please do guide me along and help me!

    So right now I have designed a database with 4 Tables in it, which uses the 4 tables I want to get a query result of a summarised data.

    Below is my Table and it's columns Employee -EmployeeID (PK) -Username -Password -GivenName -FamilyName -TeamID -ContactNo -StaffType Leave -LeaveID (PK) -EmployeeID (FK) -Date -Duration_Off -Reason Overtime -OTID (PK) -EmployeeID (FK) -Date -OT_Duation -OT_Reason Roster -DutyID (PK) -EmployeeID (FK) -Date -ShiftType -Shift Duration (Default Value = 8.25) What I am trying to do is join the data from this 4 tables using Query Ideal Table -Date (From Leave, Overtime and Roster Table) -EmployeeID (Employee Table) -GivenName (Employee Table) -FamilyName (Employee Table) -TeamID (Employee Table) -ShiftType (Roster Table) -ShiftDuration (Roster Table) -Duration_Off (Leave Table) -OT_Duration (Overtime Table) -Total_Hours (Calculation from joint table [(ShiftDuration + OT_Duration) - Duration_Off]

    Employee
    EmplyeeeID | Username | Password | GivenName | FamilyName | TeamID | ContactNo | StaffType
    -----------------------------------------------------------------------------------------------------------------------------
      123         	            123                abc                    John          Snow                    1                  999            1
      1234                     1234             abcd                 Jack           Waller                  2                  223            1 
      12345                  12345           abcde              Ali              Saw                       1                  123            1
      123456                123456        abcdef           Peter           Peter                    2                  223            1
      1234567             1234567      abcdeg           Bryan         Peter                    1                  333            1
    
    
    Roster
    Duty_ID | EmployeeID |      Date        | ShiftType | ShiftDuration
    --------------------------------------------------------------------------------
       2       	    123               2018-05-05         1                8.25
       4      	    1234             2018-05-04         1                8.25
       5       	    12345          2018-05-05         1                 8.25
       7      	    123456       2018-05-04         1                  8.25
       8      	    1234567     2018-05-05         1                 8.25
    
    
    Overtime
    OTID | EmployeeID |           Date  	       | OT_Duration | OT_Reason
    ---------------------------------------------------------------------------------
     2                1234             2018-05-05               2                 Cover Duty
    
    Leave
    LeaveID | EmployeeID |   	Date   	 | Duration_Off | Reason
    --------------------------------------------------------------------------------------------
     3                        123                    2018-05-05                        2             NIL
    
    
    IdealTable (Via Query)
            Date	    | EmployeeID | GivenName | FamilyName | TeamID | ShiftType | ShiftDuration | Duration_Off | OT_Duration | Total_Hours
    ---------------------------------------------------------------------------------------------------------------------------------
    2018-05-05      123         John         Snow         1         1             8.25            2             0            6.25
    2018-05-04      1234        Jack        Waller        1         1             8.25            0             2            10.25
    2018-05-05      12345       Ali           Saw         1         1             8.25            0             0            8.25
    2018-05-04      123456      Peter         Peter       1         1             8.25            0             0            8.25
    2018-05-05      1234567     Bryan         Peter       1         1             8.25            2             0            8.25
    

    With all the Data in the above 4 tables, I want to get the Idealtable at the bottom. (Note: All the Data from the IdealTable are manually calculated and typed in)

    Sorry I couldn't align it properly

    Please help me! Thank You!


    • Edited by envals Saturday, January 6, 2018 3:38 PM
    Saturday, January 6, 2018 3:36 PM

Answers

  • Ok

    I think I missed a column in that UNION

    try

    ;With Dates
    AS
    (
    SELECT [Date] FROM Roster
    UNION 
    SELECT [Date] FROM Leave
    UNION
    SELECT [Date] FROM Overtime
    ),
    Work_Matrix
    AS
    (
    SELECT EmployeeID,[Date],ShiftType,ShiftDuration,CAST(NULL AS Decimal(30,2)) AS Duration_Off,CAST(NULL AS Decimal(30,2)) AS OT_Duration
    FROM Roster
    UNION ALL
    SELECT EmployeeID,[Date], NULL, NULL,Duration_Off,NULL
    FROM Leave
    UNION ALL
    SELECT EmployeeID,[Date],NULL,NULL,NULL,OT_Duration
    FROM Overtime
    )
    
    SELECT d.[Date],
    e.EmployeeID,
    e.GivenName,
    e.FamilyName,
    e.TeamID,
    w.ShiftType,
    w.ShiftDuration,
    w.Duration_Off,
    w.OT_Duration,
    w.Total_Hours
    FROM  Dates d
    INNER JOIN
    (
    SELECT EmployeeID,
    [Date],
    MAX(ShiftType) AS ShiftType,
    SUM(ShiftDuration) AS ShiftDuration,
    SUM(Duration_Off) AS Duration_Off,
    SUM(OT_Duration) AS OT_Duration,
    SUM(ShiftDuration) + SUM(OT_Duration) - SUM(Duration_Off) AS Total_Hours
    FROM Work_Matrix
    GROUP BY EmployeeID,
    [Date]
    )w
    ON d.[Date] = w.[Date]
    JOIN Employee e
    ON e.EmployeeID = w.EmployeeID
    I have already taken into consideration the fact that an employee may not always have an entry on Leave and Overtime


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Sunday, January 7, 2018 6:03 AM
    Moderator

All replies

  • I think you would need a query like this

    ;With Dates
    AS
    (
    SELECT [Date] FROM Roster
    UNION 
    SELECT [Date] FROM Leave
    UNION
    SELECT [Date] FROM Overtime
    ),
    Work_Matrix
    AS
    (
    SELECT EmployeeID,[Date],ShiftType,ShiftDuration,CAST(NULL AS Decimal(30,2)) AS Duration_Off,CAST(NULL AS Decimal(30,2)) AS OT_Duration
    FROM Roster
    UNION ALL
    SELECT EmployeeID,[Date], NULL, NULL,Duration_Off
    FROM Leave
    UNION ALL
    SELECT EmployeeID,[Date],NULL,NULL,NULL,OT_Duration
    FROM Overtime
    )
    
    SELECT d.[Date],
    e.EmployeeID,
    e.GivenName,
    e.FamilyName,
    e.TeamID,
    w.ShiftType,
    w.ShiftDuration,
    w.Duration_Off,
    w.OT_Duration,
    w.Total_Hours
    FROM  Dates d
    INNER JOIN
    (
    SELECT EmployeeID,
    [Date],
    MAX(ShiftType) AS ShiftType,
    SUM(ShiftDuration) AS ShiftDuration,
    SUM(Duration_Off) AS Duration_Off,
    SUM(OT_Duration) AS OT_Duration,
    SUM(ShiftDuration) + SUM(OT_Duration) - SUM(Duration_Off) AS Total_Hours
    FROM Work_Matrix
    GROUP BY EmployeeID,
    [Date]
    )w
    ON d.[Date] = w.[Date]
    JOIN Employee e
    ON e.EmployeeID = w.EmployeeID
    


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Saturday, January 6, 2018 4:23 PM
    Moderator
  • Hi, thanks for your help! But I am getting this error 

    Msg 208, Level 16, State 1, Line 1
    Invalid object name 'Roster'.
    Any idea why?
    Sunday, January 7, 2018 2:49 AM
  • Hi, thanks for your help! But I am getting this error 

    Msg 208, Level 16, State 1, Line 1
    Invalid object name 'Roster'.
    Any idea why?

    I gave the suggestion as per the model you posted which had a table called Roster

    If you're still getting this error then it may be that you're running it on a different database

    Anyways try like this

    USE [SMRT Dashboard]
    GO
    
    ;With Dates
    AS
    (
    SELECT [Date] FROM dbo.Roster
    UNION 
    SELECT [Date] FROM dbo.Leave
    UNION
    SELECT [Date] FROM dbo.Overtime
    ),
    Work_Matrix
    AS
    (
    SELECT EmployeeID,[Date],ShiftType,ShiftDuration,CAST(NULL AS Decimal(30,2)) AS Duration_Off,CAST(NULL AS Decimal(30,2)) AS OT_Duration
    FROM dbo.Roster
    UNION ALL
    SELECT EmployeeID,[Date], NULL, NULL,Duration_Off
    FROM dbo.Leave
    UNION ALL
    SELECT EmployeeID,[Date],NULL,NULL,NULL,OT_Duration
    FROM dbo.Overtime
    )
    
    SELECT d.[Date],
    e.EmployeeID,
    e.GivenName,
    e.FamilyName,
    e.TeamID,
    w.ShiftType,
    w.ShiftDuration,
    w.Duration_Off,
    w.OT_Duration,
    w.Total_Hours
    FROM  Dates d
    INNER JOIN
    (
    SELECT EmployeeID,
    [Date],
    MAX(ShiftType) AS ShiftType,
    SUM(ShiftDuration) AS ShiftDuration,
    SUM(Duration_Off) AS Duration_Off,
    SUM(OT_Duration) AS OT_Duration,
    SUM(ShiftDuration) + SUM(OT_Duration) - SUM(Duration_Off) AS Total_Hours
    FROM Work_Matrix
    GROUP BY EmployeeID,
    [Date]
    )w
    ON d.[Date] = w.[Date]
    JOIN dbo.Employee e
    ON e.EmployeeID = w.EmployeeID


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Sunday, January 7, 2018 4:56 AM
    Moderator
  • Hi, thanks for your help! But I am getting this error 

    Msg 208, Level 16, State 1, Line 1
    Invalid object name 'Roster'.
    Any idea why?

    I gave the suggestion as per the model you posted which had a table called Roster

    If you're still getting this error then it may be that you're running it on a different database

    Anyways try like this

    USE [SMRT Dashboard]
    GO
    
    ;With Dates
    AS
    (
    SELECT [Date] FROM dbo.Roster
    UNION 
    SELECT [Date] FROM dbo.Leave
    UNION
    SELECT [Date] FROM dbo.Overtime
    ),
    Work_Matrix
    AS
    (
    SELECT EmployeeID,[Date],ShiftType,ShiftDuration,CAST(NULL AS Decimal(30,2)) AS Duration_Off,CAST(NULL AS Decimal(30,2)) AS OT_Duration
    FROM dbo.Roster
    UNION ALL
    SELECT EmployeeID,[Date], NULL, NULL,Duration_Off
    FROM dbo.Leave
    UNION ALL
    SELECT EmployeeID,[Date],NULL,NULL,NULL,OT_Duration
    FROM dbo.Overtime
    )
    
    SELECT d.[Date],
    e.EmployeeID,
    e.GivenName,
    e.FamilyName,
    e.TeamID,
    w.ShiftType,
    w.ShiftDuration,
    w.Duration_Off,
    w.OT_Duration,
    w.Total_Hours
    FROM  Dates d
    INNER JOIN
    (
    SELECT EmployeeID,
    [Date],
    MAX(ShiftType) AS ShiftType,
    SUM(ShiftDuration) AS ShiftDuration,
    SUM(Duration_Off) AS Duration_Off,
    SUM(OT_Duration) AS OT_Duration,
    SUM(ShiftDuration) + SUM(OT_Duration) - SUM(Duration_Off) AS Total_Hours
    FROM Work_Matrix
    GROUP BY EmployeeID,
    [Date]
    )w
    ON d.[Date] = w.[Date]
    JOIN dbo.Employee e
    ON e.EmployeeID = w.EmployeeID

    Msg 205, Level 16, State 1, Line 4

    All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists.

    No luck either. Is it caused the by the problem in the Leave and Overtime table? 

    As those 2 tables will only have data added if employees apply for leave and overtime.

    Sunday, January 7, 2018 5:55 AM
  • Ok

    I think I missed a column in that UNION

    try

    ;With Dates
    AS
    (
    SELECT [Date] FROM Roster
    UNION 
    SELECT [Date] FROM Leave
    UNION
    SELECT [Date] FROM Overtime
    ),
    Work_Matrix
    AS
    (
    SELECT EmployeeID,[Date],ShiftType,ShiftDuration,CAST(NULL AS Decimal(30,2)) AS Duration_Off,CAST(NULL AS Decimal(30,2)) AS OT_Duration
    FROM Roster
    UNION ALL
    SELECT EmployeeID,[Date], NULL, NULL,Duration_Off,NULL
    FROM Leave
    UNION ALL
    SELECT EmployeeID,[Date],NULL,NULL,NULL,OT_Duration
    FROM Overtime
    )
    
    SELECT d.[Date],
    e.EmployeeID,
    e.GivenName,
    e.FamilyName,
    e.TeamID,
    w.ShiftType,
    w.ShiftDuration,
    w.Duration_Off,
    w.OT_Duration,
    w.Total_Hours
    FROM  Dates d
    INNER JOIN
    (
    SELECT EmployeeID,
    [Date],
    MAX(ShiftType) AS ShiftType,
    SUM(ShiftDuration) AS ShiftDuration,
    SUM(Duration_Off) AS Duration_Off,
    SUM(OT_Duration) AS OT_Duration,
    SUM(ShiftDuration) + SUM(OT_Duration) - SUM(Duration_Off) AS Total_Hours
    FROM Work_Matrix
    GROUP BY EmployeeID,
    [Date]
    )w
    ON d.[Date] = w.[Date]
    JOIN Employee e
    ON e.EmployeeID = w.EmployeeID
    I have already taken into consideration the fact that an employee may not always have an entry on Leave and Overtime


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Sunday, January 7, 2018 6:03 AM
    Moderator