Sproc calcs correctly, then incorrectly

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)

Sproc calcs correctly, then incorrectly

Author

Topic

kirkeby
Yak Posting Veteran

57 Posts

Posted - 2001-06-26 : 14:21:33

What would cause a stored procedure to calculate correctly on some records and incorrectly on others? The records have the same type data. The line that is giving me fits is:

SUM(CASE WHEN R.Billable = 1 AND Sched_Schedule.Hours IS NOT NULL THEN (Sched_Schedule.Hours / 8) * 400 ELSE 0 END) AS BillableLabCharge,

Here it is in its entirety:

CREATE PROCEDURE usp_RptAllChargesByDate
(
@StartDate smalldatetime,
@EndDate smalldatetime
)
As
set nocount on
SELECT DISTINCT
R.Request_ID AS RequestID,
MIN(R.Request_Type) AS RequestType,
SUM(CASE WHEN R.Billable = 1 AND RCT.FixedCharge = 0 THEN RCT.TaskHours ELSE 0 END) AS BillableHours,
SUM(CASE WHEN R.Billable = 1 AND RCT.FixedCharge = 0 THEN RCT.TaskHours * 165 ELSE 0 END) AS BillableHoursCharge,
SUM(CASE WHEN R.Billable = 1 AND RCT.FixedCharge = 1 THEN RCT.TaskHours ELSE 0 END) AS BillableFixedCharge,
SUM(CASE WHEN R.Billable = 1 AND Sched_Schedule.Hours IS NOT NULL THEN (Sched_Schedule.Hours / 8) * 400 ELSE 0 END) AS BillableLabCharge,
SUM(CASE R.Billable WHEN 1 THEN (CASE RCT.FixedCharge WHEN 0 THEN (CASE WHEN Sched_Schedule.Hours IS NOT NULL
THEN ((RCT.TaskHours * 165) + (Sched_Schedule.Hours / 8) * 400) ELSE (RCT.TaskHours * 165) END) WHEN 1 THEN
(CASE WHEN Sched_Schedule.Hours IS NOT NULL THEN (RCT.TaskHours + (Sched_Schedule.Hours / 8) * 400)
ELSE RCT.TaskHours END) END) WHEN 0 THEN 0 END) AS BillableTotalCharge,
SUM(CASE WHEN R.Billable = 0 AND RCT.FixedCharge = 0 THEN RCT.TaskHours ELSE 0 END) AS NonBillableHours,
SUM(CASE WHEN R.Billable = 0 AND RCT.FixedCharge = 0 THEN RCT.TaskHours * 165 ELSE 0 END) AS NonBillableHoursCharge,
SUM(CASE WHEN R.Billable = 0 AND RCT.FixedCharge = 1 THEN RCT.TaskHours ELSE 0 END) AS NonBillableFixedCharge,
SUM(CASE WHEN R.Billable = 0 AND Sched_Schedule.Hours IS NOT NULL THEN (Sched_Schedule.Hours / 8) * 400 ELSE 0 END) AS NonBillableLabCharge,
SUM(CASE R.Billable WHEN 0 THEN (CASE RCT.FixedCharge WHEN 0 THEN (CASE WHEN Sched_Schedule.Hours IS NOT NULL
THEN ((RCT.TaskHours * 165) + (Sched_Schedule.Hours / 8) * 400) ELSE (RCT.TaskHours * 165) END) WHEN 1 THEN
(CASE WHEN Sched_Schedule.Hours IS NOT NULL THEN (RCT.TaskHours + (Sched_Schedule.Hours / 8) * 400)
ELSE RCT.TaskHours END) END) WHEN 1 THEN 0 END) AS NonBillableTotalCharge
INTO #charges
FROM ReqCommon R JOIN
ReqChargeTime RCT ON
R.Request_ID = RCT.Request_ID LEFT OUTER JOIN
ReqTesting ON
R.Request_ID = ReqTesting.Request_ID LEFT OUTER JOIN
ReqScripting ON
R.Request_ID = ReqScripting.Request_ID LEFT OUTER JOIN
ReqOther ON
R.Request_ID = ReqOther.Request_ID LEFT OUTER JOIN
ReqWTS ON
R.Request_ID = ReqWTS.Request_ID LEFT OUTER JOIN
Sched_Schedule ON
ReqTesting.ScheduleID = Sched_Schedule.ScheduleID
WHERE R.Request_Date >= @StartDate AND R.Request_Date <= @EndDate
GROUP BY R.Request_ID
HAVING COUNT(RCT.Request_ID) >= 1

SELECT RequestID AS [Request ID],
BillableHours AS [Bill. Hrs],
BillableHoursCharge AS [Bill. Hrs Chg],
BillableLabCharge AS [Bill. Lab Chg],
BillableFixedCharge AS [Bill. Fixed Chg],
BillableTotalCharge AS [Bill. Total Chg],
NonBillableHours AS [NB Hrs],
NonBillableHoursCharge AS [NB Hrs Chg],
NonBillableLabCharge AS [NB Lab Chg],
NonBillableFixedCharge AS [NB Fixed Chg],
NonBillableTotalCharge AS [NB Total Chg]
FROM #charges
ORDER BY [Request ID]
DROP TABLE #charges

When running the report from an ASP page calling the stored procedure, for any records with a billable lab charge (and I assume the non-billable lab charge fails as well), on one record where 8 hours in the lab (=one day, $400 charge), it will calculate correctly as $400, but on the next that shows 8 hours in the lab, it will calculate $1600 or $2000. It's very strange that if it's going to calc it incorrectly, that it doesn't do it on every record with a lab charge..... Thanks for any input!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources