-
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?
-
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
-
Forum Rules
|
|