Hi All,
I am using sql server 2005.
I have one fuction that calculate the totalcall made by employee during specified period.
But this function taking much time for longer duration
Is There Any way to remove function and Get the desired result in query itself
I have created index on HDr id
MY tables are
Header table
HDRID reportingdate empid
1 01-12-2010 EMP1
2 02-12-2010 EMP2
Details Table
HDRID CustomerID Product
1 Cust1 Prod1
1 Cust1 Prod2
1 Cust2 Prod1
1 Cust3 Prod1
1 Cust4 Prod1
1 Cust4 Prod2
Now When i calculate Total call done by EMP1
it should take customerid for specified period and count and result should come as four.
function is
ALTER FUNCTION [dbo].[Gettotalcall](@from_dt datetime,@to_dt datetime , @empcode varchar(50) )
RETURNS INT
AS
BEGIN
DECLARE
@totalcall int
select @totalcall = count(*) from
(
select empid , customerid
FROM Header A INNER JOIN Details B ON A.Hdrid = B.Hdrid
WHERE empid =@empcode AND (reportingdate BETWEEN @stdate AND @enddate) group by reportingdate , CustomerID
)x
return @totalcall
end
and i fetch it as
select empid , dbo.[[Gettotalcall]]( @stdate ,@enddate , @empod )
FROM Header A INNER JOIN Details B ON A.Hdrid = B.Hdrid
WHERE empid IN (SELECT Emp FROM @tbl) AND (reportingdate BETWEEN @stdate AND @enddate)
Thank you