Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2001-04-25 : 19:00:45
|
Lisa writes "Win2000, SQL Server 7.0 with latest SP's.
Is it possible to perform an aggregate function conditionally? For example, can you build a select statement as follows but do the aggregate CASE statements based on a condition:
SELECT DISTINCT R.Request_ID AS [Request ID],
(CASE R.Request_Type WHEN 'S' THEN ReqScripting.Product_Name
+ ' ' + ReqScripting.Product_Version WHEN 'T' THEN ReqTesting.Product_Name
+ ' ' + ReqTesting.Product_Version WHEN 'O' THEN ReqOther.Project_Description
END) AS Description,
SUM(CASE R.Billable WHEN 1 THEN RC.TaskHours ELSE 0 END) AS [Billable Hours],
SUM(CASE R.Billable WHEN 1 THEN (Sched_Schedule.Hours / 8) * 400 ELSE 0 END) AS [Lab Charge],
SUM(CASE R.Billable WHEN 1 THEN (RC.TaskHours * 165) + ((Sched_Schedule.Hours / 8) * 400) ELSE 0 END) AS [Total Charge]
FROM ......
In the above example, the problem I'm having is that if there is no entry in the Sched_Schedule table for the specific Request_ID, then I'm getting NULL returned for the [Total Charge] instead of RC.TaskHours * 165. I'd like for it to return the TaskHours * 165 even if there are no Sched_Schedule.Hours.
Also, even though I'm specifying "ELSE 0 END", I'm getting 'NULL' instead of '0' in the result set.
Thanks!" |
|
|
|
|
|
Aggregate functions with conditions in Stored Procedures