This article is half-done without your Comment! *** Please share your thoughts via Comment ***
Check the below input data and expected output to make one day early joining if any holiday on joining date.
Input Data for Holidays:
|
1 2 3 4 5 6 7 |
ID HolidayDate ----------- ----------- 101 2017-01-08 102 2017-02-10 103 2017-06-09 104 2017-08-08 105 2017-04-22 |
Input Data for Joining dates:
|
1 2 3 4 5 6 7 |
Jid Joining_Date ----------- ------------ 101 2017-01-08 102 2017-02-10 103 2017-05-14 104 2017-08-08 105 2017-10-26 |
Expected Output:
|
1 2 3 4 5 6 7 |
Jid Joining_Date ValidJoining_Date ----------- ------------ ----------------- 101 2017-01-08 2017-01-07 102 2017-02-10 2017-02-09 103 2017-05-14 2017-05-14 104 2017-08-08 2017-08-07 105 2017-10-26 2017-10-26 |
Create a Holiday table with sample data:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
CREATE TABLE Holidays ( ID INT ,HolidayDate DATE ) GO INSERT INTO Holidays(ID,HolidayDate) VALUES (101,'2017-01-08'), (102,'2017-02-10'), (103,'2017-06-09'), (104,'2017-08-08'), (105,'2017-04-22') |
Create a Joining dates table with sample data:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
CREATE TABLE JoiningDate ( Jid INT ,Joining_Date DATE ) GO INSERT INTO JoiningDate(Jid,Joining_Date) VALUES (101,'2017-01-08'), (102,'2017-02-10'), (103,'2017-05-14'), (104,'2017-08-08'), (105,'2017-10-26') GO |
Solution:
|
1 2 3 4 5 |
SELECT Jid, Joining_Date ,(case when Joining_Date in (select holidaydate from Holidays) then dateadd (day,-1,Joining_Date) else Joining_Date end) as ValidJoining_Date FROM JoiningDate |
Please try the different solutions for this puzzle and share it via comment...
