locked
table design for calendar use RRS feed

  • Question

  • Hello.  I am in a quandry as to adding a calendar table (or something) that will track when a person is absent from work.  Now, the work time is rotational so I do not need to be concerned about weekends or holidays.  Currently, what is used is a spreadsheet with the date (1 - 31) across the top and the months down the left side.  This approach is used for everyone so the keeper has 181 of these styled absence keepers.  Would like to make this a bit more simpler, practical, easier, as well as better reporting, and add this to the current db that has the employee file located then set up a user interface.  I suspect the user's form will resemble what they currently have what was mentioned above.  What I projected for the user form would be a matrix with 'WK#, Sun, Mon, Tues, Wed, Thurs, Fri, Sat, No._Sick, CheckIssued, Stage, WeekEnding' with numerous rows since not everyone leaves and hires on at the same time.  The user would select the person's name from a drop list, and the info would them fill in for each week.  A sampler is below.  Now invision this with all the information in a calendar type table.  The table could have all the week ending dates and week number dates already listed for the next 5 yrs which then shows like this on a gridview or a listview. 

    Wk#    Sun    Mon    Tues    Wed    Thu    Fri    Sat    No_Sick    CheckIssd    Stage    WkEdg
    1                     8       8      8                                       24           Yes              1        1/7/2012
    2                                                                               0             No              1        1/14/2012

    Has anyone ever had to make a table like this, similar to this, kinda like this?

    Thanks...John

    Thursday, August 4, 2011 11:45 PM

Answers

  •  

    First create a calendar table and populate it

    CREATE TABLE Calendar ([Date] Date)

     

    DECLARE @i INT = 0

     

    WHILE @i < 730

    BEGIN

          INSERT INTO Calendar VALUES (Getdate()-365 + @i)

          SET @i = @i + 1

    END

     

    Then your sick day table

     

    CREATE TABLE SickDay (  UserID INT,

                      [Date] Date)

     

    Create a new report in SSRS and use the following select a base for your data. There is more work to do but this should be a good starting point

     

    SELECT      *

    FROM  Calendar AS C WITH (NOLOCK)

          LEFT OUTER JOIN SickDay AS S WITH (NOLOCK) ON C.Date = S.Date

    WHERE C.Date BETWEEN @A AND @B

    AND   S.UserID = @C

    • Marked as answer by johnboy0276 Friday, August 5, 2011 4:17 PM
    Friday, August 5, 2011 9:50 AM

All replies

  •  

    First create a calendar table and populate it

    CREATE TABLE Calendar ([Date] Date)

     

    DECLARE @i INT = 0

     

    WHILE @i < 730

    BEGIN

          INSERT INTO Calendar VALUES (Getdate()-365 + @i)

          SET @i = @i + 1

    END

     

    Then your sick day table

     

    CREATE TABLE SickDay (  UserID INT,

                      [Date] Date)

     

    Create a new report in SSRS and use the following select a base for your data. There is more work to do but this should be a good starting point

     

    SELECT      *

    FROM  Calendar AS C WITH (NOLOCK)

          LEFT OUTER JOIN SickDay AS S WITH (NOLOCK) ON C.Date = S.Date

    WHERE C.Date BETWEEN @A AND @B

    AND   S.UserID = @C

    • Marked as answer by johnboy0276 Friday, August 5, 2011 4:17 PM
    Friday, August 5, 2011 9:50 AM
  • Do you really need a calendar, or just a list of dates tracking absenteeism?

    Absent
    --------
    Who
    Start
    End

    Friday, August 5, 2011 11:51 AM
    Answerer
  • Robin, thanks for the info.  It got me thinking on how to construct the table and I have tried several dry runs and I think I have it.  The Calendar table will come in handy and the SickDay table also gave me an idea. 

    Thanks again....John

    Friday, August 5, 2011 4:17 PM