Anthony Cardullo
asked on
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
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I fixed it already. just crated a function and call that in my sp. thanks though
ASKER
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
thanks
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
CLosing question due to lack of interrest.
ASKER
No followup
ASKER