# question

## Calculation on Date field in SQL Server 2008

I am trying to compute TO_DATE column for a **group of BINGID, INDUSID, COMP1.** When IsRowActive = 1 then TO_DATE = "9999-12-31" But when IsRowActive = 0, then we have to calculate To_Date which should be 1 sec less than next FROMDT in a same group Data : 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, 1002, '2016-05-27 12:14:53.0000000', '0026PU009163-031', '85.2200', '2016-05-27 12:14:53.0000000',0 UNION ALL SELECT 1002285, 1002, '2016-07-20 06:44:37.0000000', '0026PU009163-031', '90.3900', '2016-07-20 06:44:37.0000000',0 UNION ALL SELECT 1002285, 1002, '2016-11-09 13:37:13.0000000', '0026PU009163-031', '131.4500', '2016-10-18 13:49:10.0000000',1 UNION ALL SELECT 1002285, 1002, '2015-12-19 12:51:41.0000000', '10122374', 65.1400, '2015-12-19 12:51:41.0000000', 0 UNION ALL SELECT 1002285, 1002, '2016-03-03 04:11:01.0000000', '10122374', 117.2100, '2016-03-03 04:11:01.0000000', 0 UNION ALL SELECT 1002285, 1002, '2016-05-27 12:14:45.0000000', '10122374', 53.5500, '2016-05-27 12:14:45.0000000', 0 UNION ALL SELECT 1002285, 1002, '2016-07-20 06:44:29.0000000', '10122374', 48.5000, '2016-07-20 06:44:29.0000000', 0 UNION ALL SELECT 1002285, 1002, '2016-10-18 13:49:00.0000000', '10122374', 75.6800, '2016-10-18 13:49:00.0000000', 0 UNION ALL SELECT 1002285, 1002, '2016-11-09 13:37:02.0000000', '10122374', 68.2400, '2016-11-09 13:37:02.0000000', 1 UNION ALL SELECT 1000001, 1002, '2016-03-03 02:22:09.0000000', '161GDB1577', 37.1700, '2015-12-18 06:45:05.0000000',0 UNION ALL SELECT 1000001, 1002, '2016-03-03 02:22:18.0000000', '0392347402', 41.9100, '2015-12-18 06:45:14.0000000',0 UNION ALL SELECT 1000001, 1002, '2016-05-26 14:54:28.0000000', '161GDB1577', 46.7100, '2016-05-26 14:54:28.0000000',0 UNION ALL SELECT 1000001, 1002, '2016-05-26 14:54:42.0000000', '0392347402', 54.7100, '2016-05-26 14:54:42.0000000',0 UNION ALL SELECT 1000001, 1002, '2016-07-15 06:34:33.0000000', '161GDB1577', 52.4800, '2016-07-15 06:34:33.0000000',0 UNION ALL SELECT 1000001, 1002, '2016-07-15 06:34:45.0000000', '0392347402', 81.7100, '2016-07-15 06:34:45.0000000',0 UNION ALL SELECT 1000001, 1002, '2016-10-17 11:26:45.0000000', '161GDB1577', 61.6400, '2016-10-17 11:26:45.0000000',0 UNION ALL SELECT 1000001, 1002, '2016-11-09 02:21:17.0000000', '0392347402', 81.9200, '2016-10-17 11:26:58.0000000',1 UNION ALL SELECT 1000001, 1002, '2016-11-09 02:21:05.0000000', '161GDB1577', 78.3500, '2016-11-09 02:21:05.0000000',1 UNION ALL SELECT 1000005, 1002, '2018-11-09 02:21:05.0000000', '556556GHB', 78.3500, '2018-11-09 02:21:05.0000000',1 Query I tried - unfortunately it is returning the wrong data : SELECT BINGID, INDUSID, 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' Expected Output 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, 1002, '2016-03-03 04:10:58.0000000', '0026PU009163-031', '77.7600', 2015-12-19 12:51:49.0000000' 2016-05-27 12:14:52.0000000' 0 1002285, 1002, '2016-05-27 12:14:53.0000000', '0026PU009163-031', '85.2200', 2016-05-27 12:14:53.0000000' 2016-07-20 06:44:36.0000000' 0 1002285, 1002, '2016-07-20 06:44:37.0000000', '0026PU009163-031', '90.3900', 2016-07-20 06:44:37.0000000' 2016-10-18 13:49:09.0000000' 0 1002285, 1002, '2016-11-09 13:37:13.0000000', '0026PU009163-031', '131.4500', 2016-10-18 13:49:10.0000000' 9999-12-31 00:00:00.0000000 1 1002285, 1002, '2015-12-19 12:51:41.0000000', '10122374', 65.1400, '2015-12-19 12:51:41.0000000', 2016-03-03 04:11:00.0000000', 0 1002285, 1002, '2016-03-03 04:11:01.0000000', '10122374', 117.2100, '2016-03-03 04:11:01.0000000', 2016-05-27 12:14:44.0000000', 0 1002285, 1002, '2016-05-27 12:14:45.0000000', '10122374', 53.5500, '2016-05-27 12:14:45.0000000', 2016-07-20 06:44:28.0000000', 0 1002285, 1002, '2016-07-20 06:44:29.0000000', '10122374', 48.5000, '2016-07-20 06:44:29.0000000', 2016-10-18 13:48:59.0000000', 0 1002285, 1002, '2016-10-18 13:49:00.0000000', '10122374', 75.6800, '2016-10-18 13:49:00.0000000', 2016-11-09 13:37:01.0000000', 0 1002285, 1002, '2016-11-09 13:37:02.0000000', '10122374', 68.2400, '2016-11-09 13:37:02.0000000', 9999-12-31 00:00:00.0000000 1

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

·