Balance calculate getting some strange numbers middle

Question

0.00/5 (No votes)

See more:

I have this code for balance calculate but it is not working well beacouse sometimes it is plusing itself somtimes the next value for example i will show you code and output plz help me solving this
Output:

ID       Date       Operation Amount Balance
2979    07-03-2018  Cash Out  6.9   -44.88
2989    09-03-2018  Cash In   19    -25.88
2980    09-03-2018  Cash In   8.09  -17.79
2996    09-03-2018  Cash Out  98.57 -25.88
2986    09-03-2018  Cash Out  1.3   -27.18
2988    09-03-2018  Cash Out  4     -31.18
2981    09-03-2018  Cash Out  8.09  -129.75

Here what i see when balance is -44.88 it is plusing 19 and getting 25.88 this is normal then -25.88 plusing 8.09 and getting -17.79 this is true buut then it must minus 98.57 but instead of that it is minus 8.09 the prev one can you help me out?

What I have tried:

SQL

DECLARE @Datest DATE
declare @dateend DATE   
SET @Datest='2018-02-1 00:00:00'
SET @dateend='2018-12-14 06:54:42'

DECLARE @DateStart DATE='2017-01-01 00:00:00'; 

WITH cte 
     AS (SELECT  id = Row_number() OVER(ORDER BY date,(CASE WHEN Op_Type_Id=1 OR Op_Type_Id=3 OR Op_Type_Id=5  THEN 1 ELSE 0 END)DESC),
     x.ID AS iid,
                x.Date AS [Date],dbo.Op_type.Type as Operation,Op_Type_Id,
                dbo.Source.Source as Source,j.Source AS Recipient,
                dbo.Expence_Items.Name AS Purpose,dbo.Expence_Items.Name_En AS Purpose_EN,
                dbo.Expence_Items.Name_Rus AS Purpose_Ru,dbo.Executor.Name AS Executor, 
                x.Value as AmountGel,
 x.Note 
         FROM   [dbo].[Reestr] x LEFT JOIN dbo.Source AS j ON x.Sender_Id2=j.ID 
         LEFT JOIN dbo.Expence_Items ON x.Expence_Id = dbo.Expence_Items.ID 
         LEFT OUTER JOIN dbo.Source ON x.Sender_Id = dbo.Source.ID 
         LEFT JOIN  dbo.Executor ON x.Executor_Id = dbo.Executor.ID 
         LEFT OUTER JOIN dbo.Op_type ON x.Op_Type_Id = dbo.Op_type.ID 

         WHERE  DATE >= @DateStart) 
SELECT  
    id,FORMAT([Date],'dd-MM-yyyy') AS [Date],Operation,Source,Amount,
       Balance= (select(sum(case when Op_Type_Id IN (1,3,5)
            then CAST(Amount AS NUMERIC(6,2)) when Op_Type_Id IN(2,4,6) then(0-CAST(Amount AS NUMERIC(6,2))) END))
                    FROM cte
                       WHERE  id < a.id or id=a.id ),Note
FROM   cte AS a WHERE ([Date]>=@Datest AND [Date]<=@dateend)
ORDER  BY FORMAT([Date],'yyyy-MM-dd')

Posted 13-Mar-18 22:53pm

GTR0123

Updated 15-Mar-18 1:25am

Patrice T

v3

Add a Solution

Comments

CHill60 14-Mar-18 4:57am

"WHERE id < a.id or id=a.id ),Note" - that produces a syntax error. What is the actual code?

GTR0123 14-Mar-18 5:08am

you can make WHERE id < a.id ) a.id is with cte id

CHill60 14-Mar-18 5:31am

Apologies I missed the sub-query.

2 solutions

Solution 1

Quote:
Balance calculate getting some strange numbers middle

I am not an SQL expert but it smell like a reordering between the part that compute the balance and the part that print it.

What happen when you use the exact same ordering everywhare?

Posted 14-Mar-18 7:20am

Patrice T

Comments

GTR0123 15-Mar-18 7:09am

i have made it i have changed the Code used some RunningTotall but using the temp table soo it is working now corectly thanx guys

Patrice T 15-Mar-18 7:17am

If problem solved and the solution was helpful, you can accept the solution, it tells everyone that the search is over.
Or if you prefer, trow your solution with a little explanation and accept it.

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

GTR0123 · Accepted Answer · 2018-03-15T01:26:00

SELECT  x.Value ,SUM((case when Op_Type_Id IN(1,3,5)
			then Value when Op_Type_Id IN (2,4,6) then(0-Value) END)) OVER(ORDER BY x.[Date]
     ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) 
          AS Balance
         FROM   [dbo].[aaa] x LEFT JOIN  
		 LEFT OUTER JOIN dbo.Op_type ON x.Op_Type_Id = dbo.Op_type.ID 
		  WHERE  (x.[date]>'(start date)for example:'2016-01-01 00:00:00'' )
 ORDER BY x.[Date]

i created tmp table with same columns value balance and date if you want you can have id too after i used this code for

insert

and after that i run select form TMP table and used

when [Date2]>=@Datest AND [Date2]<=@dateend ORDER BY [Date2]

date2 is TMP table date this code works for me fine