April 20, 2013 at 3:44 pm
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.
April 22, 2013 at 7:05 pm
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 1479PudID,'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
April 23, 2013 at 2:58 pm
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
April 23, 2013 at 7:53 pm
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
April 24, 2013 at 10:11 am
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
April 24, 2013 at 11:39 am
Gina
in the examples you give code/spreadsheet can you please clarify what pre exists as data and what you are calculating.
sorry for being thick 😛
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
April 24, 2013 at 12:26 pm
Here is the sql I'm using for my testing, JLS. Let me know if this answers your question:
TRUNCATE TABLE testLotTrans
-- GET LOT TRANSACTIONS FOR EACH PudID
INSERT INTO testLotTrans
SELECTDISTINCT
cmpPudIDtmpPudID,
wkhHdrIDtmpHdrID,
IntKeytmpIntKey,
IntPodKeytmpPodKey,
IntLotKeytmpLotKey,
IntTranDatetmpTranDate,
IntTranQtytmpTranQty
FROMInventoryTran
INNER JOIN
ProdOrdDtl ON PodKey = IntPodKey
INNER JOIN
Operation ON OprKey = PodOprKey
INNER JOIN
DataWhse.dbo.WorkHdr ON wkhLotKey = IntLotKey AND
wkhLocKey = IntLocKey
INNER JOIN
(SELECT DISTINCT
cmpHdrID,
cmpPudID,
cmpPodKey
FROMtestWkpCompQtys) CMP ON CMP.cmpHdrID = wkhHdrID AND
CMP.cmpPodKey = IntPodKey
INNER JOIN
PurchOrderDtl ON PudID = CmpPudID
INNER JOIN
Item PURCHITEM ON PURCHITEM.ItmItemCode = PudItemID
WHEREOprType NOT IN ('BOTTLE','SHIP','RECEIVE') AND-- BECAUSE WE WANT TO KNOW THE TOTAL GALLONS PRODUCED FROM THE TONS EXCLUDING SHIPMENTS/BOTTLING/RECEIVING
IntTranQty <> 0 AND
IntTranUOM = 'GA' AND
PURCHITEM.ItmType <> 'B' AND-- DON'T INCLUDE PURCHASED BULK JUICE OR WINE FOR CALCULATING YIELDS
cmpPudID <> 0
ORDER BY IntTranDate
-- GET LOT TRANSACTIONS THAT HAPPENED PRIOR TO THE PUDID COMING INTO THE PICTURE (IF ANY)
INSERT INTO testLotTrans
SELECTDISTINCT
RB.tmpPudIDtmpPudID,
WKH.wkhHdrIDtmpHdrID,
IT.IntKeytmpIntKey,
IT.IntPodKeytmpPodKey,
IT.IntLotKeytmpLotKey,
IT.IntTranDatetmpTranDate,
IT.IntTranQtytmpTranQty
FROMtestLotTrans RB
INNER JOIN
InventoryTran IT ON IT.IntLotKey = RB.tmpLotKey
LEFT JOIN
testLotTrans RB2 ON RB2.tmpPudID = RB.tmpPudID AND
RB2.tmpIntKey = IT.IntKey
INNER JOIN
ProdOrdDtl ON PodKey = IT.IntPodKey
INNER JOIN
Operation ON OprKey = PodOprKey
INNER JOIN
DataWhse.dbo.WorkHdr WKH ON WKH.wkhLotKey = IT.IntLotKey AND
WKH.wkhLocKey = IT.IntLocKey
INNER JOIN
PurchOrderDtl ON PudID = RB.tmpPudID
INNER JOIN
Item PURCHITEM ON PURCHITEM.ItmItemCode = PudItemID
WHEREOprType NOT IN ('BOTTLE','SHIP','RECEIVE') AND-- BECAUSE WE WANT TO KNOW THE TOTAL GALLONS PRODUCED FROM THE TONS DISREGARDING SHIPMENTS/BOTTLING/RECEIVING
IT.IntTranQty <> 0 AND
IT.IntTranUOM = 'GA' AND
PURCHITEM.ItmType <> 'B' AND-- DON'T INCLUDE PURCHASED BULK JUICE OR WINE FOR CALCULATING YIELDS
RB.tmpPudID <> 0 AND
RB2.tmpIntKey IS NULL -- ONLY GET ROWS THAT DON'T EXIST YET IN THE testLotTrans TABLE
ORDER BY IT.IntTranDate
--SELECT RESULTS
SELECT AsOfPudID
, AsOfTranDate
, AsOfTranQty
, AsOfLotKey
, AsOfLotBalSeq
, tmpLotRunBal
, ISNULL(AsOfPudIDPct,0)AsOfPudIDPct
, CAST( AsOfTranQty * AsOfPudIDPct / 100 as INT) AS jlsqty
, CAST (SUM ( AsOfTranQty * AsOfPudIDPct / 100 ) OVER (PARTITION BY AsOfPudID ORDER BY AsOfPudID , AsOfTranDate , AsOfLotKey , AsOfLotBalSeq ) AS INT) AS jlstot
, CAST (SUM ( AsOfTranQty * AsOfPudIDPct / 100 ) OVER (PARTITION BY AsOfPudID ORDER BY AsOfPudID , AsOfTranDate ) AS INT) AS jlstot2
, ROUND(ISNULL(tmpLotRunBal * AsOfPudIDPct / 100, 0),0)AsOfPudIDLotBal
FROM
(SELECTWORK.tmpPudIDAsOfPudID,
WORK.tmpTranDateAsOfTranDate,
WORK.tmpTranQtyAsOfTranQty,
WORK.tmpLotKeyAsOfLotKey,
CQ.cmpCompQty / CT.totTotQty * 100AsOfPudIDPct,
tmpLotRunBal,
WORK.tmpLotRunBal * CQ.cmpCompQty / CT.totTotQtyAsOfLotBal,
ROW_NUMBER() OVER
(PARTITION BY WORK.tmpPudID, WORK.tmpLotKey
ORDER BY WORK.tmpTranDate)AsOfLotBalSeq
FROM
(SELECTtmpPudID,
tmpHdrID,
tmpIntKey,
tmpPodKey,
tmpLotKey,
tmpTranDate,
tmpTranQty,
SUM(tmpTranQty) OVER (PARTITION BY tmpPudID, tmpLotKey ORDER BY tmpTranDate) tmpLotRunBal
FROMtestLotTrans
--where tmppudid = 1479-- and tmplotkey = 71871
--ORDER BYtmpPudID, tmpTranDate
) WORK
LEFT JOIN
testWkpCompTotals CT ON CT.totHdrID = WORK.tmpHdrID AND
CT.totPodKey = WORK.tmpPodKey AND
CT.totTotQty <> 0
LEFT JOIN
testWkpCompQtys CQ ON CQ.cmpHdrID = WORK.tmpHdrID AND
CQ.cmpPudID = WORK.tmpPudID AND
CQ.cmpPodKey = WORK.tmpPodKey
--ORDER BY AsOfPudID , AsOfTranDate , AsOfLotKey , AsOfLotBalSeq;
) WORK2
WHEREAsOfPudID = 1479 and AsOfTranDate between '2011-09-06 18:15:29.000' and '2011-09-17 17:22:54.000'--AND AsOfLotKey = 71871
--AND (AsOfPudID = 751 AND AsOfLotKey = 72459)
ORDER BY AsOfPudID , AsOfTranDate , AsOfLotKey , AsOfLotBalSeq;
My experiment with using the percents of the Input side of the transaction rather than the resulting percent of the output lot did not work. I'm now considering a For/While loop to separate the lots :crying:
Viewing 7 posts - 16 through 21 (of 21 total)
You must be logged in to reply to this topic. Login to reply