none
how can i divide transaction on fifo basis

    Question

  • Hi,

       I have two table called Tansaction  and Godown . in Godown Table total Stock is available godown wise and in transacation table voucher wise stock is available. now i want to divide voucherNo  godown wise as per stock, on fifo basis. i have attached image and sample code. i don't want to use loop.

    Create Table [Transaction]
    (
    	 VoucherNo int
    	,Qty int
    )
    Create Table [Godown]
    (
    	 Godown Char(1) 
    	,StockQty int
    )
    
    Insert Into [Transaction] Values (1,10)
    Insert Into [Transaction] Values (2,10)
    Insert Into [Transaction] Values (3,20)
    Insert Into [Transaction] Values (4,30)
    Insert Into [Transaction] Values (5,40)
    
    Insert into [Godown] Values ('A',25)
    Insert into [Godown] Values ('B',25)
    Insert into [Godown] Values ('C',25)
    Insert into [Godown] Values ('D',25)
    Insert into [Godown] Values ('E',10)
    
    


    Prem Shah

    Saturday, July 21, 2012 2:26 AM

Answers

  • Turns out that the fix was easy. Change the WHERE clause in the actionrows CTE to:

        WHERE  t.TotQty - t.Qty < g.TotQty
          AND  g.TotQty - g.StockQty < t.TotQty

    That is, change <= to <.

    And, yes, the cursor-based solution had it right. Athough, in the cursor-based solution this is a special case, but is much easier to see. The cursor-based solution uses two cursors and runs a loop where it advances a cursor if we have exhausted the current transaction or godown. But if they are exhausted at the same time, both cursors are advanced.

    The set-based solution has three CTEs. The first just computes the running sum after each transaction/godown.

    The CTE actionrows forms a cross-join of all transactions and godown, and then filter these. Understanding this filter is not exactly simple, and I will have to admit that I don't have full understanding. But once you realise that there is perfect symmetry, the number of options are drastically reduced. Anyway, we compare the previous total (A) for one side with the current total for the other side. (B) B must be > than A to be able to match against A. But there are many rows with B > A that are not of interest. However, they are filtered out by the reverse condition.

    The actionrows CTE also computes PrevTransQty and PrevStockQty, but note that this is not the same as t.TotQty - t.Qty and g.TotQty - g.StockQty. The LAG expression are part of the SELECT list which is computed after the WHERE clause, so they give PrevxxxQty in the filtered output, and we use them to compute Qty in the final select.

    The set-based solution was more difficult to write, and it is more difficult to understand, whereas the cursor-based solution is fairly straight forward. It is likely to perform better, particularly if you have very many transactions and godwns, as that filtering cross-join is expensive. Whereas the cursor-based solution performs a manaul merge-join.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Proposed as answer by Naomi NModerator Saturday, August 04, 2012 7:51 PM
    • Marked as answer by Prem Shah Monday, August 13, 2012 2:32 PM
    Saturday, August 04, 2012 7:45 PM

All replies

  • I assume that your sample tables are a condensed version of your original problem. Since writing this type of queries is not precisely trivial, I will cheat a bit and refer you to

    https://groups.google.com/forum/?hl=sv&fromgroups#!topic/microsoft.public.sqlserver.programming/w9_yOa-tV5I

    where I solved a similar problem, and which also had a few more twists. Be careful to read the thread to the end, as the first solution that I posted had a couple of errors.

    Please beware of that if you are on SQL 2012, the query can be improved by using the new windowed aggregates.

    If you have any questions with regards to your problem or the solution in the old thread, please don't hesitate to ask.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Saturday, July 21, 2012 9:32 AM
  • Maybe you think me a purist, but I would strongly suggest not to name your table [Transaction] as transaction is a key word in the T-SQL language. Consider using prefixes for your object types (tbl_ for tables, vw_ for views csp_ for custom stored procedure). E.g

    CREATE TABLE tbl_Transaction ...

    CREATE PROCEDURE csp_DoSomething

    If you like using prefixes, never use the prefix sp_ for stored procedures since the prefix sp_ is reserverd for Microsoft system procedues (sp_help, sp_who, etc.).

    Saturday, July 21, 2012 9:14 PM
  • hi chris,

                this name is only for sample .


    Prem Shah

    Sunday, July 22, 2012 7:01 AM
  • Hi Erland,

                 Thanks for Reply. i assume that there is a little bit difference from your  thread. in old thread number of record is fixed, but in my scenario number of record is not fixed. every godown wise stock is fixed and outstanding voucher is fixed. my requirement is that how to divide voucher number on every godown as per stock qty of godown on fifo Basis.

    suppose in my [A] godown, there are 25 stock qty available. now i should take 10 pcs from voucher no 1, 10 pcs from voucher number 2 and 5 pcs from voucher no 3. in [B] godown , there are 25 stock qty available.  now it takes 15 pcs from voucher no 3 ( 5 pcs is adjust in [A] godown) and 10 pcs from voucher no 4. .....


    Prem Shah

    Sunday, July 22, 2012 10:59 AM
  • Yes, but I realised that your tables were just samples, and your real business problem is likely to be even more complex. The thread adresses a problem which is indeed different. There you have multiple claims (= transactions) and a single payment (= godown). In difference to your case, that problem also includes refunds.

    One possibility is to use the solution I showed in that post and iterated over the godowns.

    But I hear you. Below are two solutions to the problem, one set-based and one cursor solution. And very most likely it is the latter you should use.

    First of all, the set-based solution only runs on SQL 2012. It is possible to replace the new features in SQL 2012 with other constructs to have it to run on SQL 2008 or SQL 2005. However, my suspicion is that with a full-scale set of data, performance will be poor on SQL 2012. And a complete disaster on SQL 2008. The SQL 2012 requires forming the cartesian product of all transactions and godown rows and then filter these.

    The cursor-based solution on the other hand, runs on any version of SQL Server from SQl7 and up. (Except that I was lazy and used some convenience syntax in SQL 2008, but that syntax is not essential for the solution). And it makes a single pass over both tables.

    Furthermore, the set-based solution took me 60-90 minutes to write, whereas the I wrote the cursor-based solution in five minutes. (OK, so I reused the CASE expression from the set-based solution.)

    An interesting question is what happens if Transaction and Godown does not match up - this I haven't tested for any of the solutions.

    Create Table [Transaction]
    (
        VoucherNo int
       ,Qty int
    )
    Create Table [Godown]
    (
        Godown Char(1)
       ,StockQty int
    )

    Insert Into [Transaction] Values (1,10)
    Insert Into [Transaction] Values (2,10)
    Insert Into [Transaction] Values (3,20)
    Insert Into [Transaction] Values (4,30)
    Insert Into [Transaction] Values (5,40)

    Insert into [Godown] Values ('A',25)
    Insert into [Godown] Values ('B',25)
    Insert into [Godown] Values ('C',25)
    Insert into [Godown] Values ('D',25)
    Insert into [Godown] Values ('E',10)
    go
    WITH trans AS (
       SELECT VoucherNo, Qty,
              SUM(Qty) OVER( ORDER BY VoucherNo ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS TotQty
       FROM   [Transaction]
    ), godown1 AS (
       SELECT Godown, StockQty,
              SUM(StockQty) OVER( ORDER BY Godown ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS TotQty
       FROM   Godown    
    ), actionrows AS (
       SELECT t.VoucherNo, g.Godown, t.Qty AS TransQty, g.StockQty,
              t.TotQty AS TransTotQty, g.TotQty AS StockTotQty,
              LAG(t.TotQty, 1, 0) OVER(ORDER BY t.VoucherNo, g.Godown) AS PrevTransQty,
              LAG(g.TotQty, 1, 0) OVER(ORDER BY t.VoucherNo, g.Godown) AS PrevStockQty
       FROM   trans t
       CROSS  JOIN godown1 g
       WHERE  t.TotQty - t.Qty <= g.TotQty
         AND  g.TotQty - g.StockQty <= t.TotQty
    )
    SELECT VoucherNo, Godown, TransQty, StockQty, TransTotQty,
           CASE WHEN  PrevTransQty < PrevStockQty
                THEN CASE WHEN TransQty < PrevStockQty - PrevTransQty
                     THEN TransQty
                     ELSE PrevStockQty - PrevTransQty
                END
                WHEN PrevTransQty > PrevStockQty
                THEN CASE WHEN StockQty < PrevTransQty - PrevStockQty
                     THEN StockQty
                     ELSE PrevTransQty - PrevStockQty
                END
                ELSE CASE WHEN TransQty < StockQty
                          THEN TransQty
                          ELSE StockQty
                     END   
           END
    FROM   actionrows
    ORDER  BY VoucherNo, Godown
    go
    CREATE TABLE #result (VoucherNo int     NOT NULL,
                          Godown    char(1) NOT NULL,
                          Qty       int     NOT NULL,
                          PRIMARY KEY (VoucherNo, Godown)
    )
    DECLARE @tottransqty  int = 0,
            @totstockqty  int = 0,
            @prevtransqty int = 0,
            @prevstockqty int = 0,
            @transqty     int,
            @stockqty     int,
            @voucherno    int,
            @godown       char(1),
            @resultqty    int

    DECLARE transcur CURSOR STATIC LOCAL FOR
      SELECT VoucherNo, Qty FROM [Transaction] ORDER BY VoucherNo

    DECLARE godowncur CURSOR STATIC LOCAL FOR
      SELECT Godown, StockQty FROM Godown ORDER BY Godown

    OPEN transcur
    OPEN godowncur

    WHILE 1 = 1
    BEGIN
       IF @prevtransqty <= @prevstockqty
       BEGIN
          FETCH transcur INTO @voucherno, @transqty
          IF @@fetch_status <> 0
             BREAK
          SELECT @tottransqty += @transqty
      END

       IF @prevtransqty >= @prevstockqty
       BEGIN
          FETCH godowncur INTO @godown, @stockqty
          IF @@fetch_status <> 0
             BREAK
          SELECT @totstockqty += @stockqty
      END

      SELECT @resultqty =
           CASE WHEN @prevtransqty < @prevstockqty
                THEN CASE WHEN @transqty < @prevstockqty - @prevtransqty
                     THEN @transqty
                     ELSE @prevstockqty - @prevtransqty
                END
                WHEN @prevtransqty > @prevstockqty
                THEN CASE WHEN @stockqty < @prevtransqty - @prevstockqty
                     THEN @stockqty
                     ELSE @prevtransqty - @prevstockqty
                END
                ELSE CASE WHEN @transqty < @stockqty
                          THEN @transqty
                          ELSE @stockqty
                     END   
           END

       INSERT #result (VoucherNo, Godown, Qty)
          VALUES (@voucherno, @godown, @resultqty)

       SELECT @prevtransqty = @tottransqty, @prevstockqty = @totstockqty
    END

    CLOSE transcur
    CLOSE godowncur

    SELECT * FROM #result ORDER BY VoucherNo, Godown   

    go
    DROP TABLE [Transaction], Godown, #result


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by Prem Shah Wednesday, July 25, 2012 12:12 PM
    • Unmarked as answer by Prem Shah Friday, August 03, 2012 10:22 AM
    Sunday, July 22, 2012 6:04 PM
  • If Erland's reply answers your question, could you please mark it as 'Answer'? This has two advantages.

    1. Whenever you add a question, this site shows you previous questions on the same subject. If the answer to your question is also the answer to another person's question, she/he will find the answer without having to post a question.
    2. When people filter the forum on 'unanswered questions' because they want to spend some of their time helping others, your question will not pop up anymore.

    Thank you.


    Tuesday, July 24, 2012 3:07 PM
  • Given that I spent almost two hours on that problem, I would of course appreciate some feedback from Prem on my solutions. Then again, I am not surprised if he is just sitting crying in a corner and trying to map my solutions to his real problem. Well, I have seen him in the forums, so maybe it's not that bad, but he has just laid it aside.

    But having answered questions in many years in forums and newsgroups, I have learnt that you don't always get feedback when you have posted what you think is a good solution. It could be that the person prefers to save bandwidth and not say more in a thread that is resolved as far as he is concerned. But there could be a lots of another reasons. Maybe the poster in the meantime realised that his question was based on an incorrect approach to his problem at large. Or he got a better response from a different site.

    And sometimes the sad story is that no one gave a satisfactory answer to the question - maybe because the person who asked the question did not make him clear enough.

    My advice is that you read the forums from the NNTP Bridge like I do. Then you will not be distracted by this "marked as answer" business, which in my opinion is going out of hand. I have occasionally seen my own postings to be propsed as answer, when it's painfully clear that I did not sort out the question for the poster - I only tried to wrestle out more information.

    Going back to Prem's FIFO question, I will have to admit that I failed to explain my solutions in any detail. I was a bit exhausted at the time. Please, Prem, if you have any questions about the solutions and how to use them, the thread is still open.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by Prem Shah Wednesday, July 25, 2012 12:11 PM
    • Unmarked as answer by Prem Shah Wednesday, July 25, 2012 12:11 PM
    Tuesday, July 24, 2012 9:14 PM
  • Hi Erland,

    I am sorry for late reply. actually i am little bit busy in implementing your solutions in real problem. so i cannot able to reply.thank you for solutions. this is excatly what i am looking for. you are genius erland . last time also i had implemented your code but i miss to marks as answer but now i am marking it. i like your code . from last couple of months, many time i got solution from you .please don't say like that i had laid it aside. i like the way you write code. i also get some good logic from your code. once again sorry and thank you. :)


    Prem Shah

    Wednesday, July 25, 2012 12:11 PM
  • Hi Erland

         i am getting wrong result in following scenario.

    Create Table [Transaction]
    (
    	 VoucherNo int
    	,Qty int
    )
    Create Table [Godown]
    (
    	 Godown Char(1) 
    	,StockQty int
    )
    
    Insert Into [Transaction] Values (1,10)
    Insert Into [Transaction] Values (2,10)
    
    Insert into [Godown] Values ('A',10)
    Insert into [Godown] Values ('B',10)
    
    
    
    WITH trans AS (
        SELECT VoucherNo, Qty, 
              SUM(Qty) OVER( ORDER BY VoucherNo ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS TotQty
        FROM   [Transaction]
     ), godown1 AS (
        SELECT Godown, StockQty,
               SUM(StockQty) OVER( ORDER BY Godown ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS TotQty
        FROM   Godown     
    ), actionrows AS (
        SELECT t.VoucherNo, g.Godown, t.Qty AS TransQty, g.StockQty, 
              t.TotQty AS TransTotQty, g.TotQty AS StockTotQty,
               LAG(t.TotQty, 1, 0) OVER(ORDER BY t.VoucherNo, g.Godown) AS PrevTransQty,
               LAG(g.TotQty, 1, 0) OVER(ORDER BY t.VoucherNo, g.Godown) AS PrevStockQty
        FROM   trans t
        CROSS  JOIN godown1 g
        WHERE  t.TotQty - t.Qty <= g.TotQty
          AND  g.TotQty - g.StockQty <= t.TotQty
     )
     SELECT VoucherNo, Godown, TransQty, StockQty, TransTotQty, 
           CASE WHEN  PrevTransQty < PrevStockQty
                 THEN CASE WHEN TransQty < PrevStockQty - PrevTransQty
                      THEN TransQty
                      ELSE PrevStockQty - PrevTransQty
                 END
                 WHEN PrevTransQty > PrevStockQty
                 THEN CASE WHEN StockQty < PrevTransQty - PrevStockQty
                      THEN StockQty
                      ELSE PrevTransQty - PrevStockQty
                 END
                 ELSE CASE WHEN TransQty < StockQty
                           THEN TransQty
                           ELSE StockQty
                      END    
           END
     FROM   actionrows
     ORDER  BY VoucherNo, Godown
     


    Prem Shah

    Friday, August 03, 2012 10:21 AM
  • An ugly "special case" which probably is much more common than more complicated chains of transactions.

    I will need to look into that. However, right now I about to call it a day, and the forecasts for tomorrow suggest the chances are good for a bathing and basking excusion - and where I live, this is a top priority thing. So I may not be able to look into this until tomorrow night. But maybe you or someone else will beat me to it!

    I didn't test, but I bet the cursor solution handles this case correctly!


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Friday, August 03, 2012 10:24 PM
  • Turns out that the fix was easy. Change the WHERE clause in the actionrows CTE to:

        WHERE  t.TotQty - t.Qty < g.TotQty
          AND  g.TotQty - g.StockQty < t.TotQty

    That is, change <= to <.

    And, yes, the cursor-based solution had it right. Athough, in the cursor-based solution this is a special case, but is much easier to see. The cursor-based solution uses two cursors and runs a loop where it advances a cursor if we have exhausted the current transaction or godown. But if they are exhausted at the same time, both cursors are advanced.

    The set-based solution has three CTEs. The first just computes the running sum after each transaction/godown.

    The CTE actionrows forms a cross-join of all transactions and godown, and then filter these. Understanding this filter is not exactly simple, and I will have to admit that I don't have full understanding. But once you realise that there is perfect symmetry, the number of options are drastically reduced. Anyway, we compare the previous total (A) for one side with the current total for the other side. (B) B must be > than A to be able to match against A. But there are many rows with B > A that are not of interest. However, they are filtered out by the reverse condition.

    The actionrows CTE also computes PrevTransQty and PrevStockQty, but note that this is not the same as t.TotQty - t.Qty and g.TotQty - g.StockQty. The LAG expression are part of the SELECT list which is computed after the WHERE clause, so they give PrevxxxQty in the filtered output, and we use them to compute Qty in the final select.

    The set-based solution was more difficult to write, and it is more difficult to understand, whereas the cursor-based solution is fairly straight forward. It is likely to perform better, particularly if you have very many transactions and godwns, as that filtering cross-join is expensive. Whereas the cursor-based solution performs a manaul merge-join.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Proposed as answer by Naomi NModerator Saturday, August 04, 2012 7:51 PM
    • Marked as answer by Prem Shah Monday, August 13, 2012 2:32 PM
    Saturday, August 04, 2012 7:45 PM