Start and date values populated based on another column in sql server

Question

Hi Team,

I need to replicate for each month_startdate for loastartdate and loaenddate column values based on the loa table values.

• Input
• 

   IF OBJECT_ID('tempdb..#loa') IS NOT NULL                          
   DROP TABLE #loa                          
                          
  IF OBJECT_ID('tempdb..#Monthdata') IS NOT NULL                          
   DROP TABLE #Monthdata 

Create table #loa
(
eid int,
loa_startdate datetime,
loa_enddate datetime
)

insert into #loa values
(1,'12-01-2022','12-30-2022')
,(1,'02-01-2023','02-28-2023')
,(2,'03-01-2022','03-30-2022')
,(3,'11-01-2022','11-30-2022')
,(3,'01-01-2023','01-30-2023')

--select * from #loa


Create table #Monthdata
(Month_startdate datetime)

insert into #Monthdata values
('11-01-2022'),
('12-01-2022'),
('01-01-2023'),
('02-01-2023'),
('03-01-2023'),
('04-01-2023');

--select * from #Monthdata

• OutPut

Thanks in Advance

Answer 1

It it is not clear to me which the rules are for which row in #loa to pick for a certain month. Here is a query, but it does not match your expected output exactly.

SELECT a.eid, m.Month_startdate, l.loa_startdate, l.loa_enddate
FROM   #Monthdata m
CROSS  JOIN (SELECT DISTINCT eid FROM #loa) AS a
CROSS  APPLY (SELECT TOP (1) loa_startdate, l.loa_enddate
              FROM   #loa l
              WHERE  l.eid = a.eid
              ORDER  BY CASE WHEN m.Month_startdate <= l.loa_startdate THEN l.loa_startdate ELSE '99991231' END ASC,
                        CASE WHEN m.Month_startdate > l.loa_startdate THEN l.loa_startdate END DESC) AS l