Click here to Skip to main content
15,030,954 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:

I have a below requirement:

Joining Table
Name	Joining Date
    X	15-Aug-14
    Y	5-Aug-14
    Z	25-Aug-14

Holiday Table
Holiday         Date
IndependenceDay 15-Aug-14
CompanyHoliday 14-Aug-2014
FriendshipDay 5-Aug-14

We need the output as
Name NewJoiningDate
X 13-Aug-14
Y 4-Aug-14
Z 25-Aug-14

Please do not use a while loop or a cursor. I guess it can be done by Recursive CTE or a corelated subquery. Don't have a answer yet :(

Updated 29-Oct-14 9:30am
ArunRajendra 16-Jul-14 2:34am
Do you want to update the record or just display?
Rajesh Varma Buddaraju 16-Jul-14 2:47am
the solution can be achived with dateadd functions, But the output is on what basis? for example the out put dates some are -2 and some are -1 etc...

Got a better solution referencing Nitesh's
Thanks Nitesh

;with abc as
 SELECT Name, joiningdate FROM #Joining
 union all
 select name, DATEADD(DAY, -1, date) from #holiday  h
 inner join abc on abc.joiningdate =
select name,min(joiningdate) from abc
group by name
    SELECT a.Name, b.NewDate
    FROM Joining a
            ( SELECT DATEADD(DAY, -1, [Holiday Date]) NewDate
              FROM Holiday
              WHERE DATEADD(DAY, -1, [Holiday Date]) NOT IN (SELECT [Holiday Date] FROM Holiday)
            ) b ON a.[Joining Date] > b.NewDate
    SELECT c.Name, MAX(c.NewDate) NewDate FROM c
    GROUP BY c.Name
SELECT j.Name,
CASE WHEN h.[Holiday Date] IS NULL THEN j.[Joining Date] ELSE d.NewDate END
FROM Joining j
LEFT JOIN Holiday h ON j.[Joining Date] = h.[Holiday Date]
INNER JOIN d ON j.Name = d.Name
anki.mathur 11-Feb-15 6:35am
Thanks Nitesh, it was of great help. I have posted the optimum solution for this.
UPDATE A SET NewJoiningDate= DATEADD(DAY,-1,B.HolidayDate) FROM JoiningTable A
INNER JOIN HolidayTable B
ON A.JoiningDate=B.Holidaydate
Rajesh Varma Buddaraju 16-Jul-14 2:45am
This won't return the exact output which is to be expected. But for the above scenario DateAdd function is used.
anki.mathur 22-Jul-14 3:02am
Thanks for the reply
But this will not give the result which is at the next level i.e. X 13-Aug-14 won't be updated

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900