Get minimum value, maximum value in each column using the case clause-CodePudding

I'm trying to collect product tracking data and divide it by equipment. Here's the source table.

[dbo].[tbSerialTracking](
[IP] [nchar](15) NOT NULL,
[DATA_TIME] [datetime] NOT NULL,
[SERIAL] [nvarchar](50) NULL,
CONSTRAINT [PK_tbSerialTracking] PRIMARY KEY CLUSTERED 
(
[IP] ASC,
[DATA_TIME] ASC
)

INSERT INTO [POP].[dbo].[tbSerialTracking]
VALUES
('192.168.10.147', '2022-06-08 10:48:26.297', 'PTP22031000070 64276284028'),
('192.168.10.152', '2022-06-08 10:47:59.280', 'PTP22031000073 64276284028'),
('192.168.10.17', '2022-06-08 10:45:00.397', 'PTP22031000015 64276284028'),
('192.168.10.47', '2022-06-08 10:45:00.380', 'PTP22031000069 64276284028'),
('192.168.10.67', '2022-06-08 10:45:00.520', 'PTP22031000063 64276284028')

And, it is the table to join.

[dbo].[tbPTransfer](
[PLAN_PROD_NO] [varchar](50) NOT NULL,
[LOT_ID] [varchar](50) NULL,
[ITEM_BASE] [varchar](50) NOT NULL,
[START_TIME] [varchar](14) NULL,
[END_TIME] [varchar](14) NULL, ...)

INSERT INTO [POP].[dbo].[tbPTransfer]
VALUES
('PN202006080210', 'P03550060800D3AHLO00', 'PTP20030500142 64276284004', '20200608101014', '20200608113404'),
('PN202006080210', 'P03550060800F3V6E300', 'PTP20041500057 64276284004', '20200608114556', '20200608115240'),
('PN202006080210', 'P03550060800EFI1MW00', 'PTP20041500062 64276284004', '20200608114316', '20200608114953'),
('PN202006080210', 'P03550060800F1HGV400', 'PTP20041500064 64276284004', '20200608114425', '20200608115126'),
('PN202006080210', 'P03550060800F5FKZZ00', 'PTP20041500065 64276284004', '20200608114728', '20200608115421')

Now, I'm trying to get the result table by using this query.

DECLARE @table TABLE
(
IP NVARCHAR(19),
DATA_TIME DATETIME,
SERIAL NVARCHAR(50),
SERIAL_NEXT NVARCHAR(50),
SERIAL_BEFORE NVARCHAR(50)
);

DECLARE @dateS NCHAR(10);
DECLARE @dateE NCHAR(10);

SET @dateS = '2022-06-07';
SET @dateE = '2022-06-08';

INSERT INTO @table
SELECT
    *
FROM
    (SELECT 
        IP, DATA_TIME, SERIAL,
        LEAD(SERIAL) OVER (PARTITION BY IP ORDER BY DATA_TIME) AS SERIAL_NEXT,
        LAG(SERIAL) OVER (PARTITION BY IP ORDER BY DATA_TIME) AS SERIAL_BEFORE
    FROM [POP].[dbo].[tbSerialTracking]
    WHERE
        DATA_TIME > @dateS AND DATA_TIME < @dateE
    ) AS A
WHERE SERIAL != SERIAL_NEXT OR SERIAL != SERIAL_BEFORE

SELECT
*,
(DATEDIFF(SS, R0_TIME_START, R17_TIME_END) / 60.0) AS DURATION
FROM
(SELECT
    trans.LOT_ID AS SERIAL
    ,MIN(CASE WHEN IP = '192.168.10.17' THEN DATA_TIME END) AS  R0_TIME_START, MAX(CASE WHEN IP = '192.168.10.17' THEN DATA_TIME END) AS R0_TIME_END
    ,MIN(CASE WHEN IP = '192.168.10.27' THEN DATA_TIME END) AS  R1_TIME_START, MAX(CASE WHEN IP = '192.168.10.27' THEN DATA_TIME END) AS R1_TIME_END
    ,MIN(CASE WHEN IP = '192.168.10.37' THEN DATA_TIME END) AS  R2_TIME_START, MAX(CASE WHEN IP = '192.168.10.37' THEN DATA_TIME END) AS R2_TIME_END
    ,MIN(CASE WHEN IP = '192.168.10.47' THEN DATA_TIME END) AS  R3_TIME_START, MAX(CASE WHEN IP = '192.168.10.47' THEN DATA_TIME END) AS R3_TIME_END
    ,MIN(CASE WHEN IP = '192.168.10.57' THEN DATA_TIME END) AS  R4_TIME_START, MAX(CASE WHEN IP = '192.168.10.57' THEN DATA_TIME END) AS R4_TIME_END
    ,MIN(CASE WHEN IP = '192.168.10.67' THEN DATA_TIME END) AS  R5_TIME_START, MAX(CASE WHEN IP = '192.168.10.67' THEN DATA_TIME END) AS R5_TIME_END
    ,MIN(CASE WHEN IP = '192.168.10.77' THEN DATA_TIME END) AS  R6_TIME_START, MAX(CASE WHEN IP = '192.168.10.77' THEN DATA_TIME END) AS R6_TIME_END
    ,MIN(CASE WHEN IP = '192.168.10.87' THEN DATA_TIME END) AS  R7_TIME_START, MAX(CASE WHEN IP = '192.168.10.87' THEN DATA_TIME END) AS R7_TIME_END
    ,MIN(CASE WHEN IP = '192.168.10.97' THEN DATA_TIME END) AS  R8_TIME_START, MAX(CASE WHEN IP = '192.168.10.97' THEN DATA_TIME END) AS R8_TIME_END
    ,MIN(CASE WHEN IP = '192.168.10.107' THEN DATA_TIME END) AS R9_TIME_START, MAX(CASE WHEN IP = '192.168.10.107' THEN DATA_TIME END) AS R9_TIME_END
    ,MIN(CASE WHEN IP = '192.168.10.117' THEN DATA_TIME END) AS R10_TIME_START, MAX(CASE WHEN IP = '192.168.10.117' THEN DATA_TIME END) AS R10_TIME_END
    ,MIN(CASE WHEN IP = '192.168.10.127' THEN DATA_TIME END) AS R11_TIME_START, MAX(CASE WHEN IP = '192.168.10.127' THEN DATA_TIME END) AS R11_TIME_END
    ,MIN(CASE WHEN IP = '192.168.10.132' THEN DATA_TIME END) AS R12_TIME_START, MAX(CASE WHEN IP = '192.168.10.132' THEN DATA_TIME END) AS R12_TIME_END
    ,MIN(CASE WHEN IP = '192.168.10.137' THEN DATA_TIME END) AS R13_TIME_START, MAX(CASE WHEN IP = '192.168.10.137' THEN DATA_TIME END) AS R13_TIME_END
    ,MIN(CASE WHEN IP = '192.168.10.142' THEN DATA_TIME END) AS R14_TIME_START, MAX(CASE WHEN IP = '192.168.10.142' THEN DATA_TIME END) AS R14_TIME_END
    ,MIN(CASE WHEN IP = '192.168.10.147' THEN DATA_TIME END) AS R15_TIME_START, MAX(CASE WHEN IP = '192.168.10.147' THEN DATA_TIME END) AS R15_TIME_END
    ,MIN(CASE WHEN IP = '192.168.10.152' THEN DATA_TIME END) AS R16_TIME_START, MAX(CASE WHEN IP = '192.168.10.152' THEN DATA_TIME END) AS R16_TIME_END
    ,MIN(CASE WHEN IP = '192.168.10.167' THEN DATA_TIME END) AS R17_TIME_START, MAX(CASE WHEN IP = '192.168.10.167' THEN DATA_TIME END) AS R17_TIME_END
FROM
    @table AS A
LEFT JOIN
    (SELECT
        [LOT_ID]
        ,[ITEM_BASE]
        ,[START_TIME]
        ,[END_TIME]
    FROM [POP].[dbo].[tbPTransfer]
    WHERE
        START_TIME > CONVERT(CHAR(8), CONVERT(DATE, @dateS, 23), 112) AND
        END_TIME < CONVERT(CHAR(8), CONVERT(DATE, @dateE, 23), 112)) AS trans
ON A.SERIAL = trans.ITEM_BASE
WHERE SERIAL LIKE 'PTP%'
GROUP BY trans.LOT_ID) AS A
WHERE A.R0_TIME_START IS NOT NULL AND A.R17_TIME_END IS NOT NULL
ORDER BY R0_TIME_START

I tried to do my best for building queries but they took too much time to execute. Is there any point to fix for reducing execution time?

SQL Execution Plan

If you have any better method, please advise me.

CodePudding user response：

Your primary issue appears to be the join against tbPTransfer, which has no supporting index, and is therefore doing a full table scan per each outer row.

It's hard to say for certain which index would be the most beneficial (take the Missing Index recommendation in the plan with a pinch of salt). It depends which of the filtering and join conditions is most selective (limiting) to the final result, and whether pre-sorted grouping can be achieved.

Looking carefully at your join:

I note that a left-join as opposed to an inner join is strange, as you are grouping by trans.LOT_ID. Perhaps you should rethink that.
Because of the window functions and the primary key, tbSerialTracking will probably already be sorted by IP, DATA_TIME which is not useful for the join or grouping. So it's pointless to try achieve a merge join.
If START_TIME and END_TIME severely limit tbPTransfer then achieving a right hash-join might be preferable, after which the grouping would require a sort anyway. In which case you probably want one of the following indexes:
```
tbPTransfer (START_TIME, END_TIME) INCLUDE (LOT_ID, ITEM_BASE)
tbPTransfer (ITEM_BASE, START_TIME, END_TIME) INCLUDE (LOT_ID)
```

Another big issue is that the join between SERIAL and ITEM_BASE is mismatched: either both should be varchar or both be nvarchar.

There are other improvements:

SERIAL LIKE 'PTP%' can be pushed into the derived table.
To that end, the whole table variable can be obviated, by putting it all into a derived table.
The dates should be stored as date variables in the first place, in order to seek the new index. But you should store the values as varchar in separate variables in order to seek the other table also.

DECLARE @dateS date;
DECLARE @dateE date;

SET @dateS = '2022-06-07';
SET @dateE = '2022-06-08';

DECLARE @VdateS char(8) = CONVERT(CHAR(8), @dateS, 112);
DECLARE @VdateE char(8) = CONVERT(CHAR(8), @dateE, 112);

SELECT
    trans.LOT_ID AS SERIAL
    ,MIN(CASE WHEN IP = '192.168.10.17' THEN DATA_TIME END) AS  R0_TIME_START, MAX(CASE WHEN IP = '192.168.10.17' THEN DATA_TIME END) AS R0_TIME_END
    ,MIN(CASE WHEN IP = '192.168.10.27' THEN DATA_TIME END) AS  R1_TIME_START, MAX(CASE WHEN IP = '192.168.10.27' THEN DATA_TIME END) AS R1_TIME_END
    ,MIN(CASE WHEN IP = '192.168.10.37' THEN DATA_TIME END) AS  R2_TIME_START, MAX(CASE WHEN IP = '192.168.10.37' THEN DATA_TIME END) AS R2_TIME_END
    ,MIN(CASE WHEN IP = '192.168.10.47' THEN DATA_TIME END) AS  R3_TIME_START, MAX(CASE WHEN IP = '192.168.10.47' THEN DATA_TIME END) AS R3_TIME_END
    ,MIN(CASE WHEN IP = '192.168.10.57' THEN DATA_TIME END) AS  R4_TIME_START, MAX(CASE WHEN IP = '192.168.10.57' THEN DATA_TIME END) AS R4_TIME_END
    ,MIN(CASE WHEN IP = '192.168.10.67' THEN DATA_TIME END) AS  R5_TIME_START, MAX(CASE WHEN IP = '192.168.10.67' THEN DATA_TIME END) AS R5_TIME_END
    ,MIN(CASE WHEN IP = '192.168.10.77' THEN DATA_TIME END) AS  R6_TIME_START, MAX(CASE WHEN IP = '192.168.10.77' THEN DATA_TIME END) AS R6_TIME_END
    ,MIN(CASE WHEN IP = '192.168.10.87' THEN DATA_TIME END) AS  R7_TIME_START, MAX(CASE WHEN IP = '192.168.10.87' THEN DATA_TIME END) AS R7_TIME_END
    ,MIN(CASE WHEN IP = '192.168.10.97' THEN DATA_TIME END) AS  R8_TIME_START, MAX(CASE WHEN IP = '192.168.10.97' THEN DATA_TIME END) AS R8_TIME_END
    ,MIN(CASE WHEN IP = '192.168.10.107' THEN DATA_TIME END) AS R9_TIME_START, MAX(CASE WHEN IP = '192.168.10.107' THEN DATA_TIME END) AS R9_TIME_END
    ,MIN(CASE WHEN IP = '192.168.10.117' THEN DATA_TIME END) AS R10_TIME_START, MAX(CASE WHEN IP = '192.168.10.117' THEN DATA_TIME END) AS R10_TIME_END
    ,MIN(CASE WHEN IP = '192.168.10.127' THEN DATA_TIME END) AS R11_TIME_START, MAX(CASE WHEN IP = '192.168.10.127' THEN DATA_TIME END) AS R11_TIME_END
    ,MIN(CASE WHEN IP = '192.168.10.132' THEN DATA_TIME END) AS R12_TIME_START, MAX(CASE WHEN IP = '192.168.10.132' THEN DATA_TIME END) AS R12_TIME_END
    ,MIN(CASE WHEN IP = '192.168.10.137' THEN DATA_TIME END) AS R13_TIME_START, MAX(CASE WHEN IP = '192.168.10.137' THEN DATA_TIME END) AS R13_TIME_END
    ,MIN(CASE WHEN IP = '192.168.10.142' THEN DATA_TIME END) AS R14_TIME_START, MAX(CASE WHEN IP = '192.168.10.142' THEN DATA_TIME END) AS R14_TIME_END
    ,MIN(CASE WHEN IP = '192.168.10.147' THEN DATA_TIME END) AS R15_TIME_START, MAX(CASE WHEN IP = '192.168.10.147' THEN DATA_TIME END) AS R15_TIME_END
    ,MIN(CASE WHEN IP = '192.168.10.152' THEN DATA_TIME END) AS R16_TIME_START, MAX(CASE WHEN IP = '192.168.10.152' THEN DATA_TIME END) AS R16_TIME_END
    ,MIN(CASE WHEN IP = '192.168.10.167' THEN DATA_TIME END) AS R17_TIME_START, MAX(CASE WHEN IP = '192.168.10.167' THEN DATA_TIME END) AS R17_TIME_END

    ,(DATEDIFF(SS
        ,MIN(CASE WHEN IP = '192.168.10.17' THEN DATA_TIME END)
        ,MAX(CASE WHEN IP = '192.168.10.167' THEN DATA_TIME END)
     ) / 60.0) AS DURATION

FROM (
    SELECT *
    FROM (
        SELECT 
          IP,
          DATA_TIME,
          SERIAL,
          LEAD(SERIAL) OVER (PARTITION BY IP ORDER BY DATA_TIME) AS SERIAL_NEXT,
          LAG(SERIAL)  OVER (PARTITION BY IP ORDER BY DATA_TIME) AS SERIAL_BEFORE
        FROM [POP].[dbo].[tbSerialTracking]
        WHERE
            DATA_TIME > @dateS AND DATA_TIME < @dateE
          AND SERIAL LIKE 'PTP%'
    ) AS A
    WHERE SERIAL != SERIAL_NEXT OR SERIAL != SERIAL_BEFORE
) AS A

LEFT JOIN [POP].[dbo].[tbPTransfer] trans
    ON A.SERIAL = trans.ITEM_BASE AND
       trans.START_TIME > @VdateS AND
       trans.END_TIME < @VdateE

GROUP BY
  trans.LOT_ID

HAVING
      MIN(CASE WHEN IP = '192.168.10.17' THEN DATA_TIME END) IS NOT NULL
  AND MAX(CASE WHEN IP = '192.168.10.167' THEN DATA_TIME END) IS NOT NULL

ORDER BY R0_TIME_START;

db<>fiddle