Link to home
Start Free TrialLog in
Avatar of jeannie Jones
jeannie JonesFlag for United States of America

asked on

Group by department with a divide function in a query

I have a table with the following fields
Id
WorkOrder
Description
Location
WorkType
Status
LFC.AssestDept
TargetStart
Target Start Hour
Scheduled StartHour
ActualStart Date
ActualStartHour
Actual Finish
ActualLAborHours
Lfc-Postaudit
LfcauditSupervisor
EstamatedLaborHours
AssignedOwnergroup
StatusDate
StausTime
ReportDate
ReportTime



I have a query  

SELECT Count([MaximoReport].[WorkOrder])/(SELECT Count(MaximoReport.WorkOrder) AS [Total LEWPM Den]     FROM MaximoReport     WHERE (((MaximoReport.WorkType)="PMINS"      Or (MaximoReport.WorkType)="PMOR"      Or (MaximoReport.WorkType)="PMPDM"     Or (MaximoReport.WorkType)="PMREG" Or (MaximoReport.WorkType)="PMRT")      AND ((MaximoReport.Status)<>"CAN")      AND ((IIf(Len([Target Start])>10,[Target Start],[Target Start]+[TargetStartHour]))     >=DateAdd("h",-11.8,[Forms]![ParameterReportF]![DateFrom])      And (IIf(Len([Target Start])>10,[Target Start],[Target Start]+[TargetStartHour]))    <DateAdd("h",23,[Forms]![ParameterReportF]![DateTo]))))*100 AS PerecentageCompleted
FROM MaximoReport
WHERE (((MaximoReport.WorkType) In ("PMINS","PMOR","PMPDM","PMREG","PMRT")) And ((MaximoReport.Status) Like "*COMP") And ((MaximoReport.[Target Start])>=DateAdd("h",-1,Forms!ParameterReportF!DateFrom) And (MaximoReport.[Target Start])<DateAdd("h",23,Forms!ParameterReportF!DateTo)) And ((MaximoReport.ActualLaborHours)<>"00:00") And ((MaximoReport.ActualStartDate)>=DateAdd("h",-11.8,Forms!ParameterReportF!DateFrom) And (MaximoReport.ActualStartDate)<DateAdd("h",23,Forms!ParameterReportF!DateTo)));

Open in new window

I need to be able to group by Lfc_assetdept. I can not seem to figure it out.
Avatar of aikimark
aikimark
Flag of United States of America image

Although this 'should' work, the database engine might object to having the (SELECT...) expression in the denominator.
SELECT Lfc_assetdept, Count([MaximoReport].[WorkOrder])/(SELECT Count(MaximoReport.WorkOrder) AS [Total LEWPM Den]
FROM MaximoReport
WHERE (MaximoReport.WorkType In ("PMINS", "PMOR", "PMPDM", "PMREG") AND 
((MaximoReport.Status)<>"CAN") 
AND ((IIf(Len([Target Start])>10,[Target Start],[Target Start]+[TargetStartHour]))     >=DateAdd("h",-11.8,[Forms]![ParameterReportF]![DateFrom]) 
And (IIf(Len([Target Start])>10,[Target Start],[Target Start]+[TargetStartHour]))    <DateAdd("h",23,[Forms]![ParameterReportF]![DateTo]))))*100 AS PerecentageCompleted 

FROM MaximoReport 

WHERE (((MaximoReport.WorkType) In ("PMINS","PMOR","PMPDM","PMREG","PMRT")) And ((MaximoReport.Status) Like "*COMP") And ((MaximoReport.[Target Start])>=DateAdd("h",-1,Forms!ParameterReportF!DateFrom) And (MaximoReport.[Target Start])<DateAdd("h",23,Forms!ParameterReportF!DateTo)) And ((MaximoReport.ActualLaborHours)<>"00:00") And ((MaximoReport.ActualStartDate)>=DateAdd("h",-11.8, Forms!ParameterReportF!DateFrom) 
And 
(MaximoReport.ActualStartDate)<DateAdd("h", 23, Forms!ParameterReportF!DateTo)))

Group By Lfc_assetdept

Open in new window

This question needs an answer!
Become an EE member today
7 DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform.
View membership options
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.