Getting MAX value

  • Good morning,

    I have a table of transactions that includes an integer column called GL_Control.

    The control number is assigned sequentially within Fiscal_Year and Fiscal_Period for each transaction.

    When I insert a batch of new transactions I set GL_Control=0. This is because a transaction may be the first for it's Fiscal_Year and Fiscal_Period.

    I then want to go back and update the table setting

    GL_Control = (MAX(GL_Control) + 1) for its Fiscal_Year and Fiscal_Period WHERE GL_Control = 0

    So that if a transaction was the first in it's Fiscal_Year and Fiscal_Period it would get GL_Control = 1.  If it was the third it would get GL_Control = 3 and so on.

    Thoughts on how to accomplish this?

    Thanks you

    jmatt

  • when you insert the new batch instead of setting the

    GL_Control=0

    set it to = 1 then others will sequentially be in the correct order

    otherwise you could

    Update Yourtable

    set GL_Control= GL_Control + 1

    Where Fiscal_Year = fiscal_year_just loaded

    and Fiscal_Period = fiscal_preiod just loaded

    There are 10 types of people in this world - those that understand binary and those that don't

  • primary key, you have?

    Important it is! Without, leads to the dark side.

    Yoda will assume it is called "ID" for his example ... more meaningful, Yoda hopes yours is! hmm?

    Consider:

    UDPDATE TABLE
    YOurTable
    SET GL_Control = (SELECT COUNT(*) FROM YourTable B
                      WHERE B.Fiscal_Year = YOurTable.fiscal_year
                        and B.Fiscal_Period = YOurTable.fiscal_period
                        and B.ID <= YourTable .ID)
    WHERE Fiscal_Year = @fiscal_year
      and Fiscal_Period = @fiscal_period
    

    Presentation, often a sequence column is. Store only if necessary. Not usually a job for SQL! A true jedi will not attempt to add uncessary data to his tables! leads to the dark side, it does. Trivial to implement on web page or on report.

    If required, however, so be it. Use the technique Yoda has demonstrated, and grow to be strong with the force you will .

  • My goodness, I never thought I would have the chance to speak to the actual Master.

    Thank you for your guidance;  I need time to ponder, as it is quite deep.

    jmatt

  • hmm.

    A set-based solution, that is not. One insert required per row. One step it is not! Rather X steps! Requirements of data, depends on this does. X may be much be greater than 2, hm?

    Repeated inserts as opposed to 1 single insert and 1 single update, a jedi master would never do! Set-based, it must be. To use loops or cursors is to venture towards the dark side!

  • Your answer a little you did change, hm?

    Yoda's first post in this thread, quietly incorporated the concept, you did? Very wise, indeed.

    Though not acknowledging the error of your first post -- that is not the way of jedi.

Viewing 6 posts - 1 through 5 (of 5 total)

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