Solved

Calculate work busines days for rolling getdate days -90

Posted on 2009-07-15
7
361 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
Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

 

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

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

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

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…

777 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