Avatar

Please consider registering
guest

sp_LogInOut Log In sp_Registration Register

Register | Lost password?
Advanced Search

— Forum Scope —




— Match —





— Forum Options —





Minimum search word length is 3 characters - maximum search word length is 84 characters

sp_Feed Topic RSS sp_TopicIcon
SQL - Labor, Materials location from workorder
March 20, 2017
5:41 pm
Avatar
Dave4444
Member
Members
Forum Posts: 11
Member Since:
October 20, 2014
sp_UserOfflineSmall Offline

Hello,

I’m trying to create a basic table report in maximo which sums up labour and material costs on a workorder. Once I have the correct data I’ll grouped this by location in BIRT.

The issue im encountering at the moment is my SQL brings back duplicate records.

select locations.description as Station, LABTRANS.REGULARHRS AS Hours, LABTRANS.LINECOST, LABTRANS.TRANSTYPE, MATUSETRANS.UNITCOST, MATUSETRANS.DESCRIPTION as MATERIAL "
+ " from workorder "
+ " join locations on (WORKORDER.LOCATION=LOCATIONS.LOCATION) "
+ " join labtrans on (workorder.wonum=labtrans.refwo) "
+ " join MATUSETRANS on wonum=MATUSETRANS.refwo "
+ " where (workorder.worktype = 'CAS') and (wonum = 'AA40854') and (workorder.status IN ('CLOSED', 'HISTORY')) "
;

work order AA40854 has 4 rows of materials and 2 rows of labor (1 x WORK, 1x TRAV). The query above duplicates the labor records to 4 rows (2 x WORK, 2 x TRAV). This gives me a false total.

100% sure my lack of sql is holding this report back. Can someone point me in the right direction with this query so I retrieve my 4 rows of materials and 2 rows of labor.

Thanks.

March 21, 2017
3:55 pm
Avatar
Dave4444
Member
Members
Forum Posts: 11
Member Since:
October 20, 2014
sp_UserOfflineSmall Offline

Basic table in Birt, sorry, not maximo.

March 24, 2017
6:03 am
Avatar
aymhenry
Member
Members
Forum Posts: 4
Member Since:
November 1, 2015
sp_UserOfflineSmall Offline

I you use Birt, it is good to use more than one DataSet each will calculate group of data.

to link all in one SQL, you will get buplicated records.

April 25, 2017
3:29 pm
Avatar
jperras
Member
Members
Forum Posts: 3
Member Since:
December 21, 2015
sp_UserOfflineSmall Offline

I agree with the previous poster. You will have to split this up into multiple queries. You’re running across basic database design where you are trying to create a one to many relationship. One work order/labor hours to many parts that is why you are getting the duplicates. 

 

As a side note, you can try to use workorder.actmatcost and workorder.actlabhrs instead of trying to sum up your own material cost. (I am in 7.6 but I bet the older versions have that field). It would look like:

 

select workorder.wonum, workorder.actmatcost, workorder.actlabhrs, (workorder.actmatcost + workorder.actlabhrs) as total cost

from workorder

where workorder.worktype = ‘CAS’

and wonum = ‘AA40854’

and workorder.status IN (‘CLOSED’, ‘HISTORY’)

Forum Timezone: America/New_York

Most Users Ever Online: 54

Currently Online:
1 Guest(s)

Currently Browsing this Page:
1 Guest(s)

Top Posters:

sun kim: 60

madd0g17: 59

v.barsamov: 51

cgregory: 45

Maximofresher: 45

matta0990: 33

Member Stats:

Guest Posters: 0

Members: 14366

Moderators: 0

Admins: 4

Forum Stats:

Groups: 1

Forums: 8

Topics: 1580

Posts: 4295

Newest Members:

Megan Morgan, Devendra Verma, stephen stephens, maxexpertise Hilal, Jeroen van Proosdij, Neeraja N

Administrators: Chon: 1118, kristensibilia: 0, Joe Grassia: 0, bpaik: 0