34,575
社区成员
发帖
与我相关
我的任务
分享
DECLARE @t TABLE(orderid INT,NAME VARCHAR(20),sdate VARCHAR(20),edate VARCHAR(20))
INSERT INTO @t (orderid ,NAME ,sdate,edate)
SELECT 100000001, '张三', '2016/2/18' ,'2016/2/19' UNION ALL
SELECT 100000002, '张三', '2016/2/19' ,'2016/2/20' UNION ALL
SELECT 100000003, '张三', '2016/2/20' ,'2016/2/21' UNION ALL
SELECT 100000004, '张三', '2016/3/1' ,'2016/3/5' UNION ALL
SELECT 100000005, '张三', '2016/3/5' ,'2016/3/10'
DECLARE @ret TABLE(orderid INT IDENTITY(100000001,1),NAME VARCHAR(20),sdate VARCHAR(20),edate VARCHAR(20))
DECLARE @tmp_name VARCHAR(20),@tmp_sdate VARCHAR(20),@tmp_edate VARCHAR(20)
DECLARE cur CURSOR
FOR SELECT name,sdate,edate FROM @t ORDER BY name,sdate
DECLARE @name VARCHAR(20),@sdate VARCHAR(20),@edate VARCHAR(20)
OPEN cur
FETCH NEXT FROM cur INTO @name,@sdate,@edate
WHILE @@FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM cur INTO @tmp_name,@tmp_sdate,@tmp_edate
WHILE @@FETCH_STATUS = 0
BEGIN
IF @name = @tmp_name AND @edate = @tmp_sdate
BEGIN
SELECT @edate = @tmp_edate
FETCH NEXT FROM cur INTO @tmp_name,@tmp_sdate,@tmp_edate
END
ELSE
BEGIN
INSERT INTO @ret( NAME, sdate, edate )
SELECT @name,@sdate,@edate
SELECT @name = @tmp_name,@sdate = @tmp_sdate,@edate = @tmp_edate
FETCH NEXT FROM cur INTO @tmp_name,@tmp_sdate,@tmp_edate
END
END
INSERT INTO @ret( NAME, sdate, edate )
SELECT @name,@sdate,@edate
END
SELECT * FROM @ret
USE Test
GO
;WITH CTE AS
(
SELECT '100000001' AS Nr,'张三' AS Name,CONVERT(DATE,'2016/2/18') AS EffectiveStartDate,CONVERT(DATE,'2016/2/19') AS EffectiveEndDate UNION ALL
SELECT '100000002' AS Nr,'张三' AS Name,CONVERT(DATE,'2016/2/19') AS EffectiveStartDate,CONVERT(DATE,'2016/2/20') AS EffectiveEndDate UNION ALL
SELECT '100000003' AS Nr,'张三' AS Name,CONVERT(DATE,'2016/2/20') AS EffectiveStartDate,CONVERT(DATE,'2016/2/21') AS EffectiveEndDate UNION ALL
SELECT '100000004' AS Nr,'张三' AS Name,CONVERT(DATE,'2016/3/1') AS EffectiveStartDate,CONVERT(DATE,'2016/3/5') AS EffectiveEndDate UNION ALL
SELECT '100000005' AS Nr,'张三' AS Name,CONVERT(DATE,'2016/3/5') AS EffectiveStartDate,CONVERT(DATE,'2016/3/10') AS EffectiveEndDate UNION ALL
SELECT '100000006' AS Nr,'张三' AS Name,CONVERT(DATE,'2016/3/15') AS EffectiveStartDate,CONVERT(DATE,'2016/3/18') AS EffectiveEndDate
),CTE2 AS (
SELECT *,
ROW_NUMBER() OVER (ORDER BY a.EffectiveStartDate) AS RNr
FROM CTE a
WHERE NOT EXISTS(SELECT * FROM CTE WHERE EffectiveEndDate = a.EffectiveStartDate AND Name = a.Name)
UNION ALL
SELECT a.Nr,a.Name,a.EffectiveStartDate,b.EffectiveEndDate,a.RNr
FROM CTE2 a
INNER JOIN CTE b ON a.EffectiveEndDate = b.EffectiveStartDate AND a.Name = b.Name
)
SELECT CTE2.Nr,CTE2.Name,CTE2.EffectiveStartDate,MAX(CTE2.EffectiveEndDate) AS EffectiveEndDate,CTE2.RNr
FROM CTE2
GROUP BY CTE2.Nr,CTE2.Name,CTE2.EffectiveStartDate,CTE2.RNr