The best way to write this sql query

Question

The best way to write this sql query

fdthompson35

SSC Rookie

Points: 27
More actions
February 25, 2015 at 2:07 pm

#398080

we want the following fields from the PERSON table
personid
personnum
fullnm
we want the following field from the ASSIGNPAYRULE TABLE
payruleid
where the personid from the PERSON table = the employerid in the ASSIGNPAYRULE table AND the expirationdtm field in the ASSIGNPAYRULE table is > today

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1003926 More actions · Answer 1

fdthompson35 (2/25/2015)
we want the following fields from the PERSON table
personid
personnum
fullnm
we want the following field from the ASSIGNPAYRULE TABLE
payruleid
where the personid from the PERSON table = the employerid in the ASSIGNPAYRULE table AND the expirationdtm field in the ASSIGNPAYRULE table is > today

You've written 90% of the SELECT query above. You should give it a shot and then post your query here if it doesn't work the way you'd expect.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Grant Fritchey SSC Guru Points: 398717 More actions · Answer 2

fdthompson35 (2/25/2015)
we want the following fields from the PERSON table
personid
personnum
fullnm
we want the following field from the ASSIGNPAYRULE TABLE
payruleid
where the personid from the PERSON table = the employerid in the ASSIGNPAYRULE table AND the expirationdtm field in the ASSIGNPAYRULE table is > today

Replace your first sentence with SELECT. Add comma's after the column names and make sure you assign the table owners. You've already got the FROM statement, just add a JOIN clause to define the rules. Then use the WHERE clause for the filter and you're there.

In short, we'll help you with your homework, but we won't do it for you.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning