我正在为一组BINGID,INDUSID,COMP1计算TO_DATE列.
当IsRowActive = 1时,TO_DATE =“9999-12-31”,正在正确返回.
但是当IsRowActive = 0时,我们必须计算To_Date,这应该比下一个FROMDT小1秒
资料：
DECLARE @MYTABLE TABLE ( BINGID INT,INDUSID INT,DTSEARCH DATETIME2,COMP1 VARCHAR (100),LISTPRICE NUMERIC(10,2),FROMDT DATETIME2,IsRowActive INT ) INSERT @MYTABLE SELECT 1002285,1002,'2016-03-03 04:10:58.0000000','0026PU009163-031','77.7600','2015-12-19 12:51:49.0000000',0 UNION ALL SELECT 1002285,'2016-05-27 12:14:53.0000000','85.2200','2016-07-20 06:44:37.0000000','90.3900','2016-11-09 13:37:13.0000000','131.4500','2016-10-18 13:49:10.0000000',1 UNION ALL SELECT 1002285,'2015-12-19 12:51:41.0000000','10122374',65.1400,0 UNION ALL SELECT 1002285,'2016-03-03 04:11:01.0000000',117.2100,'2016-05-27 12:14:45.0000000',53.5500,'2016-07-20 06:44:29.0000000',48.5000,'2016-10-18 13:49:00.0000000',75.6800,'2016-11-09 13:37:02.0000000',68.2400,1 UNION ALL SELECT 1000001,'2016-03-03 02:22:09.0000000','161GDB1577',37.1700,'2015-12-18 06:45:05.0000000',0 UNION ALL SELECT 1000001,'2016-03-03 02:22:18.0000000','0392347402',41.9100,'2015-12-18 06:45:14.0000000','2016-05-26 14:54:28.0000000',46.7100,'2016-05-26 14:54:42.0000000',54.7100,'2016-07-15 06:34:33.0000000',52.4800,'2016-07-15 06:34:45.0000000',81.7100,'2016-10-17 11:26:45.0000000',61.6400,'2016-11-09 02:21:17.0000000',81.9200,'2016-10-17 11:26:58.0000000',1 UNION ALL SELECT 1000001,'2016-11-09 02:21:05.0000000',78.3500,1 UNION ALL SELECT 1000005,'2018-11-09 02:21:05.0000000','556556GHB',1
SELECT
BINGID,DTSEARCH,COMP1,LISTPRICE,FROMDT,CASE
WHEN IsRowActive = 1
THEN '9999-12-31'
ELSE TO_DATE
END AS TO_DATE,IsRowActive
FROM
@MYTABLE mt
OUTER APPLY
(SELECT
MAX(DATEADD(second,-1,FROMDT)) TO_DATE
FROM
@MYTABLE mt2
WHERE
mt2.BINGID = mt.BINGID
AND mt2.INDUSID = mt.INDUSID
AND mt2.FROMDT > mt.FROMDT) oa
WHERE
mt.INDUSID = '1002'
预期产量
BINGID INDUSID DTSEARCH COMP1 LISTPRICE FROMDT NEW_TO_DATE IsRowCurrent 1000001 1002 2016-03-03 02:22:09.0000000 161GDB1577 37.1700 2015-12-18 06:45:05.0000000 2016-05-26 14:54:27.0000000 0 1000001 1002 2016-03-03 02:22:18.0000000 0392347402 41.9100 2015-12-18 06:45:14.0000000 2016-05-26 14:54:41.0000000 0 1000001 1002 2016-05-26 14:54:28.0000000 161GDB1577 46.7100 2016-05-26 14:54:28.0000000 2016-07-15 06:34:32.0000000 0 1000001 1002 2016-05-26 14:54:42.0000000 0392347402 54.7100 2016-05-26 14:54:42.0000000 2016-07-15 06:34:44.0000000 0 1000001 1002 2016-07-15 06:34:33.0000000 161GDB1577 52.4800 2016-07-15 06:34:33.0000000 2016-10-17 11:26:44.0000000 0 1000001 1002 2016-07-15 06:34:45.0000000 0392347402 81.7100 2016-07-15 06:34:45.0000000 2016-10-17 11:26:57.0000000 0 1000001 1002 2016-10-17 11:26:45.0000000 161GDB1577 61.6400 2016-10-17 11:26:45.0000000 2016-11-09 02:21:04.0000000 0 1000001 1002 2016-11-09 02:21:17.0000000 0392347402 81.9200 2016-10-17 11:26:58.0000000 9999-12-31 00:00:00.0000000 1 1000001 1002 2016-11-09 02:21:05.0000000 161GDB1577 78.3500 2016-11-09 02:21:05.0000000 9999-12-31 00:00:00.0000000 1 1000005 1002 2018-11-09 02:21:05.0000000 556556GHB 78.3500 2018-11-09 02:21:05.0000000 9999-12-31 00:00:00.0000000 1 1002285,2015-12-19 12:51:49.0000000' 2016-05-27 12:14:52.0000000' 0 1002285,2016-05-27 12:14:53.0000000' 2016-07-20 06:44:36.0000000' 0 1002285,2016-07-20 06:44:37.0000000' 2016-10-18 13:49:09.0000000' 0 1002285,2016-10-18 13:49:10.0000000' 9999-12-31 00:00:00.0000000 1 1002285,2016-03-03 04:11:00.0000000',0 1002285,2016-05-27 12:14:44.0000000',2016-07-20 06:44:28.0000000',2016-10-18 13:48:59.0000000',2016-11-09 13:37:01.0000000',9999-12-31 00:00:00.0000000 1
谢谢.
解决方法
我真的很喜欢@ Chanukya的答案.但是,正如您使用2008年,您将无法使用LEAD功能.相反,您可以使用自联接：
-- sql Server 2008. SELECT c.*,CASE c.IsRowActive WHEN 1 THEN '9999-12-31' ELSE DATEADD(SECOND,MIN(p.FROMDT)) END AS TO_DATE FROM @MYTABLE AS c LEFT OUTER JOIN @MYTABLE AS p ON p.BINGID = c.BINGID AND p.INDUSID = c.INDUSID AND p.FROMDT > c.FROMDT GROUP BY c.BINGID,c.INDUSID,c.DTSEARCH,c.COMP1,c.LISTPRICE,c.FROMDT,c.IsRowActive ORDER BY c.FROMDT ;
逻辑类似于你的外部应用,但它应该表现更好.这是因为没有correlation.
样本数据呈现出一个小小的挑战.因为有两个FROMDT的记录,你们可以说我的结果是错误的.
