I suppose it depends how you define 'messy'.
You can probably achieve a better solution with a calendar table, but if you have a lot of these status tables, thats still a lot of steps.
Here is a sample of what I'm talking about:
CREATE TABLE #table2(
[Object] [nvarchar](1000) NULL,
[status2] [nvarchar](1000) NULL,
[date] [datetime] NULL
) ON [PRIMARY]
GO
INSERT #table2 ([Object], [status2], [date]) VALUES (N'Obj1', N'ACTIVE', CAST(0x00008D3F00000000 AS DateTime))
INSERT #table2 ([Object], [status2], [date]) VALUES (N'Obj1', N'INACTIV', CAST(0x00008F9D00000000 AS DateTime))
INSERT #table2 ([Object], [status2], [date]) VALUES (N'Obj1', N'UNKNOWN', CAST(0x000094DF00000000 AS DateTime))
INSERT #table2 ([Object], [status2], [date]) VALUES (N'Obj1', N'check', CAST(0x0000A31E00000000 AS DateTime))
go
CREATE TABLE #table1(
[Object] [nvarchar](1000) NULL,
[status1] [nvarchar](1000) NULL,
[date] [datetime] NULL
) ON [PRIMARY]
GO
INSERT #table1 ([Object], [status1], [date]) VALUES (N'Obj1', N'Open', CAST(0x00008BD200000000 AS DateTime))
INSERT #table1 ([Object], [status1], [date]) VALUES (N'Obj1', N'Closed', CAST(0x00008F4400000000 AS DateTime))
INSERT #table1 ([Object], [status1], [date]) VALUES (N'Obj1', N'Open', CAST(0x00009A5700000000 AS DateTime))
INSERT #table1 ([Object], [status1], [date]) VALUES (N'Obj1', N'Closed', CAST(0x0000A13900000000 AS DateTime))
Create table #Calendar (
calendar_datedate
)
insert into #Calendar
SELECT top 10000 dateadd(dd, row_number() over (order by s1.name), 'Dec 31, 1997')
FROM sys.syscolumns S1
CROSS JOIN sys.syscolumns S2
;
WITH table1CTE AS (
SELECT [Object], [status1], [date], row_number() over (partition by [Object] order by [date]) AS RowNum
FROM #table1
),
table1SelfJoin AS (
SELECT t1.Object,t1.status1,t1.date AS StartDate,COALESCE (t2.date,'Dec 31, 2030') AS EndDate
FROM table1CTE t1
LEFT OUTER JOIN table1CTE t2
ON t1.Object = t2.Object
AND t1.RowNum +1 =t2.RowNum
),
table1Calendar AS (
SELECT t1.Object,t2.status1,t2.StartDate,c.calendar_date
FROM #Calendar c
CROSS JOIN (select distinct [Object] from #table1) t1
LEFT OUTER JOIN table1SelfJoin t2
ON t1.Object = t2.Object
AND c.calendar_date BETWEEN t2.StartDate AND t2.EndDate
),
table2CTE AS (
SELECT [Object], [status2], [date], row_number() over (partition by [Object] order by [date]) AS RowNum
FROM #table2
),
table2SelfJoin AS (
SELECT t1.Object,t1.status2,t1.date AS StartDate,COALESCE (t2.date,'Dec 31, 2030') AS EndDate
FROM table2CTE t1
LEFT OUTER JOIN table2CTE t2
ON t1.Object = t2.Object
AND t1.RowNum +1 =t2.RowNum
),
table2Calendar AS (
SELECT t1.Object,t2.status2,t2.StartDate,c.calendar_date
FROM #Calendar c
CROSS JOIN (select distinct [Object] from #table2) t1
LEFT OUTER JOIN table2SelfJoin t2
ON t1.Object = t2.Object
AND c.calendar_date BETWEEN t2.StartDate AND t2.EndDate
)
SELECT t1.Object,t1.status1,t2.status2,t1.calendar_date
FROM table1Calendar t1
INNER JOIN table2Calendar t2
ON t1.Object = t2.Object
AND t1.calendar_date = t2.calendar_date
WHERE t1.calendar_date in (t1.StartDate,t2.StartDate)
ORDER by t1.calendar_date
DRop table #Calendar
DRop table #table1
DRop table #table2
As you can see, as is it is FAR more convoluted than your solution.
But, not only should the calendar table be permanent and built in advance (with some other handy reference columns in case you want to use it elsewhere), but you could also pre-build a view for each of your status tables which essentially mirrors the calendar CTEs.
If you pre-do those steps for your status tables, you can merge any of them per your requirements with just the select at the end. And those views will also help other queries where you want to know what a status was on a specific date.