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.

Nitin Midha · Accepted Answer · 2012-09-21T15:34:14.383

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)

You _really_ shouldn't be using `BETWEEN` (at all, really) with date/time values, _especially_ in SQL Server (because of some date/time idiosyncrasies). And if you don't have a date table, the usual result is to generate it with a recursive CTE (resulting in a 'virtual' table), which is (probably) going to be a lot better than iterating through a loop. — Clockwork-Muse, Sep 21 '12 at 15:19
thank you for the speedy reply guys! will test this monday morning. i have some previous SQL experience with case statements but the database structure was completely different to the environment that im in currently. — Thomas Hughes, Sep 21 '12 at 15:39
just had a look at this this morning, i believe it will work! however the PL_ID is automatically defined and inserted from the ASP page... how can i get it to include a new PL_ID aswell? thanks again! — Thomas Hughes, Sep 24 '12 at 09:30
What is the data type of PL_ID and what are the rules for creating new PL_ID. Is it identity in table or you get the max and then increment and insert or it is just a new guid? — Nitin Midha, Sep 24 '12 at 15:15

adding recurring employee holidays in sql server

1 Answers1