SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Help with complicated running balance


Help with complicated running balance

Author
Message
gcresse
gcresse
SSC-Enthusiastic
SSC-Enthusiastic (172 reputation)SSC-Enthusiastic (172 reputation)SSC-Enthusiastic (172 reputation)SSC-Enthusiastic (172 reputation)SSC-Enthusiastic (172 reputation)SSC-Enthusiastic (172 reputation)SSC-Enthusiastic (172 reputation)SSC-Enthusiastic (172 reputation)

Group: General Forum Members
Points: 172 Visits: 430
I have a challenge that I was hoping to solve using the new SQL Server 2012 running balance capabilities, but it's a bit more complicated than I realized. I've attached a small spreadsheet with some sample data.

Basically these are inventory transactions for multiple LotIDs that are all part of a single PudID group and I need to be able to calculate the running balance for the PudID, which is the PudIDAsOfBal column in the spreadsheet. I've included some comments on how that number is calculated. I added the "LotIDBalSeq" column thinking I could somehow sum up the last PudIDLotBal for each LotID as of the TranDate.

The following code will return the results I have to work with. What I need is one more column with the PudIDAsOfBal:

SELECT PudID,
TranDate,
TranQty,
RunBal,
PudIDPct,
LotID,
PudIDLotBal,
LotIDBalSeq,
'help?' PudIDAsOfBal
FROM
( SELECT 751 PudID,'2011-09-19 18:21:09.000' TranDate, 64298 TranQty, 64298 RunBal, 56.477600 PudIDPct, '42011025' LotID, 36314 PudIDLotBal, 1 LotIDBalSeq
UNION SELECT 751,'2011-09-20 03:20:08.000', -45938, 64298, 56.477600, '42011025', 36314, 2
UNION SELECT 751,'2011-09-20 03:20:08.000', 45938, 64298, 56.477600, '42011025', 36314, 3
UNION SELECT 751,'2011-09-20 03:21:08.000', -18360, 60346, 56.483300, '42011025', 25947, 4
UNION SELECT 751,'2011-09-20 03:21:08.000', 14408, 60346, 56.477600, '42011025A',8137, 1
UNION SELECT 751,'2011-09-20 18:16:49.000', 27122, 87468, 100.00000, '42011027', 27122, 1
UNION SELECT 751,'2011-09-20 19:51:29.000', 35454, 122922, 19.805900, '42011026', 7021, 1
UNION SELECT 751,'2011-09-20 21:30:22.000', 12757, 121271, 10.055800, '42011022A',1282, 1
UNION SELECT 751,'2011-09-20 21:30:22.000', -14408, 121271, 56.477600, '42011025A',0, 2
UNION SELECT 751,'2011-09-21 02:09:25.000', 23515, 121271, 19.805900, '42011026', 7021, 2
UNION SELECT 751,'2011-09-21 02:09:25.000', -23515, 121271, 19.805900, '42011026', 7021, 3
UNION SELECT 751,'2011-09-21 02:11:00.000', -11939, 121271, 19.800000, '42011026', 4655, 4
UNION SELECT 751,'2011-09-21 02:11:00.000', 11939, 121271, 19.805900, '42011026A',2364, 1
) WORK



I thought I could use some variation of SUM(PudIDLotBal) OVER (PARTITION... but having the multiple lots and needing the last balance for each lot as of the TranDate is over my head. Any help woud be greatly appreciated.

Thanks,
Gina
Attachments
RunBal.xlsx (39 views, 11.00 KB)
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)

Group: General Forum Members
Points: 88090 Visits: 41128
gcresse (4/15/2013)
I've included some comments on how that number is calculated.


Where? There's a whole lot of attachments.

--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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
gcresse
gcresse
SSC-Enthusiastic
SSC-Enthusiastic (172 reputation)SSC-Enthusiastic (172 reputation)SSC-Enthusiastic (172 reputation)SSC-Enthusiastic (172 reputation)SSC-Enthusiastic (172 reputation)SSC-Enthusiastic (172 reputation)SSC-Enthusiastic (172 reputation)SSC-Enthusiastic (172 reputation)

Group: General Forum Members
Points: 172 Visits: 430
Sorry about that. I must've grabbed the wrong file. I've re-uploaded the correct attachment.
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)

Group: General Forum Members
Points: 88090 Visits: 41128
I'm thinking that we should simplify the requirements a bit. From what I see, it boils down to two things.

1. Do a normal running total first.
2. Update any tied date/times of the first result set with the MAX value from that date/time group.

You might be able to do Step 2 in the same code as Step 1 if you preaggregate the data so that you can create a "single previous row" for each datetime group and then join the result set back to the original data. I've not done a deep dive on this but it looks like you'll need two passes on the table one way or another. I've also not worked with the new capabilites of the Windowing Functions in 2012 so I could certainly be wrong there.

--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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
gcresse
gcresse
SSC-Enthusiastic
SSC-Enthusiastic (172 reputation)SSC-Enthusiastic (172 reputation)SSC-Enthusiastic (172 reputation)SSC-Enthusiastic (172 reputation)SSC-Enthusiastic (172 reputation)SSC-Enthusiastic (172 reputation)SSC-Enthusiastic (172 reputation)SSC-Enthusiastic (172 reputation)

Group: General Forum Members
Points: 172 Visits: 430
OK, I'm trying plan b but the running balance is not correct. It seems to ignore my request for only the highest seq for each lot. I must be missing something obvious, but I just can't see it. Any ideas?


SELECT AsOfPudID,
AsOfTranDate,
AsOfLotKey,
AsOfLotBal,
AsOfLotBalSeq,

(SELECT SUM(B.AsOfLotBal)
FROM testAsOfProdGalsByLot B
WHERE B.AsOfPudID = A.AsOfPudID AND
B.AsOfTranDate <= A.AsOfTranDate AND
B.AsOfLotBalSeq = ( SELECT TOP 1 C.AsOfLotBalSeq
FROM testAsOfProdGalsByLot C
WHERE C.AsOfPudID = B.AsOfPudID AND
C.AsOfTranDate <= B.AsOfTranDate AND
C.AsOfLotKey = B.AsOfLotKey
ORDER BY AsOfLotBalSeq DESC))
FROM

( SELECT PudID AsOfPudID,
TranDate AsOfTranDate,
LotID AsOfLotKey,
PudIDLotBal AsOfLotBal,
LotIDBalSeq AsOfLotBalSeq
FROM
( SELECT 751 PudID,'2011-09-19 18:21:09.000' TranDate, 64298 TranQty, 64298 RunBal, 56.477600 PudIDPct, '42011025' LotID, 36314 PudIDLotBal, 1 LotIDBalSeq
UNION SELECT 751,'2011-09-20 03:20:08.000', -45938, 64298, 56.477600, '42011025', 36314, 2
UNION SELECT 751,'2011-09-20 03:20:08.000', 45938, 64298, 56.477600, '42011025', 36314, 3
UNION SELECT 751,'2011-09-20 03:21:08.000', -18360, 60346, 56.483300, '42011025', 25947, 4
UNION SELECT 751,'2011-09-20 03:21:08.000', 14408, 60346, 56.477600, '42011025A',8137, 1
UNION SELECT 751,'2011-09-20 18:16:49.000', 27122, 87468, 100.00000, '42011027', 27122, 1
UNION SELECT 751,'2011-09-20 19:51:29.000', 35454, 122922, 19.805900, '42011026', 7021, 1
UNION SELECT 751,'2011-09-20 21:30:22.000', 12757, 121271, 10.055800, '42011022A',1282, 1
UNION SELECT 751,'2011-09-20 21:30:22.000', -14408, 121271, 56.477600, '42011025A',0, 2
UNION SELECT 751,'2011-09-21 02:09:25.000', 23515, 121271, 19.805900, '42011026', 7021, 2
UNION SELECT 751,'2011-09-21 02:09:25.000', -23515, 121271, 19.805900, '42011026', 7021, 3
UNION SELECT 751,'2011-09-21 02:11:00.000', -11939, 121271, 19.800000, '42011026', 4655, 4
UNION SELECT 751,'2011-09-21 02:11:00.000', 11939, 121271, 19.805900, '42011026A',2364, 1
) WORK
) A



UPDATE: I found my issue. This code seems to work for me now:

SELECT AsOfPudID,
AsOfTranDate,
AsOfLotKey,
AsOfLotBal,
AsOfLotBalSeq,

(SELECT SUM(B.AsOfLotBal)
FROM testAsOfProdGalsByLot B
WHERE B.AsOfPudID = A.AsOfPudID AND
B.AsOfTranDate <= A.AsOfTranDate AND
B.AsOfLotBalSeq = ( SELECT TOP 1 C.AsOfLotBalSeq
FROM testAsOfProdGalsByLot C
WHERE C.AsOfPudID = A.AsOfPudID AND
C.AsOfTranDate <= A.AsOfTranDate AND
C.AsOfLotKey = B.AsOfLotKey
ORDER BY AsOfLotBalSeq DESC))
FROM

( SELECT PudID AsOfPudID,
TranDate AsOfTranDate,
LotID AsOfLotKey,
PudIDLotBal AsOfLotBal,
LotIDBalSeq AsOfLotBalSeq
FROM
( SELECT 751 PudID,'2011-09-19 18:21:09.000' TranDate, 64298 TranQty, 64298 RunBal, 56.477600 PudIDPct, '42011025' LotID, 36314 PudIDLotBal, 1 LotIDBalSeq
UNION SELECT 751,'2011-09-20 03:20:08.000', -45938, 64298, 56.477600, '42011025', 36314, 2
UNION SELECT 751,'2011-09-20 03:20:08.000', 45938, 64298, 56.477600, '42011025', 36314, 3
UNION SELECT 751,'2011-09-20 03:21:08.000', -18360, 60346, 56.483300, '42011025', 25947, 4
UNION SELECT 751,'2011-09-20 03:21:08.000', 14408, 60346, 56.477600, '42011025A',8137, 1
UNION SELECT 751,'2011-09-20 18:16:49.000', 27122, 87468, 100.00000, '42011027', 27122, 1
UNION SELECT 751,'2011-09-20 19:51:29.000', 35454, 122922, 19.805900, '42011026', 7021, 1
UNION SELECT 751,'2011-09-20 21:30:22.000', 12757, 121271, 10.055800, '42011022A',1282, 1
UNION SELECT 751,'2011-09-20 21:30:22.000', -14408, 121271, 56.477600, '42011025A',0, 2
UNION SELECT 751,'2011-09-21 02:09:25.000', 23515, 121271, 19.805900, '42011026', 7021, 2
UNION SELECT 751,'2011-09-21 02:09:25.000', -23515, 121271, 19.805900, '42011026', 7021, 3
UNION SELECT 751,'2011-09-21 02:11:00.000', -11939, 121271, 19.800000, '42011026', 4655, 4
UNION SELECT 751,'2011-09-21 02:11:00.000', 11939, 121271, 19.805900, '42011026A',2364, 1
) WORK
) A



gcresse
gcresse
SSC-Enthusiastic
SSC-Enthusiastic (172 reputation)SSC-Enthusiastic (172 reputation)SSC-Enthusiastic (172 reputation)SSC-Enthusiastic (172 reputation)SSC-Enthusiastic (172 reputation)SSC-Enthusiastic (172 reputation)SSC-Enthusiastic (172 reputation)SSC-Enthusiastic (172 reputation)

Group: General Forum Members
Points: 172 Visits: 430
The Plan B query is excrutiatingly slow Sad

Any suggestions on how I can get the same results but with better performance?
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)

Group: General Forum Members
Points: 88090 Visits: 41128
I thought you said you were using 2012 functionality. It now appears that you've used a Triangular Join ( See the following link for why they are so slow http://www.sqlservercentral.com/articles/T-SQL/61539/ ).

Like I suggested, first do a "normal" running total using the 2012 functionality and then mark the dupes with the max.

--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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
gcresse
gcresse
SSC-Enthusiastic
SSC-Enthusiastic (172 reputation)SSC-Enthusiastic (172 reputation)SSC-Enthusiastic (172 reputation)SSC-Enthusiastic (172 reputation)SSC-Enthusiastic (172 reputation)SSC-Enthusiastic (172 reputation)SSC-Enthusiastic (172 reputation)SSC-Enthusiastic (172 reputation)

Group: General Forum Members
Points: 172 Visits: 430
Thanks Jeff. Yes, I want to use the 2012 functionality, but I guess I don't quite understand what you're suggesting. I can create a running total on the balances (which will be wrong) using SUM OVER PARTITION... but after that, I'm not sure what you mean by marking the duplicates.
J Livingston SQL
J Livingston SQL
SSCertifiable
SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)

Group: General Forum Members
Points: 5529 Visits: 35459

SELECT PudID,
TranDate,
TranQty,
RunBal,
PudIDPct,
LotID,
PudIDLotBal,
LotIDBalSeq,
'help?' PudIDAsOfBal





may be I am being particulalary thick this morning but I cant seem to follow your spreadsheet for "Runbal" and "PudIDLotBal"....are these columns in your database table that are already populated by the application or are they columns that you created by some previous query?




PudID TranDate TranQty RunBal
751 9/19/11 18:21 64298 64298
751 9/20/11 3:20 -45938 64298
751 9/20/11 3:20 45938 64298
751 9/20/11 3:21 -18360 60346
751 9/20/11 3:21 14408 60346
751 9/20/11 18:16 27122 87468
751 9/20/11 19:51 35454 122922
751 9/20/11 21:30 12757 121271
751 9/20/11 21:30 -14408 121271
751 9/21/11 2:09 23515 121271
751 9/21/11 2:09 -23515 121271
751 9/21/11 2:11 -11939 121271
751 9/21/11 2:11 11939 121271






PudID TranDate TranQty JLS runtot
751 9/19/11 18:21 64298
751 9/20/11 3:20 -45938 18360
751 9/20/11 3:20 45938 64298
751 9/20/11 3:21 -18360 45938
751 9/20/11 3:21 14408 60346
751 9/20/11 18:16 27122 87468
751 9/20/11 19:51 35454 122922
751 9/20/11 21:30 12757 135679
751 9/20/11 21:30 -14408 121271
751 9/21/11 2:09 23515 144786
751 9/21/11 2:09 -23515 121271
751 9/21/11 2:11 -11939 109332
751 9/21/11 2:11 11939 121271



________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

gcresse
gcresse
SSC-Enthusiastic
SSC-Enthusiastic (172 reputation)SSC-Enthusiastic (172 reputation)SSC-Enthusiastic (172 reputation)SSC-Enthusiastic (172 reputation)SSC-Enthusiastic (172 reputation)SSC-Enthusiastic (172 reputation)SSC-Enthusiastic (172 reputation)SSC-Enthusiastic (172 reputation)

Group: General Forum Members
Points: 172 Visits: 430
The results I've included in this post are from a query. I've just tried to simplify the issue since I'm really only interested in getting the correct running balance for the PudIDLotBal. Let me see if I can clarify this a bit.

These transactions are for wine lots (LotID). We purchase grapes from various growers using purchase orders (PudID) then we crush and process the grapes on inventory transactions (TranDate, TranQty). The resulting wine gets blended into multiple lots, and wine from multiple purchase orders can be included in a single lot. What I'm trying to do is determine how many gallons of wine were produced from each purchase order as of any transaction date.

In the spreadsheet, the RunBal is simply a running balance of the total transaction qty, and is really of no use for what I'm trying to do. I should have left it out of the spreadsheet to avoid confusion. Sorry about that. The PudIDPct is the portion of the resulting lot balance that was sourced from the purchase order (PudID) and that is included simply for auditing purposes. The PudIDLotBal is the amount of wine for that Lot as of that transaction date/time that was sourced from the purchase order and that's the column that is important to me. As more and more lots of wine that have portions sourced from the purchase order enter the picture, I need to keep track of how many gallons those grapes from that purchase order have produced. Does that make sense?

As I drifted off to sleep last night, I had the idea that perhaps I should try to pivot the results so each LotID becomes a column. I haven't tried it yet, but that will probably be my next experiment, unless someone has a brilliant idea for me to try.

Thanks,
Gina
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search