I have written a query to get Production Quantity for either 1st shift, 2nd shift, 3rd shift or all shifts - based on the passed parameters.
Shift timings are following:
1st: 06:00 - 14:00
2nd: 14:00 - 22:00
3rd: 22:00 - 06:00
My query looks like following:
// Param.1 = date start , Param.2 = date end , Param.3 = shift start time , Param.4 = shift end time
// Param.5 = 1 when ShiftStarttime < ShiftEndtime ( Param.1 = 2015-12-26T06:00:00, param.2 = 2015-12-26T14:00:00, param.3 = 6 , param.4 = 10 )
// Param.5 = 2 when ShiftStarttime > ShiftEndtime ( Param.1 = 2015-12-26T22:00:00, param.2 = 2015-12-27T06:00:00, param.3 = 22 , param.4 = 2 )
// Param.5 = 3 when all of the shifts ( Param.1 = 2015-12-26T06:00:00, param.2 = 2015-12-27T06:00:00)
SELECT
B.Machine_NAME,
C.Part_Name,
SUM(A.QT_CONFIRMED) QT_CONFIRMED,
D.SCRAP_REASON
FROM
CONFIRMATION A
INNER JOIN MACHINE B ON A.Machine_ID =B.Machine_ID
INNER JOIN PART C ON B.Part_ID = C.Part_ID
LEFT JOIN SCRAP_REASON D ON A.SCRAP_REASON_ID =D.SCRAP_REASON_ID
WHERE
'[Param.5]' = '1'
AND (A.DT_CONFIRMATION BETWEEN '[Param.1]' AND '[Param.2]') AND ((DATEPART(hour, DT_CONFIRMATION) >= '[Param.3]') AND (DATEPART(hour, DT_CONFIRMATION) < '[Param.4]'))
GROUP BY
B.Machine_NAME,
C.Part_Name,
D.SCRAP_REASON
UNION
SELECT
B.Machine_NAME,
C.Part_Name,
SUM(A.QT_CONFIRMED) QT_CONFIRMED,
D.SCRAP_REASON
FROM
CONFIRMATION A
INNER JOIN MACHINE B ON A.Machine_ID =B.Machine_ID
INNER JOIN PART C ON B.Part_ID = C.Part_ID
LEFT JOIN SCRAP_REASON D ON A.SCRAP_REASON_ID =D.SCRAP_REASON_ID
WHERE
'[Param.5]' = '2'
AND (A.DT_CONFIRMATION BETWEEN '[Param.1]' AND '[Param.2]') AND (DATEPART(hour, DT_CONFIRMATION) BETWEEN 22 AND 23 OR DATEPART(hour, DT_CONFIRMATION) BETWEEN 0 and 5)
GROUP BY
B.Machine_NAME,
C.Part_Name,
D.SCRAP_REASON
UNION
SELECT
B.Machine_NAME,
C.Part_Name,
SUM(A.QT_CONFIRMED) QT_CONFIRMED,
D.SCRAP_REASON
FROM
CONFIRMATION A
INNER JOIN MACHINE B ON A.Machine_ID =B.Machine_ID
INNER JOIN PART C ON B.Part_ID = C.Part_ID
LEFT JOIN SCRAP_REASON D ON A.SCRAP_REASON_ID =D.SCRAP_REASON_ID
WHERE
'[Param.5]' = '3'
AND (A.DT_CONFIRMATION BETWEEN '[Param.1]' AND '[Param.2]')
GROUP BY
B.Machine_NAME,
C.Part_Name,
D.SCRAP_REASON
So basically depending upon the shift start time and shift end time, only on eof the union of the query will be executed due to Param.5 parameter. Please have a look at COMMENT in the query to understand parameters.
This query runs fine and gives proper result but I'm sure that it can be optimized to run faster if somehow I can remove Union or can modify some part of it.
Does anyone has better idea to write this query in more optimal way? If yes, kindly do share.
The two queries appear to be identical except for the WHERE clauses, which are different. Try connecting the two WHERE clauses into a single query using OR. Note that I added DISTINCT to the SELECT statement to make sure that duplicates still get removed (which the UNION operator was doing in your original query).
SELECT DISTINCT
B.Machine_NAME,
C.Part_Name,
SUM(A.QT_CONFIRMED) QT_CONFIRMED,
D.SCRAP_REASON
FROM
CONFIRMATION A
INNER JOIN MACHINE B ON A.Machine_ID = B.Machine_ID
INNER JOIN PART C ON B.Part_ID = C.Part_ID
LEFT JOIN SCRAP_REASON D ON A.SCRAP_REASON_ID = D.SCRAP_REASON_ID
WHERE
(
'[Param.5]' = '1' AND
(A.DT_CONFIRMATION BETWEEN '[Param.1]' AND '[Param.2]') AND
(
(DATEPART(hour, DT_CONFIRMATION) >= '[Param.3]') AND
(DATEPART(hour, DT_CONFIRMATION) < '[Param.4]')
)
)
OR
(
'[Param.5]' = '2' AND
(A.DT_CONFIRMATION BETWEEN '[Param.1]' AND '[Param.2]') AND
(
DATEPART(hour, DT_CONFIRMATION) BETWEEN 22 AND 23 OR
DATEPART(hour, DT_CONFIRMATION) BETWEEN 0 and 5
)
)
GROUP BY
B.Machine_NAME,
C.Part_Name,
D.SCRAP_REASON