June 4, 2003 at 2:19 am
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
June 4, 2003 at 2:29 am
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]
June 4, 2003 at 2:32 am
Hi Frank,
It is for reporting purpose.
Anbu
June 4, 2003 at 3:31 am
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]
June 4, 2003 at 3:54 am
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]
June 4, 2003 at 8:38 pm
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
June 5, 2003 at 12:45 am
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
June 5, 2003 at 8:59 am
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
June 10, 2003 at 10:43 am
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.
June 11, 2003 at 6:28 am
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