Not getting expected results for pivot query

• sandeep.bhutani

  Newbie

  Points: 5

  More actions

  March 10, 2009 at 3:16 am

  #135284

  I have a table having data:

  select * from rota

  empid workinghrs onDate

  ---------- ----------- -----------------------

  1 10 2009-01-01 00:00:00.000

  2 20 2009-01-01 00:00:00.000

  3 10 2009-01-01 00:00:00.000

  1 20 2009-01-02 00:00:00.000

  2 15 2009-01-02 00:00:00.000

  3 12 2009-01-02 00:00:00.000

  (6 row(s) affected)

  I am running following query:

  select empid, [1],[2] from (select empid, workingHrs, onDate, month(ondate) as d from rota) a

  pivot

  (

  sum(workingHrs)

  for d

  in ([1], [2])

  ) tab_b

  and getting following result:

  empid 1 2

  ---------- ----------- -----------

  1 10 NULL

  2 20 NULL

  3 10 NULL

  1 20 NULL

  2 15 NULL

  3 12 NULL

  (6 row(s) affected)

  Instead I was expecting

  10+20=30 for 1

  20+15=35 for 2

  and 10+12=22 for 3

  Where I am wrong. I want total hours worked by an emp month-wise

  Regards,
  Sandeep Bhutani
  http://codersdiary.somee.com

• Mark Cowne

  One Orange Chip

  Points: 26969

  More actions

  March 10, 2009 at 3:57 am

  #956641

  change

  select empid, [1],[2] from (select empid, workingHrs, onDate, month(ondate) as d from rota) a

  to

  select empid, [1],[2] from (select empid, workingHrs, month(ondate) as d from rota) a

  ____________________________________________________

  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