The LisRT results database

SQL Examples for the LisRT Results Database

Here you will find typical SQL queries for the LisRT results database.

Querying All Results of a Workpiece

Replace workpiece1234 with the actual ID of the workpiece to display the results of the last measurement cycle for this workpiece.

MS SQL Server
PostgreSQL
MariaDB
SQLite

SELECT
    a.[Key] AS Attribute,
    a.ToleranceMin AS Min,
    r.Value,
    a.ToleranceMax AS Max
FROM
    Result r
    JOIN Attribute a ON r.AttributeId = a.ID
    JOIN MeasureProcess mp ON r.MeasureProcessID = mp.ID
    JOIN MeasureCycle mc ON mp.MeasureCycleId = mc.ID
    JOIN WorkPiece wp ON mc.WorkPieceId = wp.ID
WHERE
    mc.ID = (
        SELECT
            MAX(mc2.ID)
        FROM
            MeasureCycle mc2
            JOIN WorkPiece wp2 ON mc2.WorkPieceId = wp2.ID
        WHERE
            wp2.ItemIdentifier = 'workpiece1234'
    );

SELECT
    a.Key AS Attribute,
    a.ToleranceMin AS Min,
    r.Value,
    a.ToleranceMax AS Max
FROM
    Result r
    JOIN Attribute a ON r.AttributeId = a.ID
    JOIN MeasureProcess mp ON r.MeasureProcessID = mp.ID
    JOIN MeasureCycle mc ON mp.MeasureCycleId = mc.ID
    JOIN WorkPiece wp ON mc.WorkPieceId = wp.ID
WHERE
    mc.ID = (
        SELECT
            MAX(mc2.ID)
        FROM
            MeasureCycle mc2
            JOIN WorkPiece wp2 ON mc2.WorkPieceId = wp2.ID
        WHERE
            wp2.ItemIdentifier = 'workpiece1234'
    );

SELECT
    a.`Key` AS Attribute,
    a.ToleranceMin AS Min,
    r.Value,
    a.ToleranceMax AS Max
FROM
    Result r
    JOIN Attribute a ON r.AttributeId = a.ID
    JOIN MeasureProcess mp ON r.MeasureProcessID = mp.ID
    JOIN MeasureCycle mc ON mp.MeasureCycleId = mc.ID
    JOIN WorkPiece wp ON mc.WorkPieceId = wp.ID
WHERE
    mc.ID = (
        SELECT
            MAX(mc2.ID)
        FROM
            MeasureCycle mc2
            JOIN WorkPiece wp2 ON mc2.WorkPieceId = wp2.ID
        WHERE
            wp2.ItemIdentifier = 'workpiece1234'
    );

SELECT
    a.Key AS Attribute,
    a.ToleranceMin AS Min,
    r.Value,
    a.ToleranceMax AS Max
FROM
    Result r
    JOIN Attribute a ON r.AttributeId = a.ID
    JOIN MeasureProcess mp ON r.MeasureProcessID = mp.ID
    JOIN MeasureCycle mc ON mp.MeasureCycleId = mc.ID
    JOIN WorkPiece wp ON mc.WorkPieceId = wp.ID
WHERE
    mc.ID = (
        SELECT
            MAX(mc2.ID)
        FROM
            MeasureCycle mc2
            JOIN WorkPiece wp2 ON mc2.WorkPieceId = wp2.ID
        WHERE
            wp2.ItemIdentifier = 'workpiece1234'
    );

Querying the Values of an Attribute for an Order

Replace order1234 with the order ID and Voltage with the desired attribute name.

MS SQL Server
PostgreSQL
MariaDB
SQLite

SELECT
    a.ToleranceMin AS Min,
    r.Value,
    a.ToleranceMax AS Max
FROM
    Result r
    JOIN Attribute a ON r.AttributeId = a.ID
    JOIN MeasureProcess mp ON r.MeasureProcessID = mp.ID
    JOIN MeasureCycle mc ON mp.MeasureCycleId = mc.ID
    JOIN WorkPiece wp ON mc.WorkPieceId = wp.ID
    JOIN TypeData t ON mc.TypeDataId = t.ID
    JOIN [Order] o ON wp.OrderID = O.ID
WHERE
    a.[Key] = 'Voltage'
    AND o.Identifier = 'order1234

SELECT
    a.ToleranceMin AS Min,
    r.Value,
    a.ToleranceMax AS Max
FROM
    Result r
    JOIN Attribute a ON r.AttributeId = a.ID
    JOIN MeasureProcess mp ON r.MeasureProcessID = mp.ID
    JOIN MeasureCycle mc ON mp.MeasureCycleId = mc.ID
    JOIN WorkPiece wp ON mc.WorkPieceId = wp.ID
    JOIN TypeData t ON mc.TypeDataId = t.ID
    JOIN "Order" o ON wp.OrderID = o.ID
WHERE
    a."Key" = 'Voltage'
    AND o.Identifier = 'order1234'

SELECT
    a.ToleranceMin AS Min,
    r.Value,
    a.ToleranceMax AS Max
FROM
    Result r
    JOIN Attribute a ON r.AttributeId = a.ID
    JOIN MeasureProcess mp ON r.MeasureProcessID = mp.ID
    JOIN MeasureCycle mc ON mp.MeasureCycleId = mc.ID
    JOIN WorkPiece wp ON mc.WorkPieceId = wp.ID
    JOIN TypeData t ON mc.TypeDataId = t.ID
    JOIN `Order` o ON wp.OrderID = o.ID
WHERE
    a.`Key` = 'Voltage'
    AND o.Identifier = 'order1234'

SELECT
    a.ToleranceMin AS Min,
    r.Value,
    a.ToleranceMax AS Max
FROM
    Result r
    JOIN Attribute a ON r.AttributeId = a.ID
    JOIN MeasureProcess mp ON r.MeasureProcessID = mp.ID
    JOIN MeasureCycle mc ON mp.MeasureCycleId = mc.ID
    JOIN WorkPiece wp ON mc.WorkPieceId = wp.ID
    JOIN TypeData t ON mc.TypeDataId = t.ID
    JOIN "Order" o ON wp.OrderID = o.ID
WHERE
    a.Key = 'Voltage'
    AND o.Identifier = 'order1234'

Querying All Overall Results of an Order

Enter the desired order ID (e.g. order1234) to get all overall results for this order.

MS SQL Server
PostgreSQL
MariaDB
SQLite

SELECT
    wp.ItemIdentifier,
    mc.EndTimeStamp,
    mc.Classification,
    o.ID AS OrderID,
    wp.ID AS WorkPieceID
FROM
    MeasureCycle AS mc
    JOIN WorkPiece AS wp ON mc.WorkPieceId = wp.ID
    JOIN [Order] AS o ON o.ID = wp.OrderID
    JOIN (
        -- Subquery: Get the last MeasureCycle ID for each WorkPiece in the order
        SELECT
            wp2.ID AS WorkPieceID,
            MAX(mc2.ID) AS LastMeasureCycleID
        FROM
            MeasureCycle AS mc2
            JOIN WorkPiece AS wp2 ON mc2.WorkPieceId = wp2.ID
            JOIN [Order] AS o2 ON o2.ID = wp2.OrderID
        WHERE
            o2.Identifier = 'Order1234'
        GROUP BY
            wp2.ID
    ) AS max_mc ON mc.WorkPieceId = max_mc.WorkPieceID
    AND mc.ID = max_mc.LastMeasureCycleID

SELECT
    wp.itemidentifier,
    mc.endtimestamp,
    mc.classification,
    o.id AS orderid,
    wp.id AS workpieceid
FROM
    measurecycle mc
    INNER JOIN workpiece wp ON mc.workpieceid = wp.id
    INNER JOIN "Order" o ON o.id = wp.orderid
    INNER JOIN (
        -- Subquery: Get the last MeasureCycle ID for each WorkPiece in the order
        SELECT
            wp2.id AS workpieceid,
            MAX(mc2.id) AS lastmeasurecycleid
        FROM
            measurecycle mc2
            INNER JOIN workpiece wp2 ON mc2.workpieceid = wp2.id
            INNER JOIN "Order" o2 ON o2.id = wp2.orderid
        WHERE
            o2.identifier = 'Order1234'
        GROUP BY
            wp2.id
    ) max_mc ON mc.workpieceid = max_mc.workpieceid
    AND mc.id = max_mc.lastmeasurecycleid;

SELECT
    wp.ItemIdentifier,
    mc.EndTimeStamp,
    mc.Classification,
    o.ID AS OrderID,
    wp.ID AS WorkPieceID
FROM
    MeasureCycle mc
    INNER JOIN WorkPiece wp ON mc.WorkPieceId = wp.ID
    INNER JOIN `Order` o ON o.ID = wp.OrderID
    INNER JOIN (
        -- Subquery: Get the last MeasureCycle ID for each WorkPiece in the order
        SELECT
            wp2.ID AS WorkPieceID,
            MAX(mc2.ID) AS LastMeasureCycleID
        FROM
            MeasureCycle mc2
            INNER JOIN WorkPiece wp2 ON mc2.WorkPieceId = wp2.ID
            INNER JOIN `Order` o2 ON o2.ID = wp2.OrderID
        WHERE
            o2.Identifier = 'Order1234'
        GROUP BY
            wp2.ID
    ) max_mc ON mc.WorkPieceId = max_mc.WorkPieceID
    AND mc.ID = max_mc.LastMeasureCycleID;

SELECT
    wp.ItemIdentifier,
    mc.EndTimeStamp,
    mc.Classification,
    o.ID AS OrderID,
    wp.ID AS WorkPieceID
FROM
    MeasureCycle mc
    INNER JOIN WorkPiece wp ON mc.WorkPieceId = wp.ID
    INNER JOIN "Order" o ON o.ID = wp.OrderID
    INNER JOIN (
        -- Subquery: Get the last MeasureCycle ID for each WorkPiece in the order
        SELECT
            wp2.ID AS WorkPieceID,
            MAX(mc2.ID) AS LastMeasureCycleID
        FROM
            MeasureCycle mc2
            INNER JOIN WorkPiece wp2 ON mc2.WorkPieceId = wp2.ID
            INNER JOIN "Order" o2 ON o2.ID = wp2.OrderID
        WHERE
            o2.Identifier = 'Order1234'
        GROUP BY
            wp2.ID
    ) max_mc ON mc.WorkPieceId = max_mc.WorkPieceID
    AND mc.ID = max_mc.LastMeasureCycleID;

Process Times of the Last 100 Parts of a Type

Minimum, maximum, and average times for the last 100 workpieces of a type are queried. The process times are suitable, for example, for display in a dashboard. Replace TypeData1234 with the desired type ID.

MS SQL Server
PostgreSQL
MariaDB
SQLite

SELECT
    AVG(ProcessTime) AS AvgProcessTime,
    MIN(ProcessTime) AS MinProcessTime,
    MAX(ProcessTime) AS MaxProcessTime
FROM (
    SELECT TOP 100
        DATEDIFF(millisecond, mp.StartTimeStamp, mp.EndTimeStamp) as ProcessTime
    FROM
        MeasureProcess mp
        JOIN MeasureCycle mc ON mc.ID = mp.ID
        JOIN TypeData t ON t.ID = mc.TypeDataId
    WHERE
        t.TypeIdentifier = 'Type_1234'
    ORDER BY mp.EndTimeStamp DESC
) AS Last100;

SELECT
    AVG(processtime) AS avg_processtime,
    MIN(processtime) AS min_processtime,
    MAX(processtime) AS max_processtime
FROM (
    SELECT
        EXTRACT(EPOCH FROM (mp.endtimestamp - mp.starttimestamp)) * 1000 AS processtime
    FROM
        measureprocess mp
        JOIN measurecycle mc ON mc.id = mp.id
        JOIN typedata t ON t.id = mc.typedataid
    WHERE
        t.typeidentifier = 'Type_1234'
    ORDER BY mp.endtimestamp DESC
    LIMIT 100
) AS last100;

SELECT
    AVG(ProcessTime) AS AvgProcessTime,
    MIN(ProcessTime) AS MinProcessTime,
    MAX(ProcessTime) AS MaxProcessTime
FROM (
    SELECT
        TIMESTAMPDIFF(MICROSECOND, mp.StartTimeStamp, mp.EndTimeStamp) / 1000 AS ProcessTime
    FROM
        MeasureProcess mp
        JOIN MeasureCycle mc ON mc.ID = mp.ID
        JOIN TypeData t ON t.ID = mc.TypeDataId
    WHERE
        t.TypeIdentifier = 'Type_1234'
    ORDER BY mp.EndTimeStamp DESC
    LIMIT 100
) AS Last100;

SELECT
    AVG(ProcessTime) AS AvgProcessTime,
    MIN(ProcessTime) AS MinProcessTime,
    MAX(ProcessTime) AS MaxProcessTime
FROM (
    SELECT
        CAST((julianday(mp.EndTimeStamp) - julianday(mp.StartTimeStamp)) * 86400000 AS INTEGER) AS ProcessTime
    FROM
        MeasureProcess mp
        JOIN MeasureCycle mc ON mc.ID = mp.ID
        JOIN TypeData t ON t.ID = mc.TypeDataId
    WHERE
        t.TypeIdentifier = 'Type_1234'
    ORDER BY mp.EndTimeStamp DESC
    LIMIT 100
) AS Last100;

Feedback

Was this page helpful?

Glad to hear it! If you have any suggestions for improvement write to us.

Sorry to hear that. Please tell us what we can improve.

Last modified 16.12.2024