Query using fifo

  • jsshivalik

    Mr or Mrs. 500

    Points: 506

    Hi

    I have below Data . Using Fifo i want to display output

    Code Date Debit Amount Credit Amount

    1 02-10-2019 20000
    1 04-10-2019 10000
    1 10-11-2019 15000
    1 12-11-2019 40000
    1 14-11-2019 30000


    3 04-10-2019 40000
    3 10-10-2019 20000
    3 10-11-2019 45000

    OUTPUT
    1 10-11-2019 5000
    1 14-11-2019 30000

    3 10-10-2019 15000
    Thanks
  • Vedat OZER

    Grasshopper

    Points: 24

    https://www.red-gate.com/simple-talk/sql/performance/t-sql-window-function-speed-phreakery-the-fifo-stock-inventory-problem/

    • This reply was modified 9 months, 2 weeks ago by  Vedat OZER.

    Allah bize yeter, O ne güzel vekildir.

    vedatozer@outlook.com

  • Jeff Moden

    SSC Guru

    Points: 997205

    jsshivalik wrote:

    Hi

    I have below Data . Using Fifo i want to display output

    Code Date Debit Amount Credit Amount

    1 02-10-2019 20000
    1 04-10-2019 10000
    1 10-11-2019 15000
    1 12-11-2019 40000
    1 14-11-2019 30000


    3 04-10-2019 40000
    3 10-10-2019 20000
    3 10-11-2019 45000

    OUTPUT
    1 10-11-2019 5000
    1 14-11-2019 30000

    3 10-10-2019 15000
    Thanks

    For future posts, please help us help you.  Read the article at the first link in my signature line below for both how to ask a question and how to provide readily consumable data for us to test/demonstrate with.

    With that in mind, here's one way to create the test data for the question being posed...

    /********************************************************************************
    Create a test table and populate it with test data.
    This is NOT a part of the solution. We're just building something to test with.
    REF: https://www.sqlservercentral.com/forums/topic/query-using-fifo
    ********************************************************************************/
    --===== Set the default date format according to the data provided by the OP.
    SET DATEFORMAT DMY
    ;
    --===== If the test table already exists, drop it to make testing in SSMS easier.
    IF OBJECT_ID('tempdb..#TestTable','U') IS NOT NULL
    DROP TABLE #TestTable
    ;
    --===== Create the test table.
    -- It would take all of the columns to make a PK, so I''m not making a PK.
    CREATE TABLE #TestTable
    (
    Code TINYINT
    ,Date DATE
    ,[Debit Amount] INT
    ,[Credit Amount] INT
    )
    ;
    --===== Populate the test table with data provided by the OP.
    INSERT INTO #TestTable
    (Code,Date,[Debit Amount],[Credit Amount])
    SELECT Code,Date,[Debit Amount],[Credit Amount]
    FROM (VALUES
    (1,'02-10-2019',20000,NULL)
    ,(1,'04-10-2019',10000,NULL)
    ,(1,'10-11-2019',15000,NULL)
    ,(1,'12-11-2019',NULL,40000)
    ,(1,'14-11-2019',30000,NULL)
    ,(3,'04-10-2019',40000,NULL)
    ,(3,'10-10-2019',20000,NULL)
    ,(3,'10-11-2019',NULL,45000)
    )v(Code,Date,[Debit Amount],[Credit Amount])
    ;
    --===== Let''s see what we''ve built.
    SELECT *
    FROM #TestTable
    ORDER BY Code,Date
    ;

    Shift gears to the question that was asked, I believe that the output isn't what you really want because it's NOT true FIFO.  If you have larger Credit Amounts than Debit Amounts, then there will be Credit Amounts that are NEVER consumed.  I strongly recommend that you read the rules that were used for true FIFO in the article at the link that Vedat OZER posted above.

    That, not withstanding, the following code will produce the output you indicated.  If you want the zeros to go away, you'll need to convert the amounts to a string and convert zero-values to strings.  I can't have all the fun on this one so I'll leave that up to you.  I did convert the date to your desired date format of dd/mm/yyyy.  There will be a whole lot of people that will admonish us both for doing ANY formatting in T-SQL and they'll be mostly correct.  Formatting should normally be done in the GUI or in the reporting program you're using.  If the output of a script or stored procedure IS the reporting program, then formatting in T-SQL may be justified.  "It Depends".

       WITH
    cteCreateGrouping AS
    (--==== Create groupings to control the aggregation and bring the rest of the columns forward.
    SELECT *
    ,Grp = COUNT([Credit Amount]) OVER (PARTITION BY Code ORDER BY Date ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
    FROM #TestTable
    )
    ,
    cteAggregate AS
    (--==== These are simple aggregates based on the groupings we made above.
    SELECT Code
    ,Date = MAX(DATE)
    ,[Debit Amount] = ISNULL(SUM([Debit Amount]),0)
    ,[Credit Amount] = ISNULL(SUM([Credit Amount]),0)
    FROM cteCreateGrouping
    GROUP BY Code,Grp
    )--==== This figures out whether we have a an overall debit or a credit and creates the output requested except
    -- that zeros will show up in the "other" column.
    SELECT Code
    ,Date = CONVERT(CHAR(10),Date,103) --Ensure dd/mm/yyyy format
    ,[Debit Amount] = IIF([Debit Amount] >= [Credit Amount], [Debit Amount]-[Credit Amount], 0)
    ,[Credit Amount] = IIF([Credit Amount] >= [Debit Amount], [Credit Amount]-[Debit Amount], 0)
    FROM cteAggregate
    ORDER BY Code,Date
    ;

    Be advised that just like the winning FIFO code in the link, you're going to need some indexes on the source table because there's a substantial sort at the beginning of this code.

     

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply