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?

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.

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

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?

http://www.accountingdatabasedesign.blogspot.com/