SQL Server Performance Forum – Threads Archive

How to split a field into two fields

I have the following fields in table A: GL_ID| Date |GL_Name_VC | Amount |Period_TI|Year_SI
===============================================================
1000|31/12/2005 | Sales | -8,000.00 | 12 | 2005
===============================================================
1000|06/01/2006 | Sales | -6,000.00 | 01 | 2006
===============================================================
1000|20/01/2006 | Sales | 2,000.00 | 01 | 2006
===============================================================
1000|28/01/2006 | Sales | -4,000.00 | 01 | 2006 The above database is running on Microsoft SQL Server 2000 and i would like to query
for a report that looks something as below: Period | Date | GL_Name_VC | Debit | Credit| Net Change | Balance
=====================================================================
01 |01/01/2006|Opening Bal | 0 | 0 | 0 | 8,000
01 |06/01/2006|Sales | 0 | 6,000 | 0 | 0
01 |20/01/2006|Sales | 2,000 | 0 | 0 | 0
01 |28/01/2006|Sales | 0 | 4,000 | 8,000 |16,000
The formula for the above calculated fields are as below: Opening Balance = carried forward balance from Year 2005
Debit = All positive amount
Credit = All negative amount
Net Change = Total Credit – Total Debit in Period 01
Balance = Total of Net Change + Opening Bal Guys, hope someone out there can help me with the sql command for the above report?

You need to make union of two select statements, one calculating opening balance and another displaying calculations for the current year. Take a look at select statement and union topic in Books On-Line.

Referhttp://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=13887 too for further discussion. Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

]]>