Solved

Calculate work busines days for rolling getdate days -90

Posted on 2009-07-15
7
350 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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

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

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Suggested Solutions

In database programming, custom sort order seems to be necessary quite often, at least in my experience and time here at EE. Within the realm of custom sorting is the sorting of numbers and text independently (i.e., treating the numbers as number…
If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
This video discusses moving either the default database or any database to a new volume.
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

707 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

17 Experts available now in Live!

Get 1:1 Help Now