我想要将同状态的有效时间段合并为同一条记录,每次状态变化一条记录
如:
APPNO TRANNO STATCODE CURRFROM CURRTO
10001 1 A 2013/1/1 2013/2/1
10001 3 A 2013/2/1 2013/2/10
10001 4 B 2013/2/10 2013/3/31
10001 6 A 2013/4/1 NULL
得到:
APPNO TRANNO STATCODE CURRFROM CURRTO
10001 3 A 2013/1/1 2013/2/10
10001 4 B 2013/2/10 2013/3/31
10001 6 A 2013/4/1 NULL
只按字段TRANNO的顺序进行合并,求各位帮忙!最好是SQL SERVER语法!谢谢sql server
如:
APPNO TRANNO STATCODE CURRFROM CURRTO
10001 1 A 2013/1/1 2013/2/1
10001 3 A 2013/2/1 2013/2/10
10001 4 B 2013/2/10 2013/3/31
10001 6 A 2013/4/1 NULL
得到:
APPNO TRANNO STATCODE CURRFROM CURRTO
10001 3 A 2013/1/1 2013/2/10
10001 4 B 2013/2/10 2013/3/31
10001 6 A 2013/4/1 NULL
只按字段TRANNO的顺序进行合并,求各位帮忙!最好是SQL SERVER语法!谢谢sql server
union all
select a.* from tb a where not exists(select 1 from tb b where a.appno=b.appno and a.statcode=b.statcode and (b.CURRFROM=a.currto or a.CURRFROM=b.currto))
order by APPNO,TRANNO,STATCODE,CURRFROM