Maximo

Expand all | Collapse all

SQL Window Functions: Rollup WO costs (including tasks)

Jump to Best Answer
  • 1.  SQL Window Functions: Rollup WO costs (including tasks)

    Posted 28 days ago
    Edited by User1971 28 days ago
    MAM 7.6.1.2; Oracle 19c:

    I want to use SQL window functions/analytic functions to query for WO costs including task costs.


  • 2.  RE: SQL Window Functions: Rollup WO costs (including tasks)

    Posted 28 days ago
    Edited by User1971 24 days ago
    Someone suggested that the approach I took with window functions was incorrect. So I'll put this on hold for now until I can confirm.


  • 3.  RE: SQL Window Functions: Rollup WO costs (including tasks)

    Posted 26 days ago
    We had a similar need, but for labor hours, where users at one site wanted to display all hours recorded on the work order and tasks on the work order and/or in result sets. Using automation scripts we created an object launch point to sum the hours across the work group and populate that value in a custom field in the workorder object.

    from psdi.mbo import MboConstants;

    site = mbo.getString("SITEID");
    workgroup = mbo.getMboSet("WOGROUP");

    if (site == "siteid"):
    mbo.setValue("X_ACTLABCOSTWG",workgroup.sum("actlabhrs"),MboConstants.NOACCESSCHECK+MboConstants.NOVALIDATION);

    ------------------------------
    John Rice
    ------------------------------



  • 4.  RE: SQL Window Functions: Rollup WO costs (including tasks)

    Posted 28 days ago
    Edited by User1971 28 days ago


  • 5.  RE: SQL Window Functions: Rollup WO costs (including tasks)

    Posted 27 days ago
    Perhaps the costs are already rolled up on the parent work order?
    If you view the costs in Maximo, it displays the costs for the current work order, and for the work package.
    Select Actions -> View -> Costs.

    ------------------------------
    Scott Taylor
    Specialist Business Systems
    Port Waratah Coal Services
    ------------------------------



  • 6.  RE: SQL Window Functions: Rollup WO costs (including tasks)

    Posted 27 days ago
    Edited by User1971 24 days ago
    Thanks for your reply.

    In my case, I'm hoping to access WO costs (including tasks, but not including child WOs) via:
    • Ad-hoc reports
    • Excel application export
    • WOTRACK Advanced Search and list view
    It's my understanding that the View Costs information isn't available as OOB WORKORDER fields (persistent). So I'm trying to find a good way to generate that info as persistent fields in a Maximo object.
    • ACTLABCOSTS_INCL_TASKS
    • ACTMATCOSTS_INCL_TASKS
    • ACTSERVCOSTS_INCL_TASKS
    • ACTTOOLCOSTS_INCL_TASKS
    • ACTTOTALOSTS_INCL_TASKS


  • 7.  RE: SQL Window Functions: Rollup WO costs (including tasks)
    Best Answer

    Posted 25 days ago
    Edited by User1971 24 days ago


    This is what I came up with:



    Records for testing:



    Query:

    I used the SUM analytical function to get the total WOGROUP costs.
    And wrapped the SUM analytic query in an outer query that hides the task rows.

    --The suffix "_ti" stands for "tasks included".
    select
        wonum,
        istask,
        actlabcost_ti,
        actmatcost_ti,
        acttoolcost_ti,
        actservcost_ti,
        acttotalcost_ti,
        other_wo_columns
    from
        (
            select
            wonum,
            istask,
            sum(actlabcost ) over (partition by wogroup) as actlabcost_ti,
            sum(actmatcost ) over (partition by wogroup) as actmatcost_ti,
            sum(acttoolcost) over (partition by wogroup) as acttoolcost_ti,
            sum(actservcost) over (partition by wogroup) as actservcost_ti,
            sum(actlabcost + actmatcost + acttoolcost + actservcost) over (partition by wogroup) as acttotalcost_ti,
            rowstamp as other_wo_columns
        from
             workorder
        )
    where
        istask=0


    Result:


    The task costs are rolled up to the WO rows. And the task rows are hidden.


    Summary:

    The benefit of this approach is that it performs well because it only does a single full table scan. It avoids the need for a self-join (via GROUP BY and JOIN). More info here: Group by x, get other fields too (comparing options and performance).
    It's also easy to read.

    (There is another option that's probably just as good. It's a simplified GROUP BY option that takes advantage of the selective aggregates technique.)



    Notes:

    1. The cost columns are not nullable. So we don't need to worry about converting nulls to zeros (to avoid doing math on nulls; 1+null=null).
    2. Yes, I'm aware that I should include SITE and WOCLASS in the WHERE clause.
    3. I found this course on PluralSight to be very helpful for learning about window functionsAdvanced SQL Queries in Oracle and SQL Server. That course might be a bit dated, but it was still useful.
      • I was always nervous about using window functions/analytic functions. I didn't understand until recently that window functions are applied last to a resultset. Window functions only add additional information to the resultset as an extra column. They don't add or remove rows to the resultset, which is what I was afraid of.
    4. Here's a related post where I took a different approach (self-join via GROUP BY/JOIN): Select WO actuals and include task actuals?


  • 8.  RE: SQL Window Functions: Rollup WO costs (including tasks)

    Posted 24 days ago
    Thanks John. Good to know.