Author |
Topic |
ravininave
Posting Yak Master
111 Posts |
Posted - 2013-07-09 : 14:49:54
|
In my app, I receive payment withdrawal request in ForRec Table and I've the list of Vendors who will give them a Payment. They are in ForSend Table.ForRec Table Structure: (Whose request I receive for Withdrawal)SNo,CommNo,AppMstID,RecDate,Tag,Amount,PaidForSend Table Structure :(Who will Pay the Amount)SNo, AppMstID,SendDate,Amount,Paid,TagNow, when I got request I runs a procedure which finds record from ForRec Table suppose I find AppMstID = 20 (applicant no.) which has to withdraw Rs. 50000 then It goes to ForSend Table and find the data from top and issue the payment.Suppose in ForSend Table AppMstID 1 has assign Rs. 35000 and AppMstID 2 has assign Rs. 40000 to issue then in this case we have to send request of 35000 to ID 1 and Rs. 15000 to ID 2.for this I'd written a procedure like :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 INTSET @RowNo = 1;DECLARE @DateTable AS TABLE (RowNo INT IDENTITY(1,1),mSNo INT,AppCode INT, SendBal INT, AchiverTag INT)INSERT INTO @DateTableSELECT 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 @DateTableWHILE @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 + 1END Sometimes, this works fine but sometimes it issues the amount twice or thrice.Suppose ID 1 has a limit of Rs. 35,000 to issue then it issues 70,000 from his own a/cI'm not able to find any mistake in above code.Can anyone suggest me the problem.?VB6/ASP.NET------------------------http://www.nehasoftec.com |
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-07-09 : 16:37:17
|
Not having the ability to run and look at your code and with the code being somewhat lengthy, it is hard for me to figure out why you are getting duplicates. The only thing I can suggest is to put print statements in your code to see under what conditions it repeats loops that it should not. If this is being used by multiple users simultaneously, that also can cause the type of issues that you are seeing.If you can post sample data and table DDLs in a consumable format (i.e., something that a person can copy and paste to their SSMS and run), you might get more concrete and more prompt responses. See here if you need help in generating DDL's and sample data: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx |
|
|
ravininave
Posting Yak Master
111 Posts |
Posted - 2013-07-09 : 16:58:25
|
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. |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-07-09 : 17:02:15
|
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.
Does the procedure execute multiple times, or does the procedure execute once, but the amount deducted is incorrect?If the procedure is getting executed multiple times, you have to looka the calling program to see if they are doing something incorrect that causes the multiple invocations. |
|
|
ravininave
Posting Yak Master
111 Posts |
Posted - 2013-07-09 : 17:13:12
|
Employees who will Send Amount:CREATE TABLE [dbo].[ForSend]( [SNo] [int] NULL, [AppMstID] [int] NULL, [SendDate] [datetime] NULL, [Amount] [int] NULL, [Paid] [int] NULL, [Tag] [int] NULL, [uLevel] [int] NULL, [CancTag] [int] NULL, [BlockTag] [int] NULL, [CommNo] [int] NULL) ON [PRIMARY]Insert into ForSend (SNo , AppMstID , SendDate , Amount , Paid,Tag) values (1,45,'2013-06-08 20:52:24.793',2000,0,0)Insert into ForSend (SNo , AppMstID , SendDate , Amount , Paid,Tag) values (2,46,'2013-06-08 20:52:24.793',10000,0,0)Insert into ForSend (SNo , AppMstID , SendDate , Amount , Paid,Tag) values (3,47,'2013-06-08 20:52:24.793',3000,0,0)--Request For Withdrawal amoountCREATE TABLE [dbo].[ForRec]( [SNo] [int] NULL, [CommNo] [int] NULL, [AppMstID] [int] NULL, [RecDate] [datetime] NULL, [Tag] [int] NULL, [Amount] [int] NULL, [Paid] [int] NULL, [Qty] [int] NULL, [uLevel] [int] NULL, [IsAchiverTag] [int] NULL) ON [PRIMARY]Insert into ForRec (SNo , AppMstID , RecDate ,Tag, Amount , Paid) values (1,20,'2013-06-08 20:52:24.793',0,6000,0)Insert into ForRec (SNo , AppMstID , RecDate ,Tag, Amount , Paid) values (1,88,'2013-06-08 20:52:24.793',0,8000,0)--Table where I've to put all the Result.CREATE TABLE [dbo].[RequestMast]( [SNo] [int] NULL, [CommNo] [int] NULL, [AppSNo] [int] NULL, [AppMstID] [int] NULL, [RecID] [int] NULL, [FromTime] [datetime] NULL, [ToTime] [datetime] NULL, [Amount] [int] NULL, [ApproveTag] [int] NULL, [AcceptDate] [datetime] NULL, [uLevel] [int] NULL, [TimeExtend] [int] NULL, [BlockTag] [int] NULL, [SendSNo] [int] NULL, [RejectDate] [datetime] NULL) ON [PRIMARY]So, In this case ID No. 20 had sent a request to withdraw 6000 so system will withdraw 2000 from ID 45 and remaining 4000 from id 46.ID No. 88 had send a request of 8000 so system will withdraw remining 6000 from id 46 and 2000 from id 47.It will put data in Requestmast table. |
|
|
ravininave
Posting Yak Master
111 Posts |
Posted - 2013-07-09 : 17:14:39
|
Whenever, there is the withdrawal request, this procedure executes. Sometimes multiple persons executes the with request. Sometimes, it generates perfectly but sometimes it generates twice or thrice. |
|
|
ravininave
Posting Yak Master
111 Posts |
Posted - 2013-07-09 : 18:22:18
|
Hey one more thing, whenever I run the procedure it took so much time and then I came to know that for some reason I've to add Index and after that my proc is executing fastly. So, can this be the reason to execute it twice or thrice. |
|
|
|
|
|