How to populate new column by comparing one row with its next row

Transact-SQL
1

I am finding difficulty in writing a query to get the desired output. I need to get date difference between two consecutive rows and If date difference is <=6 months and reason code is valid "Yes" Then new column should be populate with values "Yes" other wise "NO"
I have to populate new column with values "Yes" or "NO" based on criteria given below
Criteria 1:

For first row, New column value should be always "No" From second row, If two dates difference is <=6 moths and reason code is valid then new column value should be "Yes" other wise "No"
Date difference of first row and second row is <=6 months and code valid "yes" hence new column value is Yes.
Date diff of second row and third row is <=6 months but valid is "No" hence new column value is "No".
Date diff of third row and forth row is more than >6 months and but valid is "Yes" hence new column value is "No".
Input
|Cust_id |Enter_Date | Rscode | Valid
|123 |2020-04-11 | BCB | Yes
|123 |2020-06-13 | ABC | Yes
|123 |2020-09-01 | FBS | No
|123 |2021-05-01 | ABC | Yes
|123 |2021-07-05 | ABC | Yes

Out put should be like below
|Cust_id |Enter_Date | Rscode | Valid | New_column
|123 |2020-04-11 | BCB | Yes |No
|123 |2020-06-13 | ABC | Yes |Yes
|123 |2020-09-01 | FBS | No |No
|123 |2021-05-01 | ABC | Yes |NO
|123 |2021-07-05 | ABC | Yes |Yes

Criteria 2:
If reason code is starts with "RQT" and any of the valid code is followed By RQT(even date difference is 6 months and reason code is valid) then new column should be "No" from next consecutive row, the output is same as is.

Custid enter_date Rscode Valid New_column
345 2020-02-19 RQT Yes NO
345 2020-03-22 BCB Yes NO
345 2020-06-18 RQT Yes Yes
345 2020-10-29 BCB Yes Yes
345 2021-03-24 ABC Yes Yes

2

Would you please provide directly usable sample data -- CREATE TABLE and INSERT statement(s) for the sample data -- rather than just text lines of the data? We can't write code against text lines.

3

CREATE TABLE Rscode
(
Custid int,
Enter_Dt date,
Rsn_Cd varchar(50),
IsValid varchar(50)
);

INSERT INTO Rscode (Custid, Enter_Dt, Rsn_Cd, IsValid )
VALUES (123, '2020-04-17', 'BCB', 'Yes');
INSERT INTO Rscode (Custid, Enter_Dt, Rsn_Cd, IsValid )
VALUES (123, '2020-06-13', 'ABC', 'Yes');
INSERT INTO Rscode (Custid, Enter_Dt, Rsn_Cd, IsValid )
VALUES (123, '2020-09-23', 'FBS', 'No');
INSERT INTO Rscode(Custid, Enter_Dt, Rsn_Cd, IsValid )
VALUES (123, '2021-05-13', 'ABC', 'Yes');
INSERT INTO Rscode (Custid, Enter_Dt, Rsn_Cd, IsValid )
VALUES (123, '2021-07-16', 'ABC', 'Yes');

INSERT INTO Rscode (Custid, Enter_Dt, Rsn_Cd, IsValid )
VALUES (345, '2020-02-19', 'RQT', 'No');
INSERT INTO Rscode (Custid, Enter_Dt, Rsn_Cd, IsValid )
VALUES (345, '2020-03-22', 'BCB', 'Yes');
INSERT INTO Rscode (Custid, Enter_Dt, Rsn_Cd, IsValid )
VALUES (345, '2020-06-18', 'RQT', 'Yes');
INSERT INTO Rscode (Custid, Enter_Dt, Rsn_Cd, IsValid )
VALUES (345, '2020-10-29', 'BCB', 'Yes');
INSERT INTO Rscode (Custid, Enter_Dt, Rsn_Cd, IsValid )
VALUES (345, '2021-03-24', 'ABC', 'Yes');

4

Here's the code for Criterion 1. I don't really understand Criterion 2, so I can't write code for it now.

;WITH cte_rscode AS (
    SELECT *, LAG(Enter_Dt) OVER(PARTITION BY Custid ORDER BY Enter_Dt) AS Previous_Enter_Dt
    FROM Rscode
)
SELECT Custid, Enter_Dt, Rsn_Cd, IsValid,
    CASE WHEN Previous_Enter_Dt IS NULL THEN 'No'
        ELSE CASE WHEN IsValid = 'Yes' AND Enter_Dt <= DATEADD(MONTH, 6, Previous_Enter_Dt) THEN 'Yes' ELSE 'No' END END AS New_Column
FROM cte_rscode
ORDER BY Custid, Enter_Dt
5

Thank you so much. I see expected result for criteria 1, the criteria 2 is if any reason code (second row) followed by RQT(first row) than new column value should be "NO"
Example; The first row reason code of custid 345/346 is 'RQT' and if any of the reason code (BCB or RQT or any other reason code) followed by "RQT" than new column should be "NO" which is applicable only for second row, for remaining rows the output is same as criteria 1.
Expected output :
Custid enter_date Rscode Valid New_column
345 2020-02-19 RQT Yes NO
345 2020-03-22 BCB Yes NO
345 2020-06-18 RQT Yes Yes
345 2020-10-29 BCB Yes Yes
345 2021-03-24 ABC Yes Yes

Custid enter_date Rscode Valid New_column
346 2020-02-19 RQT Yes NO
346 2020-03-22 RQT Yes NO
346 2020-06-18 RQT Yes Yes
346 2020-10-29 BCB Yes Yes
346 2021-03-24 ABC Yes Yes