Join Less than or Equal - 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 2000 Forums

Transact-SQL (2000)

Join Less than or Equal

Author

Topic

scoyle@gilbaneco.com
Starting Member

2 Posts

Posted - 2008-05-19 : 16:49:37

Hi all...I need to update a code and it's description for each employee number by work date. The table I'm getting the code and desc from has employee number, effective start date, code and description. There may be multiple records in the secondary file by emp# and date. The MAX(code) is good enough. Here is what I came up with but it does not work. Looking for alternate approaches. Thanks in advance.


Update JDELaborHours X  
Set  X.JobCode, X.JobCodeDescriptin = (
Select A.JobCode, A.JobCodeDescription
from     JobFacts A  
WHERE    (X.EmployeeNumber = A.EmployeeNumber) AND 
		(A.EffectiveDate =
                          (SELECT     MAX(B.EffectiveDate)
                            FROM          JobFacts B
                            WHERE      B.EmployeeNumber = X.EmployeeNumber AND B.EffectiveDate <= X.WorkDate)) AND 
                (A.JobCode =
                          (SELECT     MAX(C.JobCode)
                            FROM          JobFacts C
                            WHERE      C.EmployeeNumber = X.EmployeeNumber AND C.EffectiveDate = A.EffectiveDate))  )

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2008-05-19 : 19:55:03

When is this homework assignment due?

"Set X.JobCode, X.JobCodeDescriptin = (....." is incorrect syntax. Please look up the correct syntax in Books Online, correct your code, and then return if you would like some more help.

e4 d5 xd5 Nf6

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-20 : 07:19:18

SInce you've tried something i'm giving you a solution,

Update X  
Set  X.JobCode=A.JobCode,
X.JobCodeDescriptin = A.JobCodeDescription 
from     JDELaborHours X 
INNER JOIN JobFacts A  
ON X.EmployeeNumber = A.EmployeeNumber
WHERE A.EffectiveDate =
                          (SELECT     MAX(B.EffectiveDate)
                            FROM          JobFacts B
                            WHERE      B.EmployeeNumber = X.EmployeeNumber AND B.EffectiveDate <= X.WorkDate) 
AND 
A.JobCode =
                          (SELECT     MAX(C.JobCode)
                            FROM          JobFacts C
                            WHERE      C.EmployeeNumber = X.EmployeeNumber AND C.EffectiveDate = A.EffectiveDate)

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2008-05-20 : 09:20:23

Lame....he didn't even have compilable code.

e4 d5 xd5 Nf6

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2008-05-20 : 09:40:37

Plus....using nested subqueries in the WHERE clause? Now he's not even learning best practices.

e4 d5 xd5 Nf6

scoyle@gilbaneco.com
Starting Member

2 Posts

Posted - 2008-05-21 : 11:03:06

Thank you visakh16.

I realize a sub select in the where clause will give me poor performance but what is the alternative?
Should views be created specific for this application?

Thanks again...

cat_jesus
Aged Yak Warrior

547 Posts

Posted - 2008-05-21 : 23:52:11

quote:
Originally posted by scoyle@gilbaneco.com

Thank you visakh16.

I realize a sub select in the where clause will give me poor performance but what is the alternative?
Should views be created specific for this application?

Thanks again...

The alternative in this instance is inner joins using derived tables

An infinite universe is the ultimate cartesian product.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources