bbbbbbbbbbbbbbbb

/****** Object:  StoredProcedure [dbo].[PRC_CST_Get_Activities]    Script Date: 18/11/2019 10:37:59 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		Xelo ,Farhan
-- Create date: 4/11/2019
-- Description:
-- =============================================
ALTER PROCEDURE [dbo].[PRC_CST_Get_Activities]
	-- Add the parameters for the stored procedure here
	@ManagerName nvarchar(50) = '',
	@CompanyName nvarchar(50) = '',
	@startDate DATE = '',
	@endDate DATE = ''
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	SELECT
		C.COMPANYNAME,
		C.FIRSTNAME,
		C.LASTNAME,
		P.NUMBERVALUE,
		TBL_ACTIVITYTYPE.NAME as ACTIVITY_TYPE,
		A.ENDTIME,
		A.DURATION,
		TBL_ACTIVITYPRIORITY.NAME as ACTIVITY_PRIORITY,
		A.REGARDING
		,CC.FirstName + ' ' + CC.Lastname AS ManagerName
	FROM TBL_ACTIVITY A
		INNER JOIN TBL_CONTACT_ACTIVITY CA ON CA.ACTIVITYID = A.ACTIVITYID
		INNER JOIN TBL_CONTACT C ON C.CONTACTID = CA.CONTACTID
		INNER JOIN TBL_CONTACT CC ON CC.CONTACTID = A.CreateUserId
		INNER JOIN TBL_PHONE P ON P.CONTACTID = CA.CONTACTID
		INNER JOIN TBL_ACTIVITYTYPE ON TBL_ACTIVITYTYPE.ACTIVITYTYPEID = A.ACTIVITYTYPEID
		LEFT JOIN TBL_ACTIVITYSERIESAPPLIED on A.ACTIVITYSERIESAPPLIEDID = TBL_ACTIVITYSERIESAPPLIED.ACTIVITYSERIESAPPLIEDID
		INNER JOIN TBL_ACCESSOR_ACTIVITY on TBL_ACCESSOR_ACTIVITY.ACTIVITYID = A.ACTIVITYID
		LEFT JOIN TBL_ACTIVITYSERIESITEM on TBL_ACTIVITYSERIESAPPLIED.ACTIVITYSERIESID = TBL_ACTIVITYSERIESITEM.ACTIVITYSERIESID
		INNER JOIN TBL_ACTIVITYPRIORITY on (TBL_ACTIVITYSERIESITEM.ACTIVITYPRIORITYID  = TBL_ACTIVITYPRIORITY.ACTIVITYPRIORITYID or TBL_ACCESSOR_ACTIVITY.ACTIVITYPRIORITYID  = TBL_ACTIVITYPRIORITY.ACTIVITYPRIORITYID)
	WHERE C.COMPANYNAME like '%'+ @CompanyName +'%'
	GROUP BY C.COMPANYNAME,C.FIRSTNAME,C.LASTNAME, P.NUMBERVALUE,
		TBL_ACTIVITYTYPE.NAME,A.ENDTIME,A.DURATION,TBL_ACTIVITYPRIORITY.NAME,
		A.REGARDING, A.CREATEUSERID, CC.FirstName,CC.LastName
	Order By C.COMPANYNAME


	--SELECT  DISTINCT A.ACTIVITYID,
	--	C.COMPANYNAME,
	--	C.FIRSTNAME,
	--	C.LASTNAME,
	--	P.NUMBERVALUE,
	--	TBL_ACTIVITYTYPE.NAME as ACTIVITY_TYPE,
	--	A.ENDTIME,
	--	A.DURATION,
	--	TBL_ACTIVITYPRIORITY.NAME as ACTIVITY_PRIORITY,
	--	A.REGARDING,
	--	aa = (SELECT TOP 1 FIRSTNAME + ' ' + LASTNAME FROM TBL_CONTACT WHERE CONTACTID = A.CREATEUSERID)--,
	-- --A.*
	--FROM TBL_ACTIVITY A
	--	INNER JOIN TBL_CONTACT_ACTIVITY CA ON CA.ACTIVITYID = A.ACTIVITYID
	--	INNER JOIN TBL_CONTACT C ON C.CONTACTID = CA.CONTACTID
	--	INNER JOIN TBL_PHONE P ON P.CONTACTID = CA.CONTACTID
	--	INNER JOIN TBL_ACTIVITYTYPE ON TBL_ACTIVITYTYPE.ACTIVITYTYPEID = A.ACTIVITYTYPEID
	--	LEFT JOIN TBL_ACTIVITYSERIESAPPLIED on A.ACTIVITYSERIESAPPLIEDID = TBL_ACTIVITYSERIESAPPLIED.ACTIVITYSERIESAPPLIEDID
	--	INNER JOIN TBL_ACCESSOR_ACTIVITY on TBL_ACCESSOR_ACTIVITY.ACTIVITYID = A.ACTIVITYID
	--	LEFT JOIN TBL_ACTIVITYSERIESITEM on TBL_ACTIVITYSERIESAPPLIED.ACTIVITYSERIESID = TBL_ACTIVITYSERIESITEM.ACTIVITYSERIESID
	--	INNER JOIN TBL_ACTIVITYPRIORITY on (TBL_ACTIVITYSERIESITEM.ACTIVITYPRIORITYID  = TBL_ACTIVITYPRIORITY.ACTIVITYPRIORITYID or TBL_ACCESSOR_ACTIVITY.ACTIVITYPRIORITYID  = TBL_ACTIVITYPRIORITY.ACTIVITYPRIORITYID)
	--WHERE C.COMPANYNAME LIKE @CompanyName+'%'
	--Order By C.COMPANYNAME Desc




END



GO