Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Help with complicated running balance


Help with complicated running balance

Author
Message
J Livingston SQL
J Livingston SQL
Hall of Fame
Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)

Group: General Forum Members
Points: 3445 Visits: 33000
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 :-P

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

gcresse
gcresse
SSC Journeyman
SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)

Group: General Forum Members
Points: 82 Visits: 426
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
SELECT   DISTINCT
      cmpPudID         tmpPudID,
      wkhHdrID         tmpHdrID,
      IntKey            tmpIntKey,
      IntPodKey         tmpPodKey,
      IntLotKey         tmpLotKey,
      IntTranDate         tmpTranDate,
      IntTranQty         tmpTranQty

FROM   InventoryTran

      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
       FROM   testWkpCompQtys) CMP ON CMP.cmpHdrID = wkhHdrID AND
                              CMP.cmpPodKey = IntPodKey

      INNER JOIN
      PurchOrderDtl ON PudID = CmpPudID

      INNER JOIN
      Item PURCHITEM ON PURCHITEM.ItmItemCode = PudItemID

WHERE   OprType 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
SELECT   DISTINCT
      RB.tmpPudID            tmpPudID,
      WKH.wkhHdrID         tmpHdrID,
      IT.IntKey            tmpIntKey,
      IT.IntPodKey         tmpPodKey,
      IT.IntLotKey         tmpLotKey,
      IT.IntTranDate         tmpTranDate,
      IT.IntTranQty         tmpTranQty

FROM   testLotTrans 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

WHERE   OprType 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
   (   SELECT   WORK.tmpPudID                              AsOfPudID,
            WORK.tmpTranDate                           AsOfTranDate,
            WORK.tmpTranQty                              AsOfTranQty,
            WORK.tmpLotKey                              AsOfLotKey,
            CQ.cmpCompQty / CT.totTotQty * 100               AsOfPudIDPct,
            tmpLotRunBal,
            WORK.tmpLotRunBal * CQ.cmpCompQty / CT.totTotQty   AsOfLotBal,
            ROW_NUMBER() OVER
               (PARTITION BY WORK.tmpPudID, WORK.tmpLotKey
                ORDER BY WORK.tmpTranDate)                  AsOfLotBalSeq
      FROM
      (   SELECT   tmpPudID,
               tmpHdrID,
               tmpIntKey,
               tmpPodKey,
               tmpLotKey,
               tmpTranDate,
               tmpTranQty,
               SUM(tmpTranQty) OVER (PARTITION BY tmpPudID, tmpLotKey ORDER BY tmpTranDate) tmpLotRunBal
          FROM   testLotTrans
          --where tmppudid = 1479-- and tmplotkey = 71871
          --ORDER BY   tmpPudID, 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;

   Wink WORK2
WHERE   AsOfPudID = 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
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