Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

Help with complicated running balance Expand / Collapse
Author
Message
Posted Monday, April 15, 2013 7:46 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, February 25, 2014 12:56 PM
Points: 77, Visits: 392
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


  Post Attachments 
RunBal.xlsx (30 views, 11.33 KB)
Post #1442557
Posted Monday, April 15, 2013 9:22 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 2:50 PM
Points: 36,711, Visits: 31,159
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1442564
Posted Monday, April 15, 2013 11:46 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, February 25, 2014 12:56 PM
Points: 77, Visits: 392
Sorry about that. I must've grabbed the wrong file. I've re-uploaded the correct attachment.
Post #1442592
Posted Tuesday, April 16, 2013 6:52 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 2:50 PM
Points: 36,711, Visits: 31,159
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1442718
Posted Wednesday, April 17, 2013 12:10 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, February 25, 2014 12:56 PM
Points: 77, Visits: 392
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


Post #1443422
Posted Wednesday, April 17, 2013 7:08 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, February 25, 2014 12:56 PM
Points: 77, Visits: 392
The Plan B query is excrutiatingly slow

Any suggestions on how I can get the same results but with better performance?
Post #1443571
Posted Friday, April 19, 2013 4:55 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 2:50 PM
Points: 36,711, Visits: 31,159
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1444646
Posted Friday, April 19, 2013 5:01 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, February 25, 2014 12:56 PM
Points: 77, Visits: 392
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.
Post #1444648
Posted Saturday, April 20, 2013 3:40 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 5:34 PM
Points: 1,875, Visits: 18,439
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 !
__________________________________________________________________
Post #1444695
Posted Saturday, April 20, 2013 8:54 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, February 25, 2014 12:56 PM
Points: 77, Visits: 392
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
Post #1444716
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse