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 Saturday, April 20, 2013 10:29 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 11:56 PM
Points: 1,031, Visits: 6,727
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
Post #1444720
Posted Saturday, April 20, 2013 10:41 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
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
Post #1444722
Posted Saturday, April 20, 2013 11:56 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 9:39 PM
Points: 1,912, Visits: 19,449
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
Post #1444731
Posted Saturday, April 20, 2013 1:37 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
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

Gina
Post #1444739
Posted Saturday, April 20, 2013 1:49 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 9:39 PM
Points: 1,912, Visits: 19,449
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

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


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
Post #1444740
Posted Saturday, April 20, 2013 3:44 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
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.
Post #1444748
Posted Monday, April 22, 2013 7:05 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 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


  Post Attachments 
RunBal.xlsx (29 views, 13.32 KB)
Post #1445236
Posted Tuesday, April 23, 2013 2:58 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 9:39 PM
Points: 1,912, Visits: 19,449
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

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
Post #1445687
Posted Tuesday, April 23, 2013 7:53 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
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
Post #1445745
Posted Wednesday, April 24, 2013 10:11 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
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
Post #1446101
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse