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-03-23 : 12:56:21
|
Lisa writes "SQL Server 7.0 (latest service packs)
Windows 2000 5.00.2195
I have created the following stored procedure to do a simple report listing a name, total hours and the corresponding charge for those hours. I need to take it further by putting a condition on it as follows: If the Billable field (bit field) = 1 (true) then give me another column showing those hours and entitle the column 'Billable Hours' and also give me a corresponding column entitled 'Billable Charges', showing that amount. I also want to show 2 more columns--if the Billable field = 0, then give me those hours in a column entitled 'NonBillable Hours' and a corresonding column entitled 'NonBillable Charges', showing that amount. I want to show it all. The problem I'm having is that, as I stated before, Billable is a bit field and it is not allowed in an aggregate function or a Group By clause. UGH! I already have my ASP pages written to print out these beautiful little reports with alternating row colors, etc. and I don't want to re-write all of that because I can't get an IF statement to work in SQL. I also need to know if you can format the output for the charges columns or do you just have to take it as SQL spits it out? Can you help? Thank you!!
CREATE Procedure usp_RptByScripter
(
@StartDate smalldatetime,
@EndDate smalldatetime
)
As
set nocount on
SELECT DISTINCT L.Scripter_Name AS [Scripter Name],
SUM(s.TaskHours) AS [Total Hours],
SUM(s.TaskHours) * 165 AS [Total Charge]
FROM
(SELECT * FROM ReqChargeTime
WHERE (TaskDate >= @StartDate AND TaskDate < @EndDate)
OR
(TaskDate < @StartDate AND TaskDate >= @EndDate)) s
JOIN ReqScripterDef L ON s.Scripter_ID = L.Scripter_ID
LEFT OUTER JOIN ReqCommon R ON s.Request_ID = R.Request_ID
WHERE (L.Active <> '0')
GROUP BY L.Scripter_Name
ORDER by L.Scripter_Name
return" |
|
|
|
|
|
Stored Procedures w/Aggregate Functions and Conditions