How to get a Historical information considering all the previous records about a customer in SQL Server?

Question

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

Answer 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

Demo here

Answer 2

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

select 
         id
        ,CustomerNumber
        ,Payment_in_installments
        ,Date
        ,max(previous_Payment_in_installments) 
                     over (partition by CustomerNumber order by Date) as HasInstallmentPaymentInThePast
from  (
          select 
                  id
                , CustomerNumber
                , Payment_in_installments
                ,Date
                ,lag(Payment_in_installments,1,0) 
                               over (partition by CustomerNumber order by Date) as previous_Payment_in_installments
          from CustomerPurchaseInfo
)s 
order by id 

You can to insert the basic data with the following codes

drop table if exists CustomerPurchaseInfo

create table CustomerPurchaseInfo (Id int,CustomerNumber int,Payment_in_installments int,Date date  )
insert into CustomerPurchaseInfo
(id,CustomerNumber,Payment_in_installments,Date)
           select 1 as Id ,2    as CustomerNumber,0 as Payment_in_installments,'2022-01-02' as Date
union all select 2  as Id ,2    as CustomerNumber,0 as Payment_in_installments,'2022-02-10' as Date
union all select 3  as Id ,2    as CustomerNumber,1 as Payment_in_installments,'2022-04-05' as Date
union all select 4  as Id ,3    as CustomerNumber,0 as Payment_in_installments,'2022-06-01' as Date
union all select 5  as Id ,2    as CustomerNumber,0 as Payment_in_installments,'2022-06-08' as Date
union all select 6  as Id ,2    as CustomerNumber,0 as Payment_in_installments,'2022-08-22' as Date
union all select 7  as Id ,2    as CustomerNumber,1 as Payment_in_installments,'2022-10-03' as Date
union all select 8  as Id ,3    as CustomerNumber,1 as Payment_in_installments,'2022-11-04' as Date
union all select 9  as Id ,2    as CustomerNumber,0 as Payment_in_installments,'2023-01-04' as Date

Answer 3

A bit simpler version:

select  *
,   ISNULL(max([Payment in installments]) over(partition by customernumber order by date ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING), 0)
from (
    VALUES  (1, 2, 0, N'2022-01-02')
    ,   (2, 2, 0, N'2022-02-10')
    ,   (3, 2, 1, N'2022-04-05')
    ,   (4, 3, 0, N'2022-06-01')
    ,   (5, 2, 0, N'2022-06-08')
    ,   (6, 2, 0, N'2022-08-22')
    ,   (7, 2, 1, N'2022-10-03')
    ,   (8, 3, 0, N'2022-11-04')
    ,   (9, 2, 0, N'2023-01-04')
) t (Id,CustomerNumber,[Payment in installments],Date)
order by id

By using ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING you can skip the current row's value and get previous ones