SELECT
S.Store_ID,
ST.NAME,
S.DateFrom,
S.DateTo,
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,
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 DISTINCT
SD2.STORE_ID,
SD.DOCDATE$DATE AS DateFrom,
COALESCE(SDA.DOCDATE$DATE, 100000) AS DateTo
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 DOCROWBATCHES DRB ON DRB.STOREBATCH_ID = SN.STOREBATCH_ID
JOIN STOREDOCUMENTS2 SD2 ON SD2.ID = DRB.PARENT_ID
JOIN STOREDOCUMENTS SD ON SD.DOCUMENTTYPE IN ('28', '24') AND SD.ID = SD2.PARENT_ID --prijmy do skladu
LEFT JOIN DOCROWBATCHES DRBA ON DRBA.STOREBATCH_ID = SN.STOREBATCH_ID AND DRBA.ID <> DRB.ID
LEFT JOIN STOREDOCUMENTS2 SD2A ON SD2A.ID = DRBA.PARENT_ID AND SD2A.FLOWTYPE IN ('22')
LEFT JOIN STOREDOCUMENTS SDA ON SDA.ID = SD2A.PARENT_ID AND SDA."DOCDATE$DATE" > SD."DOCDATE$DATE"
LEFT JOIN DOCROWBATCHES DRBB ON DRBB.STOREBATCH_ID = SN.STOREBATCH_ID AND DRBB.ID <> DRB.ID AND DRBB.ID <> DRBA.ID --sparovani prijmu a nasledujiciho vydeje - vydejka s nizsim datem nesmi existovat
LEFT JOIN STOREDOCUMENTS2 SD2B ON SD2B.ID = DRBB.PARENT_ID
LEFT JOIN STOREDOCUMENTS SDB ON SDB.DOCUMENTTYPE IN ('22') AND SDB.ID = SD2B.PARENT_ID AND SDB."DOCDATE$DATE" < SDA."DOCDATE$DATE"
WHERE
FP2PARENT.GENESIS_ID = :OBJID
AND SDB.ID IS NULL
) S
JOIN STORES ST ON ST.ID = S.Store_ID
LEFT JOIN IOTDATA IOT ON IOT.OBJECTCLASS = 'SFMSB5W15BDL3CL100C4RHECN0' AND IOT.OBJECT_ID = S.Store_ID AND IOT.RECORDEDAT$DATE BETWEEN S.DateFrom AND S.DateTo
LEFT JOIN IOTSENSORS IOTS ON IOTS.ID = IOT.SENSOR_ID
GROUP BY S.Store_ID,ST.NAME, S.DateFrom, S.DateTo, 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