Query with Join Condition - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Query with Join Condition

Author

Topic

vision.v1
Yak Posting Veteran

72 Posts

Posted - 2010-06-15 : 11:45:44

Hi,

I have two tables Employee and Hours

Employee Table
---------------
Employeeid FirstName

111 Steven

HoursTable
----------

Employeeid weekNo glAccount weekEndDate weekHours

111 22 NULL 05/29/2010 88
111 22 145 05/29/2010 24.3
111 23 145 06/06/2010 44

Now i want to show the result set of the two table in single row like

Employeeid glAccount weekEnd1Date week1Hours weekEnd2Date week2Hours

111 NULL 05/29/2010 88 06/06/2010 0
111 145 05/29/2010 24.3 06/06/2010 44

please tell me how to join both the tables to get the result as shown above

I tried like below:

SELECT
a.employeeId,
'05/29/2010' [Week 1 End Date],
SUM(b.weekHours) week1Reg,
'06/06/2010' [Week 2 End Date],
SUM(c.weekHours) week2Reg
FROM
Employee a
LEFT JOIN
HoursTable b
ON
a.employeeid = b.employeeid
AND
b.weekNo = 22
LEFT JOIN
HoursTable c
ON
a.employeeId = c.employeeid
AND
c.weekNo = 23
GROUP BY
a.employeeid

but the result is showing wrong values

please suggest

Thanks

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-06-15 : 13:36:59

Your expected result doesn't make sense to me. On what basis are you splitting the records like how you are doing.

What is there is no more row like this?

111 22 NULL 05/29/2010 99

Can you provide a bigger sample of your data and show expected results.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources