34,575
社区成员
发帖
与我相关
我的任务
分享
WITH CTE_1
AS
(SELECT ROW_NUMBER() OVER (ORDER BY GETDATE())-1 AS SEQ
FROM MASTER.DBO.SPT_VALUES A
JOIN MASTER.DBO.SPT_VALUES B ON 1=1
WHERE A.TYPE='P' AND B.TYPE='P' AND A.NUMBER<=1000 AND B.NUMBER<=100 ),
CTE_2
AS
(SELECT *,DATEADD(DAY,B.SEQ,A.EFFECTIVE_DATE) AS NEW_DATE
FROM TABLE A
JOIN CTE_1 B ON B.SEQ<=DATEDIFF(DAY,EFFECTIVE_DATE,CASE WHEN EXPIRATION_DATE='9999-12-31' THEN '2099-12-31' ELSE EXPIRATION_DATE END)
),
CTE_3
AS
(SELECT NEW_DATE,MAX(NUMBER) AS NUMBER FROM CTE_2
GROUP BY NEW_DATE)
SELECT NUMBER,MIN(NEW_DATE) AS START_DATE,
CASE WHEN MAX(NEW_DATE)='2099-12-31' THEN '9999-12-31' ELSE MAX(NEW_DATE) END AS END_DATE
FROM CTE_3
GROUP BY NUMBER
ORDER BY MIN(NEW_DATE)
USE tempdb
GO
IF OBJECT_ID('dbo.[t]') IS NOT NULL
DROP TABLE dbo.[t]
GO
CREATE TABLE dbo.[t](
[ID] INT
,[NUMBER] INT
,[EFFECTIVE_DATE] DATE
,[EXPIRATION_DATE] DATE
)
GO
SET NOCOUNT ON
INSERT INTO dbo.[t] VALUES(1,50,'2018-01-01','2018-04-01')
INSERT INTO dbo.[t] VALUES(1,70,'2018-04-02','2018-06-06')
INSERT INTO dbo.[t] VALUES(1,60,'2018-06-07','9999-12-31')
INSERT INTO dbo.[t] VALUES(1,100,'2018-02-01','2018-04-15')
INSERT INTO dbo.[t] VALUES(1,200,'2018-05-15','2018-07-01')
select * from t order by EFFECTIVE_DATE
;WITH cte AS (
SELECT
ROW_NUMBER() OVER (ORDER BY [EFFECTIVE_DATE]) AS rid
,*
FROM t
)
SELECT
a.ID
,a.[NUMBER]
,CASE WHEN a.[NUMBER]<ISNULL(b.[NUMBER],0) and a.[EFFECTIVE_DATE]<b.[EXPIRATION_DATE] THEN DATEADD(DAY,1,b.[EXPIRATION_DATE]) ELSE a.[EFFECTIVE_DATE] END AS [EFFECTIVE_DATE]
,CASE WHEN a.[NUMBER]>c.[NUMBER] and a.[EXPIRATION_DATE]>c.[EFFECTIVE_DATE] THEN a.[EXPIRATION_DATE] ELSE Isnull(DATEADD(DAY,-1,c.[EFFECTIVE_DATE]),a.[EXPIRATION_DATE]) END AS [EXPIRATION_DATE]
FROM cte AS a
LEFT JOIN cte AS b ON a.rid=b.rid+1
LEFT JOIN cte AS c ON a.rid=c.rid-1
ORDER BY a.rid
看到答案不一定就是对的,借用2楼代码一用。
USE tempdb
GO
IF OBJECT_ID('dbo.[t]') IS NOT NULL
DROP TABLE dbo.[t]
GO
CREATE TABLE dbo.[t](
[ID] INT
,[NUMBER] INT
,[EFFECTIVE_DATE] DATE
,[EXPIRATION_DATE] DATE
)
GO
SET NOCOUNT ON
INSERT INTO dbo.[t] VALUES(1,50,'2018-01-01','2018-04-01')
INSERT INTO dbo.[t] VALUES(1,70,'2018-04-02','2018-06-06')
INSERT INTO dbo.[t] VALUES(1,60,'2018-06-07','9999-12-31')
INSERT INTO dbo.[t] VALUES(1,100,'2018-02-01','2018-04-15')
INSERT INTO dbo.[t] VALUES(1,200,'2018-05-15','2018-07-01')
;WITH cte AS (
SELECT
ROW_NUMBER() OVER (ORDER BY [EFFECTIVE_DATE]) AS rid
,*
FROM t
)
SELECT
a.ID
,a.[NUMBER]
,CASE WHEN a.[EFFECTIVE_DATE]<b.[EXPIRATION_DATE] THEN DATEADD(DAY,1,b.[EXPIRATION_DATE]) ELSE a.[EFFECTIVE_DATE] END AS [EFFECTIVE_DATE]
,CASE WHEN a.[EFFECTIVE_DATE]>c.[EXPIRATION_DATE] THEN DATEADD(DAY,1,c.[EXPIRATION_DATE]) ELSE a.[EXPIRATION_DATE] END AS [EXPIRATION_DATE]
FROM cte AS a
LEFT JOIN cte AS b ON a.rid=b.rid+1
LEFT JOIN cte AS c ON a.rid=c.rid-1
ORDER BY a.rid