Click here to Skip to main content
15,314,303 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi,

C#
employee table-

employeeid,employeename,departmentid

timesheet table-

employeeid,startdate,enddate,hourstaken,departmentid

departmentactivity tabel-

departmentid,activityname


i want activity name also when i insert startdate,enddate and no. of hours.

i am using this queery it works fine for getting no of hours .

C#
SELECT e.employeename,convert(VARCHAR, t.startdate, 111) as startdate,SUM(t.hourstaken) as hourstaken
FROM employee e INNER JOIN timesheet t  ON e.employeeid=t.employeeid where convert(VARCHAR, t.startdate, 111)  = '2012/04/10'
GROUP BY e.employeeid,e.employeename,convert(VARCHAR, t.startdate, 111)  HAVING SUM(t.hourstaken) < 7 order by e.employeename


Thanks in Advance
Posted

1 solution

SQL
SELECT
   e.employeename
  ,CONVERT(VARCHAR, t.startdate, 111) as startdate
  ,SUM(t.hourstaken) as hourstaken
  ,d.activityname
FROM
  employee e
  INNER JOIN timesheet t ON e.employeeid = t.employeeid
  INNER JOIN departmentactivity d ON e.departmentid = d.departmentid
WHERE
  CONVERT(VARCHAR, t.startdate, 111) = '2012/04/10' --2012, October 4th
GROUP BY
   e.employeeid
  ,e.employeename
  ,CONVERT(VARCHAR, t.startdate, 111)
  ,d.activityname
HAVING
  SUM(t.hourstaken) < 7
ORDER BY
  e.employeename
   
v2
Comments
mayankshrivastava 21-Apr-12 6:27am
   
following error - Column 'd.ActivityName' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
phil.o 21-Apr-12 7:02am
   
See my updated solution.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900