DECLARE @Percent DECIMAL(18,2);SET @Percent = 10;
DECLARE @RowNo INT,@CurrCode INT, @RecCode INT, @MaxSNo INT, @CurrLevel INT, @BalAmt INT, @SendBal INT, @GiveAmt INT,@SNo INT,@RSNo INT, @AchiverTag INT
SET @RowNo = 1;
DECLARE @DateTable AS TABLE (RowNo INT IDENTITY(1,1),mSNo INT,AppCode INT, SendBal INT, AchiverTag INT)
INSERT INTO @DateTable
SELECT  SNo , ForRec.AppMstID, (COALESCE(Amount,0) - COALESCE(Paid,0)), COALESCE(ForRec.IsAchiverTag,0) FROM ForRec INNER JOIN AppMast ON ForRec.AppMstID = AppMast.AppMstID 
WHERE COALESCE(ForRec.IsAchiverTag,0) = 0  AND 
RecDate <= DATEADD(MINUTE,330,GETUTCDATE()) AND (COALESCE(Amount,0) - COALESCE(Paid,0)) > 0 AND Tag = 0 AND AppMast.AppMstActivate = 1 ORDER BY SNo; -- AND (((COALESCE(Amount,0) - COALESCE(Paid,0)) % 500) = 0) 

SELECT * FROM @DateTable
WHILE @RowNo < = ( SELECT COUNT(RowNo) FROM @DateTable)
BEGIN		
	PRINT @RowNo;
	SET @SendBal = 0; SET @AchiverTag = 0;
	SELECT @RSNo = mSNo , @CurrCode= AppCode, @SendBal = COALESCE(SendBal,0), @AchiverTag = AchiverTag FROM @DateTable WHERE RowNo = @RowNo;	
	WHILE @SendBal > 0
	BEGIN		
		SET @RecCode = 0; SET @BalAmt = 0;
		/******* START OF FOR ACHIVERS *********/
		IF @AchiverTag = 1
		BEGIN
			SELECT Top 1 @SNo = ForSend.Sno, @RecCode = ForSend.AppMstID, @CurrLevel = ForSend.uLevel, @BalAmt = CASE WHEN CAST(COALESCE(ForSend.Amount,0)AS DECIMAL(18,2)) * (@Percent / CAST(100 AS DECIMAL(18,2)) ) < 500 THEN COALESCE(500,0) - COALESCE(ForSend.Paid,0) ELSE CAST(CAST(COALESCE(ForSend.Amount,0)AS DECIMAL(18,2)) * (@Percent / CAST(100 AS DECIMAL(18,2))) AS INT) - COALESCE(ForSend.Paid,0)END FROM ForSend  INNER JOIN AppMast ON  ForSend.AppMstID = AppMast.AppMstID   WHERE ForSend.Tag = 0 AND AppMast.AppMstActivate =  1 AND (@CurrCode <> ForSend.AppMstID) AND ((CAST(COALESCE(ForSend.Paid,0) AS DECIMAL(18,2))/ CAST(COALESCE(ForSend.Amount,0)AS DECIMAL(18,2)))* CAST(100 AS DECIMAL(18,2)) < CAST(10 AS DECIMAL(18,2)) OR CAST(COALESCE(ForSend.Paid,0) AS DECIMAL(18,2)) < CAST(500 AS DECIMAL(18,2))) 
			AND(CASE WHEN CAST(COALESCE(ForSend.Amount,0)AS DECIMAL(18,2)) * (CAST(10 AS DECIMAL(18,2))/ CAST(100 AS DECIMAL(18,2))) < CAST(500 AS DECIMAL(18,2)) THEN COALESCE(500,0) - COALESCE(ForSend.Paid,0) ELSE CAST(CAST(COALESCE(ForSend.Amount,0)AS DECIMAL(18,2)) * (CAST(10 AS DECIMAL(18,2))/ CAST(100 AS DECIMAL(18,2))) AS INT) - COALESCE(ForSend.Paid,0)END) > 0
			--AND(CASE WHEN CAST(COALESCE(ForSend.Amount,0)AS DECIMAL(18,2)) * (CAST(10 AS DECIMAL(18,2))/ CAST(100 AS DECIMAL(18,2))) < CAST(500 AS DECIMAL(18,2)) THEN COALESCE(500,0) - COALESCE(ForSend.Paid,0) ELSE CAST(CAST(COALESCE(ForSend.Amount,0)AS DECIMAL(18,2)) * (CAST(10 AS DECIMAL(18,2))/ CAST(100 AS DECIMAL(18,2))) AS INT) - COALESCE(ForSend.Paid,0)END) % 500 = 0
			ORDER BY ForSend.SendDate; --ForSend.SendDate  <= DATEADD(MINUTE,330,GETUTCDATE()) AND
		END
		/******* END OF FOR ACHIVERS *********/
		/****** START OF FOR ALL USERS *********/
		ELSE
		BEGIN
	 		SELECT TOP 1 @SNo = ForSend.Sno, @RecCode = ForSend.AppMstID, @CurrLevel = ForSend.uLevel, @BalAmt = (COALESCE(ForSend.Amount,0) - COALESCE(ForSend.Paid,0))  FROM ForSend  INNER JOIN AppMast ON  ForSend.AppMstID = AppMast.AppMstID   WHERE ForSend.SendDate  <= DATEADD(MINUTE,330,GETUTCDATE()) and ForSend.Tag = 0 AND (COALESCE(ForSend.Amount,0) - COALESCE(ForSend.Paid,0)) > 0 AND AppMast.AppMstActivate =  1 AND (@CurrCode <> ForSend.AppMstID) ORDER BY ForSend.SNO;--AND (COALESCE(ForSend.Amount,0) - COALESCE(ForSend.Paid,0))% 500 = 0
		END
		/****** END OF FOR ALL USERS *********/
		IF COALESCE(@RecCode ,0) > 0 AND @SendBal > 0 AND @BalAmt > 0
		BEGIN		
			SET @GiveAmt = 0	
			SET @GiveAmt = CASE WHEN @BalAmt <= @SendBal THEN @BalAmt ELSE @SendBal END;						
			Select @MaxSNo = MAX(COALESCE(SNo,0)) + 1 FROM RequestMast;
			
			INSERT INTO RequestMast (SNo,AppSNo, AppMstID,RecID,FromTime,ToTime,ApproveTag,uLevel,Amount, SendSNo )VALUES(@MaxSNo,@RSNo , @CurrCode,@RecCode, DATEADD(MINUTE,330,GETUTCDATE()),DATEADD(MINUTE,4650,GETUTCDATE()),0,@CurrLevel, @GiveAmt, @SNo);
			 					
			UPDATE ForRec SET paid  = COALESCE(paid,0) + @GiveAmt  WHERE AppMstID = @CurrCode  AND Tag = 0 AND SNo = @RSNo;					 			
			UPDATE ForRec SET Tag = 1 WHERE Amount = Paid AND SNo = @RSNo;
			
			UPDATE ForSend SET Paid = COALESCE(Paid,0) + @GiveAmt WHERE AppMstID = @RecCode  AND SNo = @SNo AND Tag = 0;
			UPDATE ForSend SET Tag = 1 WHERE Amount  = Paid AND SNo = @SNo;  
			SET @SendBal = @SendBal - @GiveAmt;
			IF @SendBal <= 0
			BEGIN
				BREAK;
			END
		END
		ELSE
		BEGIN		
			BREAK;
		END		
	END
	SET @RowNo = @RowNo + 1
END

quote:

---

Originally posted by ravininave

Okay James K, I will post sample data and DDLs. Meanwhile tell me what should we do if there are multiple users. This procedure executes multiple times whenever anyone puts the request for withdrawal.

---