I have a table CustomerPurchaseInfo
in SQL Server that looks like this:
| Id | CustomerNumber | Payment in installments | Date |
| ------------ | ------------ | ------------ | ------------ |
| 1 | 2 | 0 | 2022-01-02 |
| 2 | 2 | 0 | 2022-02-10 |
| 3 | 2 | 1 | 2022-04-05 |
| 4 | 3 | 0 | 2022-06-01 |
| 5 | 2 | 0 | 2022-06-08 |
| 6 | 2 | 0 | 2022-08-22 |
| 7 | 2 | 1 | 2022-10-03 |
| 8 | 3 | 0 | 2022-11-04 |
| 9 | 2 | 0 | 2023-01-04 |
This table shows purchase history of customers and has a column that shows if a customer paid that purchase in installments or not.
Now I want a query that if any past purchase of a customer has Paymentininstallment = 1
, it shows that this customer has a history of installment payments.
This is the output I'm looking for from this query:
| Id | CustomerNumber | Payment in installments | Date | HasInstallmentPaymentInThePast |
| ------------ | ------------ | ------------ | ------------ | ------------ |
| 1 | 2 | 0 | 2022-01-02 | 0 |
| 2 | 2 | 0 | 2022-02-10 | 0 |
| 3 | 2 | 1 | 2022-04-05 | 0 |
| 4 | 3 | 0 | 2022-06-01 | 0 |
| 5 | 2 | 0 | 2022-06-08 | 1 |
| 6 | 2 | 0 | 2022-08-22 | 1 |
| 7 | 2 | 1 | 2022-10-03 | 1 |
| 8 | 3 | 0 | 2022-11-04 | 0 |
| 9 | 2 | 0 | 2023-01-04 | 1 |
In fact by the first time that customer pays with installments, all purchases after that purchase will have HasInstallmentPaymentInThePast = 1
3条答案
按热度按时间0vvn1miw1#
A bit simpler version:
By using ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING you can skip the current row's value and get previous ones
q3aa05252#
This can be done using
lag()
window function to get the previous row, andsum()
window function to retrieve a cumulative sum based on theprevious_installement
value :Demo here
hs1ihplo3#
You can try using a subquery and Window function for solve your problem
first you must find previous_Payment_in_installments with lag in Window function
then you can find HasInstallmentPaymentInThePast with max
You can to insert the basic data with the following codes