Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

User Defined Function

Status
Not open for further replies.
jnavarro

jnavarro

Programmer
Joined
Dec 1, 2003
Messages
89
Location
US
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
 
Sort by date Sort by votes
Code: 
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.
 
Upvote 0 Downvote
What are you returning back to the calling program? Is it the days worked?

Regards,
AA
 
Upvote 0 Downvote
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?


 
Upvote 0 Downvote
Can you post the code with return(actual value)?
 
Upvote 0 Downvote
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

 
Upvote 0 Downvote
Did you run the query by itself with some preset values for startdate and enddate? If yes, what was the outcome?
 
Upvote 0 Downvote
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.

 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

grnzbra
  • Locked
  • Question Question
Create Function Within IF statement.
Replies
4
Views
2K
mikrom
mikrom
briangriffin
  • Locked
  • Question Question
Function Returning Rowcounts
Replies
1
Views
431
briangriffin
briangriffin
Auguy
  • Locked
  • Question Question
Convert Code to a Function
Replies
7
Views
632
Auguy
Auguy
Cpreston
  • Locked
  • Question Question
Last date per userid
Replies
3
Views
489
Andrzejek
Andrzejek
leifoet
  • Locked
  • Question Question
DATEDIFF() function does not work
Replies
5
Views
1K
leifoet
leifoet

Part and Inventory Search

Sponsor

Back
Top