Calculation help - 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 2005 Forums

Transact-SQL (2005)

Calculation help

Author

Topic

jcb267
Constraint Violating Yak Guru

291 Posts

Posted - 2008-12-04 : 17:31:41

I am working with a database in SQL 2005 and I do not have the rights to create views. I have used the equation (below)c.previous_balance + c.payments_amount to calculate overpayments.

SELECT e.rid, SUBSTRING(a.acctNumber, 12, 10) AS Member_Acct, e.firstName, e.lastName, c.plan_type, c.mco, c.dateCreated, c.past_due_amount AS Unpaid_Balance, c.previous_balance + c.payments_amount AS Overpayments
FROM employee AS e INNER JOIN
account AS a ON e.account = a.id INNER JOIN chargeable_invoice AS c ON a.id = c.account_id AND c.dateCreated > '2008-10-31 23:59:59' AND c.dateCreated < '2008-12-01'
ORDER BY c.dateCreated

However, the previous balance number in my database is not accurate. Instead, what I need to do is calculate the previous balance in my query like this:

Previous_Balance = (c.new_charge_amount+past_due_amount) when the invoice date is invoice sent out prior to the current month and year. In this case, I am looking at AR related to Novemberm 2008 so I would like to calculate the Previous_Balance for October 2008.

Without the ability to create views, I have to do the calculation within the current query.

I do not know enough about SQL to do it, can anyone help?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-05 : 02:43:15

show some sample and explain what you want