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

ghhaqwfi  于 2023-04-19  发布在  SQL Server
关注(0)|答案(3)|浏览(132)

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

0vvn1miw

0vvn1miw1#

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

q3aa0525

q3aa05252#

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

hs1ihplo

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

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

相关问题