User Defined Function

[jnavarro]

Can someone tell me what is wrong with this function. I am receiving an error near begin on line 200. I have display part of my code.

CREATE FUNCTION dbo.fn_MonthlyUpdatesCalculation (@StartDate datetime, @EndDate datetime)
RETURNS TABLE
AS
BEGIN
DECLARE @MinAudits int, @WorkDays int
SET @MinAudits = 40
SET @WorkDays =
DATEDIFF("d", @StartDate, @EndDate) / 7 * 5

RETURN(SQL Statement)
END


When I remove the sql statement and run it I receive no error messages.

Thanks

 

[Jamfool]

CREATE FUNCTION dbo.fn_MonthlyUpdatesCalculation (@StartDate datetime, @EndDate datetime)
RETURNS int
AS
BEGIN
    DECLARE @MinAudits int, @WorkDays int
    SET @MinAudits = 40
    SET @WorkDays = 
          DATEDIFF(d, @StartDate, @EndDate) / 7 * 5

       DECLARE @what_you_want_to_return as int

	--?
       set  @what_you_want_to_return = @workdays

       RETURN(@what_you_want_to_return)
END

go

select dbo.fn_MonthlyUpdatesCalculation(getdate(),getdate()+100)

drop FUNCTION dbo.fn_MonthlyUpdatesCalculation

Not quite sure what your function is trying to do.

 

[amrita418]

What are you returning back to the calling program? Is it the days worked?

Regards,
AA

 

[jnavarro]

What I am returning is a table based on the sql statement.

I have created user defined function similiar to this one however, the difference is that I did not declare or set any values. Any idea on what is causing this?

 

[amrita418]

Can you post the code with return(actual value)?

 

[jnavarro]

CREATE FUNCTION dbo.fn_MonthlyUpdatesCalculation (@StartDate datetime, @EndDate datetime)
RETURNS TABLE
AS
BEGIN
DECLARE @MinAudits int, @WorkDays int
SET @MinAudits = 40
SET @WorkDays = DATEDIFF("d", @StartDate, @EndDate) / 7 * 5

RETURN(SELECT dbo.fn_FiscalMonthDate (@StartDate, @EndDate) as FiscalMonth, tblAssocInfo.FirstName, tblAssocInfo.LastName, tblAssocInfo.Dept, tblAssocInfo.Title,
LeadStats.TAPercentage, CASE WHEN LeadStats.TAPercentage >= tblMonthlyScale.TurnAroundO AND LeadStats.TurnAroundRate = 5 THEN 5 WHEN LeadStats.TAPercentage >= tblMonthlyScale.TurnAroundV THEN 4 WHEN LeadStats.TAPercentage >= tblMonthlyScale.TurnAroundE THEN 3 WHEN LeadStats.TAPercentage >= tblMonthlyScale.TurnAroundM THEN 2 WHEN LeadStats.TAPercentage <= tblMonthlyScale.TurnAroundU THEN 1 ELSE 0 END AS TurnAroundRating,
LeadStats.ErrorPercentage, CASE WHEN LeadStats.ErrorPercentage >= tblMonthlyScale.ErrorRateO AND LeadStats.ErrorRate = 5 THEN 5 WHEN LeadStats.ErrorPercentage >= tblMonthlyScale.ErrorRateV THEN 4 WHEN LeadStats.ErrorPercentage >= tblMonthlyScale.ErrorRateE THEN 3 WHEN LeadStats.ErrorPercentage >= tblMonthlyScale.ErrorRateM THEN 2 WHEN LeadStats.ErrorPercentage >= tblMonthlyScale.ErrorRateU THEN 1 ELSE 0 END AS ErrorRating,
--Audit Rating
CASE WHEN LeadStats.NumAssoc * @MinAudits = 0 OR LeadStats.numAudits = 0 THEN 0 ELSE LeadStats.NumAudits / CONVERT(DECIMAL(6,2),LeadStats.NumAssoc * @MinAudits) END AS AuditPercent,
CASE WHEN LeadStats.NumAssoc * @MinAudits = 0 OR LeadStats.numAudits = 0 THEN 0 ELSE
CASE WHEN LeadStats.ExtraAudits = LeadStats.NumAssoc THEN
CASE WHEN LeadStats.NumAudits / CONVERT(DECIMAL(6,2),LeadStats.NumAssoc * @MinAudits) >= tblMonthlyScale.AuditsRateO THEN 5
WHEN LeadStats.NumAudits / CONVERT(DECIMAL(6,2),LeadStats.NumAssoc * @MinAudits) >= tblMonthlyScale.AuditsRateV THEN 4
ELSE 3
END
WHEN LeadStats.ExtraAudits + LeadStats.MinAudits = LeadStats.NumAssoc THEN
CASE WHEN LeadStats.NumAudits / CONVERT(DECIMAL(6,2),LeadStats.NumAssoc * @MinAudits) >= tblMonthlyScale.AuditsRateO THEN 4
WHEN LeadStats.NumAudits / CONVERT(DECIMAL(6,2),LeadStats.NumAssoc * @MinAudits) >= tblMonthlyScale.AuditsRateV THEN 3
ELSE 2
END
WHEN LeadStats.BelowAudits = 1 THEN
CASE WHEN LeadStats.NumAudits / CONVERT(DECIMAL(6,2),LeadStats.NumAssoc * @MinAudits) >= tblMonthlyScale.AuditsRateO THEN 3
WHEN LeadStats.NumAudits / CONVERT(DECIMAL(6,2),LeadStats.NumAssoc * @MinAudits) >= tblMonthlyScale.AuditsRateV THEN 2
ELSE 1
END
ELSE
CASE WHEN LeadStats.NumAudits / CONVERT(DECIMAL(6,2),LeadStats.NumAssoc * @MinAudits) >= tblMonthlyScale.AuditsRateO THEN 2
WHEN LeadStats.NumAudits / CONVERT(DECIMAL(6,2),LeadStats.NumAssoc * @MinAudits) >= tblMonthlyScale.AuditsRateV THEN 1
ELSE 0
END
END
END AS AuditRate,
LeadStats.TestPercentage, CASE WHEN LeadStats.TestPercentage >= tblMonthlyScale.TestRateO THEN 5 WHEN LeadStats.TestPercentage >= tblMonthlyScale.TestRateV THEN 4 WHEN LeadStats.TestPercentage >= tblMonthlyScale.TestRateE THEN 3 WHEN LeadStats.TestPercentage >= tblMonthlyScale.TestRateM THEN 2 WHEN LeadStats.TestPercentage >= tblMonthlyScale.TestRateU THEN 1 ELSE 0 END AS TestRating,
LeadStats.AUEPoints, CASE WHEN LeadStats.AUEPoints <= tblMonthlyScale.AUERateO THEN 5 WHEN LeadStats.AUEPoints <= tblMonthlyScale.AUERateV THEN 4 WHEN LeadStats.AUEPoints <= tblMonthlyScale.AUERateE THEN 3 WHEN LeadStats.AUEPoints <= tblMonthlyScale.AUERateM THEN 2 WHEN LeadStats.AUEPoints <= tblMonthlyScale.AUERateU THEN 1 ELSE 0 END AS AUERating,
LeadStats.PendingOnHand, CASE WHEN LeadStats.PendingOnHand <= tblMonthlyScale.AvgPedingO THEN 5 WHEN LeadStats.PendingOnHand <= tblMonthlyScale.AvgPedingV THEN 4 WHEN LeadStats.PendingOnHand <= tblMonthlyScale.AvgPedingE THEN 3 WHEN LeadStats.PendingOnHand <= tblMonthlyScale.AvgPedingM THEN 2 WHEN LeadStats.PendingOnHand <= tblMonthlyScale.AvgPedingU THEN 1 ELSE 0 END AS AvgPendingRating,
LeadStats.Advocate,

--Total Rating
(CASE WHEN LeadStats.TAPercentage >= tblMonthlyScale.TurnAroundO AND LeadStats.TurnAroundRate = 5 THEN 5 WHEN LeadStats.TAPercentage >= tblMonthlyScale.TurnAroundV THEN 4 WHEN LeadStats.TAPercentage >= tblMonthlyScale.TurnAroundE THEN 3 WHEN LeadStats.TAPercentage >= tblMonthlyScale.TurnAroundM THEN 2 WHEN LeadStats.TAPercentage <= tblMonthlyScale.TurnAroundU THEN 1 ELSE 0 END * tblMonthlyWeights.TurnAround +
CASE WHEN LeadStats.TAPercentage >= tblMonthlyScale.TurnAroundO AND LeadStats.TurnAroundRate = 5 THEN 5 WHEN LeadStats.TAPercentage >= tblMonthlyScale.TurnAroundV THEN 4 WHEN LeadStats.TAPercentage >= tblMonthlyScale.TurnAroundE THEN 3 WHEN LeadStats.TAPercentage >= tblMonthlyScale.TurnAroundM THEN 2 WHEN LeadStats.TAPercentage <= tblMonthlyScale.TurnAroundU THEN 1 ELSE 0 END * tblMonthlyWeights.ErrorRate +
CASE WHEN LeadStats.NumAssoc * @MinAudits = 0 OR LeadStats.numAudits = 0 THEN 0 ELSE
CASE WHEN LeadStats.ExtraAudits = LeadStats.NumAssoc THEN
CASE WHEN LeadStats.NumAudits / CONVERT(DECIMAL(6,2),LeadStats.NumAssoc * @MinAudits) >= tblMonthlyScale.AuditsRateO THEN 5
WHEN LeadStats.NumAudits / CONVERT(DECIMAL(6,2),LeadStats.NumAssoc * @MinAudits) >= tblMonthlyScale.AuditsRateV THEN 4
ELSE 3
END
WHEN LeadStats.ExtraAudits + LeadStats.MinAudits = LeadStats.NumAssoc THEN
CASE WHEN LeadStats.NumAudits / CONVERT(DECIMAL(6,2),LeadStats.NumAssoc * @MinAudits) >= tblMonthlyScale.AuditsRateO THEN 4
WHEN LeadStats.NumAudits / CONVERT(DECIMAL(6,2),LeadStats.NumAssoc * @MinAudits) >= tblMonthlyScale.AuditsRateV THEN 3
ELSE 2
END
WHEN LeadStats.BelowAudits = 1 THEN
CASE WHEN LeadStats.NumAudits / CONVERT(DECIMAL(6,2),LeadStats.NumAssoc * @MinAudits) >= tblMonthlyScale.AuditsRateO THEN 3
WHEN LeadStats.NumAudits / CONVERT(DECIMAL(6,2),LeadStats.NumAssoc * @MinAudits) >= tblMonthlyScale.AuditsRateV THEN 2
ELSE 1
END
ELSE
CASE WHEN LeadStats.NumAudits / CONVERT(DECIMAL(6,2),LeadStats.NumAssoc * @MinAudits) >= tblMonthlyScale.AuditsRateO THEN 2
WHEN LeadStats.NumAudits / CONVERT(DECIMAL(6,2),LeadStats.NumAssoc * @MinAudits) >= tblMonthlyScale.AuditsRateV THEN 1
ELSE 0
END
END
END * tblMonthlyWeights.Audit +
CASE WHEN LeadStats.TestPercentage >= tblMonthlyScale.TestRateO THEN 5 WHEN LeadStats.TestPercentage >= tblMonthlyScale.TestRateV THEN 4 WHEN LeadStats.TestPercentage >= tblMonthlyScale.TestRateE THEN 3 WHEN LeadStats.TestPercentage >= tblMonthlyScale.TestRateM THEN 2 WHEN LeadStats.TestPercentage >= tblMonthlyScale.TestRateU THEN 1 ELSE 0 END * tblMonthlyWeights.TestRate +
CASE WHEN LeadStats.AUEPoints <= tblMonthlyScale.AUERateO THEN 5 WHEN LeadStats.AUEPoints <= tblMonthlyScale.AUERateV THEN 4 WHEN LeadStats.AUEPoints <= tblMonthlyScale.AUERateE THEN 3 WHEN LeadStats.AUEPoints <= tblMonthlyScale.AUERateM THEN 2 WHEN LeadStats.AUEPoints <= tblMonthlyScale.AUERateU THEN 1 ELSE 0 END * tblMonthlyWeights.AUERate +
CASE WHEN LeadStats.PendingOnHand <= tblMonthlyScale.AvgPedingO THEN 5 WHEN LeadStats.PendingOnHand <= tblMonthlyScale.AvgPedingV THEN 4 WHEN LeadStats.PendingOnHand <= tblMonthlyScale.AvgPedingE THEN 3 WHEN LeadStats.PendingOnHand <= tblMonthlyScale.AvgPedingM THEN 2 WHEN LeadStats.PendingOnHand <= tblMonthlyScale.AvgPedingU THEN 1 ELSE 0 END * tblMonthlyWeights.AvgPending +
LeadStats.Advocate * tblMonthlyWeights.Advocate) AS TotalRating
FROM tblAssocInfo INNER JOIN
(SELECT TurnAround.SupID AS LeadID,
CASE WHEN TurnAround.TurnAroundEffective IS NULL OR TurnAround.TurnAroundEffective = 0 THEN 0 ELSE (CONVERT(DECIMAL(3,2),TurnAround.TurnAroundEffective) / CONVERT(DECIMAL(3,2),TurnAround.TATotalCount)) END AS TAPercentage,
CASE WHEN ErrorRate.ErrorEffective IS NULL OR ErrorRate.ErrorEffective = 0 THEN 0 ELSE (CONVERT(DECIMAL(3,2),ErrorRate.ErrorEffective) / CONVERT(DECIMAL(3,2),ErrorRate.ErrorTotalCount)) END AS ErrorPercentage,
CASE WHEN TestPercentage.TestPossible IS NULL THEN 0 ELSE CASE WHEN TestPercentage.TestAchieve IS NULL THEN 0 ELSE CONVERT(DECIMAL(3,2),TestPercentage.TestAchieve) END / TestPercentage.TestPossible END AS TestPercentage,
CASE WHEN AuditSummary.NumAssoc IS NULL THEN 0 ELSE AuditSummary.NumAssoc END AS NumAssoc,
CASE WHEN AuditSummary.NumAudits IS NULL THEN 0 ELSE AuditSummary.NumAudits END AS NumAudits,
CASE WHEN AuditSummary.AuditsBelow IS NULL THEN 0 ELSE AuditSummary.AuditsBelow END AS BelowAudits,
CASE WHEN AuditSummary.AuditsRequired IS NULL THEN 0 ELSE AuditSummary.AuditsRequired END AS MinAudits,
CASE WHEN AuditSummary.AuditsExtra IS NULL THEN 0 ELSE AuditSummary.AuditsExtra END AS ExtraAudits,
CASE WHEN Attendance.UnexcusedPoints IS NULL THEN 0 ELSE Attendance.UnexcusedPoints END AS AUEPoints,
CASE WHEN AdvocateScore.Advocate IS NULL THEN 0 ELSE AdvocateScore.Advocate END AS Advocate,
AveragePending.PendingOnHand, PreviousLeadStats.TurnAroundRate ,PreviousLeadStats.ErrorRate
FROM (
--Turn Around Time
(SELECT dbo.fn_FiscalMonthDate (@StartDate, @EndDate) as FiscalMonth, TATotal.SupID, TATotal.TATotalCount,CASE WHEN TAECount.TAEffectiveCount IS NULL THEN 0 ELSE TAECount.TAEffectiveCount END AS TurnAroundEffective
FROM ((SELECT tblMonthlyStats.FiscalMonth, tblMonthlyStats.SupID, COUNT(TurnAroundRate) AS TATotalCount
FROM tblMonthlyStats
WHERE tblMonthlyStats.FiscalMonth = dbo.fn_FiscalMonthDate (@StartDate, @EndDate)
GROUP BY tblMonthlyStats.FiscalMonth, tblMonthlyStats.SupID) AS TATotal

LEFT OUTER JOIN
(SELECT tblMonthlyStats.FiscalMonth, tblMonthlyStats.SupID, COUNT(TurnAroundRate) AS TAEffectiveCount
FROM tblMonthlyStats
WHERE TurnAroundRate >= 3 AND tblMonthlyStats.FiscalMonth = dbo.fn_FiscalMonthDate (@StartDate, @EndDate)
GROUP BY tblMonthlyStats.FiscalMonth, tblMonthlyStats.SupID) AS TAECount ON TAECount.SupID = TATotal.SupID)) AS TurnAround

LEFT OUTER JOIN
(SELECT tblMonthlyStats.AssocNum, tblMonthlyStats.TurnAroundRate, tblMonthlyStats.ErrorRate
FROM tblMonthlyStats
WHERE tblMonthlyStats.FiscalMonth = dbo.fn_FiscalMonthDate(DateAdd("d",-35, @StartDate), DateAdd("d",-1, @StartDate)) AND tblMonthlyStats.Title in('LD','SLD')) AS PreviousLeadStats ON PreviousLeadStats.AssocNum = TurnAround.SupID

LEFT OUTER JOIN

--Error Rate
(SELECT ErrorTotal.SupID,ErrorTotal.ErrorTotalCount,CASE WHEN ErrorECount.ErrorEffectiveCount IS NULL THEN 0 ELSE ErrorECount.ErrorEffectiveCount END AS ErrorEffective
FROM (
(SELECT tblMonthlyStats.FiscalMonth, tblMonthlyStats.SupID, COUNT(ErrorRate) AS ErrorTotalCount
FROM tblMonthlyStats
WHERE tblMonthlyStats.FiscalMonth = dbo.fn_FiscalMonthDate (@StartDate, @EndDate)
GROUP BY tblMonthlyStats.FiscalMonth, tblMonthlyStats.SupID) AS ErrorTotal
LEFT OUTER JOIN
(SELECT SupID, COUNT(ErrorRate) AS ErrorEffectiveCount
FROM tblMonthlyStats
WHERE (ErrorRate >= 3 AND tblMonthlyStats.FiscalMonth = dbo.fn_FiscalMonthDate (@StartDate, @EndDate))
GROUP BY tblMonthlyStats.FiscalMonth, tblMonthlyStats.SupID) AS ErrorECount ON ErrorECount.SupID = ErrorTotal.SupID)) AS ErrorRate ON ErrorRate.SupID = TurnAround.SupID)

LEFT OUTER JOIN

--Test Percentage
(SELECT tblMonthlyStats.SupID, SUM(TestInfo.Achieve) AS TestAchieve, MAX(TestInfo.Possible) * Count(tblMonthlyStats.SupID) as TestPossible
FROM tblMonthlyStats
LEFT JOIN (
SELECT tblTestInfo.AssocNum, SUM(PointsAchieve) AS Achieve, SUM(PointsPossible) AS Possible
FROM tblTestInfo
WHERE (tblTestInfo.MediaDate BETWEEN @StartDate AND @EndDate)
GROUP BY AssocNum) AS TestInfo ON tblMonthlyStats.AssocNum = TestInfo.AssocNum
GROUP BY tblMonthlyStats.SupID) AS TestPercentage ON TestPercentage.SupID = TurnAround.SupID

LEFT OUTER JOIN

--Attendance
(SELECT tblAttendanceIncidents.AssocNum, SUM(tblIncidentType.Points) AS UnexcusedPoints
FROM tblAttendanceIncidents INNER JOIN tblIncidentType ON tblAttendanceIncidents.Incident = tblIncidentType.Incident INNER JOIN tblJustificationType ON tblAttendanceIncidents.Justification = tblJustificationType.Justification
WHERE (tblAttendanceIncidents.MediaDate BETWEEN @StartDate AND @EndDate) AND (tblAttendanceIncidents.Justification = 1)
GROUP BY tblAttendanceIncidents.AssocNum) as Attendance ON Attendance.AssocNum = TurnAround.SupID

LEFT OUTER JOIN

--Pending on Hand
(SELECT AdjustmentsOnHand.FiscalMonth, SUM(AdjustmentsOnHand.PendingAdjustments)/ @WorkDays as PendingOnHand
FROM(SELECT dbo.fn_FiscalMonthDate (@StartDate, @EndDate) as FiscalMonth, tblAI.DateAssigned, COUNT(tblAI.DateAssigned) AS PendingAdjustments
FROM tblAI
WHERE (tblAI.DateAssigned BETWEEN @StartDate AND @EndDate) and ((DATEDIFF(d, DateAssigned, DateResolve) > 0) OR (DateResolve IS NULL)) AND NOT (DateAssigned IS NULL)
GROUP BY DateAssigned) AS AdjustmentsOnHand
GROUP BY AdjustmentsOnHand.FiscalMonth) AS AveragePending ON AveragePending.FiscalMonth = TurnAround.FiscalMonth

LEFT OUTER JOIN

--Advocate Score
(SELECT tblAdvocateScore.AssocNum, AVG(Rating) AS Advocate
FROM tblAdvocateScore
WHERE (tblAdvocateScore.MediaDate BETWEEN @StartDate AND @EndDate)
GROUP BY AssocNum) AS AdvocateScore ON AdvocateScore.AssocNum = TurnAround.SupID

LEFT OUTER JOIN

--Audit Information
(SELECT AssocRespond.SupID, AssocRespond.NumAssoc,
CASE WHEN AuditedNumbers.NumAudits IS NULL THEN 0 ELSE AuditedNumbers.NumAudits END AS NumAudits,
CASE WHEN BelowAudit.Below IS NULL THEN 0 ELSE BelowAudit.Below END AS AuditsBelow,
CASE WHEN RequiredAudit.Required IS NULL THEN 0 ELSE RequiredAudit.Required END AS AuditsRequired,
CASE WHEN ExtraAudit.Extra IS NULL THEN 0 ELSE ExtraAudit.Extra END AS AuditsExtra
FROM (
--Number of Associates
(SELECT tblAssocReportTo.SupID, Count(tblAssocReportTo.SupID) AS NumAssoc
FROM tblAssocReportTo INNER JOIN tblAssocInfo ON tblAssocReportTo.AssocNum = tblAssocInfo.AssocNum
WHERE tblAssocInfo.Status in ('F','P')
GROUP BY tblAssocReportTo.SupID) AS AssocRespond

LEFT OUTER JOIN
--Number of Audits Completed
(SELECT tblAI.AuditedBy, Count(tblAI.AssocNum) AS NumAudits
FROM tblAI INNER JOIN tblAssocReportTo ON tblAI.AssocNum = tblAssocReportTo.AssocNum AND tblAssocReportTo.SupID = tblAI.AuditedBy
WHERE NOT(AuditedBy IS NULL) AND tblAI.DateAudited BETWEEN @StartDate and @EndDate
GROUP BY tblAI.AuditedBy) AS AuditedNumbers ON AssocRespond.SupID = AuditedNumbers.AuditedBY

LEFT OUTER JOIN
--Below
(SELECT tblAssocReportTo.SupID AS AuditedBy, COUNT(tblAssocReportTo.AssocNum) as Below
FROM tblAssocReportTo LEFT OUTER JOIN (
SELECT tblAI.AssocNum, Count(tblAI.AssocNum) AS NumAudits
FROM tblAssocReportTo INNER JOIN tblAI on tblAssocReportTo.AssocNum = tblAI.AssocNum AND tblAssocReportTo.SupID = tblAI.AuditedBy INNER JOIN tblAssocInfo ON tblAssocReportTo.AssocNum = tblAssocInfo.AssocNum
WHERE NOT(AuditedBy IS NULL) AND (tblAI.DateAudited BETWEEN @StartDate AND @EndDate) AND (tblAssocInfo.Status in ('F','P'))
GROUP BY tblAI.AssocNum) AS BelowAuditSummary
ON tblAssocReportTo.AssocNum = BelowAuditSummary.AssocNum
WHERE BelowAuditSummary.NumAudits <= @MinAudits-1 or BelowAuditSummary.NumAudits IS NULL
GROUP BY tblAssocReportTo.SupID) AS BelowAudit ON AssocRespond.SupID = BelowAudit.AuditedBy

LEFT OUTER JOIN
--Required
(SELECT RequiredAuditSummary.AuditedBy, Count(RequiredAuditSummary.AssocNum) AS Required
FROM (SELECT tblAI.AuditedBy ,tblAI.AssocNum, Count(tblAI.AssocNum) AS NumAudits
FROM tblAI INNER JOIN tblAssocReportTo ON tblAI.AssocNum = tblAssocReportTo.AssocNum AND tblAssocReportTo.SupID = tblAI.AuditedBy INNER JOIN tblAssocInfo ON tblAssocReportTo.AssocNum = tblAssocInfo.AssocNum
WHERE NOT(AuditedBy IS NULL) AND (tblAI.DateAudited BETWEEN @StartDate and @EndDate) AND (tblAssocInfo.Status in ('F','P'))
GROUP BY tblAI.AuditedBy, tblAI.AssocNum) AS RequiredAuditSummary
WHERE RequiredAuditSummary.NumAudits = @MinAudits
GROUP BY RequiredAuditSummary.AuditedBy) AS RequiredAudit ON AssocRespond.SupID = RequiredAudit.AuditedBy)

LEFT OUTER JOIN
--Extra
(SELECT ExtraAuditSummary.AuditedBy, Count(ExtraAuditSummary.AssocNum) AS Extra
FROM (SELECT tblAI.AuditedBy ,tblAI.AssocNum, Count(tblAI.AssocNum) AS NumAudits
FROM tblAI INNER JOIN tblAssocReportTo ON tblAI.AssocNum = tblAssocReportTo.AssocNum AND tblAssocReportTo.SupID = tblAI.AuditedBy INNER JOIN tblAssocInfo ON tblAssocReportTo.AssocNum = tblAssocInfo.AssocNum
WHERE NOT(AuditedBy IS NULL) AND tblAI.DateAudited BETWEEN @StartDate and @EndDate AND (tblAssocInfo.Status in ('F','P'))
GROUP BY tblAI.AuditedBy, tblAI.AssocNum) AS ExtraAuditSummary
WHERE ExtraAuditSummary.NumAudits > @MinAudits
GROUP BY ExtraAuditSummary.AuditedBy) AS ExtraAudit ON AssocRespond.SupID = ExtraAudit.AuditedBy) AS AuditSummary ON AuditSummary.SupID = TurnAround.SupID) AS LeadStats
ON LeadStats.LeadID=tblAssocInfo.AssocNum INNER JOIN tblMonthlyScale ON tblAssocInfo.Title= tblMonthlyScale.Title INNER JOIN tblMonthlyWeights ON tblAssocInfo.Title = tblMonthlyWeights.Title)
END

 

[amrita418]

Did you run the query by itself with some preset values for startdate and enddate? If yes, what was the outcome?

 

[jnavarro]

Yes, I ran the query and it return the information that I was looking for.

I try create a sp with the same information and it work, however when I try create a function is give me errors. I think it has something to do with how I declare the variables. When I remove the variables it works.