15,032,208 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

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)

Top Experts
Last 24hrsThis month
 Dave Kreskowiak 130 Rick York 105 Joe Woodbury 100 OriginalGriff 90 Richard MacCutchan 75
 OriginalGriff 2,537 Richard Deeming 1,563 Richard MacCutchan 1,392 CPallini 900 Dave Kreskowiak 646

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