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!

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                          
 

Open in new window

pvsbandiAsked:
Who is Participating?
 
Pawan KumarConnect With a Mentor Database ExpertCommented:
The solution was working. You just need to add Id and you will one row :)
Also note that ranking you are doing will degrade the performance as SQL Server will have to sort the data and give u ranking.

/*------------------------
SELECT * FROM 
(
	SELECT * 
	FROM response
	WHERE Resp_CD = 'PN'
)r
WHERE EXISTS ( SELECT NULL FROM response r1 WHERE r1.Id = r.Id AND r1.Status = r.status AND r1.Resp_CD IS NULL )
------------------------*/
ID          Status Resp_CD Status_DT
----------- ------ ------- -----------------------
1           P      PN      2017-11-04 00:00:00.000

(1 row(s) affected)

Open in new window

0
 
Pawan KumarDatabase ExpertCommented:
Where this record came from ?

2      P              NM                 11/3/2017
0
 
pvsbandiAuthor Commented:
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
0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

 
Pawan KumarDatabase ExpertCommented:
Please try this solution -

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

Open in new window


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)

Open in new window

0
 
Pawan KumarDatabase ExpertCommented:
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)

Open in new window

0
 
pvsbandiAuthor Commented:
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
0
 
Pawan KumarDatabase ExpertCommented:
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)

Open in new window

0
 
pvsbandiAuthor Commented:
Your query is returning 2 rows, while i need only 1.
  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

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.