jdjammer

Hey Guys.. I have a sql problem I was hoping you could help me with. I have two tables that have dates in them,
CREATE TABLE LABOR_RATE
(
Effective_Date DATE PRIMARY KEY ,
Labor_Rate NUMBER(5,2) NOT NULL
);


CREATE TABLE WORK_ORDER
(
WO# NUMBER ( 3) PRIMARY KEY ,
WO_Date DATE NOT NULL ,
VIN CHAR ( 4) NOT NULL ,
Mileage NUMBER ( 7) NOT NULL
);

I need to join these two tables together so I can view a work order and date with the effective labor rate and date for that work order. The results would look like:

WO# WO_DATE EFF_DATE LAB_RATE
---- --------- --------- --------
101 04-JUL-00 27-DEC-99 $50.00
102 04-JUL-01 27-DEC-99 $50.00
103 04-JUL-02 11-SEP-01 $52.50
104 04-JUL-03 26-OCT-02 $55.00
105 04-JUL-04 26-OCT-02 $55.00
106 01-JAN-05 26-OCT-02 $55.00
107 05-MAY-05 05-MAY-05 $57.50
108 04-JUL-05 22-JUN-05 $58.75
109 11-SEP-05 29-JUL-05 $60.00

Does anyone know of the SQL command I would use?

zen_tom

What database are you running? Plus, what happens if the effective labor rate changes halfway through a job?

ratbastid

Unless you have very standard labor rates, and only a few of them, it might not make a lot of sense to normalize this data into two tables. Based on the data you showed there, you'd have six LABOR_RATE records and nine WORK_ORDER records. Probably doesn't get you all that much to abstract your labor rates into their own table. I'd probably suck it up and duplicate a little data, especially if this was a frequent query.

However, how you'd do this is, you'd add an "ID" field to the LABOR_RATE table (probably an auto-incrementing integer) and assign that as PRIMARY KEY. Then you'd add a RATE_ID field to WORK_ORDER, and in each WORK_ORDER record, put the ID of the appropriate LABOR_RATE field.

Then, once you've got data in both tables to actually relate them, you join them with the SQL command:

SELECT * FROM WORK_ORDER, LABOR_RATE WHERE LABOR_RATE.ID = WORK_ORDER.RATE_ID;

You can list specific fields you want out of it by saying "SELECT WORK_ORDER.WO_ID, RATE.Rate FROM..."

I'm guessing you intend to use Effective_Date as the primary key on LABOR_RATE, which would probably work, though it's a better practice to use a key that is ONLY the key and doesn't actually carry any data meaning. It would be easy to just up and alter that value in one table or the other because the value changes, and that leaves you with data syncronization problems.

Incidentally, the classic naming convention for tables and fields is to have tables be First Capped and fields be lower case. SQL commands are traditionally typed all upper case, so it's easy to see what's what in a SQL statement:

SELECT * FROM Work_Order, Labor_Rate WHERE Labor_Rate.id = Work_Order.rate_id;

Lebell

I wouldn't create a table for this, I would create a view. But other than that, Ratbastid's code should work.

__________________
"Of all tyrannies, a tyranny exercised for the good of its victims may be the most oppressive. It may be better to live under robber barons than under omnipotent moral busybodies. The robber baron's cruelty may sometimes sleep, his cupidity may at some point be satiated; but those who torment us for our own good will torment us without end, for they do so with the approval of their own conscience." – C. S. Lewis

The ONLY sponsors we have are YOU!

Please Donate!

jdjammer

Thank you guys very much for responding! wow. I should have been more clear though. This is actually for a database class I am taking, and can not change the tables. We are using oracle mySQL. Also, I was able to get this out of my professor " My solution involved a "nested select statement" and the other solution involved an outer join." Thank you guys though! I really do appreciate the help.

jdjammer

If anyone was interested... this is the solution. I got it wrong.
COL WO# FORMAT 999
COL LAB_RATE FORMAT $90.00
COL JOB# FORMAT 9
COL LAB_HRS FORMAT 0.00
COL LAB_CHARGE FORMAT $999.00
COL "APPROVE?" FORMAT A8

SELECT A.WO#, A.WO_DATE, A.EFF_DATE, LABOR_RATE LAB_RATE
FROM
(SELECT WO#, WO_DATE, MAX(EFFECTIVE_DATE) EFF_DATE
FROM LABOR_RATE, WORK_ORDER
WHERE WO_DATE >= EFFECTIVE_DATE
GROUP BY WO#, WO_DATE) A
LEFT OUTER JOIN
(SELECT EFFECTIVE_DATE, LABOR_RATE
FROM LABOR_RATE) B
ON (A.EFF_DATE = B.EFFECTIVE_DATE)
ORDER BY WO# ;

Nimetic

Oracle mySQL?

You mean Oracle I think. Judging by those formatting commands. : >

Leviathan[NCV]

"LEFT OUTER JOIN" is a bit redundant, being left outer and right inner are the same...