SQL Server : how to use SQL statements to get the desired results -
i can use cursors, it's inefficient. there better way use sql statements these results?
table： a
a1 id a2 -------------------- aaa 8:30 bbb 9:30 ccc 10:00 table： b
id b2 ---------- 8:30 9:00 9:10 9:30 9:50 10:01 12:00 desired results：
id b2 a1 --------------------- 8:30 aaa 9:00 aaa 9:10 aaa 9:30 bbb 9:50 bbb 10:00 bbb 10:01 ccc 12:00 ccc
although not have method achieve,
because explanation not enough.you should explain why 9:30 bbb , why 10:00 bbb
i tried using sql server 2012+
declare @t table(a1 varchar(50),id varchar(50), a2 time) insert @t values ('aaa','a','8:30') ,('bbb','a','9:30') ,('ccc','a','10:00') --select *,lead(a2,1)over(order id ) a2_endtime @t declare @t1 table(id varchar(50), b2 time) insert @t1 values ('a','8:30') ,('a','9:00') ,('a','9:10') ,('a','9:30') ,('a','9:50') ,('a','10:01') ,('a','12:00') ;with cte ( select *,lead(a2,1)over(order id ) a2_endtime @t ) --select * cte ,cte1 (select t1.id,t1.b2 @t1 t1 ) ,cte2 ( select * @t c not exists(select b2 cte1 c1 c1.b2=c.a2) ) ,cte3 ( select id,b2 cte1 union select id,a2 cte2 ) select t1.id,t1.b2 ,(select top 1 t.a1 cte t ((t.a2_endtime null , t1.b2>=t.a2 ) or (t.a2_endtime not null , (t1.b2 >= t.a2 , t1.b2< t.a2_endtime) ))) cte3 t1
Comments
Post a Comment