New Row Based on computing column in sql server 2008

Question

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

Answer 1

I suggest you to create a view like this:

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

Output:

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

Answer 2

In SQL Server 2012+, you would simply use lead(). In SQL Server 2008, you can do this with a correlated subquery or outer apply:

select t.*,
       coalesce(t2.from_date, '9999-12-31') as new_to_date
from mytable t outer apply
     (select top 1 t2.*
      from mytable t2
      where t2.findid = t.findid and t2.from_date > t.from_date
     ) t2;