Link to home
Start Free TrialLog in
Avatar of pvsbandi
pvsbandiFlag for United States of America

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!

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

Avatar of Pawan Kumar
Pawan Kumar
Flag of India image

Where this record came from ?

2      P              NM                 11/3/2017
Avatar of pvsbandi

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

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

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

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

ASKER CERTIFIED SOLUTION
Avatar of Pawan Kumar
Pawan Kumar
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial