J'ai créé une procédure stockée dans SQL Server 2008 R2. Il exécute un tableau croisé dynamic. Donc, ce dont j'ai besoin pour les prochaines étapes, ce sont 3 nouvelles colonnes, qui seraient créées dans cette variable de requête déclarée. Dans ces 3 colonnes, je dois effectuer un calcul.
Au début, mon code de procédures stockées: UPDATE Procédure stockée:
BEGIN SET NOCOUNT ON; DECLARE @colNo nvarchar(max) DECLARE @colSum nvarchar(max) DECLARE @query nvarchar(max) SET NOCOUNT ON; WITH vals AS ( SELECT DISTINCT ds.No FROM QRTestView ds ) SELECT @colNo = COALESCE(@colNo + ', ', '') + QUOTENAME(No) @colSum = COALESCE(@colSum + '+ ', '') + QUOTENAME(No) FROM vals ORDER BY No SET @query = 'SELECT *, [Bonus] + 125 as [Fee], ([Bonus] + 125) * [ALL] as [Fee2] FROM ( SELECT *, CASE WHEN 70-(100 - Quote) * 10 > -80 THEN 70-(100 - Quote) * 10 ELSE -70 END as [Bonus] FROM ( SELECT *, '+@colSum+' as [ALL] FROM ( SELECT CAST(ISIN as CHAR(12)) As No, CAST(Quote as CHAR(7)) As Quote, CAST(Quote as CHAR(7)) As Q FROM QRTestView WHERE Datum >= @from_val and Datum <= @to_val ) AS sel PIVOT ( COUNT(Q) FOR No IN ('+ @colNo +') ) AS p UNION ALL SELECT ''ALL'', *, '+@colSum+'[ALL] FROM ( SELECT CAST(No as CHAR(12)) As No, COUNT(CAST(Quote as CHAR(7))) As Quote FROM QRTestView WHERE Datum >= @from_val and Datum <= @to_val GROUP BY CAST(No as CHAR(12)) ) AS sel PIVOT ( MAX(Quote) FOR No in ('+ @colNo +') ) AS p ) AS sel2 ) as ff ORDER BY CASE WHEN Quote = ''ALL'' THEN 101 ELSE CAST(Quote as INT) END DESC' EXEC sp_executesql @query, N'@from_val datetime, @to_val datetime', @from_val = @from, @to_val = @to END
Pour comprendre la question, je vais vous montrer la table source:
| No | Quote | Datum | DE10101 | 100 | 2016-01-01 | DE10121 | 100 | 2016-01-02 | DE10101 | 100 | 2016-01-05 | DE22034 | 98 | 2016-01-05 | DE10101 | 98 | 2016-01-10 | DE10121 | 80 | 2016-01-10 | DE22034 | 98 | 2016-01-10 | DE22034 | 80 | 2016-01-11 | DE10101 | 100 | 2016-01-20 | DE10121 | 80 | 2016-01-21
Et la table cible a 3 colonnes supplémentaires:
| Quote | DE10101 | DE10121 | DE22034 | ALL | Bonus | Fee | Fee2 | | 100 | 3 | 1 | 0 | 4 | 70 | 195 | 780 | | 98 | 1 | 0 | 2 | 3 | 50 | 175 | 525 | | 80 | 0 | 2 | 1 | 3 | -70 | 55 | 165 | | ALL | 4 | 3 | 3 | 10 | | | 1470 |
les 3 nouvelles colonnes sont: Bonus, Fee, Fee2
Le calcul fonctionne comme suit:
Bonus: WHEN 70-(100 - Quote) * 10 > -80 THEN 70-(100 - Quote) * 10 ELSE -70
Frais: Bonus + 125
Frais 2: ALL * Fee
Comment puis-je définir ces 3 colonnes et calcul dans ma variable de requête dans la procédure stockée?
Vous devez utiliser CTE
SET NOCOUNT ON; DECLARE @colNo nvarchar(max) DECLARE @SUMCols nvarchar(max) DECLARE @query nvarchar(max) SET NOCOUNT ON; WITH vals AS ( SELECT DISTINCT ds.[No] FROM QRTestView ds ) SELECT @colNo = COALESCE(@colNo + ', ', '') + QUOTENAME([No]), @SUMCols = COALESCE(@SUMCols + ', ', '') + 'SUM(' + QUOTENAME([No]) +')' FROM vals ORDER BY No SELECT @query = N' ;WITH cte as ( SELECT *, [Bonus] + 125 as [Fee], ([Bonus] + 125) * [ALL] as [Fee2] FROM ( SELECT *, CASE WHEN 70-(100 - Quote) * 10 > -80 THEN 70-(100 - Quote) * 10 ELSE -70 END as [Bonus] FROM ( SELECT *, '+REPLACE(@colNo,',','+')+' as [ALL] FROM ( SELECT CAST(No as CHAR(12)) As No, CAST(Quote as CHAR(7)) As Quote, CAST(Quote as CHAR(7)) As Q FROM QRTestView WHERE Datum >= @from_val and Datum <= @to_val ) AS sel PIVOT ( COUNT(Q) FOR No IN ('+@colNo+') ) AS p ) AS d ) as ff ) SELECT * FROM ( SELECT * FROM cte UNION ALL SELECT ''ALL'', '+@SUMCols+' SUM([ALL]), NULL, NULL, SUM(Fee2) FROM cte ) as t ORDER BY CASE WHEN Quote = ''ALL'' THEN 101 ELSE CAST(Quote as INT) END DESC' EXEC sp_executesql @query, N'@from_val datetime, @to_val datetime', @from_val = @from, @to_val = @to