What is the best way to join 2 tables: 1) hours worked, 2) employees - to get employee information (name, supervisor, etc.) for employees who recorded hours worked in a date range (the dates will be in the hours table.)?<br>I don't want to see employees who did not work in that date range.<br>I would start with something like:<br><br>SELECT hours.employee_id, hours.reg_hours, hours.ot_hours, employees.employee_name, employees.supervisor_id FROM Hours, Employees WHERE hours.employee_id = employee.employee_id. AND hours.record_date BETWEEN to_date('06/02/2000','MM/DD/YYYY') AND to_date('06/09/2000','MM/DD/YYYY');<br><br>This will probably be an SQL pass through query from MS Access to search through rather large Oracle tables. Performance is important. Thanks in advance!<br><br>
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
table joins - find all hours worked plus employee information
- Thread starter Kincaco
- Start date
Well, you could start with:<br><br>SELECT h.employee_id, <br> sum(h.reg_hours), <br> sum(h.ot_hours),<br> e.employee_name,<br> e.supervisor_id <br>FROM Hours h, Employees e <br>WHERE <br> h.employee_id = e.employee_id <br> AND h.record_date <br> BETWEEN<br> to_date('06/02/2000','MM/DD/YYYY') <br> AND<br> to_date('06/09/2000','MM/DD/YYYY')<br>GROUP BY h.employee_id,<br> e.employee_name,<br> e.supervisor_id <br>HAVING sum(h.reg_hours) + sum(h.ot_hours)>0;<br><br>This should get you the information you're looking for. Then you can start working on performance.
- Thread starter
- #3
Thanks carp. That join worked. The problem (which I failed to mention originally) is that I want to append this data to an Access table for temporary processing. Eventually I will be pulling information from 4 or 5 tables into one data set for reporting. The problem is that I am pulling from 2 groups of tables that were not designed to work together. I have to trim spaces from employee ids in one set before I can join them to another set. Trying to do this in an SQL pass-through query is causing more performance problems than it is solving. I can get the whole thing to work in MS Access without the pass-through queries but it takes about 6 minutes to run. I was hoping to get smarter about SQL queries so that I could improve the performance. If I can make this work better, the same logic will be applied to larger (much slower) queries. Some of the reports that we want to clean up can take up to 4 hours to run and other users notice when they are running.<br><br>Each of the following SQL works but I don't know how to combine them into one query:<br><br>SELECT salary_employee_hours.week_ending_date, salary_employee_hours.employee_id, <br>'Nontouch' AS labor_type, vw_salary_employee_master.employee_name, <br>vw_salary_employee_master.employee_site, vw_salary_employee_master.status_code, <br>vw_salary_employee_master.cost_element, vw_salary_employee_master.employee_type, <br>vw_salary_employee_master.exempt_type, vw_salary_employee_master.supervisor_id<br>FROM salary_employee_hours, vw_salary_employee_master<br>WHERE (salary_employee_hours.employee_id = vw_salary_employee_master.employee_id) <br>AND record_date BETWEEN TO_DATE('06/03/2000','MM/DD/YYYY') <br>AND TO_DATE('06/09/2000','MM/DD/YYYY');<br><br>SELECT LAPP.WEEK_ENDING_DATE, LAPP.DATE_OF_EMP_APPROVAL, LAPP.DATE_OF_SUP_APPROVAL, <br>rtrim(LAPP.EMPLOYEE_ID) AS EMPLOYEE_ID, 'Touch' AS Labor_Type<br>FROM LABOROWNER_LAPP<br>WHERE WEEK_ENDING_DATE BETWEEN TO_DATE('06/03/2000','MM/DD/YYYY') <br>And TO_DATE('06/09/2000','MM/DD/YYYY');<br><br>The dates come from an Access form where the user requests any of a number of reports from the data built by these and a few other queries. It's the building of the data table that takes a long time. The code behind the form creates the SQL and concantonates in the dates, runs it, then runs the report off the internal temp table. The only reason for the temp table is to pull the information together since I don't know how to put it all in one query.<br><br>I realize this is alot to read through. I appreciate your help. <br>Thanks,<br>Connie<br><br>
These tables seem fairly easy to join - but there don't seem to be any fields that refer to hours, etc.<br>Joining tables in the from clause rather than the where clause tends to make things a little easier to read, and is generally a good practice, especially with outer joins. Or did I miss something in your post?<br>SELECT salary_employee_hours.week_ending_date<br> , LAPP.DATE_OF_EMP_APPROVAL<br> , LAPP.DATE_OF_SUP_APPROVAL<br> , salary_employee_hours.employee_id<br> , 'Whatever' AS labor_type<br> , vw_salary_employee_master.employee_name<br> , vw_salary_employee_master.employee_site<br> , vw_salary_employee_master.status_code<br> , vw_salary_employee_master.cost_element<br> , vw_salary_employee_master.employee_type<br> , vw_salary_employee_master.exempt_type<br> , vw_salary_employee_master.supervisor_id<br>FROM salary_employee_hours<br> INNER JOIN vw_salary_employee_master ON salary_employee_hours.employee_id = vw_salary_employee_master.employee_id<br> INNER JOIN LABOROWNER_LAPP ON rtrim(LAPP.EMPLOYEE_ID) = salary_employee_hours.employee_id<br>WHERE record_date BETWEEN TO_DATE('06/03/2000','MM/DD/YYYY') AND TO_DATE('06/09/2000','MM/DD/YYYY')<br> AND WEEK_ENDING_DATE BETWEEN TO_DATE('06/03/2000','MM/DD/YYYY') And TO_DATE('06/09/2000','MM/DD/YYYY')<br> <p>Malcolm Wynden<br><a href=mailto:wynden@island.dot.net>wynden@island.dot.net</a><br><a href= > </a><br>
- Status
Similar threads
