CODEWITHSUNDEEP
sqlsql-server
Ayman
Ayman

Reputation: 71

Query get wrong calculated values

hello I'm trying to get statement for certain customer

the query i use 

SELECT distinct t.S_Type as Type,t.Number,t.Date, t.Debit, t.Credit, t.CustID, b.Balance
FROM Statement as t CROSS apply
     (SELECT Balance = SUM(Debit) - SUM(Credit)
      FROM Statement as x
      WHERE x.Number<= t.Number AND x.CustID = t.CustID
     ) b
where t.CustID ='7003' and date between '8/21/2015' and '8/25/2015'
ORDER BY t.date

out put

type            Number  Date        Debit  CREDIT   cust_id  Balance
Sales Invoice   1     2015-08-23    400.00  0.00    7003    400.00
Sales Invoice   2     2015-08-23    1500.00 0.00    7003    1900.00
Receipt Voucher 3     2015-08-24    0.00    400.00  7003    1500.00

here you noticed Receipt Voucher number value is bigger than sales invoice value

the problem occur when i have Receipt Voucher number value is less than the Sales Invoice number values for example 1

the out put i get when Receipt Voucher is less than sales value

type            Number  Date        Debit  CREDIT   cust_id  Balance
Sales Invoice   1    2015-08-23  400.00    0.00     7003        0.00
Sales Invoice   2    2015-08-23  1500.00    0.00    7003        1500.00
Receipt Voucher 1    2015-08-24  0.00     400.00    7003       -400.00

which is not right the out put should be like the first out out what is the problem

another thing the query give the right result if i change x.Number<= t.Number to x.Number>= t.Number but it give wrong values when i have Receipt Voucher value is bigger than sales value it give wrong values also.

thanks in advance

Upvotes: 0

Views: 74

Answers (2)

eugenioy
eugenioy

Reputation: 12393

I believe the problem in the second case is that the number for the "Receipt Voucher" is less, but the date is greater.

Your query seems to assume that a lower "Statement.Number" show also have a lower "Statement.Date".

Shouldn't that be the case?

Try changing the date in the second example so that the "Receipt Voucher" has an earlier date as well.

Or else, order the results by "t.number" instead of date.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269933

Instead of doing the calculation on number, why not do it on date and number?

SELECT t.S_Type as Type, t.Number, t.Date, t.Debit, t.Credit, t.CustID, b.Balance
FROM Statement as t CROSS apply
     (SELECT Balance = SUM(Debit) - SUM(Credit)
      FROM Statement as x
      WHERE (x.date < t.date or
             x.date = t.date and x.Number <= t.Number
            ) AND x.CustID = t.CustID
     ) b
WHERE t.CustID = '7003' and date between '2015-08-21' and '2015-08-25'
ORDER BY t.date;

Or, better yet, just use cumulative sums (in SQL 2012+):

SELECT t.S_Type as Type, t.Number, t.Date, t.Debit, t.Credit, t.CustID, 
       SUM(t.debit - t.credit) OVER (PARTITION BY t.CustId ORDER BY t.date, t.Number) as Balance
FROM Statement as t
WHERE t.CustID = '7003' and date between '2015-08-21' and '2015-08-25'
ORDER BY t.date;

Upvotes: 1

Related Questions