CODEWITHSUNDEEP
sqlsql-servert-sql
user3183055
user3183055

Reputation: 83

How to assign previous date?

table A

+====+======+============+============+
| id | m_id |    s_dt    |    e_dt    |
+====+======+============+============+
|  2 |  101 | 2015-06-28 | 2059-12-31 |
+----+------+------------+------------+
| 10 |  101 | 2018-01-07 | 2059-12-31 |
+----+------+------------+------------+

When id = 2,I want to assign e_dt = 2018-01-06 (i.e. 1 day prior to the st_dt of id=10).

How do i do that?

Upvotes: 2

Views: 231

Answers (3)

Reza ArabQaeni
Reza ArabQaeni

Reputation: 4907

You need information of two record in one record in a sequence order, So first prepare it in orderd sequence with CTE and ROW_NUMBER then join result with itself to arrange a record with next record in sequence:

with TA as
(select ROW_NUMBER() over(order by id) as rn,* from TableA)
select t1.id,t1.m_id,t1.s_dt,DATEADD(day, -1,t2.st_dt) as e_dt 
from TA t1 join TA t2 on t1.rn+1=t2.rn

Upvotes: 0

Brien Foss
Brien Foss

Reputation: 3367

To do this, I would recommend using a Common Table Expression, for example:

--use common table expression to sort the m_id and id into unique row numbers
;WITH cte
AS (
    SELECT ROW_NUMBER() OVER (ORDER BY m_id, id) AS rowNum
    , *
    FROM TableA
    )
--Update the original table, using the row number + 1 
--(use the next row to determine the next s_dt) 
UPDATE tblA
SET tblA.e_dt = DATEADD(day, - 1, tblB.s_dt)
FROM cte tblA
INNER JOIN cte tblB ON tblA.rowNum + 1 = tblB.rowNum and tblA.m_id = tblB.m_id

In my example, you'll see that the e_dt when its the last/latest row for a corresponding m_id, will be your future date 2059-12-31, because there is no row with a corresponding s_dt for it yet. Based on the way the columns are named, basically what I am saying is "There current run for this m_id has no "end date" yet... It is still running".

Also, you will see that this works even if the data was inserted into the table in non-sequential order. Meaning, an m_id with a different value 'could' very well be inserted in-between a pair of start and end dates for another m_id.

Results of test:

enter image description here

Code I used to test, with additional test values:

CREATE TABLE [dbo].[TableA](
    [ID] [INT] IDENTITY(1,1) NOT NULL,
    [m_id] [INT],
    [s_dt] [DATETIME],
    [e_dt] [DATETIME]
 CONSTRAINT [PK_TableA_ID] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON)
) ON [PRIMARY]
GO


INSERT INTO [dbo].[TableA]
SELECT '101','2015-06-28','2059-12-31' UNION ALL
SELECT '101','2018-01-07','2059-12-31' UNION ALL
SELECT '102','2015-06-28','2059-12-31' UNION ALL
SELECT '102','2016-05-07','2059-12-31' UNION ALL
SELECT '103','2015-06-28','2059-12-31' UNION ALL
SELECT '103','2017-01-07','2059-12-31' UNION ALL
SELECT '102','2017-05-28','2059-12-31' UNION ALL
SELECT '104','2018-01-07','2059-12-31' UNION ALL
SELECT '103','2018-01-07','2059-12-31' UNION ALL
SELECT '102','2018-06-28','2059-12-31' UNION ALL
SELECT '104','2018-01-24','2059-12-31'
GO

--verify data
SELECT * FROM TableA

--use common table expression to sort the m_id and id into unique row numbers
;WITH cte
AS (
    SELECT ROW_NUMBER() OVER (ORDER BY m_id, id) AS rowNum
    , *
    FROM TableA
    )
--Update the original table, using the row number + 1 (use the next row to determine the next s_dt) 
UPDATE tblA
SET tblA.e_dt = DATEADD(day, - 1, tblB.s_dt)
FROM cte tblA
INNER JOIN cte tblB ON tblA.rowNum + 1 = tblB.rowNum and tblA.m_id = tblB.m_id

--verify
SELECT * FROM TableA ORDER BY m_id, id


--DROP TABLE dbo.[TableA]

Upvotes: 0

Vahid Farahmandian
Vahid Farahmandian

Reputation: 6568

If you are using SQL Server 2012 or later, you can try this:

SELECT id, s_dt , DATEADD(DAY,-1,LEAD(s_dt) OVER(ORDER BY id)) AS e_dt FROM YourTable

Upvotes: 2

Related Questions