SQL custom return value based on condition

I am new to SQL server, can you please help me with the query below query : I have below Table - Table1 :

PostingDate ReturnCheckReason PaymentStatus PolicyNumber

7/23/2020 Cancel Payment Return 1234

8/6/2020 Cancel Payment Return 1234

Here I would to display return response as, this is customized response

PostingDate ReturnCheckReason PaymentStatus PolicyNumber

null Cancel Payment ALL Payments are Canceled 1234

Input to my stored Proc is PolicyNumber e.g 1234 which return 5 rows

Here I need to cover below cases :-

1) If the first payment is not cancelled , I need to return that row.

2) If all the payments are cancelled for that policy as shown above, then we need to display customized message , or else my query will display NULL

I have same table with below Data , then I need to handle Case 1 as shown above.

PostingDate ReturnCheckReason PaymentStatus PolicyNumber

7/23/2020 Null Payment 1234

8/6/2020 Null Payment 1234

8/5/2020 Cancel Payment Return 1234

This is my Query :-

SELECT TOP 1 [PolicyNumber], [PostingDate], [PaymentStatus] FROM [dbo].[Bil_PaymentSearch] WITH (NOLOCK) WHERE (PolicyNumber = @PolicyNumber) AND (REturnCheckreason <> 'Cancel payment') AND (PaymentOrReturn <> 'Return') ORDER BY PostingDate ASC

My query handles first case , but it will not handle 2nd case, it will display as NULL, please help me

1 Reply

Know the answer? Post it — somebody with the same question will find it here.