SELECT S.ID, S.NAME, S.MinDate, S.MaxDate, MAX(IOT.SENSOR_ID) as SENSOR_ID, IOT.SENSORQUANTITY, IOT.VALUEDATATYPE, MIN(IOT.NUMERICVALUE) as NumericValueMin, MAX(IOT.NUMERICVALUE) as NumericValueMax, AVG(IOT.NUMERICVALUE) as NumericValueAVG, CASE WHEN IOT.ValueDataType = 1 THEN IOT.SHORTSTRINGVALUE ELSE 'X' END AS SHORTSTRINGVALUE, --stringvalue může být neprázdná i u jiného typu, pak to namnožilo řádky CASE WHEN IOT.ValueDataType = 2 THEN IOT.DATETIMEVALUE$DATE ELSE 0 END AS DATETIMEVALUE$DATE, SUM(case when IOT.BOOLEANVALUE = 'A' then 1 else 0 end) as NumberOfYes, SUM(case when IOT.BOOLEANVALUE = 'N' then 1 else 0 end) as NumberOfNo, MAX(IOTS.NAME) AS SensorName, COUNT(*) as AggregCount FROM (SELECT WP.ID, WP.NAME, MIN(O.STARTEDAT$DATE) AS MinDate,MAX(O.FINISHEDAT$DATE) AS MaxDate FROM PLMFINISHEDPRODUCTS2 FP2PARENT #IB LEFT JOIN PLMGETCHILDJOBORDERSSN(FP2PARENT.JOBORDERSSN_ID) T ON 1=1 #MS CROSS APPLY PLMGETCHILDJOBORDERSSN(FP2PARENT.JOBORDERSSN_ID) T #ORA JOIN TABLE(PLMGETCHILDJOBORDERSSN(FP2PARENT.JOBORDERSSN_ID)) T ON 1=1 #ALL JOIN PLMJOBORDERSSN SN ON SN.ID = T.ID JOIN PLMJOOUTPUTITEMS JOO ON JOO.ID = SN.PARENT_ID JOIN PLMJOBORDERSROUTINES JR ON JR.PARENT_ID = JOO.ID JOIN PLMOPERATIONS O ON O.JOBORDERSROUTINES_ID = JR.ID JOIN PLMWORKPLACES WP ON WP.ID = O.WORKPLACE_ID WHERE FP2PARENT.GENESIS_ID = :OBJID AND ((O.JOBORDERSSN_ID = SN.ID) OR (O.JOBORDERSSN_ID IS NULL) OR (O.JOBORDERSSN_ID = '')) GROUP BY WP.ID, WP.NAME) S LEFT JOIN IOTDATA IOT ON IOT.OBJECTCLASS = '5XGE0QFOGIWOB5ANMTYRIVKJ0O' AND IOT.OBJECT_ID = S.ID AND IOT.RECORDEDAT$DATE BETWEEN S.MinDate AND S.MaxDate LEFT JOIN IOTSENSORS IOTS ON IOTS.ID = IOT.SENSOR_ID GROUP BY S.ID, S.NAME, S.MinDate, S.MaxDate, IOT.SENSORQUANTITY, IOT.VALUEDATATYPE, CASE WHEN IOT.ValueDataType = 1 then IOT.SHORTSTRINGVALUE else 'X' end, CASE WHEN IOT.ValueDataType = 2 THEN IOT.DATETIMEVALUE$DATE ELSE 0 END
Generated by ABRA Software a.s. 27.10.2021 16:34:49