adding recurring employee holidays in sql server

Question

i currently have this table in SQL server:

PL_ID User_ID Log_Date Out_Time In_Time Reason Details

PL stands for people logger... then you have the user ID, the log date (date of day off), out time, in time, reason and details... there is a ASP front which allows the user to add their day off into the table which has been written by someone else... currently the users being able to do this themselves isnt an issue however, i have been given the task to add employee days off for the next YEAR.... they are recurring days off (1-3pm every monday) or (2-5 every thursday)... there must be an easier way to do this rather than just adding in one day off at a time. i have tried experimenting with dateadd but i cannot figure out the syntax to include it in the insert... one day i plan to add this to the userpage but for now i just need to get their days off into the table! sorry for being a bit vauge but i am very new to this and if i dont figure out an easy way to insert the days off, i will have to manually do it every week for the next year!

thanks in advance guys,

Tom.

Answer 1

If you have a master date table, you can simply query the table for Mondays and Thrusdays for next year and can use Insert Select ... Syntax.

DECLARE @StartDate DateTime
DECLARE @EndDate DateTime
SET @StartDate = '1/1/2013'
SET @EndDate = '12/31/2013'

INSERT INTO DestinationTable(Date,StartTime,EndTime)
SELECT DM.Date,
       CASE WHEN DATENAME(dw,DM.Date) = 'Thursday' THEN '1400'
            WHEN DATENAME(dw,DM.Date) = 'Monday' THEN '1300'
        END AS StartTime,
        CASE WHEN DATENAME(dw,DM.Date) = 'Thursday' THEN '1700'
            WHEN DATENAME(dw,DM.Date) = 'Monday' THEN '1500'
        END AS EndTime     
  FROM DateMaster DM
 WHERE DM.Date Between @StartDate AND @EndDate
   AND DATENAME(dw,DM.Date) IN ('Thursday','Monday')

However if you do not have a master date table then we can just loop the whole year and fill in the table.

DECLARE @StartDate DateTime
DECLARE @EndDate DateTime
DECLARE @DateVar DateTime

SET @StartDate = '1/1/2013'
SET @EndDate = '12/31/2013'
SET @DateVar = @StartDate

WHILE @DateVar <= @EndDate
BEGIN 
     INSERT INTO DestinationTable(Date,StartTime,EndTime)
     SELECT @DateVar,
            CASE WHEN DATENAME(dw,@DateVar) = 'Thursday' THEN '1400'
                 WHEN DATENAME(dw,@DateVar) = 'Monday' THEN '1300'
             END AS StartTime,
            CASE WHEN DATENAME(dw,@DateVar) = 'Thursday' THEN '1700'
                 WHEN DATENAME(dw,@DateVar) = 'Monday' THEN '1500'
             END AS EndTime      
      WHERE DATENAME(dw,@DateVar) IN ('Thursday','Monday')

     SET @DateVar = DATEADD(d,1,@DateVar)
 END

You can make it more efficient by jumping other days instead of just adding one day...

CTE

DECLARE @StartDate DateTime
DECLARE @EndDate DateTime
SET @StartDate = '1/1/2013'
SET @EndDate = '12/31/2013'

;WITH DateMaster(Date)
AS
(
    SELECT @StartDate
     UNION ALL
    SELECT DATEADD(d,1,Date)
      FROM DateMaster
     WHERE DATEADD(d,1,Date) <= @EndDate 
    )


INSERT INTO DestinationTable(Date,StartTime,EndTime)
SELECT DM.Date,
       CASE WHEN DATENAME(dw,DM.Date) = 'Thursday' THEN '1400'
            WHEN DATENAME(dw,DM.Date) = 'Monday' THEN '1300'
        END AS StartTime,
        CASE WHEN DATENAME(dw,DM.Date) = 'Thursday' THEN '1700'
            WHEN DATENAME(dw,DM.Date) = 'Monday' THEN '1500'
        END AS EndTime     
  FROM DateMaster DM
 WHERE DM.Date >= @StartDate AND DM.Date <= @EndDate
   AND DATENAME(dw,DM.Date) IN ('Thursday','Monday')
OPTION (MAXRECURSION 366)