This is an archive of the Maximo Yahoo Community. The content of this pages may be a sometimes obsolete so please check post dates.
Thanks to the community owner Christopher Wanko for providing the content.
Hi Group,
I've trying to set up PMAutomatic WOgen based on Leadtime. Although the cron was running just fine before with just PM and siteid on the Where Clause in Organizations, now seems the function is stuck.
I've used the following sql script to get the Cron working but nothing comes up:
((siteid = 'BEDFORD') and ((nextdate - leadtime) = getdate()) and leadtime != 0)
Any thoughts?
Shirley
I have some thoughts.
Run this:
SELECT pmnum, nextdate, leadtime, getdate(), (nextdate - leadtime) AS diffNext,
CASE WHEN (getdate() = (nextdate - leadtime)) THEN 'Match'
WHEN (getdate() > (nextdate - leadtime)) THEN 'Past'
ELSE 'Future'
END AS theDiffWeSee
FROM pm
WHERE leadtime > 0
AND nextdate IS NOT NULL AND status = 'ACTIVE'
ORDER BY 5
;
If necessary, expand your displayed date:
--
SELECT CONVERT(VARCHAR(19), nextdate, 20) expandedNext, leadtime, CONVERT(VARCHAR(19), GETDATE(), 20) expandedNOW, CONVERT(VARCHAR(19), nextdate - leadtime, 20) as expandedDiffNext,
CASE WHEN (getdate() = (nextdate - leadtime)) THEN 'Match'
WHEN (getdate() > (nextdate - leadtime)) THEN 'Past'
ELSE 'Future'
END AS theDiffWeSee
FROM maxtest.dbo.pm
WHERE leadtime > 0
AND nextdate IS NOT NULL AND status = 'ACTIVE'
ORDER BY 5
;
My thinking is that if it's less than or equal to today, you should generate it. This keeps you out of having to match to a precise second or minute.
-C
Shirley - you're not likely to get a result because you're trying to match the system date time with a specific date time in PMs that will not likely match ever. You might try truncating the getdate function so you only return the date and not date & time. And instead of equals, try greater than or greater than equals to.
http://stackoverflow.com/questions/1177449/best-approach-to-remove-time-part-of-datetime-in-sql-server http://stackoverflow.com/questions/1177449/best-approach-to-remove-time-part-of-datetime-in-sql-server
Hi Shirley,
This is the clause I use (SQL Server) in Maximo 7.1
coalesce(extdate,nextdate) < dateadd(d, leadtime, getdate()) and status = 'ACTIVE' and siteid = 'BEDFORD'
This also takes into account the override date this has been working for several years without any problems