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
This can be done using lag() window function to get the previous row, and sum() window function to retrieve a cumulative sum based on the previous_installement value :
with cte as (
select *, lag(Payment_installments, 1, 0) over (partition by CustomerNumber order by Date) as previous_installement
from mytable
),
cte2 as (
select *, sum(previous_installement) over (partition by CustomerNumber order by Id) as sm
from cte
)
select Id, CustomerNumber, Payment_installments, Date, case when sm > 0 then 1 else 0 end as HasInstallmentPaymentInThePast
from cte2
order by Id