SQL custom return value based on condition

Question

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 15:30 Cancel Payment                  Return          1234
8/6/2020 17:40  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

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

If the Table Data like this :-

PostingDate     ReturnCheckReason   PaymentStatus   PolicyNumber
7/23/2020 15:30 Null                    Payment         1234
8/6/2020 17:40  Null                    Payment         1234
8/4/2020 14:29  Null                    Payment         1234
8/5/2020 6:09   Null                    Payment         1234
8/5/2020 12:47  Cancel Payment          Return          1234

Then I need to return If the first payment is not cancelled , I need return that row. So I wrote this 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

So Now I need to combine your query and my query to handle both the below scenario, can you please help me

If the first payment is not cancelled , I need return that row.
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

Gordon Linoff · Accepted Answer · 2020-08-07 19:50:49Z

2

You can use aggregation and conditional logic:

SELECT PolicyNumber, MAX(ReturnCheckReason), 
       (CASE WHEN MIN(ReturnCheckReason) = MAX(ReturnCheckReason) AND MAX(ReturnCheckReason) = 'Cancel payment'
             THEN 'All Payments Cancelled'
         END)
FROM [dbo].[Bil_PaymentSearch] bps
WHERE PolicyNumber = @PolicyNumber 
GROUP BY PolicyNumber

edited Aug 7, 2020 at 19:50

answered Aug 7, 2020 at 19:01

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

user613400 Over a year ago

Hi Gordon, Thanks for your quick response, I am sorry to say that , there is little bit modification in my question, can you please help me

John Cappelletti Over a year ago

Wouldn't MAX(ReturnCheckReason) always equal MAX(ReturnCheckReason) ?

user613400 Over a year ago

Can you please help me John or Gordon

user613400 Over a year ago

Hi Gordon, I tried to solve using below Query, kindly let me know if it is any other better way I can able to solve :-

Collectives™ on Stack Overflow

SQL custom return value based on condition

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related