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:
    736
    Likes Received:
    38
    Trophy Points:
    28
    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:
    736
    Likes Received:
    38
    Trophy Points:
    28
    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

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice