How to get the previous 6 months profile based on specific date in SQL?-CodePudding

I would like to know how would one go about getting the PaymentProfile for the previous 6 months for each payment made, example of what I am looking for is displayed below but not sure how it would look in SQL syntax:

P = Paid
U = Unpaid
? = Pending

From this output:

| PaidHistory | PersonId | PaymentId | PaymentDueDt | 
     .....        ....       ....          .....        
|     P       |    101   |   23      |   2022-01-26 |  
|     U       |    101   |   24      |   2022-02-26 |  
|     P       |    101   |   25      |   2022-03-26 |  
|     P       |    101   |   26      |   2022-04-26 |   
|     P       |    101   |   27      |   2022-05-26 |   
|     P       |    101   |   28      |   2022-06-26 |    
|     U       |    101   |   29      |   2022-07-26 |    
|     P       |    101   |   30      |   2022-08-26 |  
|     ?       |    101   |   31      |   2022-09-26 | 

To this output:

     | PaidHistory | PersonId | PaymentId | PaymentDueDt | PaymentProfile |
     .....        ....       ....          .....        ..........
|     P       |    101   |   23      |   2022-01-26 |     PPPPPP     |
|     U       |    101   |   24      |   2022-02-26 |     PPPPPU     |
|     P       |    101   |   25      |   2022-03-26 |     PPPPUP     |
|     P       |    101   |   26      |   2022-04-26 |     PPPUPP     |
|     P       |    101   |   27      |   2022-05-26 |     PPUPPP     |
|     P       |    101   |   28      |   2022-06-26 |     PUPPPP     |
|     U       |    101   |   29      |   2022-07-26 |     UPPPPU     |
|     P       |    101   |   30      |   2022-08-26 |     PPPPUP     |
|     ?       |    101   |   31      |   2022-09-26 |     PPPUP?     |

So basically how to create a column such as PaymentProfile? Showing the 6 months of Payments for that specific PaymentDueDt.

CodePudding user response：

You have an overlapping groups problem here, you may solve it by performing a self-join. If your version of SQL Server supports the STRING_AGG function you may try the following:

SELECT T.PaidHistory, T.PersonId, T.PaymentId, T.PaymentDueDt,
       STRING_AGG(D.PaidHistory, '') WITHIN GROUP (ORDER BY D.PaymentDueDt) PaymentProfile 
FROM table_name T JOIN table_name D
ON D.PaymentDueDt BETWEEN DATEADD(MONTH, -5, T.PaymentDueDt) AND T.PaymentDueDt
AND T.PersonId = D.PersonId
GROUP BY T.PaidHistory, T.PersonId, T.PaymentId, T.PaymentDueDt
ORDER BY T.PersonId, T.PaymentDueDt

See a demo.