Problem in querry -joining three tables

Question

0.00/5 (No votes)

See more:

Hi, I am using querry for checking employees working how much hours in a day.

employee table -employeeid,employeename

timesheet - employeeid,totalhours,startdate

leave - leaveemployeeid,leavetype

C#

SELECT e.employeename,convert(VARCHAR, t.startdate, 103) as startdate,SUM(t.hourstaken) as hourstaken
 FROM employee e 
INNER JOIN 
timesheet t  ON e.employeeid=t.employeeid
 where convert(VARCHAR, t.startdate, 103) = '23/04/2012'
 GROUP BY e.employeeid,e.employeename,convert(VARCHAR, t.startdate, 103) --,leave.leavetype
 HAVING SUM(t.hourstaken) < 9 order by e.employeename

it works fine for getting no. of hours now i want to add one more field if any employee take half day.so i want in result one more column add name 'leavetype'
and any employee take half day so just wirte in colum half day.

Posted 26-Apr-12 20:36pm

mayankshrivastava

Updated 26-Apr-12 21:04pm

v2

Add a Solution

Comments

The Doer 27-Apr-12 2:45am

what is the datatype of leaveType?? varchar or you are taking in number of hours??

mayankshrivastava 27-Apr-12 3:04am

leave type is varchar,and above querry is works fine.
i want to add one more coloumn is leave type.

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

The Doer · Answer 1 · 2012-04-26T20:57:00

where convert(VARCHAR, t.startdate, 103) = '23/04/2012'

is wrong in Your Query, TimeSheet has no startDate column, Corrrect it with e.startDate..

Try This, Its working fine..

SQL

SELECT e.employeename,convert(VARCHAR, e.startdate, 103) as startdate,SUM(t.totalhours) as hourstaken,l.leavetype
 FROM employee e
INNER JOIN
timesheet t  ON e.employeeid=t.employeeid
inner join
leave l on l.leaveemployeeid=t.employeeid
 where convert(VARCHAR, e.startdate, 103) = '27/04/2012'
 GROUP BY e.employeeid,e.employeename,convert(VARCHAR, e.startdate, 103),l.leavetype
 HAVING SUM(t.totalhours) < 9 order by e.employeename

idds24 · Answer 2 · 2012-04-26T21:02:00

Solution 2

Try this..

SQL

SELECT e.employeename,convert(VARCHAR, t.startdate, 103) as startdate,SUM(t.hourstaken) as hourstaken
 FROM employee e
INNER JOIN
timesheet t  ON e.employeeid=t.employeeid
 where convert(VARCHAR, t.startdate, 103) = '23/04/2012'
INNER JOIN 
leave l ON e.employeeid = l.leaveemployeeid
 GROUP BY e.employeeid,e.employeename,convert(VARCHAR, t.startdate, 103), l.leavetype
 HAVING SUM(t.hourstaken) < 9 order by e.employeename

HTH!

Posted 26-Apr-12 21:02pm

idds24

Comments

The Doer 27-Apr-12 3:06am

how come t.startDate?? (timeSheet t) has no StartDate column..

idds24 27-Apr-12 3:38am

may be now author has edited it.

idds24 27-Apr-12 3:14am

I see it is there in the question..

employee table -employeeid,employeename
timesheet - employeeid,totalhours,startdate
leave - leaveemployeeid,leavetype