我有一个如下表- sql server 2008
我正在尝试为特定的FINDID 列再创建一个,称为NEW_DATE
第一条记录的新日期比第二条记录的FROM_DATE少-1秒,依此类推…..
行以from_date升序排序
identify = 1的新日期始终保持为'12 / 31/9999 12:00:00 AM
DDL:CREATE TABLE MYTABLE
(
FINDID INT,
SUPPLIERID INT ,
SUPPLIERSKU VARCHAR (35),
PRICE decimal (14,4),
FROM_DATE datetime ,
IDENTIFY bit
)
INSERT MYTABLE
SELECT 1000001, 1001, '012RE0020', 4, '5/12/2016 6:49:40 AM', 0 UNION ALL
SELECT 1000001, 1001, '012RE0020', 4, '5/12/2016 7:16:24 AM', 0 UNION ALL
SELECT 1000001, 1001, '012RE0020', 5, '5/12/2016 7:52:55 AM', 1 UNION ALL
SELECT 1000002, 1001, '101ME0320', 21, '5/12/2016 6:49:40 AM', 0 UNION ALL
SELECT 1000002, 1001, '101ME0320', 21, '5/12/2016 7:16:24 AM', 0 UNION ALL
SELECT 1000002, 1001, '101ME0320', 26, '5/12/2016 7:33:38 AM', 0 UNION ALL
SELECT 1000002, 1001, '101ME0320', 27, '5/12/2016 7:52:55 AM', 1
输出看起来像FINDID SUPPLIERID SUPPLIERSKU PRICE FROM_DATE NEW_TO_DATE IDENTIFY
1000001 1001 012RE0020 4 5/12/2016 6:49:40 AM 5/12/2016 7:16:23 AM 0
1000001 1001 012RE0020 4 5/12/2016 7:16:24 AM 5/12/2016 7:52:54 AM 0
1000001 1001 012RE0020 5 5/12/2016 7:52:55 AM 12/31/9999 12:00:00 AM 1
1000002 1001 101ME0320 21 5/12/2016 6:49:40 AM 5/12/2016 7:16:23 AM 0
1000002 1001 101ME0320 21 5/12/2016 7:16:24 AM 5/12/2016 7:33:37 AM 0
1000002 1001 101ME0320 26 5/12/2016 7:33:38 AM 5/12/2016 7:52:54 AM 0
1000002 1001 101ME0320 27 5/12/2016 7:52:55 AM 12/31/9999 12:00:00 AM 1
谁能在SQL中帮助我
最佳答案
我建议您创建这样的 View :
CREATE VIEW dbo.MyView
AS
WITH cte AS (
SELECT *, ROW_NUMBER() OVER (PARTITION BY FINDID ORDER BY FINDID, FROM_DATE) as RN
FROM MYTABLE m
)
SELECT c.FINDID,
c.SUPPLIERID,
c.SUPPLIERSKU,
c.PRICE,
c.FROM_DATE,
ISNULL(DATEADD(second,-1,c2.FROM_DATE),'12/31/9999 12:00:00 AM') as NEW_TO_DATE,
c.IDENTIFY
FROM cte c
LEFT JOIN cte c2
ON c2.RN = c.RN + 1 AND c.FINDID = c2.FINDID;
GO
输出:
FINDID SUPPLIERID SUPPLIERSKU PRICE FROM_DATE NEW_TO_DATE IDENTIFY
1000001 1001 012RE0020 4.0000 2016-05-12 06:49:40.000 2016-05-12 07:16:23.000 0
1000001 1001 012RE0020 4.0000 2016-05-12 07:16:24.000 2016-05-12 07:52:54.000 0
1000001 1001 012RE0020 5.0000 2016-05-12 07:52:55.000 9999-12-31 00:00:00.000 1
1000002 1001 101ME0320 21.0000 2016-05-12 06:49:40.000 2016-05-12 07:16:23.000 0
1000002 1001 101ME0320 21.0000 2016-05-12 07:16:24.000 2016-05-12 07:33:37.000 0
1000002 1001 101ME0320 26.0000 2016-05-12 07:33:38.000 2016-05-12 07:52:54.000 0
1000002 1001 101ME0320 27.0000 2016-05-12 07:52:55.000 9999-12-31 00:00:00.000 1