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.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2005-01-12 : 08:38:16
|
| Paul Rayner writes "The easiest way to describe this is to use a Balance Sheet/Financial Example.I have a table of Balances for Accounts at each end of Month and I wish to convert the data to its equivalent movements. Can this be done using a normal T-SQL script or do I need a CURSOR?There is only one balance for each account per period.I want to refine this later to allow the routine to identify accounts which do or do not move between periods and other tasks.The input table will typically have: PeriodEndDate, AccountId, PeriodEndBalanceand needs to be converted to: PeriodEndDate, AccountId, PeriodMovement" |
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2005-01-12 : 10:47:31
|
| select b.PeriodEndDate, b.Accountid, b.PeriodEndBalance - a.PeriodEndBalancefrom mytable ainner join mytable b on a.AccountId = b.AccountIdwhere b.PeriodEndDate between dateadd(d,a.PeriodEndDate, 30) and dateadd(d,a.PeriodEndDate, 35)order by b.accountid, a.PeriodEndDateshould (??) be along the lines of what you want....you may need to adjust the DATEADD portions to match the min/max lengths of each period |
 |
|
|
PaulRayner
Starting Member
3 Posts |
Posted - 2005-01-13 : 18:29:31
|
| Thanks, I should have seen that one. Not enough exercise in the 'SQL Center' of my brain.The table should have been designed and normalised a bit possibly having a 'Period Counter' with an FK to the actual date, which would have made this a lot easier and quicker and reduced the WHERE clause to b.PeriodEndCounter = a.PeriodEndCounter - 1Hindsight is a wonderful thing, plus I'm just glad it wasn't me who designed the Application/Database |
|
|
|
|
|
|
|
|
Converting Absolute Values to Movement Values