15,039,470 members
See more:
Hi,

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 :(

Thanks
Anki
Posted
Updated 29-Oct-14 9:30am
v2
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...

## Solution 3

Got a better solution referencing Nitesh's
Thanks Nitesh

SQL
```;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 = h.date
)
select name,min(joiningdate) from abc
group by name```

## Solution 2

SQL
```;WITH
c
AS
(
SELECT a.Name, b.NewDate
FROM Joining a
LEFT JOIN
( 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
),
d
AS
(
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.

## Solution 1

SQL
```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

But this will not give the result which is at the next level i.e. X 13-Aug-14 won't be updated

Top Experts
Last 24hrsThis month
 Richard MacCutchan 145 OriginalGriff 110 CPallini 45 KarstenK 20 Tushar Shukla 2021 18
 OriginalGriff 3,368 Richard MacCutchan 1,875 Richard Deeming 1,848 CPallini 1,148 Dave Kreskowiak 809

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