0

I want to use IN @Parameter while using dateadd function in below code

 declare @interval int
--set @interval
SELECT ROW_NUMBER() OVER (ORDER BY datetime) AS Row, 
       RIGHT(LEFT(E.Name,  11), 8) AS Device, P.[DateTime],
       day(p.DateTime) AS Day,
       month(p.DateTime) AS Month,  
       P.AverageValue, p.StandardDeviation
FROM [dbo].[vManagedEntity] AS E  
INNER JOIN [Perf].[vPerfDaily] AS P ON E.ManagedEntityRowId = P.ManagedEntityRowId    
INNER JOIN [dbo].[vPerformanceRuleInstance] AS I ON 
P.PerformanceRuleInstanceRowId = I.PerformanceRuleInstanceRowId  
INNER JOIN [dbo].[vPerformanceRule] AS R ON R.RuleRowId = I.RuleRowId
WHERE     right(left(e.name,6),3)  IN (@Site) 
      AND R.CounterName = 'MPLS    Utilization % (Average)' 
      AND p.DateTime >= DATEADD(MM, (@interval), GetUTCDate())

I want to use like

p.DateTime >= DATEADD(MM, IN (@interval), GetUTCDate())

but it is not allowing it.

rbr94
  • 2,227
  • 3
  • 23
  • 39
Piyush Jain
  • 5
  • 6
  • What's the error you get? – rbr94 Nov 02 '16 at 12:43
  • An error occured during local Report processsing. An error occured during Report processing. Query execution failed for dataset 'dataset1. 'Incorrect Syntax near ','. – Piyush Jain Nov 02 '16 at 12:45
  • I mean the sql error. There should be something like that. Where do you actually set `@interval`? Is there a value set for it? – rbr94 Nov 02 '16 at 12:47
  • Yes i am taking value like last 3 months , last 6 months , last 1 year .. – Piyush Jain Nov 02 '16 at 12:49
  • Why do you actually want to use `IN`? `@interval` is an `int`. That does not make sense to me – rbr94 Nov 02 '16 at 12:52
  • actually data is from SCOM DW and i want to use it to generate the forecast Report and generate trendline – Piyush Jain Nov 02 '16 at 12:53
  • i want to take intervals like check values from last 3 months and Show graph and same for last 6 months values. – Piyush Jain Nov 02 '16 at 12:55
  • what i want is to get the values from last 3 months and Show the graph then get values from last 6 months and Show in same graph – Piyush Jain Nov 02 '16 at 12:59
  • Just a friendly tip, you may want to read over this page: [The How-To-Ask Guide](https://stackoverflow.com/help/how-to-ask) so you can always be sure that your questions are easily answerable and as clear as possible. Be sure to include any efforts you've made to fix the problem you're having, and what happened when you attempted those fixes. Also don't forget to your show code and any error messages! – Matt C Nov 02 '16 at 18:15

0 Answers0