I'm lost with this one - I am trying to use a stored procedure to work out the time difference from a date field and the current date/time - the stored procedure only counts office hours (e.g. 9 - 5:30 mon to fri).

 The stored procedure is as below:

1    USE [###############_MSCRM]
2    GO
3    /****** Object:  StoredProcedure [dbo].[OfficeWorkingHours]    Script Date: 05/07/2008 18:20:53 ******/
4    SET ANSI_NULLS ON
5    GO
6    SET QUOTED_IDENTIFIER ON
7    GO
8    -- =============================================
9    -- Author:		Stephen Provis
10   -- Create date: 07/05/2008
11   -- Description:	Stored Proc for calculating office working hours
12   -- =============================================
13   ALTER PROCEDURE [dbo].[OfficeWorkingHours] 
14   	-- Add the parameters for the stored procedure here
15   	@p1 DateTime = 0
16   AS
17   BEGIN
18   declare @timeRequest As DateTime 
19   declare @timeResponse As DateTime 
20   declare @timeSpan As float 
21   begin
22   set @timeRequest = convert(datetime, @p1) --example
23   set @timeResponse = GETDATE() --example
24   If (DatePart("w", @timeRequest) >= 2 And DatePart("w", @timeRequest) <= 5) and DateDiff("hour", @timeRequest, @timeResponse) > 8 --from Monday to Thurday
25   begin
26   set @timeSpan = DateDiff("hour", @timeRequest, @timeResponse) - (24 - 17.5 + 9) 
27   end
28   Else If DatePart("w", @timeRequest) = 6 and DateDiff("hour", @timeRequest, @timeResponse) > 8 --Friday
29   begin
30   set @timeSpan = DateDiff("hour", @timeResponse, @timeRequest) - (24 - 17.5 + 9 + 48) 
31   end
32   
33   SELECT     createdon, createdonutc, new_respondedat, new_respondedatutc, new_responded, ticketnumber, statecodename, statuscodename, statecode, 
34                         statuscode, modifiedonutc, owneridname, DATEDIFF(hour, createdon, { fn NOW() }) AS [Hours Neglected], DATEDIFF(minute, createdon, { fn NOW() }) 
35                         AS [Minutes Neglected], accountidname, title, casetypecode, casetypecodename, prioritycode
36   FROM         FilteredIncident
37   WHERE     (statecode = 0) AND (statuscode <> 11) AND (casetypecode = 1 OR
38                         casetypecode = 2) AND (statuscode <> 5) AND (new_responded = 2)
39   ORDER BY [Hours Neglected] DESC
40   end
41   
42   END

The problem is that the paramater @p1 needs to look at the createdonutc field and then output the result on each line of the query as a Field rather than prompting for a parameter.

I have searched high and low but am not sure how to do this - any advice is greatly appreciated.

(I hope the above mates sense)

Steve

Hi,

In your procedure, first you are setting the @timeSpan parameter according to the result of datepart function. And the variable does not relate to the following select statement, right?

And in your select statement, USE GETDATE() to replace {fn NOW()}

SELECT     createdon, createdonutc, new_respondedat, new_respondedatutc, new_responded, ticketnumber, statecodename, statuscodename, statecode,

                         statuscode, modifiedonutc, owneridname, DATEDIFF(hour, createdon,GETDATE()) AS [Hours Neglected], DATEDIFF(minute, createdon, GETDATE())

                         AS [Minutes Neglected], accountidname, title, casetypecode, casetypecodename, prioritycode

   FROM         FilteredIncident

Thanks.