Solved

Calculate work busines days for rolling getdate days -90

Posted on 2009-07-15
7
355 Views
Last Modified: 2012-05-07
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

0
Comment
Question by:acardullo
  • 6
7 Comments
 
LVL 17

Assisted Solution

by:pssandhu
pssandhu earned 20 total points
ID: 24860217
Can you pelase specify your database product?
P.
0
 

Author Comment

by:acardullo
ID: 24869097
sql 2005
0
 

Author Comment

by:acardullo
ID: 24869112
I fixed it already.  just crated a function and call that in my sp.  thanks though
0
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 

Author Comment

by:acardullo
ID: 24869178
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
 

Accepted Solution

by:
acardullo earned 0 total points
ID: 24869184
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
 

Author Comment

by:acardullo
ID: 24924000
CLosing question due to lack of interrest.
0
 

Author Comment

by:acardullo
ID: 24924011
No followup
0

Featured Post

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL Query to display duplicates ? 6 37
data lookup in Oracle - need suggestions 55 101
mySQL Syntax 7 34
Help Extract Specific in SQL 8 15
'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
This is a video describing the growing solar energy use in Utah. This is a topic that greatly interests me and so I decided to produce a video about it.

932 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now