Creating a running difference - sort of

Question

Creating a running difference - sort of

Lori Fortner

SSC Enthusiast

Points: 150
More actions
August 7, 2008 at 3:03 pm

#189055

[font="Arial"]I have five columns in my table:
Statement_num
Fees_Due
Initials
Fees_Billed
Credit_Amt
Representative data would look like this:
Statement_num Fees_Due Initials Fees_Billed Credit_Amt
1007 118.40 ADL 240.00 0.00
1009 7634.67 GTC 3761.74 0.00
1009 7634.67 BCB 10769.92 0.00
1009 7634.67 JNR 88.34 0.00
I have to breakdown the fees_due amount among the people (Initials) listed for each statement number. In this example, I have two statements, 1007 and 1009. The breakdown for 1007 is easy - there's only one person, he gets credit for the entire 118.40 due.
For statement 1009, there are 3 people listed and this is where I am having trouble. In this example, GTC gets credited with the full amount he billed, 3761.74 (they never receive credit for more than they billed). Subtract that credited amount of 3761.74 from the fees_due on the statement, 7634.67, and that leaves 3,872.93 left still to distribute to the remaining two people, BCB and JNR.
The entire remaining amount of fees_due, 3,872.93, will go to BCB since he billed way in excess of that amount, 10769.92. That now exhausts the amount of fees due so the remaining person, JNR gets 0.
So what this table should utlimately look like is this:
Statement_num Fees_Due Initials Fees_Billed Credit_Amt
1007 118.40 ADL 240.00 118.40
1009 7634.67 GTC 3761.74 3761.74
1009 7634.67 BCB 10769.92 3872.93
1009 7634.67 JNR 88.34 0.00
This is way above my sql prowess. Any help or guidance on how to approach this would be greatly appreciated.
[/font]

Viewing 9 posts - 1 through 8 (of 8 total)

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

Buzz-523717 SSC-Addicted Points: 494 More actions · Answer 1

Why wouldn't JNR receive 88.34, GTC receive 3761.74 and BCB receive 3864.59? Or, why wouldn't BCB receive 7634.67 and JNR and GTC receive nothing?

How are you determining which set of initials is credited first?

Lori Fortner SSC Enthusiast Points: 150 More actions · Answer 2

The people for each statement have to be evaluated in the order in which they appear in the table. The table's origin is a text file that listed each person according to their rank within the company and that order was kept when the data was input into the table.

Buzz-523717 SSC-Addicted Points: 494 More actions · Answer 3

-- SET UP A TABLE VARIABLE WITH ROW ID FOR ORDERING

DECLARE@Sales as Table(

RowID int IDENTITY(1,1),

Statement_num int,

Fees_Due smallmoney,

Initials char(3),

Fees_Billed smallmoney)

-- INSERT SOME TEST DATA

-- IN THE SAME ORDER IT WOULD COME IN

-- IN THE TEXT FILE

INSERT INTO @Sales(

Statement_num,

Fees_Due,

Initials,

Fees_Billed)

SELECT1007, 118.40, 'ADL', 240.00

UNION ALL

SELECT1009, 7634.67, 'GTC', 3761.74

UNION ALL

SELECT1009, 7634.67, 'BCB', 10769.92

UNION ALL

SELECT1009, 7634.67, 'JNR', 88.34

-- PULL OUT THE DATA

SELECTRowID,

Statement_num,

Fees_Due,

Initials,

Fees_Billed,

CASE WHEN

ISNULL((SELECT SUM(CASEWHEN Fees_Due <= Fees_Billed THEN Fees_Due

ELSE Fees_Billed END)

FROM@Sales

WHEREStatement_Num = s.Statement_Num

ANDRowID < s.RowID),0) < Fees_Due

THEN CASE WHEN Fees_Billed > Fees_Due

THEN Fees_Due -

ISNULL((SELECTSUM(CASEWHEN Fees_Due <= Fees_Billed THEN Fees_Due ELSE Fees_Billed END)

FROM@Sales

WHEREStatement_Num = s.Statement_Num

ANDRowID < s.RowID),0)

ELSE Fees_Billed -

ISNULL((SELECTSUM(CASEWHEN Fees_Due <= Fees_Billed THEN Fees_Due ELSE Fees_Billed END)

FROM@Sales

WHEREStatement_Num = s.Statement_Num

ANDRowID < s.RowID),0)

END

ELSE

0

END

FROM@Sales s

ORDER BY s.RowID

Jeff Moden SSC Guru Points: 1004434 More actions · Answer 4

Lori Fortner (8/7/2008)
Statement_num Fees_Due Initials Fees_Billed Credit_Amt
1007 118.40 ADL 240.00 0.00
1009 7634.67 GTC 3761.74 0.00
1009 7634.67 BCB 10769.92 0.00
1009 7634.67 JNR 88.34 0.00

There's nothing in your sample data to indicate what the order of the rows is based on. Is there a date column or and IDENTITY column associated with the rows above?

Also, beware running to solutions that have inequalities in the solution. See the following link for why...

http://www.sqlservercentral.com/articles/T-SQL/61539/

--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)

Buzz-523717 SSC-Addicted Points: 494 More actions · Answer 5

Thanks Jeff, good article. My solution definitely fits into that category and would probably only work for a very limited number of rows...not a good solution.

With that said, and without going row by agonizing row...

Do you have handy any links to alternative solutions whereby a running aggregate like this could be accomplished?

Jeff Moden SSC Guru Points: 1004434 More actions · Answer 6

Buzz (8/8/2008)
Thanks Jeff, good article. My solution definitely fits into that category and would probably only work for a very limited number of rows...not a good solution.
With that said, and without going row by agonizing row...
Do you have handy any links to alternative solutions whereby a running aggregate like this could be accomplished?

Hi Buzz,

Man, I hope you didn't think I was slamming on you... I just saw the inequalities in the relationships in the code and immediately thought "triangular join".

Yes... I do have a high speed alternate... please see the following...

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)

Buzz-523717 SSC-Addicted Points: 494 More actions · Answer 7

Not at all, I come here primarily to learn, and to try to help others out if I can. Thank you for the link. Again, another good article with a wealth of helpful information I will use. I have some reworking to do on some of my existing code based on what I've learned.

Thanks again, Jeff!

P.S. I also reviewed some of your other articles now. It took me a while to grasp the concept of the Tally table but I think I have it now. That too will prove very helpful to me. Thank you.

Jeff Moden SSC Guru Points: 1004434 More actions · Answer 8

Awesome. Thanks for the feedback, Buzz.

--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)