CODEWITHSUNDEEP
sqldatabaset-sqldatabase-design
ℂybernetician
ℂybernetician

Reputation: 139

Get last previous value of a record if condition is true

I have the following table:

+ ---------+------------+--------------+-------------+-------------+----------------+
| RecordId | Client     | Subscription | Status      | Date        | Payment Method |
+ ---------+------------+--------------+-------------+-------------+----------------+
| ABC1234  | John Doe   | NetFlex      | Renewed     | 13 Nov 2018 | Credit Card    |
| ABC1233  | Jane Smith | SplitiFy     | Not Renewed | 01 Sep 2018 | NULL           |
| ABC1232  | John Doe   | NetFlex      | Renewed     | 13 Nov 2017 | PayPart        |
| ABC1231  | Jane Smith | SplitiFy     | Renewed     | 01 Sep 2017 | Cash           |
| ABC1230  | John Doe   | NetFlex      | Renewed     | 13 Nov 2016 | Cash           |
+ ---------+------------+--------------+-------------+-------------+----------------+

I would like to have a new column in which I can provide the previous payment method for a specific client, and setting it to NULL if there was not a previous payment. In other words, I would like to have the following information

+ ---------+------------+--------------+-------------+-------------+----------------+-------------------------+
| RecordId | Client     | Subscription | Status      | Date        | Payment Method | Previous Payment Method |
+ ---------+------------+--------------+-------------+-------------+----------------+-------------------------+
| ABC1234  | John Doe   | NetFlex      | Renewed     | 13 Nov 2018 | Credit Card    | PayPart                 |
| ABC1233  | Jane Smith | SplitiFy     | Not Renewed | 01 Sep 2018 | None           | Cash                    |
| ABC1232  | John Doe   | NetFlex      | Renewed     | 13 Nov 2017 | PayPart        | Cash                    |
| ABC1231  | Jane Smith | SplitiFy     | Renewed     | 01 Sep 2017 | Cash           | NULL                    |
| ABC1230  | John Doe   | NetFlex      | Renewed     | 13 Nov 2016 | Cash           | NULL                    |
+ ---------+------------+--------------+-------------+-------------+----------------+-------------------------+

Does someone know how to create this latter table?

Upvotes: 0

Views: 58

Answers (1)

Sanal Sunny
Sanal Sunny

Reputation: 617

If you are using SQL Server 2012 or higher then you can make use of LAG which will return data from a previous row in the same result set without the use of a self-join

So here we can use LAG In combination with PARTITION BY to get the Expected Result 

SELECT RecordId,Client,Subscription,Status,Date,PaymentMethod 
       ,LAG (PaymentMethod, 1, 0) OVER (PARTITION BY Client,Subscription ORDER BY Date) 
         AS PreviousPaymentMethod 
 FROM Table

Upvotes: 2

Related Questions