MP2 SQL Script

[usernameblocked]

My WTP and WWTP use MP2 as a CMMS. I’ve been writing SQL queries that use MP2 data to calculate various information/metrics. Not sure the best place to share these, but it looks like this forum is fairly active so I’ll start posting them here for anyone to use. Feel free to share your own or correct/critique mine (I'm a novice so feedback is useful).

The first SQL query I wanted to post calculates the total work order cost by Location given the close date of the work order. It also breaks the cost down by CM and PM costs. In the script below you can see the close date is for the most recent 30 days.

WITH cte (WONUM, EQNUM, WODEPARTMENT, WOLOCATION, DEPARTMENT, LOCATION, CM_LBHOURS, PM_LBHOURS, IN_LBHOURS, LBHOURS, CM_LBCOST, PM_LBCOST, IN_LBCOST, LBCOST, CM_MTCOST, PM_MTCOST, IN_MTCOST, MTCOST, CM_VDCOST, PM_VDCOST, IN_VDCOST, VDCOST)
AS
(SELECT WONUM, EQNUM, WODEPARTMENT, WOLOCATION, DEPARTMENT, LOCATION, CM_LBHOURS, PM_LBHOURS, IN_LBHOURS, LBHOURS, CM_LBCOST, PM_LBCOST, IN_LBCOST, LBCOST, CM_MTCOST, PM_MTCOST, IN_MTCOST, MTCOST, CM_VDCOST, PM_VDCOST, IN_VDCOST, VDCOST
FROM (
SELECT dbo.WO.WONUM, dbo.WOEQLIST.EQNUM, dbo.WOEQLIST.DEPARTMENT AS WODEPARTMENT, dbo.WOEQLIST.LOCATION AS WOLOCATION,
(SELECT SUM(ISNULL(dbo.WOE.REGHRS,0)+ISNULL(dbo.WOE.OTHRS,0)) from dbo.WOE WHERE dbo.WO.WONUM=dbo.WOE.WONUM AND dbo.WOEQLIST.EQNUM=dbo.WOE.EQNUM AND dbo.WO.WOTYPE='CM') AS CM_LBHOURS,
(SELECT SUM(ISNULL(dbo.WOE.REGHRS,0)+ISNULL(dbo.WOE.OTHRS,0)) from dbo.WOE WHERE dbo.WO.WONUM=dbo.WOE.WONUM AND dbo.WOEQLIST.EQNUM=dbo.WOE.EQNUM AND (dbo.WO.WOTYPE='PM' OR dbo.WO.WOTYPE='CMR')) AS PM_LBHOURS,
(SELECT SUM(ISNULL(dbo.WOE.REGHRS,0)+ISNULL(dbo.WOE.OTHRS,0)) from dbo.WOE WHERE dbo.WO.WONUM=dbo.WOE.WONUM AND dbo.WOEQLIST.EQNUM=dbo.WOE.EQNUM AND dbo.WO.WOTYPE='IN') AS IN_LBHOURS,
(SELECT SUM(ISNULL(dbo.WOE.REGHRS,0)+ISNULL(dbo.WOE.OTHRS,0)) from dbo.WOE WHERE dbo.WO.WONUM=dbo.WOE.WONUM AND dbo.WOEQLIST.EQNUM=dbo.WOE.EQNUM) AS LBHOURS,
(SELECT SUM((ISNULL(dbo.WOE.REGHRS,0)+ISNULL(dbo.WOE.OTHRS*dbo.WOE.OTFACTOR,0))*ISNULL(dbo.WOE.RATE,0)) from dbo.WOE WHERE dbo.WO.WONUM=dbo.WOE.WONUM AND dbo.WOEQLIST.EQNUM=dbo.WOE.EQNUM AND dbo.WO.WOTYPE='CM') AS CM_LBCOST,
(SELECT SUM((ISNULL(dbo.WOE.REGHRS,0)+ISNULL(dbo.WOE.OTHRS*dbo.WOE.OTFACTOR,0))*ISNULL(dbo.WOE.RATE,0)) from dbo.WOE WHERE dbo.WO.WONUM=dbo.WOE.WONUM AND dbo.WOEQLIST.EQNUM=dbo.WOE.EQNUM AND (dbo.WO.WOTYPE='PM' OR dbo.WO.WOTYPE='CMR')) AS PM_LBCOST,
(SELECT SUM((ISNULL(dbo.WOE.REGHRS,0)+ISNULL(dbo.WOE.OTHRS*dbo.WOE.OTFACTOR,0))*ISNULL(dbo.WOE.RATE,0)) from dbo.WOE WHERE dbo.WO.WONUM=dbo.WOE.WONUM AND dbo.WOEQLIST.EQNUM=dbo.WOE.EQNUM AND dbo.WO.WOTYPE='IN') AS IN_LBCOST,
(SELECT SUM((ISNULL(dbo.WOE.REGHRS,0)+ISNULL(dbo.WOE.OTHRS*dbo.WOE.OTFACTOR,0))*ISNULL(dbo.WOE.RATE,0)) from dbo.WOE WHERE dbo.WO.WONUM=dbo.WOE.WONUM AND dbo.WOEQLIST.EQNUM=dbo.WOE.EQNUM) AS LBCOST,
(SELECT SUM(ISNULL(dbo.WOP.TOTALUNITCOST, 0)) from dbo.WOP WHERE dbo.WO.WONUM=dbo.WOP.WONUM AND dbo.WOEQLIST.EQNUM=dbo.WOP.EQNUM AND dbo.WO.WOTYPE='CM') AS CM_MTCOST,
(SELECT SUM(ISNULL(dbo.WOP.TOTALUNITCOST, 0)) from dbo.WOP WHERE dbo.WO.WONUM=dbo.WOP.WONUM AND dbo.WOEQLIST.EQNUM=dbo.WOP.EQNUM AND (dbo.WO.WOTYPE='PM' OR dbo.WO.WOTYPE='CMR')) AS PM_MTCOST,
(SELECT SUM(ISNULL(dbo.WOP.TOTALUNITCOST, 0)) from dbo.WOP WHERE dbo.WO.WONUM=dbo.WOP.WONUM AND dbo.WOEQLIST.EQNUM=dbo.WOP.EQNUM AND dbo.WO.WOTYPE='IN') AS IN_MTCOST,
(SELECT SUM(ISNULL(dbo.WOP.TOTALUNITCOST, 0)) from dbo.WOP WHERE dbo.WO.WONUM=dbo.WOP.WONUM AND dbo.WOEQLIST.EQNUM=dbo.WOP.EQNUM) AS MTCOST,
(SELECT SUM(ISNULL(dbo.WOV.TOTALCOST, 0)) from dbo.WOV WHERE dbo.WO.WONUM=dbo.WOV.WONUM AND dbo.WOEQLIST.EQNUM=dbo.WOV.EQNUM AND dbo.WO.WOTYPE='CM') AS CM_VDCOST,
(SELECT SUM(ISNULL(dbo.WOV.TOTALCOST, 0)) from dbo.WOV WHERE dbo.WO.WONUM=dbo.WOV.WONUM AND dbo.WOEQLIST.EQNUM=dbo.WOV.EQNUM AND (dbo.WO.WOTYPE='PM' OR dbo.WO.WOTYPE='CMR')) AS PM_VDCOST,
(SELECT SUM(ISNULL(dbo.WOV.TOTALCOST, 0)) from dbo.WOV WHERE dbo.WO.WONUM=dbo.WOV.WONUM AND dbo.WOEQLIST.EQNUM=dbo.WOV.EQNUM AND dbo.WO.WOTYPE='IN') AS IN_VDCOST,
(SELECT SUM(ISNULL(dbo.WOV.TOTALCOST, 0)) from dbo.WOV WHERE dbo.WO.WONUM=dbo.WOV.WONUM AND dbo.WOEQLIST.EQNUM=dbo.WOV.EQNUM) AS VDCOST
from dbo.WO Inner JOIN dbo.WOEQLIST ON dbo.WO.WONUM=dbo.WOEQLIST.WONUM
where (dbo.WO.CLOSEDATE>=GETDATE()-30 AND dbo.WO.CLOSEDATE<=GETDATE())
AND (dbo.WO.WOTYPE='CM' OR dbo.WO.WOTYPE='CMR' OR dbo.WO.WOTYPE='IN' OR dbo.WO.WOTYPE='PM')
AND (dbo.WOEQLIST.LOCATION<>'-' AND dbo.WOEQLIST.LOCATION<>'EQUIPMENT STORAGE' AND dbo.WOEQLIST.LOCATION<>'RETIRED')
) AS cte1
FULL OUTER JOIN (SELECT DISTINCT DEPARTMENT, LOCATION FROM dbo.WOEQLIST
where (dbo.WOEQLIST.CLOSEDATE>=GETDATE()-1096 AND dbo.WOEQLIST.CLOSEDATE<=GETDATE())
AND (dbo.WOEQLIST.LOCATION<>'-' AND dbo.WOEQLIST.LOCATION<>'EQUIPMENT STORAGE' AND dbo.WOEQLIST.LOCATION<>'RETIRED')
) AS cte2
ON cte1.WOLOCATION=cte2.LOCATION
)
SELECT DEPARTMENT, LOCATION, ROUND((SELECT(SUM(ISNULL(LBHOURS,0)))),2) AS TOTAL_HOURS, CAST(ROUND((SELECT(SUM(ISNULL(LBCOST,0)))),2) AS MONEY) AS LABOR_COSTS,
CAST(ROUND((SELECT(SUM(ISNULL(MTCOST,0)))),2) AS MONEY) AS MATERIAL_COSTS, CAST(ROUND((SELECT(SUM(ISNULL(VDCOST,0)))),2) AS MONEY) AS VENDOR_COSTS, CAST(ROUND((SELECT SUM(ISNULL(LBCOST,0)+ISNULL(MTCOST,0)+ISNULL(VDCOST,0))),2) AS MONEY) AS TOTAL_COSTS,
CAST(ROUND((SELECT SUM(ISNULL(CM_LBCOST,0)+ISNULL(CM_MTCOST,0)+ISNULL(CM_VDCOST,0))),2) AS MONEY) AS CM_TOTAL_COSTS, CAST(ROUND((SELECT SUM(ISNULL(PM_LBCOST,0)+ISNULL(PM_MTCOST,0)+ISNULL(PM_VDCOST,0))),2) AS MONEY) AS PM_TOTAL_COSTS, CAST(ROUND((SELECT SUM(ISNULL(IN_LBCOST,0)+ISNULL(IN_MTCOST,0)+ISNULL(IN_VDCOST,0))),2) AS MONEY) AS IN_TOTAL_COSTS
from cte
GROUP BY DEPARTMENT, LOCATION
ORDER BY DEPARTMENT, LOCATION;