April 11, 2006 at 11:57 pm
I have the following fields in table A:
GL_ID|GL_Name_VC| Amount |Period_TI|Year_SI|
===================================================
1000| Sales_HW| -20,000.00 | 01 | 2005
===================================================
1000| Sales_SW| -10,000.00 | 01 | 2005
===================================================
1001| Cost_HW | 5,000.00 | 01 | 2005
===================================================
1001| Cost_SW | 5,000.00 | 01 | 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:
Sales Category | Sales | Cost | Profit
=================================================
HW |-20,000.00 |5,000.00| -15,000.00
SW |-10,000.00 |5,000.00| -5,000.00
=================================================
Total |-30,000.00 |10,000.00|-20,000.00
The above report have 4 columns, with last column being a calculated field (Sales-Cost)
Guys, hope someone out there can help me with the sql command for the above report?
April 12, 2006 at 6:24 am
OK, I'm making an assumption here that the last two letters of the GL_Name_VC will always uniquely identify the Sales Category. If that is not the case, then you will have to do more work with identifying the substrings you want.
SELECT RIGHT(A.GL_Name_VC,2) AS [Sales Category],
SUM(CASE LEFT(A.GL_Name_VC,4) WHEN 'Sale' THEN A.Amount ELSE 0 END) AS Sales,
SUM(CASE LEFT(A.GL_Name_VC,4) WHEN 'Cost' THEN A.Amount ELSE 0 END) AS Cost,
SUM(A.Amount) AS Profit
FROM A
GROUP BY RIGHT(A.GL_Name_VC,2)
As before, you'll want to save generating the Total line for your reporting application.
April 13, 2006 at 6:23 pm
Hi Marshall,
Thanks for your kind reply, i've tested the script, but it seems that no value are reflected under the Sales & Cost column except for the Profit
column that reflected some figures accurately, any idea?
Below is the output after running the script:
| Sales Category | Sales | Cost | Profit |
| HW | 0 | 0 | -20000 |
| SW | 0 | 0 | -10000 |
| W | 0 | 0 | 10000 |
April 14, 2006 at 5:43 am
Are you sure that the first four characters are 'Sale' and 'Cost' respectively? If they aren't, then your CASE statement will always return 0.
If that's not the problem, I'm not sure what it would be.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply