Computation for running Difference

Question

Post reply

Computation for running Difference

clint_pow

Say Hey Kid

Points: 690
More actions
October 7, 2008 at 1:19 am

#194451

Hi To All,
I would like to ask for assistance regarding my query, I dont know how to pass on the recent difference result, sorry I dont know how to generate a script for the data. below is the information :
SELECT
T0.DocEntry,
T0.DocDate,
T0.CardName,
T0.DocTotal,
SUM(T1.LineTotal) AS LineTotal,
T2.BaseAbs,
T2.DrawnSum,
T3.DocTotal,
T3.DocTotal - T2.DrawnSum AS Bal
FROM
dbo.OPCH T0
INNER JOIN dbo.PCH1 T1 ON (T0.DocEntry = T1.DocEntry)
INNER JOIN dbo.PCH9 T2 ON (T1.DocEntry = T2.DocEntry)
INNER JOIN dbo.ODPO T3 ON (T2.BaseAbs = T3.DocEntry)
WHERE
T0.DocEntry = 1347 OR
T0.DocEntry = 1354
GROUP BY
T0.DocEntry,
T0.DocDate,
T0.CardName,
T0.DocTotal,
T2.BaseAbs,
T2.DrawnSum,
T3.DocTotal,
T3.DocEntry
The bold line is where must be the deduction happens, sample result is :
Total Deduction Current UptoDate
3000 1000 3000 2000
3000 1000 2000 1000
3000 1000 1000 0
Thank you very much,
Regards,
Clint

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply

Clive Strong SSChampion Points: 11222 More actions · Answer 1

What exactly are you asking?

The result set doesn't match up to the query, so I'm a little confused.

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 2

Clint,

This is a "running balance" or "running total" problem and there are lots of methods to pull it off including some really slow triangular joins, cursors, and While loops. To be sure, I wouldn't use any of those methods.

Instead, take a look at the following article... it shows a method for solving the problem of calculating running totals with some nasty fast performance. All you really have to decide is what the sort order of the data should be so you can make the proper clustered index to drive it. And, when I say fast, I mean to the tune of doing a running total on a million rows on a relatively slow 6 year old desktop computer in 7 seconds.

Take a look...

http://www.sqlservercentral.com/articles/Advanced+Querying/61716/

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

clint_pow Say Hey Kid Points: 690 More actions · Answer 3

Hi Clive,

Im very sorry for the explanation, I have focus on the Running balance or Difference result : Here is the query,

CREATE TABLE [Query_Result] (

[DocEntry] INT,

[DocDate] DATETIME,

[CardName] NVARCHAR(100),

[DocTotal] NUMERIC(15,2),

[LineTotal] NUMERIC(15,2),

[BaseAbs] INT,

[DrawnSum] NUMERIC(15,2),

[DocTotal_1] NUMERIC(15,2),

[Bal] NUMERIC(15,2)

)

GO

/* Data for the `Query_Result` table (Records 1 - 2) */

INSERT INTO [Query_Result] ([DocEntry], [DocDate], [CardName], [DocTotal], [LineTotal], [BaseAbs], [DrawnSum], [DocTotal_1], [Bal])

VALUES (1347, '20080103', N'SAADA PASIGAN', N'0', N'142118.74', 117, N'142118.74', N'200000', N'57881.26')

GO

INSERT INTO [Query_Result] ([DocEntry], [DocDate], [CardName], [DocTotal], [LineTotal], [BaseAbs], [DrawnSum], [DocTotal_1], [Bal])

VALUES (1354, '20080103', N'SAADA PASIGAN', N'28310.05', N'86191.31', 117, N'57881.26', N'200000', N'142118.74')

GO

From table Doctotal_1 is the main Value which is 200000, The DrawnSum will deduct the Doctotal_1. From the first row of the the value 200000 must deduct the value from DrawnSum which is 142118.74 the remaining balance of 200000 is now 57881.26.

If you could see the second row data, the value 57881.26 must be carry out to the value Balance of 57881.26. The recent query does not deduct the total value instead it still deduct the 200000.

Hope that you understand my explanation.

Thank you.

regards,

Clint

clint_pow Say Hey Kid Points: 690 More actions · Answer 4

Hi Jeff,

That is a running balance, I have posted a much clearer data for you to understand better.

Thank you in advance.

REgards,

Clint

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 5

Clint -

Jeff is pointing you to a high speed solution to your running totals problem. He's written a couple of articles on the topic. Follow the link in his last post to get yourself plenty of info on how to implement the solution.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

clint_pow Say Hey Kid Points: 690 More actions · Answer 6

Hi matt,

Im sorry for that and the purpose of Jeff and it is very well appreciated at all times. I apologize for being a newbies and not an expert like you guys!

Thank you very much!

Regards,

Clint

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 7

Heh... I recognize that, Clint. And, I'm not being evasive about code. I just recognize that you're both intelligent as well as being a bit new to SQL. I realize you're also a bit under the gun to get some of this stuff done, but the only way to become the expert I think you can be is to do things like read an article on the subject, figure out how it applies, and then write/test some code.

If you get really hammered by requirements and deadlines, I'll be happy to contrive a bit of code for you... I just want you to learn enough, in the process, so it'll never be a problem for you again. A little extra time up front on this first one will make your life a lot easier the next time it happens.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

clint_pow Say Hey Kid Points: 690 More actions · Answer 8

Hi Jeff,

thank you very much for the support!.

Regards,

Clint

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 9

It's been a couple of days, Clint, and I'm just following up... are you all set on this?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

clint_pow Say Hey Kid Points: 690 More actions · Answer 10

Hi Jeff,

I have already got the idea of running difference, I have read an article and its said that running total can be established if you inner join it from the same Table itself. I can already established a running total sample from a single table but when I base the query from my existing Query statement of multiple tables, i cant figure it out!

Thank you for the follow up, I thought I have lose some hopes.

Regards,

Clint

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 11

Hmmm... do you have a link to that article? I'd like to take a peek at what they're trying to do.

On your problem, I'm not sure what you mean. Your post with actual data (a couple of posts above this one) only showed a single table. See if you can simplify the problem a bit with some table creation statements and some sample date inserts along with a description of what you're trying to do, and I'll see if I can help out code wise.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

clint_pow Say Hey Kid Points: 690 More actions · Answer 12

Jeff,

This is the link dont remember if this is the one http://articles.techrepublic.com.com/5100-10878_11-6158593.html.

In my given script it only have one table since it is already the output or result of my query then I create the script. But if you see in my original query it involves four 4 tables :

SELECT

T0.DocEntry,

T0.DocDate,

T0.CardName,

T0.DocTotal,

SUM(T1.LineTotal) AS LineTotal,

T3.DocDate as CADate,

T2.BaseAbs,

T2.DrawnSum,

T3.DocTotal as TotalDP

FROM

OPCH T0

INNER JOIN PCH1 T1 ON (T0.DocEntry = T1.DocEntry)

INNER JOIN PCH9 T2 ON (T1.DocEntry = T2.DocEntry)

INNER JOIN ODPO T3 ON (T2.BaseAbs = T3.DocEntry)

GROUP BY

T0.DocEntry,

T0.DocDate,

T0.CardName,

T0.DocTotal,

T3.DocDate,

T2.BaseAbs,

T2.DrawnSum,

T3.DocTotal,

T3.DocEntry

I dont know how to create the three tables since the tables are from SAP Business One program, so what I did is, the output can only made a script. so thats why it have only one table.

Hope that is it cleared enough.

Thank you.

Clint

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 13

clint_pow (10/16/2008)
Jeff,
This is the link dont remember if this is the one http://articles.techrepublic.com.com/5100-10878_11-6158593.html.

I guess you didn't read the article I directed you to which says the method in the link you provided is probably the worst way you could ever do a running total or running difference.... and I mean absolutely the worst... it can be thousands of times slower than a cursor.

Take the time to read my article... you already have the link from above. In the meantime, I'll see if I can come up with an example for you.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

clint_pow Say Hey Kid Points: 690 More actions · Answer 14

Jeff,

If very much slow then dont bother it anymore Im just taking too much of your time. We cant make it very complicated, if there is any other way like creating a temporary table or what or Views thats what in mind but dont know how to establish it.

Thank you in advance Jeff.

Regards,

Clint