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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

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
Pawan KumarDatabase 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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.