I have a table like as below -- **sql server 2008**

I am trying to create one more **for specific FINDID** column called as NEW\_DATE

**The new date for 1st record will be -1 sec less of FROM\_DATE of second record and so on…..**

the rows are sorted with from\_date ascending order
The new date **where identify = 1 is always remain '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

```

The output will Look like

```
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

```

can anyone please help me if possible in sql