SQL Server - How to prepare query instead of this function

Asked By imran khan on 28-Dec-10 01:53 AM
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






  

query to replace function - Lalitha Kumaran replied to imran khan on 03-Jan-11 06:17 AM


select t1.empid,count(*) from t1
inner join t2
on t2.hdrid=t1.hdrid
where empid='emp1'
and
reportdate between cast('01-12-2010' as datetime) and cast('01-12-2010' as datetime)
group by t1.empid