Samuele E. Locatelli
42 lines
1.6 KiB
SQL
42 lines
1.6 KiB
SQL
-- =============================================
|
|
-- Author: Steamware
|
|
-- Mod date: 2014.05.23
|
|
-- Description: conteggio totale record QL
|
|
-- =============================================
|
|
CREATE PROCEDURE dbo.stp_QL_fullDataCount
|
|
(
|
|
@CodClient NVARCHAR(50) = ''
|
|
, @CodItem NVARCHAR(50) = ''
|
|
, @RawMat NVARCHAR(50) = ''
|
|
, @DataMin DATETIME
|
|
, @DataMax DATETIME
|
|
, @qtaMin INT = 0
|
|
, @qtaMax INT = 999999
|
|
, @QuoteType CHAR(1) = '*'
|
|
, @IdxQState INT = '-1'
|
|
, @search NVARCHAR(250) = '*'
|
|
)
|
|
AS
|
|
SET NOCOUNT ON
|
|
|
|
;WITH qfq_filt AS
|
|
(
|
|
SELECT *
|
|
FROM v_QuoteFull_Q
|
|
WHERE ClientName LIKE CASE WHEN @search <> '*' THEN '%'+@search+'%' ELSE ClientName END
|
|
OR RawMat LIKE CASE WHEN @search <> '*' THEN '%'+@search+'%' ELSE RawMat END
|
|
OR CodItem LIKE CASE WHEN @search <> '*' THEN '%'+@search+'%' ELSE CodItem END
|
|
OR CONVERT(NVARCHAR(30), CodQuote) LIKE CASE WHEN @search <> '*' THEN '%'+@search+'%' ELSE CONVERT(NVARCHAR(30), CodQuote) END
|
|
)
|
|
|
|
SELECT COUNT(DISTINCT CodQuote) AS num
|
|
FROM qfq_filt vq
|
|
WHERE vq.CodClient = CASE WHEN ISNULL(@CodClient,'') = '' THEN vq.CodClient ELSE @CodClient END
|
|
AND vq.CodItem = CASE WHEN ISNULL(@CodItem,'') = '' THEN vq.CodItem ELSE @CodItem END
|
|
AND vq.RawMat = CASE WHEN ISNULL(@RawMat,'') = '' THEN vq.RawMat ELSE @RawMat END
|
|
AND vq.CodQuote BETWEEN CONVERT(NVARCHAR(30), @DataMin, 12)*10000 AND (CONVERT(NVARCHAR(30), @DataMax, 12) +1 )*10000
|
|
AND vq.BatchQty BETWEEN @qtaMin AND @qtaMax
|
|
AND vq.QuoteType = CASE WHEN @QuoteType = '*' THEN vq.QuoteType ELSE @QuoteType END
|
|
AND vq.IdxQState = CASE WHEN @IdxQState = -1 THEN vq.IdxQState ELSE @IdxQState END
|
|
|
|
RETURN |