pvsbandi
asked on
SQL Previous row
Hi,
I have data like the following, in SQL Server 2012.
I want those records where the Resp_CD changed from a blank to PN, partition by ID and order by Status_Dt
In this scenario, the second row in the below grid is what i need.
I know we can write a subquery, but was wondering if there are any easier ways to query.
Thank you!
I have data like the following, in SQL Server 2012.
I want those records where the Resp_CD changed from a blank to PN, partition by ID and order by Status_Dt
In this scenario, the second row in the below grid is what i need.
I know we can write a subquery, but was wondering if there are any easier ways to query.
Thank you!
ID Status Resp_CD Status_DT
1 A PN 11/5/2017
1 P PN 11/4/2017
1 P 11/2/2017
1 C 11/1/2017
------------------------------------------------------
2 P PN 11/5/2017
2 P NM 11/3/2017
2 A 11/1/2017
ASKER
Hi,
I don't want the record where the status changed from NM to PN. I'm only interested in those records where the previous value was a blank and the current value is a PN
I don't want the record where the status changed from NM to PN. I'm only interested in those records where the previous value was a blank and the current value is a PN
Please try this solution -
Output
CREATE TABLE response
(
ID INT
,[Status] VARCHAR(1)
,Resp_CD VARCHAR(2)
,Status_DT DATETIME
)
GO
INSERT INTO response VALUES
(1,'A','PN','11/5/2017'),
(1,'P','PN','11/4/2017'),
(1,'P',NULL,'11/2/2017'),
(1,'C',NULL,'11/1/2017'),
(2,'P','PN','11/5/2017'),
(2,'P','NM','11/3/2017'),
(2,'A',NULL,'11/1/2017')
GO
SELECT * FROM
(
SELECT * , ROW_NUMBER() OVER (PARTITION BY [Status] ORDER BY Status_DT) rnk
FROM response
WHERE Resp_CD = 'PN' OR Resp_CD IS NULL
)r WHERE rnk = 2
Output
/*------------------------
OUTPUT
------------------------*/
ID Status Resp_CD Status_DT rnk
----------- ------ ------- ----------------------- --------------------
1 A PN 2017-11-05 00:00:00.000 2
1 P PN 2017-11-04 00:00:00.000 2
(2 row(s) affected)
Removed rnk column.
/*------------------------
SELECT ID,[Status],[Resp_CD],[Status_DT] FROM
(
SELECT * , ROW_NUMBER() OVER (PARTITION BY [Status] ORDER BY Status_DT) rnk
FROM response
WHERE Resp_CD = 'PN' OR Resp_CD IS NULL
)r WHERE rnk = 2
------------------------*/
ID Status Resp_CD Status_DT
----------- ------ ------- -----------------------
1 A PN 2017-11-05 00:00:00.000
1 P PN 2017-11-04 00:00:00.000
(2 row(s) affected)
ASKER
Thanks! But I only want the first instance when the resp code turned to PN.
The status field doesn't play much role in this
The status field doesn't play much role in this
ok, please use this-
/*------------------------
SELECT * FROM
(
SELECT *
FROM response
WHERE Resp_CD IS NULL
)r
WHERE EXISTS ( SELECT NULL FROM response r1 WHERE r1.Status = r.Status AND r1.Resp_CD = 'PN' )
------------------------*/
ID Status Resp_CD Status_DT
----------- ------ ------- -----------------------
1 P NULL 2017-11-02 00:00:00.000
2 A NULL 2017-11-01 00:00:00.000
(2 row(s) affected)
ASKER
Your query is returning 2 rows, while i need only 1.
I actually used the below logic and got 1 row.
I actually used the below logic and got 1 row.
WITH AA AS
(
SELECT ID,STS_CD,FIN_CD,POSTING_DT
, ROW_NUMBER() OVER(PARTITION BY ID ORDER BY POSTING_DT) AS RN
FROM
(SELECT 1 ID,'P' AS STS_CD,'' AS FIN_CD,20171101 AS POSTING_DT
UNION
SELECT 1 ID,'P' AS STS_CD,'PN' AS FIN_CD,20171106 AS POSTING_DT
UNION
SELECT 1 ID,'P' AS STS_CD,'PN' AS FIN_CD,20171108 AS POSTING_DT
UNION
SELECT 1 ID,'P' AS STS_CD,'PN' AS FIN_CD,20171108 AS POSTING_DT
) TMP
)
SELECT TMP.*
FROM
(
SELECT AA.*
,BB.FIN_CD AS PREV_FIN_CD
FROM AA
LEFT JOIN AA AS BB
ON AA.ID = BB.ID AND AA.RN = BB.RN + 1
WHERE (AA.FIN_CD = 'PN' AND BB.FIN_CD = '')
) TMP
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
2 P NM 11/3/2017