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.
Jason,
You can use a decode statement and the two tables workperiod and modavail to get this information. The table called modavail stores any entries that cause a variation from a person's standard availability based on their calendar. The workperiod table store the hours a person is available based on their assigned calendar.
We use the code below to sum up the hours a person is available for work between two dates (startDate and endDate) at a particular site (siteid). The decode statement replaces the standard calendar based work hours with the hours in modavail whenever there is a corresponding entry in the modavail table.
select sum(decode(modavail.modavailworkhours,null,wp.workhours,modavail.modavailworkhours)) As TOTAL_CALHRS
from labor lab,personcal person,workperiod wp, (select MODAVAIL.PERSONID, modavail.workhours MODAVAILWORKHOURS, modavail.workdate MODWORKDATE, modavail.starttime MODSTARTTIME
,modavail.endtime MODENDTIME
from modavail
where
MODAVAIL.WORKDATE
Between :startDate And :endDate) MODAVAIL
where lab.personid = person.personid
and lab.orgid = person.orgid
and person.calnum=wp.calnum
and person.shiftnum=wp.shiftnum
and person.orgid=wp.orgid
and person.personid = modavail.personid(+)
and wp.workdate
Between :startDate And :endDate
and lab.worksite =:siteid
Dave Teece
Dave - Thanks for the great example, Oracle users are going to be very happy with this query. Unfortunately we're a SQL Server shop and 'DECODE' isn't a supported function.
--- In MAXIMO@yahoogroups.com, david_teece@... wrote:
>
> Jason,
> You can use a decode statement and the two tables workperiod and modavail to get this information. The table called modavail stores any entries that cause a variation from a person's standard availability based on their calendar. The workperiod table store the hours a person is available based on their assigned calendar.
>
> We use the code below to sum up the hours a person is available for work between two dates (startDate and endDate) at a particular site (siteid). The decode statement replaces the standard calendar based work hours with the hours in modavail whenever there is a corresponding entry in the modavail table.
>
> select sum(decode(modavail.modavailworkhours,null,wp.workhours,modavail.modavailworkhours)) As TOTAL_CALHRS
> from labor lab,personcal person,workperiod wp, (select MODAVAIL.PERSONID, modavail.workhours MODAVAILWORKHOURS, modavail.workdate MODWORKDATE, modavail.starttime MODSTARTTIME
> ,modavail.endtime MODENDTIME
> from modavail
> where
> MODAVAIL.WORKDATE
> Between :startDate And :endDate) MODAVAIL
> where lab.personid = person.personid
> and lab.orgid = person.orgid
> and person.calnum=wp.calnum
> and person.shiftnum=wp.shiftnum
> and person.orgid=wp.orgid
> and person.personid = modavail.personid(+)
> and wp.workdate
> Between :startDate And :endDate
> and lab.worksite =:siteid
>
> Dave Teece
>
>
>
>
>
>