
/*************************************************
* STORED stp_conteggioDtx
*
* esegue conteggio DataMatrix OK nell'intervallo First-Last
*
* Steamware, S.E.L.
* mod: 2014.03.07
*
*************************************************/
CREATE PROCEDURE [dmtx].[stp_conteggioDtx]
(
	@DataMatrixFirst NVARCHAR(50),
	@DataMatrixLast NVARCHAR(50)
)
AS

-- recupero le 2 data-ora dell'intervallo
DECLARE	@DtFirst	DATETIME,
		@DtLast		DATETIME

SELECT	@DtFirst = ISNULL(DataOra,GETDATE()) FROM dmtx.v_ElencoDatamatrixBMW
WHERE	DataMatrix = @DataMatrixFirst

SELECT	@DtLast = ISNULL(DataOra,@DtFirst) FROM dmtx.v_ElencoDatamatrixBMW
WHERE	DataMatrix = @DataMatrixLast

-- calcolo!
SELECT Trasc.Particolare
	, COUNT(Dtx.DataOra) AS Qta
FROM dmtx.v_ElencoDatamatrixBMW AS Dtx
LEFT JOIN dmtx.v_TrascDatamatrix AS Trasc
	ON dmtx.f_getPN(Dtx.DataMatrix) = Trasc.PartNumber
WHERE dtx.EsitoMarcatura = 'OK'
	AND (
		DataOra BETWEEN @DtFirst
			AND @DtLast
		)
	AND NOT (Trasc.Particolare IS NULL)
GROUP BY Trasc.Particolare

RETURN