SP not returning Distinct recs w/aggregate func

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.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

SP not returning Distinct recs w/aggregate func

Author

Topic

kirkeby
Yak Posting Veteran

57 Posts

Posted - 2001-04-24 : 10:25:12

Could someone please tell me why this SP is not returning distinct records on R.Request_ID? It's returning each entry instead of summing all entries for each Request_ID. Thanks!

CREATE Procedure usp_RptByRequest
(
@StartDate smalldatetime,
@EndDate smalldatetime
)
As
set nocount on
SELECT DISTINCT R.Request_ID AS [Request ID],
SUM(CASE WHEN R.Billable = 1 THEN s.TaskHours ELSE '0' END) AS [Billable Hours],
SUM(CASE WHEN R.Billable = 1 AND Sched_Schedule.Hours IS NOT NULL THEN (Sched_Schedule.Hours / 8) * 400 ELSE '0' END) AS [Lab Charge],
SUM(CASE WHEN R.Billable = 1 AND Sched_Schedule.Hours IS NOT NULL THEN ((s.TaskHours * 165) + (Sched_Schedule.Hours / 8) * 400) ELSE (s.TaskHours * 165) END) AS [Total Charge],
SUM(CASE WHEN R.Billable = 0 THEN s.TaskHours ELSE '0' END) AS [Non-Billable Hours],
SUM(CASE WHEN R.Billable = 0 THEN (s.TaskHours * 165) ELSE '0' END) AS [Non-Billable Charge]
FROM
(SELECT * FROM ReqChargeTime
WHERE (TaskDate >= @StartDate AND TaskDate < @EndDate)
OR (TaskDate < @StartDate AND TaskDate >= @EndDate)) s
JOIN ReqCommon R ON s.Request_ID = R.Request_ID
LEFT JOIN ReqTesting ON R.Request_ID = ReqTesting.Request_ID
LEFT JOIN Sched_Schedule ON ReqTesting.ScheduleID = Sched_Schedule.ScheduleID
GROUP BY R.Request_ID, ReqTesting.ScheduleID, Sched_Schedule.ScheduleID, s.TaskHours, Sched_Schedule.Hours
ORDER by R.Request_ID
return

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources