SQL – Row manipulation - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

SQL – Row manipulation

Author

Topic

carlmack
Starting Member

2 Posts

Posted - 2005-10-20 : 09:44:22

I am relatively new to SQL Server but have used Access before

I am working on a financial integration system that receives lists of financial journal transactions from several accounting packages.

I import and sumarise the transactions and end up with a transactions table like the one below. I now want to produce an Accounts summary (by period) table. I am trying to figure out how to do this just using SQL.

I do not want to use record by record VB because it is slow and I have a million+ records.

Can anybody help ? Is it possible ?

example

Transactions Table (input)

Account Period Amount
101 1 100
101 5 100

Account Summary (desired output table)

Account Period Opening Movement Closing
101 1 0 100 100
101 2 100 0 100
101 3 100 0 100
101 4 100 0 100
101 5 100 100 200

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-10-20 : 10:31:26

shouldn't
101 5 100
be
101 5 200
in your transactions table?

Go with the flow & have fun! Else fight the flow

carlmack
Starting Member

2 Posts

Posted - 2005-10-20 : 12:10:44

For Period 5. The opening balance on the account would be 100, the movement would be another 100 (transaction 2) and the Closing Balance 200.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources