14,977,979 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