WHILE LOOP IN SQL SERVER

multi 4u

9y803

here is my sql query stored procedure given below.

i want some change in my query that if the lapucurrentbal is not 0.00 than it will fetch the last record so for that i have to used while loop here that if it contains 0.00 than it go for top1 and if in top 1 record lapucurrentbal is also 0.00 than again it go for top2 and thus it will continue until and unless lapucurrentbal is not 0.00

create procedure sp_LapuOpeningBal

(

@lapumobileno varchar(50)

)

begin

select top 1 LapuCurrentBal as Opening,lapumobileno,RechargeDate from recharge where RechargeDate

< dateadd(day,datediff(day,0,GETDATE()),0)

and RechargeDate >dateadd(day,datediff(day,1,GETDATE()),0)

and lapumobileno=@lapumobileno group by LapuCurrentBal,LapuMobileNo,RechargeDate order by RechargeDate desc

end

2 Replies

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

Ramesh Barik9y ago
create procedure sp_LapuOpeningBal
(
@lapumobileno varchar(50)
)
as
begin
;
WITH QResult
AS (SELECT
ROW_NUMBER() OVER (
PARTITION BY RD.lapumobileno ORDER BY RD.RechargeDate DESC
) AS Row
,RD.lapumobileno
,RD.LapuCurrentBal AS Opening
,RD.RechargeDate
FROM recharge AS RD
WHERE RechargeDate
< DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)
AND RD.RechargeDate > DATEADD(DAY, DATEDIFF(DAY, 1, GETDATE()), 0)
AND RD.lapumobileno = @lapumobileno
AND RD.LapuCurrentBal <> 0.0)
SELECT
*
FROM QResult
WHERE ROW = 1;
END

==================

Try this it may help you.
Nitin Sontakke9y ago
My dear friend, will it not help us if you could give us some sample data in script (text) format.

Please help us help you better!