1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

How to split a field into two fields

Discussion in 'SQL Server' started by fdtoo, Apr 12, 2006.

  1. fdtoo

    fdtoo New Member

    Joined:
    Apr 12, 2006
    Messages:
    14
    Likes Received:
    0
    Trophy Points:
    0
    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?
     
  2. coderzone

    coderzone Super Moderator

    Joined:
    Jul 25, 2004
    Messages:
    734
    Likes Received:
    37
    Trophy Points:
    0
    Here is the query
    Code:
    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
     
  3. fdtoo

    fdtoo New Member

    Joined:
    Apr 12, 2006
    Messages:
    14
    Likes Received:
    0
    Trophy Points:
    0
    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?
     
  4. coderzone

    coderzone Super Moderator

    Joined:
    Jul 25, 2004
    Messages:
    734
    Likes Received:
    37
    Trophy Points:
    0
    Looking at the first line will tell you whats dt1. It should be like this
    Code:
    SELECT Period, Date as dt1, GL_Name_VC, 
    see the as between Date and dt1
     

Share This Page