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
ChrisM@home
ChrisM@home
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2052 Visits: 10370
gcresse (4/15/2013)
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.



Hi Gina

I remember a similar requirement from a year or two ago - how did you solve it then, and how different is the requirement now?


Low-hanging fruit picker and defender of the moggies





For better assistance in answering your questions, please read this.




Understanding and using APPLY, (I) and (II) Paul White

Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
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
Hi Chris,

That other post was an attempt to see if I could use some sort of "fast" running balance to calculate blend percentages (variety of grape, vintage of grape, appellation of grape) on-the-fly for a single lot rather than having to maintain a data warehouse that updates during a nightly job. I was never able to create a view that was fast enough to calculate the percentages so we continue to use the data warehouse.

This current post is about using the percent data from that same data warehouse but needing to group and sort those results according the purchase order that sourced the grapes that ultimately became wine.

Thanks,
Gina
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: 5468 Visits: 35427
is the following any where near what you are looking for?
note...rounding issues...used for simplicity



SELECT PudID
, TranDate
, TranQty
, LotID
, CAST( TranQty * PudIDPct / 100 as INT) AS jlsqty
, CAST (SUM ( TranQty * PudIDPct / 100 ) OVER ( ORDER BY PudID , TranDate , LotID , LotIDBalSeq ) AS INT) AS jlstot
, CAST (SUM ( TranQty * PudIDPct / 100 ) OVER ( ORDER BY PudID , TranDate ) AS INT) AS jlstot2
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
ORDER BY PudID , TranDate , LotID , LotIDBalSeq;




________________________________________________________________
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
Perfect! That's exactly the answer I was looking for. I tried all sorts of SUM OVER PARTITION BY combinations with no luck, but I see the PARTITION wasn't required. I guess I was over-thinking it.

Thank you so much, JLS. If ever you want a really good bottle of wine, just let me know :-D

Gina
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: 5468 Visits: 35427
gcresse (4/20/2013)
Perfect! That's exactly the answer I was looking for. I tried all sorts of SUM OVER PARTITION BY combinations with no luck, but I see the PARTITION wasn't required. I guess I was over-thinking it.

Thank you so much, JLS. If ever you want a really good bottle of wine, just let me know :-D

Gina


Thanks Gina....would suggest that you examine exec plans for larger data sets and index where applicable.

good luck and thanks for the offer (Châteauneuf-du-Pape 1998_<grin>Wink


edit ...for multiple PudId use partition by

eg

OVER ( PARTITION BY PUDID ORDER BY PudID , TranDate )



________________________________________________________________
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
Yes, as I was driving to meet a friend for lunch, it dawned on me that I will need to partition by PudID when I include them all. Thanks for the confirmation.
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 ran into a bit of a glitch with the solution, JLS. We have some situations where there was already gallons of wine in a lot prior to introducing gallons for a particular PudID. Here is a sample:


SELECT PudID
, TranDate
, TranQty
, LotID
, LotIDBalSeq
, LotRunBal
, PudIDPct
, CAST( TranQty * PudIDPct / 100 as INT) AS jlsqty
, CAST (SUM ( TranQty * PudIDPct / 100 ) OVER ( ORDER BY PudID , TranDate , LotID , LotIDBalSeq ) AS INT) AS jlstot
, CAST (SUM ( TranQty * PudIDPct / 100 ) OVER ( ORDER BY PudID , TranDate ) AS INT) AS jlstot2
FROM
( SELECT 1479 PudID, '2011-09-06 18:15:29.000' TranDate, 15586 TranQty, 71871 LotID, 1 LotIDBalSeq, 15586 LotRunBal, 0.0000000000000000 PudIDPct
UNION SELECT 1479, '2011-09-07 08:02:49.000', 10168, 71871, 2, 25754, 20.9598514468311413
UNION SELECT 1479, '2011-09-07 17:43:48.000', 23750, 71909, 1, 23750, 24.5894491788983578
UNION SELECT 1479, '2011-09-10 04:57:00.000', -39114, 71813, 2, 38204, 0.0000000000000000
UNION SELECT 1479, '2011-09-10 04:57:00.000', 38204, 71813, 3, 38204, 0.0000000000000000
UNION SELECT 1479, '2011-09-10 16:45:13.000', 25382, 71871, 3, 25382, 20.9598514523353064
UNION SELECT 1479, '2011-09-10 16:45:13.000', -25754, 71871, 4, 25382, 21.0000000000000000
UNION SELECT 1479, '2011-09-11 15:11:35.000', 23327, 71871, 5, 48709, 22.6980833670217669
UNION SELECT 1479, '2011-09-11 15:11:35.000', -23750, 71909, 2, 0, 24.5894491788983578
UNION SELECT 1479, '2011-09-12 20:06:06.000', -38204, 71813, 4, 37505, 0.0000000000000000
UNION SELECT 1479, '2011-09-12 20:06:06.000', 37505, 71813, 5, 37505, 0.0000000000000000
UNION SELECT 1479, '2011-09-17 04:49:58.000', 34282, 71813, 6, 71787, 0.0000000000000000
UNION SELECT 1479, '2011-09-17 04:51:51.000', 24250, 71813, 7, 96037, 0.0000000000000000
UNION SELECT 1479, '2011-09-17 17:22:54.000', 46329, 71813, 8, 142366, 7.3864385186086848
UNION SELECT 1479, '2011-09-17 17:22:54.000', -48709, 71871, 6, 0, 22.6980833670217669
) WORK
ORDER BY PudID , TranDate , LotID , LotIDBalSeq;



If you run this, you'll see that we end up with a negative balance for the jlstot2 column at the end of the transactions. I've uploaded a new spreadsheet and on the PUDID1479 tab you can see what the PudIDAsOfBal should be (in the last column). I've identified each lot by color, and I've used a formula to calculate the PudIDAsOfBal. I'm back to my original thought that I need to grab the last LotIDBalSeq for each lot as of the tran date for figuring the PudIDAsOfBal.

The catch is that the PudIDPct is not the percent of the TranQty that belongs to the PudID, but rather it is the percent of the LotRunBal that belongs to the PudID at the end of that transaction.

Burning more brain cells now... Thanks for any input/ideas you may have.

Thanks,
Gina
Attachments
RunBal.xlsx (32 views, 13.00 KB)
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: 5468 Visits: 35427
Hi Gina

just back from work...so sorry for delay.

I must admit that I am struggling to understand your reqs.

there are rows for exactly same date/time/lotid...with a qualifying column of lotidbalseq...have you not got a sequential uniquie ID ref?

in your last spreadsheet you are now presenting data that is different than before... in that the PUDIDPct column is often zero where as before this was always populated (tab PUDID751)

I am still not sure what bits of the data come from the db and what you have already calcultated in your query....me thinks it may be useful to provide data based on what exists in the app and start from there....and to provide some sample data that mimic a start from the beginning rather than jumping into a set of transactions half way thro....eg start from zero PUDID and zero LOTId and walk thro what you need....we can worry about opening balances later on.

sorry to sound so negative Sick

an idea possible maybe to create a temptable and join back to the originating data using 2012 sliding windows...depends really on what you need as final output...are you determined to have every row with the results or just a summary (your spreadsheet tab 1479 has four rows for LotId 7183 all with the same PUDIDAsOfBal...11056..??)


not sure if the following demo code will help/hinder/inspire.....but take a look..especially with ref to
"ROWS 2 PRECEDING"



--- windows sum over for running total http://msdn.microsoft.com/en-us/library/ms189461.aspx

USE [tempdb]
GO


CREATE TABLE [dbo].[TAB1](
[trandid] [int] NULL,
[lotid] [int] NULL,
[lotidseq] [int] NULL,
[qty] [int] NULL
) ON [PRIMARY]

GO
INSERT [dbo].[TAB1] ([trandid], [lotid], [lotidseq], [qty]) VALUES (1, 1, 1, 100)
INSERT [dbo].[TAB1] ([trandid], [lotid], [lotidseq], [qty]) VALUES (2, 2, 1, 50)
INSERT [dbo].[TAB1] ([trandid], [lotid], [lotidseq], [qty]) VALUES (3, 3, 1, 75)
INSERT [dbo].[TAB1] ([trandid], [lotid], [lotidseq], [qty]) VALUES (4, 1, 2, 44)
INSERT [dbo].[TAB1] ([trandid], [lotid], [lotidseq], [qty]) VALUES (5, 2, 2, 56)
INSERT [dbo].[TAB1] ([trandid], [lotid], [lotidseq], [qty]) VALUES (6, 3, 2, 25)
INSERT [dbo].[TAB1] ([trandid], [lotid], [lotidseq], [qty]) VALUES (7, 1, 3, 12)
INSERT [dbo].[TAB1] ([trandid], [lotid], [lotidseq], [qty]) VALUES (8, 2, 3, 0)
INSERT [dbo].[TAB1] ([trandid], [lotid], [lotidseq], [qty]) VALUES (9, 3, 3, 0)
INSERT [dbo].[TAB1] ([trandid], [lotid], [lotidseq], [qty]) VALUES (10, 1, 4, 56)
INSERT [dbo].[TAB1] ([trandid], [lotid], [lotidseq], [qty]) VALUES (11, 2, 4, 18)
INSERT [dbo].[TAB1] ([trandid], [lotid], [lotidseq], [qty]) VALUES (12, 3, 4, 94)
INSERT [dbo].[TAB1] ([trandid], [lotid], [lotidseq], [qty]) VALUES (13, 1, 5, 68)
INSERT [dbo].[TAB1] ([trandid], [lotid], [lotidseq], [qty]) VALUES (14, 2, 5, 45)
INSERT [dbo].[TAB1] ([trandid], [lotid], [lotidseq], [qty]) VALUES (15, 3, 5, 66)
INSERT [dbo].[TAB1] ([trandid], [lotid], [lotidseq], [qty]) VALUES (16, 1, 6, 45)
INSERT [dbo].[TAB1] ([trandid], [lotid], [lotidseq], [qty]) VALUES (17, 2, 6, 87)
INSERT [dbo].[TAB1] ([trandid], [lotid], [lotidseq], [qty]) VALUES (18, 3, 6, 12)



SELECT trandid
, lotid
, lotidseq
, qty
, sum (qty) over (partition by lotid order by trandid) lotidRT
, sum (qty) over ( partition by lotid order by trandid ROWS 2 PRECEDING ) lastRT
, sum (qty) over ( order by trandid) RT
FROM TAB1;







________________________________________________________________
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
Hi JLS,

I know it's not really straight forward. I've add comments to the attached spreadsheet to explain why some transactions have the same date/time and why I have to include the transactions that have 0 percent for the PudID. Basically, when we move wine from one tank to another, the transaction is a 2-sided transaction and both sides have the same TranDate because they happen simultaneously. I need to include those movements because we can gain/lose gallons in the move and I'm keeping a running balance for each LotID so I can calculate how much of each LotIDBal belongs to the PudID at each TranDate.

The end goal: We purchase grapes on purchase order (PudID) 1479 and as we process those grapes I need to know how many gallons have been produced from those specific grapes so I can calculate the yield as of any transaction. Since there can be multiple lots that make up the inventory for the PudID, I need to sum up the last balance for each lot as of the selected TranDate.

I'm looking at some of the other Window functions in SQL Server 2012 to see if I can come up with an answer. If I could just include a WHERE statement in the SUM OVER PARTITION and limit the SUM(PudIDLotBal) results to the last TranDate for each LotID up to the As of TranDate, it would work, but I don't see that as an option. I've also considered trying to PIVOT the data somehow, but that would be a challenge since there can be any number of LotIDs for each PudID.

It would be a monumental task to try to recreate the data as it exists in our database to present here instead of just presenting the simplified results, and I fear it would cause even more confusion. I appreciate your efforts and I hope I've cleared up most of your questions.

Thanks for all your help,
Gina
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
JLS, I'm going to see if I can go back to your original code but instead of using the final output percent, I will see if I can get the PudIDPercent for the input side of the transaction. I'll let you know if that works. fingers crossed.

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