Tags

, , , , , , , , , , , , , , , , , , , , , , , , , , , ,


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

Advertisements