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

How to group by date

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|  Software|    2,000.00   |  01     |  2005
    ===================================================
      1000|  Software|    2,000.00   |  01     |  2005
    ===================================================
      1000|  Software|    5,000.00   |  02     |  2005
    ===================================================
      1000|  Software|    5,000.00   |  02     |  2005
    ==================================================
      1001|  Hardware|   10,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:
    Description      | Hardware  |  Software  | Total
    ====================================================
    Opening Balance  |    0.00   |  4,000.00  | 4,000.00
    Period 02        | 10,000.00 | 10,000.00  |20,000.00
    ====================================================
    Closing Balance  | 10,000.00 | 14,000.00  |24,000.00
    The above report has 4 columns, with 1st & last column being a calculated field.
    Notice that the Opening Balance description is a sum of all amount under period 01,
    with software having a total Opening Balance of 4,000.00 (2,000.00 + 2,000.00).
    The amount for both Hardware & software are group by period 02, and a Closing Balance
    is derived by adding the sum of Period 01 & Period 02.Total column is a summation of
    both Hardware & Software amount.

    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
    Reply to [thread=710]How to split a field into two fields[/thread] will solve this query as well.
     

Share This Page