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)  )
@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 

return @totalcall 

 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'
reportdate between cast('01-12-2010' as datetime) and cast('01-12-2010' as datetime)
group by t1.empid