[fdtoo]

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?

[coderzone]

Here is the query

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]

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]

Quote:

Originally Posted by fdtoo

Invalid column name 'dt1'.

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

SELECT Period, Date AS dt1, GL_Name_VC,

see the as between Date and dt1