Query Conversion

• shantanusenin

  Old Hand

  Points: 308

  More actions

  January 25, 2008 at 3:54 am

  #93026

  Deal All

  Kindly help me to convert the query, its giving error "Identifiers are too long"

  Thanks and regards

  SELECT MTH.MONTH, T.ReqATDR1, T.ReqChange

  FROM MTH LEFT JOIN [SELECT Sum(BUMetrics.ReqPlanned) AS ReqATDR1, Sum(BUMetrics.ReqDeleted) +Sum(BUMetrics.ReqAdded) +Sum(BUMetrics.ReqModified) AS ReqChange,

  FORMAT(BUMetrics.ProjectCompletionDate, 'mm') AS MONTH, FORMAT(BUMetrics.ProjectCompletionDate, 'yyyy') AS YEAR

  FROM BUMetrics

  WHERE ((BUMetrics.BU) IN (Select BUName FROM Conditions) OR ((BUMetrics.Competance) IN (Select Competence FROM Conditions)))

  AND (BUMetrics.ProjectType) IN (Select ProjectType FROM Conditions) AND (BUMetrics.ProjectStatus IN (Select ProjectStatus FROM Conditions))

  AND (BUMetrics.Num)=(Select MAX(NUM) From BUMetrics Where Month = (Select DISTINCT Month FROM Conditions Where ISNUMERIC(Month))

  AND Year = (Select DISTINCT Year FROM Conditions Where ISNUMERIC(YEAR)))

  AND Month = (Select DISTINCT Month FROM Conditions Where ISNUMERIC(Month))

  AND Year = (Select DISTINCT Year FROM Conditions Where ISNUMERIC(YEAR))

  AND ProjectType='DR1DR4'

  GROUP BY

  FORMAT(BUMetrics.ProjectCompletionDate, 'mm'), FORMAT(BUMetrics.ProjectCompletionDate, 'yyyy')]. AS T ON (MTH.MONTH = T.MONTH) AND (MTH.YEAR = T.YEAR)

  WHERE MTH.YEAR = (Select DISTINCT Conditions.ReportingYear FROM Conditions WHERE ISNULL(Conditions.ReportingYear)=0)

  ORDER BY MTH.YEAR, MTH.MONTH;

• Mohammad Mazharuddin Ehsan

  SSCarpal Tunnel

  Points: 4552

  More actions

  January 25, 2008 at 4:35 am

  #771652

  First of all, replace the "[" and "]" with "(" and ")" and also remove the "." after "]"

  If the rest of the things are good it will solve the problem.

  -----------------------------------------------------------[font=Arial Black]Time Is Money[/font][font=Arial Narrow]Calculating the Number of Business Hours Passed since a Point of Time[/url][/font][font=Arial Narrow]Calculating the Number of Business Hours Passed Between Two Points of Time[/font]

• Grant Fritchey

  SSC Guru

  Points: 398909

  More actions

  January 25, 2008 at 5:53 am

  #771685

  You may run into problems with this:

  FORMAT(BUMetrics.ProjectCompletionDate, 'mm') AS MONTH, FORMAT(BUMetrics.ProjectCompletionDate, 'yyyy') AS YEAR

  That's not TSQL syntax.

  "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
  - Theodore Roosevelt

  Author of:
  SQL Server Execution Plans
  SQL Server Query Performance Tuning