Previous date's value

  • Hi,

    I have a table where I have transaction date, ending balance, begining balance. It has to be so that the ending balance of the previous day has to be the begining balance of the next day. Do anyone have any idea how to do this in a T-SQL statement. Please respond ASAP. It is really urgent issue for me.

    Thanks and regards,

    Anbu

  • Hi Anbu,

    do you want this for reporting purposes, or do you want to add a new row?

    Cheers,

    Frank

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • Hi Frank,

    It is for reporting purpose.

    Anbu

  • Hi Anbu,

    sorry for being late.

    If you need a quick working solution, create a temp table and push everything you need into that table instead of wasting time figuring more or less complex SQL statements.

    Cheers,

    Frank

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • Oops,

    I hit ENTER too early.

    Maybe something like this will work

    SELECT a.transaction_date, a.ending_balance, b.beginning_balance FROM table a INNER JOIN table b ON a.transaction_date = b.transaction_date.

    Haven't verified this!

    Cheers,

    Frank

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • very simple one .. use self join and take from one starting balance and from another ending balance

    Cheers

    AMIT KULSHRESTHA

    SOFTWARE ENGINEER

    NEW DELHI,INDIA


    AMIT KULSHRESTHA
    SOFTWARE ENGINEER
    NEW DELHI,INDIA

  • Anbu.

    I frequently have to do this type of query.

    What i do is to create a temp table with an identity column.

    Add required rows ordering by key columns and date.

    Do Left join to self on key columns and tab1.id - 1 = tab2.id( retrieving previous day )

    usually quite fast

    Anzio

  • Just a thought from a rattled brain but...

    If you dont need to see the previous days balance prior to doing an insert or update, then why not just throw a trigger on the table that gets the highest value from the previous days balance, and updates the column for you during an insertion or update.

    If you do need to see it prior to doing an insert or update, then perhaps write a proc to make an insert of the initial record into the table carrying over the previous days balance into the column and then schedule it to run once per day at perhaps 11:59:00 PM or something.

    Rick Brown


    Rick Brown

  • Why don't you just throw away the starting balance and only keep the ending balance in the table? Keeping redundant data like this is almost asking for inconsistencies to develop.

  • If all the dates are consecutive then join the table to itself with date-1.

    If the dates are not consecutive then join the table to itself with max(date) < date

    If the dates are not consecutive and you want an entry for all dates between a range then create a table containing all the required dates and join the other table with max(date) < date

    Far away is close at hand in the images of elsewhere.
    Anon.

Viewing 10 posts - 1 through 9 (of 9 total)

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