-1

I have one table HolidayList and another table JoiningDateList:

HolidayId   HolidayDate            CandidateId    JoiningDate  
1           2016-01-26                1            2016-01-26
2           2016-03-07                2            2016-02-12
3           2016-04-08                3            2016-04-08
                                      4            2016-06-06

What i want is if holiday date clashing with joiningDate it should skip that date and if the new date clashes with weekends then it should skip that as well.

1
  • 1
    What have you tried? Could you give an example of expected output based on the sample data you've posted. I suspect the downvote is due to a poorly written question. Oct 5 '16 at 18:32
0

This problem is pretty easy to solve if you've created a Date Table. This is simply a table that every date between a set of dates with information about that date, like day of week, and if it's a holiday. See here for details on how to create and populate this table. The only change I would make from what the blog post says would be to make the table key a "Date" data type and not in int.

Once you have the date table the solution would look something like this:

IF OBJECT_ID('tempdb..#Users') IS NOT NULL
  DROP TABLE #Users

CREATE TABLE #Users(id INT NOT NULL PRIMARY KEY, JoiningDate DATE NOT NULL)

INSERT INTO #Users(id, JoiningDate)
     VALUES (1, '7/3/2016') -- weekend day before holiday
          , (2, '7/5/2016') -- good day
          , (3, '7/9/2016') -- weekend

SELECT u.id, u.JoiningDate, d.AdjustedDate
  FROM #Users u
       CROSS APPLY (SELECT MIN([Date]) AS AdjustedDate
                      FROM dbo.Dates d
                     WHERE [Date] >= u.JoiningDate AND d.DayOfWeek BETWEEN 2 AND 6 AND d.Holiday = 0) d

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.