1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82
|
CREATE PROCEDURE [dbo].[GetOperationResultsTotalGoodBadLast]
@SelectionGroupID int = 3469,
@OperationType int = 0,
@CaliberID int = NULL,
@IcID int = NULL,
@ModelID int = NULL,
@ProgramNameID int = NULL,
@TestID int = NULL,
@ProgramID int = NULL,
@RecipeID int = NULL,
@FactoryID int = NULL,
@DeviceGroupID int = NULL,
@DeviceID int = NULL,
@OrderNumber nvarchar(25) = NULL,
@OrderOperation nvarchar(5) = NULL,
@DateStart datetime2(7) = '1900.01.01',
@DateEnd datetime2(7) = '2999.01.01'
AS
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
--
-- Create tempory table
--
CREATE TABLE #OperationResultTmp (
[PieceUID] bigint,
[DateStart] datetime2(7)
)
--
-- Request
--
INSERT INTO #OperationResultTmp ([PieceUID], [DateStart])
SELECT [o].[PieceUID],
MAX([o].[DateStart]) AS [DateStart]
FROM [dbo].[OperationResults] [o]
INNER JOIN [dbo].[Pieces] [p] ON [o].[PieceUID] = [p].[PieceUID]
WHERE ([p].[SelectionGroupID] = @SelectionGroupID) AND
([p].[Deleted] = 0) AND
([o].[Type] = @OperationType) AND
(@CaliberID IS NULL OR [o].[SelectionOR10] = @CaliberID) AND
(@IcID IS NULL OR [o].[SelectionOR11] = @IcID) AND
(@ModelID IS NULL OR [o].[SelectionOR12] = @ModelID) AND
(@ProgramNameID IS NULL OR [o].[SelectionOR01] = @ProgramNameID) AND
(@TestID IS NULL OR [o].[SelectionOR02] = @TestID) AND
(@ProgramID IS NULL OR [o].[SelectionOR03] = @ProgramID) AND
(@RecipeID IS NULL OR [o].[SelectionOR04] = @RecipeID) AND
(@FactoryID IS NULL OR [o].[SelectionOR07] = @FactoryID) AND
(@DeviceGroupID IS NULL OR [o].[SelectionOR08] = @DeviceGroupID) AND
(@DeviceID IS NULL OR [o].[SelectionOR09] = @DeviceID) AND
(@OrderNumber IS NULL OR [o].[OperationInfoID] IN (SELECT [o1].[OperationInfoID] FROM dbo.OperationInfos [o1] WHERE [o1].[OrderNumber] LIKE @OrderNumber AND (@OrderOperation IS NULL OR [o1].[OrderOperation] LIKE @OrderOperation))) AND
(DATEDIFF(minute, @DateStart, [o].[DateStart])) >= 0 AND
(DATEDIFF(minute, @DateEnd, [o].[DateStart])) <= 0
GROUP BY [o].[PieceUID]
ORDER BY [o].[PieceUID]
--
-- Select and return results
--
SELECT COUNT(*) AS [Total],
SUM(CASE WHEN [o].[Flag] = 0 THEN 1 ELSE 0 END) AS [Good]
FROM [dbo].[OperationResults] [o]
INNER JOIN #OperationResultTmp [p] ON [o].[PieceUID] = [p].[PieceUID] AND [o].DateStart = [p].DateStart
END
GO |
Partager