join/merge 2 status tables - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

join/merge 2 status tables

Author

Topic

tariq2k
Starting Member

6 Posts

Posted - 2013-10-15 : 06:53:13

Hello,

I have 2 tables indicating since when is the status valid for an object. I need to kind of join/merge/union them so I know in one table what are the both stuses at any given change.

Table1:
Object status1 date
Obj1 Open 1998-01-01
Obj1 Closed 2000-06-01
Obj1 Open 2008-03-06
Obj1 Closed 2013-01-01

Table2:
Object status2 date
Obj1 ACTIVE 1999-01-01
Obj1 INACTIV 2000-08-29
Obj1 UNKNOWN 2004-05-06
Obj1 check 2014-05-01

as result I would need such thing:
Object status1 status2 date
Obj1 open NULL 1998-01-01
Obj1 open ACTIVE 1999-01-01
Obj1 CLOSED ACTIVE 2000-06-01
Obj1 CLOSED INACTIV 2000-06-01
Obj1 CLOSED UNKNOWN 2004-05-06
Obj1 open UNKNOWN 2008-03-06
Obj1 CLOSED UNKNOWN 2013-01-01
Obj1 CLOSED CHECK 2014-05-01

anyone has idea?
Tomek

tariq2k
Starting Member

6 Posts

Posted - 2013-10-15 : 07:31:51

Well....

I kind of have a solution, but it's messy (especially that I acutally have more tables like these to merge, and obviuosly there come joins with other tables).
Maybe someone could suggest something better?

;WITH cte as(
select DISTINCT t.[Object], t.date FROM table1 t
union
select DISTINCT t.[Object], t.date FROM table2 t
)

select * FROM cte
outer APPLY (SELECT TOP 1 status1 FROM table1 tx
WHERE tx.date<=cte.date AND tx.[Object]=cte.[OBJECT]
ORDER BY tx.date DESC) t1
outer APPLY (SELECT TOP 1 status2 FROM table2 tx
WHERE tx.date<=cte.date and tx.[Object]=cte.[OBJECT]
ORDER BY tx.date DESC) t2

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources