June 30, 2009 at 10:39 am
I guess I've been away from coding for too long because I'm stuck on a little thing.
I have an "HoursWorked" table and an "Employee" table. The HoursWorked table has multiple records per employee per day as they clock in and out for breaks & lunch. The Employee table has multiple records for the same employee because as their job titles change over time, they get a new record with a more recent ActiveDate value.
Anyway, I just want to read the HoursWorked table for today, and join by Emp# to Employee just to get the FName and LName data, without joining on the multiple Employee records.
Thoughts ?
June 30, 2009 at 10:42 am
Could you provide the DDL for the tables and some sample data? It can be abbreviated to those columns you need and required for joining the tables together. Be sure to include the ValidDate column and enough data to simulate your issues.
I'm sure the query is fairly easy, I just need something to work with.
June 30, 2009 at 11:04 am
Cant you do a
Select a.* from employee as a
JOIN
(Select distinct empNum, fName,LName from Employee) as b
on a.empNum = b.empNum
where date = '30-Jun-2009'
??
-Roy
June 30, 2009 at 12:13 pm
Roy Ernest (6/30/2009)
Cant you do aSelect a.* from employee as a
JOIN
(Select distinct empNum, fName,LName from Employee) as b
on a.empNum = b.empNum
where date = '30-Jun-2009'
??
Thanks ! Assuming you meant: Select a.* from HoursWorked as a.............
I think that gets me what I need. I didn't think of putting a distinct into the join
June 30, 2009 at 12:15 pm
Yes, I think I need more coffee....:-D
-Roy
June 30, 2009 at 12:22 pm
I'd still like the DDL for the tables and some sample data. I'd like to be sure that you get an answer that ensures you get the correct join between the tables. Using the disinct may work, but what if an employee changes their name? You may now have two records with the same EmployeeID but different names.
June 30, 2009 at 12:29 pm
Lynn Pettis (6/30/2009)
I'd still like the DDL for the tables and some sample data. I'd like to be sure that you get an answer that ensures you get the correct join between the tables. Using the disinct may work, but what if an employee changes their name? You may now have two records with the same EmployeeID but different names.
You're exactly right, that does happen. What I since realized is there's also an "effective_start_date" and "effective_stop_date" in the employee table so I can use that to isolate the correct employee record. I don't need the distinct after all, and can write a simple "normal" select. Thanks to you both.
June 30, 2009 at 12:39 pm
homebrew01 (6/30/2009)
Lynn Pettis (6/30/2009)
I'd still like the DDL for the tables and some sample data. I'd like to be sure that you get an answer that ensures you get the correct join between the tables. Using the disinct may work, but what if an employee changes their name? You may now have two records with the same EmployeeID but different names.You're exactly right, that does happen. What I since realized is there's also an "effective_start_date" and "effective_stop_date" in the employee table so I can use that to isolate the correct employee record. I don't need the distinct after all, and can write a simple "normal" select. Thanks to you both.
Yes. That is what I was trying to get to, but without the DDL for your tables, I had no idea how to write the query for you to work with.
It would really help when asking for help if you provide as much information up front regarding your problem. Table DDL, sample data, expected results, what you have done so far.
Glad we were still able to help.
You should also post the query you eventually write (or a sanatized version) as others may have a similar problem and seeing how you solved your problem may help others.
June 30, 2009 at 12:56 pm
Here's a sanitized version, with unrelated fields removed:
CREATE TABLE employee(
emp_id int NOT NULL,
first_name varchar(15) NULL,
middle_name varchar(15) NULL,
last_name varchar(20) NULL,
company_sys_id int NULL,
dept_id int NULL,
eff_start_date int NOT NULL,
eff_stop_date int NOT NULL
.
.
.
emp_id first_name middle last_name dept_id eff_start eff_stop
----------- ---------- --------------- --------- ----------- ----------- -----------
19 Jane D Doe 15 27871 27996
19 Jane D Doe 15 27997 28003
19 Jane D Doe 15 28004 28010
19 Jane D Doe 15 28011 28017
19 Jane D Doe 15 28018 28024
19 Jane D Doe 15 28025 28038
19 Jane D Doe 15 28039 28045
19 Jane D Doe 15 28046 28052
19 Jane D Doe 15 28053 28066
19 Jane D Doe 15 28067 28073
19 Jane D Doe 15 28074 28101
19 Jane D Doe 15 28102 28108
19 Jane D Doe 15 28109 28129
19 Jane D Doe 15 28130 28150
19 Jane D Doe 15 28151 28318
19 Jane D Doe 15 28319 28325
19 Jane D Doe 15 28326 28332
19 Jane D Doe 15 28333 28339
19 Jane D Doe 15 28340 28346
19 Jane D Doe 15 28347 28353
19 Jane D Doe 15 28354 28360
19 Jane D Doe 15 28361 28367
19 Jane D Doe 15 28368 28374
19 Jane D Doe 15 28375 28381
19 Jane D Doe 15 28382 28388
19 Jane D Doe 15 28389 28395
19 Jane D Doe 15 28396 28402
19 Jane D Doe 15 28403 28409
19 Jane D Doe 15 28410 28423
19 Jane D Doe 15 28424 28430
19 Jane D Doe 15 28431 28437
19 Jane D Doe 15 28438 28444
19 Jane D Doe 15 28445 28451
19 Jane D Doe 15 28452 28458
19 Jane D Doe 15 28459 28465
19 Jane D Doe 15 28466 28493
19 Jane D Doe 15 28494 28507
19 Jane D Doe 15 28508 28514
19 Jane D Doe 15 28515 28521
19 Jane D Doe 15 28522 28724
19 Jane D Doe 15 28725 29004
19 Jane D Doe 15 29005 32767
.
.
.
CREATE TABLE dbo.hoursworked(
emp_id int NOT NULL,
payperiod smallint NOT NULL,
sequence_number smallint NOT NULL,
item_date smallint NOT NULL,
start_time int NOT NULL,
stop_time int NOT NULL,
pay_period_system_id smallint NULL,
week_ending smallint NULL,
item_status char(1) NULL,
line_comment varchar(255) NULL
.
.
emp_id payperiod seq# item_date start_time stop_time week_ending item_status
----------- --------- ------ --------- ----------- ----------- ----------- -----------
19 29032 39 29031 41805628 41805630 29032 C
19 29032 40 29031 41805606 41805628 29032 C
19 29032 41 29031 41805555 41805600 29032 C
19 29032 42 29031 41805424 41805442 29032 C
19 29032 43 29031 41805422 41805424 29032 C
19 29032 44 29031 41805540 41805555 29032 C
19 29032 45 29031 41805421 41805422 29032 C
19 29032 46 29031 41805391 41805392 29032 C
19 29032 47 29031 41805600 41805606 29032 C
19 29032 48 29031 41805444 41805540 29032 C
19 29032 49 29031 41805124 41805391 29032 C
19 29032 50 29031 41805121 41805124 29032 C
19 29032 51 29031 41805442 41805444 29032 C
19 29032 52 29031 41805120 41805121 29032 C
This system uses "date" field that are calculated as # of days since 01-01-1930, and time fields as # of minutes since 1930 .... just to add some extra fun to the mix.
My current query is simple now:
SELECT hrs.*
FROM hoursworked hrs
join employee emp on hrs.emp_id = emp.emp_id and item_date between emp.eff_start_date and emp.eff_stop_date
where item_date between 29031 and 29034
order by item_date, emp_id, sequence_number
June 30, 2009 at 1:02 pm
Mind decoding your dates??
June 30, 2009 at 1:08 pm
Lynn Pettis (6/30/2009)
Mind decoding your dates??
Sorry ... edited above.
June 30, 2009 at 1:11 pm
Lynn Pettis (6/30/2009)
Mind decoding your dates??
I think it is the number of days since 01/01/1930.
"This system uses "date" field that are calculated as # of days since 01-01-1930, and time fields as # of minutes since 1930 .... just to add some extra fun to the mix."
That is where I got that... But I did not try to calculate it.. Strange way to store the start date if I may say so Homebrew...:-P
-Roy
June 30, 2009 at 1:13 pm
Roy Ernest (6/30/2009)
Lynn Pettis (6/30/2009)
Mind decoding your dates??I think it is the number of days since 01/01/1930.
"This system uses "date" field that are calculated as # of days since 01-01-1930, and time fields as # of minutes since 1930 .... just to add some extra fun to the mix."
That is where I got that... But I did not try to calculate it.. Strange way to store the start date if I may say so Homebrew...:-P
Wasn't my idea !! I just got "volunteered" to work on this project.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply