Looping in SQL with Where condition

Kumar AU

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

4 Replies

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

Varun Setia6y ago
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"
-1
Kumar AU6y ago
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

+1
Varun Setia6y ago
Hi,
This SQL Query is good, are you facing any difficulty as compared to final result ?
+1
Jenith Thakkar6y ago
Hi
Your select top 1 query is quite good
What's going wrong with that query
+2