Query : select op.name,op.customeridname,op.createdon,op.owneridname ,fp.activitytypecodename,fp.new_callbackname,fp.subject from
FilteredOpportunity op,FilteredPhoneCall fp
where op.opportunityid=fp.regardingobjectid
function:
USE [neworg_MSCRM]
GO
/****** Object: UserDefinedFunction [dbo].[Getopportunitycount] Script Date: 09/06/2012 11:36:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:
<Author,,Name>
-- Create date: <Create Date, ,>
-- Description:
<Description, ,>
-- =============================================
ALTER FUNCTION [dbo].[Getopportunitycount]
(
-- Add the parameters for the function here
@createdate date,
@uname varchar(50)
)
RETURNS int
AS
BEGIN
-- Declare the return variable here
DECLARE
@ResultVar int
-- Add the T-SQL statements to compute the return value here
set @ResultVar =
(select count(*) from FilteredPhoneCall where (CONVERT(DATETIME, FLOOR(CONVERT(FLOAT, createdon))) = @createdate) and (new_callback=1) and (createdbyname = @uname ))
set @ResultVar= (select COUNT(*) from FilteredOpportunity op,FilteredPhoneCall fp where (op.opportunityid=fp.regardingobjectid) and
(op.createdbyname = @uname) and (CONVERT(DATETIME, FLOOR(CONVERT(FLOAT, op.createdon))) = @createdate) )
-- Return the result of the function
RETURN @ResultVar
END
query :
IF OBJECT_ID('tempdb..#AllReportees') IS NOT NULL
DROP TABLE #AllReportees
CREATE TABLE #AllReportees
(
createdate Date,
uname varchar(50),
ownids uniqueidentifier,
phoneact varchar(50),
statuscode varchar(50)
)
INSERT INTO #AllReportees
select CONVERT(VARCHAR, createdon, 106) AS date1,createdbyname,ownerid,activityid,statuscodename from FilteredPhoneCall
union
select CONVERT(VARCHAR, createdon, 106) AS date1,createdbyname,ownerid,opportunityid,statuscodename from FilteredOpportunity
select *
,dbo.Getwrongphonecallcount(dd.createdate,dd.uname) as 'Wrong Calls' ,dbo.Getnoresponsecount(dd.createdate,dd.uname) as ' No Response' , dbo.Getcallbackcount(dd.createdate,dd.uname) as 'Callback',
dbo.Getopportunitycount( dd.createdate,dd.uname ) as 'Opportunities'
from (
select ss.createdate,ss.uname,
count(distinct fp.activityid) as Phone
from #AllReportees ss
left OUTER JOIN FilteredSystemUser AS su WITH (NOLOCK) ON su.systemuserid=ss.ownids
left OUTER JOIN FilteredPhoneCall as fp WITH (NOLOCK) ON (fp.activityid=ss.phoneact)
left OUTER JOIN FilteredOpportunity fl WITH (NOLOCK) ON (fl.opportunityid=ss.phoneact)
left OUTER JOIN FilteredAppointment AS fa WITH (NOLOCK) ON (fa.activityid=ss.phoneact)
group by ss.createdate,uname
) dd order by dd.uname,dd.createdate
