with sql server 2014:
i have 2 tables - events , locations, share time column , need merge them 1 table order time. in events table there event column need place in locations row following event (time wise), here example:
events:
time event ------------ 09:00 2 09:10 3 10:15 1 10:17 2 10:30 3 locations:
time x y ------------- 09:01 1 3 09:02 2 3 09:05 4 1 09:09 6 4 09:10 7 8 09:11 8 8 09:12 9 7 10:17 1 2 10:19 5 4 10:20 4 3 10:25 5 4 10:28 3 5 merged table:
time x y event -------------------- 09:00 0 0 2 09:01 1 3 2 < 09:02 2 3 2 < 09:05 4 1 2 < 09:09 6 4 2 < 09:10 0 0 3 09:10 7 8 3 < 09:11 8 8 3 < 09:12 9 7 3 < 10:15 0 0 1 10:17 0 0 2 10:17 1 2 2 < 10:19 5 4 2 < 10:20 4 3 2 < 10:25 5 4 2 < 10:28 3 5 2 < 10:30 0 0 3 the elements mark '<' inserted events.
any ideas , on how perform task welcome.
you can use union all , apply:
select [time], x = 0, y = 0, [event] [events] union select l.*, x.event locations l cross apply( select top 1 * [events] [time] <= l.[time] order [time] desc )x order [time] 
Comments
Post a Comment