CODEWITHSUNDEEP
sqlsql-server
user613400
user613400

Reputation: 53

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 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

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

Upvotes: 0

Views: 415

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269603

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

Upvotes: 2

Related Questions