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