Looping in SQL with Where condition

Question

Hi Team,

I am new to SQL server, can you please help me to write query on my scenerios:

I have below Table - Table1 :-

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

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

Here I need to cover below cases :-

If the first payment is not cancelled , I need return that row.
If the first payment is cancelled , look for a next non-cancelled payment. If a next payment is found that was not cancelled, I need to return that row.

My Query Is :- I think I need to loop all the data for that policy till I get Non Cancelled payment, please let me know how do I that

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

Jenith Thakkar · Answer

Hi Your select top 1 query is quite good What's going wrong with that query

Kumar AU · Answer

Hi Jenith / varun, Thanks for your response. However I would like to know how do I return from Stored Proc that If all the Payment are Cancelled for that Policy. like I need to return back as "All Payments are Cancelled" in the "PaymentStatus" Column . At present my qurey return as NULL. PostingDate ReturnCheckReason PaymentStatus PolicyNumber 8/6/2020 17:40 Cancel Payment Return 1234 7/23/2020 15:30 Cancel Payment Return 1234 Expected :- PostingDate ReturnCheckReason PaymentStatus PolicyNumber 8/6/2020 17:40 Cancel Payment All Payments are Cancelled 1234

Varun Setia · Answer

Hi, This SQL Query is good, are you facing any difficulty as compared to final result ?

Varun Setia · Answer

Hi, One way is, use if exists for entrie query IF EXISTS ( 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 ) BEGIN 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 END ELSE SELECT "All Payments are Cancelled" ; Second, use temp table to store result, to simplify query SELECT TOP 1 [PolicyNumber], [PostingDate], [PaymentStatus] FROM [dbo].[Bil_PaymentSearch] WITH (NOLOCK) INTO #PAYMENT WHERE (PolicyNumber = @PolicyNumber) AND (REturnCheckreason 'Cancel payment' ) AND (PaymentOrReturn 'Return' ) ORDER BY PostingDate ASC IF EXISTS ( SELECT * FROM #PAYMENT) SELECT * FROM #PAYMENT ELSE SELECT "All Payments are Cancelled"