CODEWITHSUNDEEP
sqlsql-servert-sqlwindow-functionshistorical-db
nasim_bbb
nasim_bbb

Reputation: 31

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

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

Upvotes: 0

Views: 87

Answers (3)

siggemannen
siggemannen

Reputation: 9287

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

Upvotes: 4

abolfazl sadeghi
abolfazl sadeghi

Reputation: 2368

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

Upvotes: 2

SelVazi
SelVazi

Reputation: 16063

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

Upvotes: 2

Related Questions