June 17, 2003 at 9:57 am
I have code that works fine in SQL Server 2000 and I need it to work in SQL Server 7.0. The problem is I am using a UDF and they are not supported in 7.0.
Can this work with a stored procedure?
They are three separate SQL statements,
select p.[Name] AS [Priority], avg(datediff(dd,dbo.LastLookupDate('Priority', r.Priority, r.RequestID),r.ClosedDate)) AS [Average Days to Close]
FROM a_request r INNER JOIN r_priority p ON r.Priority = p.PriorityID
WHERE p.Retired = 'N'
AND NOT ClosedDate IS NULL
AND (@StartDate IS NULL OR ClosedDate >= @StartDate)
AND (@EndDate IS NULL OR ClosedDate <= @EndDate)
GROUP BY p.[Name]
select p.[Name] AS [Priority], min(datediff(hh,dbo.LastLookupDate('Priority', r.Priority, r.RequestID),r.ClosedDate)) AS [Minimum Hours to Close]
FROM a_request r INNER JOIN r_priority p ON r.Priority = p.PriorityID
WHERE p.Retired = 'N'
AND NOT ClosedDate IS NULL
AND (@StartDate IS NULL OR ClosedDate >= @StartDate)
AND (@EndDate IS NULL OR ClosedDate <= @EndDate)
GROUP BY p.[Name]
select p.[Name] AS [Priority], max(datediff(dd,dbo.LastLookupDate('Priority', r.Priority, r.RequestID),r.ClosedDate)) AS [Maximum Days to Close]
FROM a_request r INNER JOIN r_priority p ON r.Priority = p.PriorityID
WHERE p.Retired = 'N'
AND NOT ClosedDate IS NULL
AND (@StartDate IS NULL OR ClosedDate >= @StartDate)
AND (@EndDate IS NULL OR ClosedDate <= @EndDate)
The user defined function pretty much returns the last updated date, and if there were no updates, it returns the opened date
User Defined Function
CREATE FUNCTION LastLookupDate(
@Action VARCHAR(20),
@Value VARCHAR(20),
@RequestID INT
)
RETURNS DateTime
AS
BEGIN
DECLARE @LastUpdate DATETIME
SELECT @LastUpdate = LastUpdated
FROM a_log l
WHERE Type = @Action
AND RequestID = @RequestID
AND [NewID] = @Value
AND LogID = (SELECT MAX(LogID) FROM a_log WHERE Type = @Action AND RequestID = @RequestID)
IF @LastUpdate IS NULL BEGIN
SELECT @LastUpdate = OpenDate FROM a_request WHERE RequestID = @RequestID
END
RETURN @LastUpdate
END
June 18, 2003 at 8:14 am
Don't have your tables, so it'd be easier for you to check this out than me.
I'd try simply rewriting things as an inner join. For the first SQL statement you showed, I got this:
select p.[Name] AS [Priority]
,avg(datediff(dd,ISNULL(l.LastUpdated, r.OpenDate),r.ClosedDate)) AS [Average Days to Close]
FROM a_request r INNER JOIN r_priority p ON r.Priority = p.PriorityID
INNER JOIN a_log l ON (l.RequestID = r.RequestID)
WHERE p.Retired = 'N'
AND NOT ClosedDate IS NULL
AND (@StartDate IS NULL OR ClosedDate >= @StartDate)
AND (@EndDate IS NULL OR ClosedDate <= @EndDate)
AND l.Type = 'Priority'
AND l.RequestID = r.RequestID
AND [NewID] = r.Priority
AND LogID = (SELECT MAX(LogID) FROM a_log WHERE Type = 'Priority' AND RequestID = r.RequestID)
GROUP BY p.[Name]
If that works, you should be able to make the same changes to your other statements to adapt them to working without the UDF.
R David Francis
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply