1. We have moved from vBulletin to XenForo and you are viewing the site in the middle of the move. Though the functional aspect of everything is working fine, we are still working on other changes including the new design on Xenforo.
    Dismiss Notice

How to create an aggregated field

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|GL_Name_VC |    Amount    |Period_TI|Year_SI
    ===================================================
      1000|  Inventory|   8,000.00   |  01     |  2005
    ===================================================
      1000|  Inventory|  -3,000.00   |  02     |  2005
    ===================================================
      1000|  Inventory|   5,000.00   |  02     |  2005
    ===================================================
     
    the fields above have the following datatype:

    Code:
     Fields        | Datatype                 
     ===================================
     GL_ID         | Integer            
     GL_Name_VC    | Variable Character
     Amount        | Integer
     Period_TI     | TinyInteger
     Year_SI       | SmallInteger
    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:
    GL_ID  | GL_Name_VC |Op Bal|Period_Dr|Period_Cr|Period Bal|Closing Bal
    ======================================================================
    1000   | Inventory  |8,000 |  5,000  | -3,000  |  2,000   |10,000
    The above report has an Op Bal column which is the sum of all amount in Period 01 in
    Year 2005 carried forward as opening balance in Period 02, Period_Dr Column would contain
    all positive amount in Period 02 & Period_Cr Column would contain all negative amount
    in Period 02. Period Bal is the summation of both Period_Dr & Period_Cr and Closing Bal
    column is the summation of Op Bal + Period 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
    Code:
    SELECT GL_ID, GL_Name_VC , 
    SELCT SUM(Amount)  from <TableName> where Period_TI = 1 GROUP BY GL_ID as Op Bal, 
    SELCT SUM(Amount)  from <TableName> where Period_TI = 2 AND Amount >0 GROUP BY GL_ID as Period_Dr, 
    SELCT SUM(Amount)  from <TableName> where Period_TI = 2 AND Amount <0 GROUP BY GL_ID as Period_Cr, 
    (Debit + Credit) as Period Bal, (OpBal + Period Bal) as Closing Bal
    FROM <tablename>
    GROUP BY GL_ID
    
     

Share This Page