Samuele E. Locatelli
58 lines
2.1 KiB
Transact-SQL
58 lines
2.1 KiB
Transact-SQL
-- =============================================
|
|
-- STORED PROCEDURE stp_QL_fullDataSearch
|
|
--
|
|
-- Author: Steamware
|
|
-- Mod date: 2014.02.11
|
|
-- Description: Recupera tutti i dati dalle tab QuoteList x avere schema con ricerca x cliente, item, materiale
|
|
-- =============================================
|
|
CREATE PROCEDURE [dbo].[stp_QL_fullDataSearch]
|
|
(
|
|
@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
|
|
|
|
-- cte x selezionare solo ultime revisioni...
|
|
;WITH myCte AS
|
|
(
|
|
SELECT QuoteType, CodQuote, MAX(QuoteRev) AS QuoteRev
|
|
FROM QuoteList
|
|
GROUP BY QuoteType, CodQuote
|
|
),
|
|
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
|
|
)
|
|
-- calcolo con aggiunta campi sommati...
|
|
SELECT vq.*
|
|
FROM qfq_filt vq
|
|
INNER JOIN myCte mc ON vq.QuoteType = mc.QuoteType AND vq.CodQuote = mc.CodQuote AND vq.QuoteRev = mc.QuoteRev
|
|
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
|
|
|
|
-- AND vq.ClientName LIKE '%{0}%' OR RawMat LIKE '%{0}%' OR CodItem LIKE '%{0}%' OR CONVERT(CodQuote, 'System.String') LIKE '%{0}%'
|
|
|
|
|
|
ORDER BY CodQuote DESC
|
|
|
|
RETURN
|