Click here to Skip to main content
14,981,116 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
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
Comments
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

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
   
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
   
Comments
anki.mathur 11-Feb-15 6:35am
   
Thanks Nitesh, it was of great help. I have posted the optimum solution for this.
SQL
UPDATE A SET NewJoiningDate= DATEADD(DAY,-1,B.HolidayDate) FROM JoiningTable A
INNER JOIN HolidayTable B
ON A.JoiningDate=B.Holidaydate
   
Comments
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