T-SQL Query | [ The Candidate Joining Puzzle ] – In this puzzle we have to find out the valid candidate joining date for each candidate. E.g if you check for CID the joining date is 10-01-2015 and as per the company’s holiday table they have holiday. So in this case we have to prepone the joining by one day. Hence for CJ10101 the valid joining date would be 08-01-2015 as they have holiday on 09-01-2015 also. Please check out the sample input and expected output for details

Sample Input

 CandidateJoining CId CJoiningDate CJ10101 10-01-2015 CJ10104 10-01-2015 CJ10105 18-02-2015 CJ10121 11-03-2015 CJ10198 11-04-2015 Holidays ID HolidayDate 101 10-01-2015 102 09-01-2015 103 19-02-2015 104 11-03-2015 105 11-04-2015

Expected Output

 CId CJoiningDate ValidJoiningDate CJ10101 10-01-2015 08-01-2015 CJ10104 10-01-2015 08-01-2015 CJ10105 18-02-2015 18-02-2015 CJ10121 11-03-2015 10-03-2015 CJ10198 11-04-2015 10-04-2015

Rules/Restrictions

• The solution should be should use “SELECT” statement or “CTE”.
• Send your solution to pawankkmr@gmail.com
• Do not post you solution in comments section

Script

Use the below script to generate the source table and fill them up with the sample data.

 ``` --Create Table CREATE TABLE Holidays ( ID INT ,HolidayDate DATETIME ) GO --Insert Data INSERT INTO Holidays(ID,HolidayDate) VALUES (101,'01/10/2015'), (102,'01/09/2015'), (103,'02/19/2015'), (104,'03/11/2015'), (105,'04/11/2015') --Verify Data SELECT ID,HolidayDate FROM Holidays --Create Table CREATE TABLE CandidateJoining ( CId VARCHAR(17) ,CJoiningDate DATETIME ) GO --Insert Data INSERT INTO CandidateJoining(CId,CJoiningDate) VALUES ('CJ10101','01/10/2015'), ('CJ10104','01/10/2015'), ('CJ10105','02/18/2015'), 'CJ10121','03/11/2015'), ('CJ10198','04/11/2015') --Verify Data SELECT CId,CJoiningDate FROM CandidateJoining ```

UPDATE – 24-Apr-2015 – Solution 1

 ``` -- ;WITH CTE AS ( SELECT MIN(HolidayDate) MinDate , MAX(HolidayDate) MaxDate FROM ( SELECT * , DAY(HolidayDate) - ROW_NUMBER() OVER (ORDER BY HolidayDate ASC) rnk FROM Holidays ) a GROUP BY rnk ) SELECT CId , CASE WHEN MinDate IS NULL THEN CJoiningDate ELSE MinDate -1 END CandidateJoining FROM CandidateJoining j LEFT JOIN CTE c ON j.CJoiningDate BETWEEN c.MinDate AND c.MaxDate -- ```

Add a comment if you have any other solution in mind. We all need to learn.

Keep Learning

http://MSBISkills.com