Checking for waiting jobs and long processing jobs

I am too darn lazy to spend the hours necessary to write a sql script to do the following:

1- Find jobs that have been waiting for more than x minutes

2- Find jobs that have been processing for more than x minutes

I really hate messing with the JDE date/time fields. Has anyone created scripts to do the above?

Brother,

You don't have your database listed but the following will give you a SQL data time value from a JDE Date and JDE Time in SQL. Using that, you should be able to contruct a query using this function and getdate values in SQL along with any criteria to create a view that will give you what you want.

CREATE FUNCTION dbo.fn_JDEJulianAndJDETimeToDateTime (@JDEDate Numeric(18,0), @Time Float)
RETURNS datetime AS
BEGIN

Declare @Year as CHAR(4)
Declare @Date as datetime
Declare @Days as int
Declare @Seconds as INT
Declare @Minutes as INT
Declare @Hours as INT
Declare @TimeStr as CHAR(6)

-- converts the float time to a string
Set @TimeStr = STR(@Time,6,0)
-- removes leading and trailing spaces
Set @TimeStr = RTRIM(LTRIM(@TimeStr))

-- pad with leading 0's until we get a 6 char length string
-- Since JDE Time Fields are floats, the leading 0's are dropped
-- This formats the time to a constant HHMMSS format
While LEN(@TimeStr) < 6
BEGIN
Set @TimeStr = '0' + @TimeStr
END

-- gets the year from the julian date
Set @Year = LEFT(@JDEDate + 1900000,4)

-- gets the number of days into the year
Set @Days = RIGHT(@JDEDate,3)

-- gets the hours part of the time
Set @Hours = LEFT(@TimeStr,2)

-- gets the minutes part of the time
Set @Minutes = SUBSTRING(@TimeStr,3,2)

-- gets the seconds part of the time
Set @Seconds = RIGHT(@TimeStr,2)

-- the first day of the year
Set @Date = CAST('1/1/' + @Year as datetime)
-- add in the days, taking away one as we start on day 1
Set @Date = DATEADD(dd, @Days - 1,@Date)
-- add in the hours
Set @Date = DATEADD(hh, @Hours,@Date)
-- add in the minutes
Set @Date = DATEADD(mi, @Minutes,@Date)
-- add in the seconds
Set @Date = DATEADD(ss, @Seconds,@Date)

-- return the date and time
RETURN @Date

END

And once you get the function in place, a select/view like this with the appropriate data selections should get you everthing you need.

SELECT SVM7334.F986110.*, dbo.fn_JDEJulianAndJDETimeToDateTime(JCSBMDATE, JCSBMTIME) AS SubmittedDateTime,
dbo.fn_JDEJulianAndJDETimeToDateTime(JCACTDATE, JCACTTIME) AS LastActivityDateTime, DATEDIFF(mi,
dbo.fn_JDEJulianAndJDETimeToDateTime(JCSBMDATE, JCSBMTIME), GETDATE()) AS MinutesSinceSubmit, DATEDIFF(mi,
dbo.fn_JDEJulianAndJDETimeToDateTime(JCACTDATE, JCACTTIME), GETDATE()) AS MinutesSinceLastActivity, DATEDIFF(mi,
dbo.fn_JDEJulianAndJDETimeToDateTime(JCSBMDATE, JCSBMTIME), dbo.fn_JDEJulianAndJDETimeToDateTime(JCACTDATE, JCACTTIME))
AS MinutesToComplete
FROM SVM7334.F986110