Calculate work busines days for rolling getdate days -90

I need to get a count of business days for the last rolling 90 days.
I.e  i nee to calculate how many average calls a service rep has made per business days.

The sp belowis what i have now. I need to see one row per representative with the record count for that rep and the average call per day based on the amount of calls /Business days.
Hope this makes sense.

Thank you
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Anthony Cardullo
-- Create date: 07/15/09
-- Description:	Calculate Average Per day call completion per rep for last 90 days
-- EXEC[dbo].[usp.AverageCallPerdayPerRep90Days]
-- =============================================
ALTER PROCEDURE [dbo].[usp.AverageCallPerdayPerRep90Days]
	
	
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET ROWCOUNT 0
	SET NOCOUNT ON;
 
    -- Insert statements for procedure here
	SELECT DISTINCT 
                      dbo.root_SERIAL_EQUIP.PROGRAM_TYPE, dbo.root_SERIAL_EQUIP.INSTALL_DATE_SEQ, dbo.root_SERV_CALL.CALL_ID, 
                      dbo.root_SERV_CALL.CALL_STATUS_SCA, dbo.root_SERV_CALL.CALL_TYPE, dbo.root_SERV_CALL.SYMPTOM_CODE, 
                      dbo.root_SERV_CALL.OPEN_DATE_SCA, dbo.root_SERV_CALL.MODEL, dbo.root_SERV_CALL.SERIAL, dbo.root_SERIAL_EQUIP.VENDOR, 
                      dbo.root_SERV_CALL.REPRESENTATIVE, dbo.root_SERV_CALL.COMPLETE_DATE_SCA
FROM         dbo.root_SERV_CALL INNER JOIN
                      dbo.root_SERIAL_EQUIP ON dbo.root_SERV_CALL.MODEL = dbo.root_SERIAL_EQUIP.MODEL AND 
                      dbo.root_SERV_CALL.SERIAL = dbo.root_SERIAL_EQUIP.SERIAL
WHERE     (dbo.root_SERV_CALL.OPEN_DATE_SCA > DATEADD(day, - 90, GETDATE()))
END

Open in new window

acardulloSystems Administrator. DBAAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

pssandhuCommented:
Can you pelase specify your database product?
P.
0
acardulloSystems Administrator. DBAAuthor Commented:
sql 2005
0
acardulloSystems Administrator. DBAAuthor Commented:
I fixed it already.  just crated a function and call that in my sp.  thanks though
0
Become a Microsoft Certified Solutions Expert

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

acardulloSystems Administrator. DBAAuthor Commented:
i have a parmeter now that has the amount of @bizdays for the period.  now i need to do the proper calculation on how many average calls each representative does per day

thanks
0
acardulloSystems Administrator. DBAAuthor Commented:
This is my code now  wiht the call to the function giving the biz day value
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Anthony Cardullo
-- Create date: 07/15/09
-- Description:	Calculate Average Per day call completion per rep for last 90 days
-- EXEC[dbo].[usp.AverageCallPerdayPerRep90Days]
-- =============================================
ALTER PROCEDURE [dbo].[usp.AverageCallPerdayPerRep90Days]
 
	
	
AS
BEGIN
DECLARE 
	--@StartDate datetime,
	--@EndDate datetime,	
	@Count   int,
	@BizDays int
 
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	--SET @StartDate = DATEADD(day, - 90, GETDATE())
	--SET @EndDate = GETDATE()
	SET @BizDays = SUM(dbo.fhGetWorkingDaysWithoutWeekEnds (DATEADD(day, - 90, GETDATE()), GETDATE()))
	SET @Count = (SELECT DISTINCT COUNT( *) FROM dbo.root_SERV_CALL INNER JOIN
                      dbo.root_SERIAL_EQUIP ON dbo.root_SERV_CALL.MODEL = dbo.root_SERIAL_EQUIP.MODEL AND 
                      dbo.root_SERV_CALL.SERIAL = dbo.root_SERIAL_EQUIP.SERIAL)
--WHERE     (dbo.root_SERV_CALL.OPEN_DATE_SCA > DATEADD(day, - 90, GETDATE()))
	SET ROWCOUNT 0
	SET NOCOUNT ON;
 
    -- Insert statements for procedure here
	SELECT DISTINCT 
                      dbo.root_SERIAL_EQUIP.PROGRAM_TYPE, dbo.root_SERIAL_EQUIP.INSTALL_DATE_SEQ, dbo.root_SERV_CALL.CALL_ID, 
                      dbo.root_SERV_CALL.CALL_STATUS_SCA, dbo.root_SERV_CALL.CALL_TYPE, dbo.root_SERV_CALL.SYMPTOM_CODE, 
                      dbo.root_SERV_CALL.OPEN_DATE_SCA, dbo.root_SERV_CALL.MODEL, dbo.root_SERV_CALL.SERIAL, dbo.root_SERIAL_EQUIP.VENDOR, 
                      dbo.root_SERV_CALL.REPRESENTATIVE, dbo.root_SERV_CALL.COMPLETE_DATE_SCA, @BizDays, @Count
FROM         dbo.root_SERV_CALL INNER JOIN
                      dbo.root_SERIAL_EQUIP ON dbo.root_SERV_CALL.MODEL = dbo.root_SERIAL_EQUIP.MODEL AND 
                      dbo.root_SERV_CALL.SERIAL = dbo.root_SERIAL_EQUIP.SERIAL
WHERE     (dbo.root_SERV_CALL.OPEN_DATE_SCA > DATEADD(day, - 90, GETDATE()))
END

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
acardulloSystems Administrator. DBAAuthor Commented:
CLosing question due to lack of interrest.
0
acardulloSystems Administrator. DBAAuthor Commented:
No followup
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.