SqlServer 同表记录 上条记录结束日期为下条记录的开始日期的前

源数据

要达成的结果

SELECT  A.Startdate,B.Startdate AS Enddate,isnull(A.orgcode4_name,'') as orgcode4_name,isnull(zige_name,'') as zige_name,isnull(zhizhcode_name,'') as zhizhcode_name,isnull(positioncode_name,'') as positioncode_name
FROM 
(SELECT ROW_NUMBER() over(order by startdate asc) as Seq,a.orgcode4_name,zige_name,zhizhcode_name,positioncode_name,convert(nvarchar(10),startdate,120) as startdate
 FROM alps_empinfo a,psnaccount b
WHERE a.empcode=b.employeeid
and b.personid='2dffdd7e-5d5f-4739-a3b9-308071597259'
and convert(nvarchar(20),a.startdate,120)<'2014-10-23'
) A,(SELECT (ROW_NUMBER() over(order by startdate asc)-1) as Seq,120) as startdate FROM 
 (SELECT convert(nvarchar(10),(cast(startdate as datetime)-1),120) as startdate 
 FROM alps_empinfo a,120)<'2014-10-23'
UNION SELECT '2014-10-23') C
) B
WHERE A.Seq=B.Seq 

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!