Results 1 to 5 of 5

Thread: How to group by date

  1. #1
    Join Date
    Apr 2006
    Posts
    11

    How to group by date

    I have the following fields in table A:

    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:

    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:

    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. #2
    Join Date
    Dec 2004
    Posts
    502
    There's a couple different ways to do this. For the totalling, you can use SQL Server's COMPUTE function:

    SELECT
    CASE Period_TI WHEN '01' THEN 'Opening Balance'
    ELSE 'Period ' + Period_TI END AS [Description],
    SUM(CASE WHEN GL_Name_VC = 'Hardware' THEN Amount ELSE 0 END) AS [Hardware],
    SUM(CASE WHEN GL_Name_VC = 'Software' THEN Amount ELSE 0 END) AS [Software],
    SUM(Amount) AS [Total]
    FROM TableA
    GROUP BY CASE Period_TI WHEN '01' THEN 'Opening Balance'
    ELSE 'Period ' + Period_TI END
    COMPUTE SUM(SUM(CASE WHEN GL_Name_VC = 'Hardware' THEN Amount ELSE 0 END)),
    SUM(SUM(CASE WHEN GL_Name_VC = 'Software' THEN Amount ELSE 0 END)),
    SUM(SUM(Amount))


    If you don't like the way that looks, you can also do this:

    SELECT
    CASE Period_TI WHEN '01' THEN 'Opening Balance'
    ELSE 'Period ' + Period_TI END AS [Description],
    SUM(CASE WHEN GL_Name_VC = 'Hardware' THEN Amount ELSE 0 END) AS [Hardware],
    SUM(CASE WHEN GL_Name_VC = 'Software' THEN Amount ELSE 0 END) AS [Software],
    SUM(Amount) AS [Total]
    FROM TableA
    GROUP BY CASE Period_TI WHEN '01' THEN 'Opening Balance'
    ELSE 'Period ' + Period_TI END

    UNION ALL

    SELECT 'Closing Balance', SUM(A.Hardware), SUM(A.Software), SUM(A.Total)
    FROM
    (SELECT
    CASE Period_TI WHEN '01' THEN 'Opening Balance'
    ELSE 'Period ' + Period_TI END AS [Description],
    SUM(CASE WHEN GL_Name_VC = 'Hardware' THEN Amount ELSE 0 END) AS [Hardware],
    SUM(CASE WHEN GL_Name_VC = 'Software' THEN Amount ELSE 0 END) AS [Software],
    SUM(Amount) AS [Total]
    FROM TableA
    GROUP BY CASE Period_TI WHEN '01' THEN 'Opening Balance'
    ELSE 'Period ' + Period_TI END) AS A

    However, this way doesn't necessarily guarantee that all the rows will order properly. That would take additional coding. SQL isn't really designed for report formatting.

  3. #3
    Join Date
    Apr 2006
    Posts
    11
    Hi Nosepicker,

    Thanks for your kind reply, my requirement is almost met except that
    there are no value reflected under both of your sql command in which
    i have tested, below are there output after running the script under
    the

  4. #4
    Join Date
    Apr 2006
    Posts
    11
    SQL Query Analyser:

    Output for the second sql command:

    Description Hardware Software Total
    ----------------- ----------- ----------- -----------
    Opening Balance 0 0 4000
    Period 2 0 0 20000
    Closing Balance 0 0 24000

    I noticed that CASE statement will always return 0 if a searched datatype
    of a field is not a numeric datatype, is the CASE statement only works with
    numeric datatype?

  5. #5
    Join Date
    Apr 2006
    Posts
    11

    Log in to this website for more info


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •