T-SQL Query | [ The Candidate Joining Puzzle ]

23 Monday Mar 2015

Posted by Pawan Kumar Khowal in T SQL Puzzles

Tags

Complex SQL Challenges, Complex TSQL Challenge, Interview Qs.SQL SERVER Questions, Interview Questions on SQL, InterviewQuestions, InterviewQuestions for SQL, Learn complex SQL, Learn SQL, Learn T-SQL, PL/SQL Challenges, Puzzles, Queries for SQL Interview, SQL Challenge, SQL Challenges, SQL pl/sql puzzles, SQL Puzzles, SQL SERVER Interview questions, SQL SERVER2005/2008, SQL Sudoku, SQLSERVER, T SQL Puzzles, T-SQL Challenge, Tough SQL Challenges, Tough SQL Puzzles, TSQL, TSQL Challenge, TSQL Challenges, TSQL Interview questions, TSQL Queries

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

10 thoughts on “T-SQL Query | [ The Candidate Joining Puzzle ]”

Akshay Barakoti said:

September 6, 2017 at 4:51 AM

Thanks for this post
But the solution provided is not giving the correct answer
Please refer to below solution :-
WITH CTE_1
AS (
SELECT DISTINCT ID
,CASE
WHEN LAG(HolidayDate) OVER (
ORDER BY HolidayDate
) = (HolidayDate – 1)
THEN LAG(HolidayDate) OVER (
ORDER BY HolidayDate
)
ELSE HolidayDate
END AS MinHolidayDate
,HolidayDate AS MaxHolidayDate
FROM Holidays
)
,CTE_2
AS (
SELECT *
FROM (
SELECT *
,ROW_NUMBER() OVER (
PARTITION BY ID ORDER BY MinHolidayDate
) AS ROW_NUM
FROM CTE_1
) D
WHERE Row_Num = 1
)
SELECT CId
,CJoiningDate
,CASE
WHEN CJoiningDate = MaxHolidayDate
THEN (MinHolidayDate – 1)
ELSE CJoiningDate
END AS ValidJoiningDate
FROM CandidateJoining CJ
LEFT JOIN CTE_2 a ON MaxHolidayDate = CJoiningDate

LikeLike

Reply
- Raja Shekar said:
  
  September 12, 2017 at 11:14 AM
  
  What if we have three consecutive days as holidays?
  Then above code also fails.
  For example, Sept 8, sept 9, sept 10 are holidays.
  If employee joining date is Sept 10.
  
  Here, you are checking if previous day is holiday if yes then it will be minholidaydate and holidaydate as maxholidaydate
  You are joining main table with cte based on maxholidaydate. if they both match then you are taking minholidaydate-1.
  Now, if joining date is sept10, then you will have minholidaydate as sept 9 then you are subtracting 1 from it and showing sept 8 as joining date.
  But if sept 8 is also a holiday then your code also fails.
  
  LikeLike
  
  Reply
Akshay Barakoti said:

October 28, 2017 at 8:42 PM

Thanks RajaShekar for calling this out
I have Updated the Query and now the query is handling sep 8 case also
You can check

INSERT INTO Holidays
SELECT 100,’2015-01-08 00:00:00.000′

WITH CTE_1
AS
(
SELECT ID,HolidayDate,
CASE
WHEN HolidayDate=LAG(HolidayDate,1)OVER(ORDER BY HolidayDate )+1
THEN 0
ELSE 1
END AS LAGGER
FROM Holidays
)
,CTE_2 AS
(
SELECT ID,HolidayDate,
SUM(Lagger)OVER(ORDER BY HOLIDAYDATE) AS SUMMER from CTE_1
)
,CTE_3 AS
(
SELECT MIN(HolidayDate) AS MIN_DATE,MAX(HolidayDate) AS MAX_DATE
FROM CTE_2
GROUP BY SUMMER
)

SELECT CId,CJoiningDate,
CASE
WHEN
CJoiningDate=MAXI.MAX_DATE
THEN MAXI.MIN_DATE-1
WHEN
CJoiningDate=MINI.MIN_DATE
THEN MINI.MIN_DATE-1
ELSE
CJoiningDate
END AS ValidJoiningDate
FROM CandidateJoining
LEFT JOIN CTE_3 MINI
ON CandidateJoining.CJoiningDate=MINI.MIN_DATE
LEFT JOIN CTE_3 MAXI
ON CandidateJoining.CJoiningDate=MAXI.MAX_DATE

LikeLike

Reply
sanjay singh said:

December 28, 2017 at 5:34 PM

;with cte
as
(
select cid,CJoiningDate,dateadd(day,-1,MIN(HolidayDate)) as validjoiningdate
from #CandidateJoining a
left join #Holidays b
on a.CJoiningDate=b.HolidayDate
or dateadd(day,-1,a.CJoiningDate)=b.HolidayDate
group by cid,CJoiningDate
)
select CId,CJoiningDate,case when validjoiningdate IS null then cjoiningdate
else validjoiningdate end as validjoiningdate
from cte

LikeLiked by 1 person

Reply
- Pawan Kumar Khowal said:
  
  December 28, 2017 at 8:00 PM
  
  Great !
  
  LikeLike
  
  Reply
Vishnu Kunchur said:

October 3, 2018 at 5:11 AM

Using recursive CTE (PostGRESQL solution):

WITH RECURSIVE cte AS
(
SELECT *, 1 AS IsHoliday FROM candidateJoining

UNION

SELECT * FROM
(
SELECT cid, cjoiningdate, CASE WHEN cjoiningdate IN (SELECT holidaydate FROM Holidays) THEN 1 ELSE 0 END AS IsHoliday
FROM
(
SELECT cid, CASE WHEN cjoiningdate IN (SELECT holidaydate FROM Holidays) THEN cjoiningdate – 1 ELSE cjoiningdate END AS cjoiningdate, 1 AS IsHoliday
FROM cte
) a1
) a2

)

SELECT * FROM cte WHERE isHoliday = 0

LikeLike

Reply
Sai Vinutna said:

December 4, 2018 at 8:21 PM

with cte as
(
select id, holidaydate, lag(holidaydate) over (order by holidaydate) as lv,
case when datediff(day,holidaydate,lag(holidaydate) over (order by holidaydate) ) = -1 then 0
when datediff(day,holidaydate,lag(holidaydate) over (order by holidaydate) ) is null then 0 else 1 end as sum1
from holidays
),
cte1 as
(
select *, sum(sum1) over (order by holidaydate)as sum2 from cte
),
cte2 as
(
select *, dateadd(day,-1,min(holidaydate) over (partition by sum2 order by holidaydate)) as mi, max(holidaydate) over (partition by sum2 order by holidaydate)as ma from cte1
),
cte3 as
(
select cid, cjoiningdate,mi as validjoiningdate from CandidateJoining a
left join cte2
on a.CJoiningDate = cte2.holidaydate
)
select cid,cjoiningdate,case when validjoiningdate IS null then cjoiningdate
else validjoiningdate end as validjoiningdate
from cte3

LikeLike

Reply
Sai Vinutna said:

May 31, 2019 at 12:57 PM

with cte as
(
select *,lag(holidaydate) over (order by (select 1)) as lv from holidays
),
cte1 as
(
select *, case when lv – holidaydate = 1 then 0 else 1 end as req1 from cte
),
cte2 as
(
select *, sum(req1) over (order by id) as rs from cte1
),
cte3 as
(
select *,row_number() over (partition by rs order by holidaydate ) as rn from cte2
),
cte4 as
(
select *,dateadd(day,-rn,holidaydate) as validjoiningdate from cte3
)
select cid, cjoiningdate ,isnull( validjoiningdate,CJoiningDate) as validjoiningdate from cte4
right join CandidateJoining
on cte4.holidaydate = CandidateJoining.CJoiningDate

LikeLiked by 1 person

Reply
Rishabh Mishra said:

June 2, 2019 at 7:56 PM

with cte as
(select *, lag(holidaydate) over(order by HolidayDate) as lag from Holidays)
,cte2 as (select *, case when HolidayDate = dateadd(day, 1, lag) then 0 else 1 end as grouper from cte)
,cte3 as (select *, sum(grouper) over(order by HolidayDate) as grouper2 from cte2)
,cte4 as (select grouper2, min(HolidayDate) as minimum_date from cte3 group by grouper2 )
,cte5 as (select cte3.*, dateadd(day,-1,minimum_date) as correctdate from cte3 inner join cte4 on cte3.grouper2=cte4.grouper2)

select a.*,coalesce(correctdate,CJoiningDate) as correct_date from CandidateJoining a
left join cte5 b
on a.CJoiningDate = b.HolidayDate

LikeLiked by 1 person

Reply
vishal goel (@vishal2704) said:

August 6, 2020 at 4:31 AM

with DATES as
(select cast(’01-01-2015′ as date) cal_date
union all
select dateadd(day, 1 , cal_date)
from DATES
where dateadd(day, 1 , cal_date) < '01-01-2016'
),
CTE1 AS
(
SELECT CAL_DATE, CASE WHEN HOLIDAYS.HolidayDate IS NULL THEN 1 ELSE 0 END AS COLS
FROM DATES
LEFT JOIN HOLIDAYS
ON DATES.CAL_DATE = HOLIDAYS.HolidayDate
),
CTE2 AS
(SELECT CAL_DATE, SUM(COLS) OVER(ORDER BY CAL_DATE) GROUP_COLS
FROM CTE1
),
CTE3 AS
(SELECT CAL_DATE, MIN(CAL_DATE) OVER(PARTITION BY GROUP_COLS) NON_HOLIDAY_DATE
FROM CTE2
)
SELECT * FROM CandidateJoining INNER JOIN CTE3 ON
CandidateJoining.CJoiningDate = CTE3.CAL_DATE
OPTION(MAXRECURSION 366);

LikeLike

Reply