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.
| Author |
Topic |
|
Massiv
Starting Member
1 Post |
 Posted - 2012-06-05 : 13:14:49
|
| Person I work with apparently uses a custom created Microsoft Access query to view employee hours for payroll.The POS system allows managers to go in and append an employees hours if someone forgets to punch in etc, but the query does not currently see these changes since that data is written to a separate database.The actual punch in data goes to "Emp_Clock_Data" table and is written under "adjusted_clockin_dttime" and "adjusted_clockout_dttime"If a manager goes in and changes the hours that data goes to "Sum_Employee_Clock_PC_Actual" table and is written under "adjusted_clock_in_date_time" and "adjusted_clock_out_date_time"The current query looks like this:SELECT dbo_Emp_Master.emp_last_name AS LName, dbo_Emp_Master.emp_first_name AS FName, dbo_Jobcode_Master.jobcode_name AS Jobe_Code, dbo_Emp_Clock_Data.adjusted_clockin_dttime AS In_Time, dbo_Emp_Clock_Data.adjusted_clockout_dttime AS Out_Time, Round(((adjusted_clockout_dttime-dbo_Emp_Clock_Data.adjusted_clockin_dttime)*24),2) AS HoursFROM (dbo_Jobcode_Master INNER JOIN dbo_Emp_Clock_Data ON dbo_Jobcode_Master.jobcode_id = dbo_Emp_Clock_Data.clock_jobcode_id) INNER JOIN dbo_Emp_Master ON dbo_Emp_Clock_Data.emp_id = dbo_Emp_Master.emp_idWHERE (((dbo_Emp_Clock_Data.adjusted_clockin_dttime) Between [Enter Start Date (mm/dd/yy):] And [Enter Finish Date (mm/dd/yy):]) AND ((dbo_Emp_Clock_Data.clock_jobcode_id) In (30,8,31)) AND ((dbo_Jobcode_Master.ent_id)=1))ORDER BY dbo_Emp_Clock_Data.clock_jobcode_id, dbo_Emp_Master.emp_last_name, dbo_Emp_Master.emp_first_name, dbo_Emp_Clock_Data.adjusted_clockin_dttime;I want it to show both the actual punch in time and the adjusted time.What do I have to do besides add in the additional table and values to get it to show both? |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-06-05 : 14:47:41
|
| put a separate query to take adjusted values from required table and merge with current query using UNION ALL.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
|
|
|
|
|
Help updating a report with three additional field