Year Function returns error in subquery

• huskerwendy

  Hall of Fame

  Points: 3633

  More actions

  September 23, 2009 at 9:39 am

  #385418

  I'm trying to execute a query using the Year function and it works if I just execute the query alone, but when I put it in the subquery, I get the following error:

  Msg 102, Level 15, State 1, Line 2

  Incorrect syntax near '2008'.

  here's the query:

  SELECT c.ClientID, c.ClientName, p.id, p.ProjectName,

  (SELECT SUM(t.Hours) FROM TimeSheets as t WHERE t.ProjectID = p.id GROUP BY t.ProjectID) as TotalHours,

  (SELECT t.ProjectID, SUM(t.Hours) FROM TimeSheets as t WHERE YEAR(t.TaskDate) = 2008 AND t.ProjectID = p.id GROUP BY t.ProjectID) as 2008Hours

  FROM Clients as c

  LEFT JOIN Projects as p on c.ClientID = p.ClientID

  ORDER BY c.ClientID

  But when I run the subquery for the 2008Hours by itself, I don't get any errors:

  SELECT t.ProjectID, SUM(t.Hours)

  FROM TimeSheets as t

  WHERE YEAR(t.TaskDate) = 2008

  GROUP BY t.ProjectID

  Any help would be greatly appreciated.

  Thanks.

  
  Wendy Schuman

• Mark Cowne

  One Orange Chip

  Points: 26952

  More actions

  September 23, 2009 at 9:48 am

  #1057605

  Try changing 2008Hours to [2008Hours]

  ____________________________________________________

  Deja View - The strange feeling that somewhere, sometime you've optimised this query before

  How to get the best help on a forum

  http://www.sqlservercentral.com/articles/Best+Practices/61537

• huskerwendy

  Hall of Fame

  Points: 3633

  More actions

  September 23, 2009 at 9:51 am

  #1057607

  That was the problem. Thanks!

  
  Wendy Schuman