Go4Expert

Go4Expert (http://www.go4expert.com/)
-   SQL Server (http://www.go4expert.com/forums/sql-server-forum/)
-   -   How to split a field into two fields (http://www.go4expert.com/forums/split-field-fields-t710/)

fdtoo 12Apr2006 06:25

How to split a field into two fields
 
I have the following fields in table A:

Code:

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:

Code:

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:

Code:

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?

coderzone 12Apr2006 15:00

Re: How to split a field into two fields
 
Here is the query
Code: SQL

SELECT Period, Date dt1, GL_Name_VC,
(SELECT Amount FROM <TableName> WHERE Amount > 0 AND Period = 1 AND Date = dt1) AS Debit,
(SELECT Amount FROM <TableName> WHERE Amount < 0 AND Period = 1 AND Date = dt1)*-1 AS Credit, (Deb-Cred) AS NetChange, (Bal + NetChange) AS Balance
FROM <TableName>
WHERE Period = 1
GROUP BY dt1


fdtoo 14Apr2006 06:35

Re: How to split a field into two fields
 
Hi Coderzone

Thanks for the help, but, there seems to be some problem here while running the script,
here is the error message from prompted by the sql query analyzer:

Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'dt1'.

Any idea what's the error here?

coderzone 14Apr2006 07:40

Re: How to split a field into two fields
 
Quote:

Originally Posted by fdtoo
Invalid column name 'dt1'.

Looking at the first line will tell you whats dt1. It should be like this
Code: SQL

SELECT Period, Date AS dt1, GL_Name_VC,

see the as between Date and dt1


All times are GMT +5.5. The time now is 18:35.