How to prepare query instead of this function

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

How to prepare query instead of this function

Author

Topic

imranabdulaziz
Yak Posting Veteran

83 Posts

Posted - 2010-12-28 : 01:51:31

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

Sachin.Nand

2937 Posts

Posted - 2010-12-28 : 04:26:16

Won't just this suffice your requierment?


select empid , dbo.[[Gettotalcall]]( @stdate ,@enddate , @empod ),COUNT(distinct CustomerID)
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) 
group by empid

PBUH

Sachin.Nand

2937 Posts

Posted - 2010-12-28 : 04:29:15

As far as indexes are concered I suggest you to create a composite non clustered index on empid,reportingdate covering CustomerID

PBUH

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources