Join Question

Hi
I drop table employee
go
create table employee
( fname char(20),
lname char(36),
dept char(6),
in_dt char(6)
)
insert into employee values ('Joe','Doe','legal','980622')
insert into employee values ('Joe','Doe','legal','990313')
insert into employee values ('Joe','Doe','legal','990704')
insert into employee values ('Joe','Doe','legal','991015')
insert into employee values ('Joe','Doe','legal','000329')
insert into employee values ('Joe','Doe','legal','010503')
drop table work_day
go
create table work_day
(fname char(20),
lname char(36),
dept char(6),
out_dt char(6)
)
insert into work_day values ('Joe','Doe','legal','990228')
insert into work_day values ('Joe','Doe','legal','000617')
insert into work_day values ('Joe','Doe','legal','010407')
select c.fname,
c.lname,
c.dept,
c.in_dt,
e.out_dt
from employee c
left join work_day e
on c.fname = e.fname
and c.lname=e.lname
and c.dept = e.dept
and convert(datetime,c.in_dt) < convert(datetime,e.out_dt)
go
I am getting the multiple records
fname lname dept in_dt
out_dt
-- -- -- -- --
--
Joe Doe legal 980622
990228
Joe Doe legal 980622
000617
Joe Doe legal 980622
010407
Joe Doe legal 990313
000617
Joe Doe legal 990313
010407
Joe Doe legal 990704
000617
Joe Doe legal 990704
010407
Joe Doe legal 991015
000617
Joe Doe legal 991015
010407
Joe Doe legal 000329
000617
Joe Doe legal 000329
010407
Joe Doe legal 010503 NULL
and I need the following output
fname lname dept in_dt
out_dt
-- -- -- -- --
--
Joe Doe legal 980622
990228
Joe Doe legal 990313 NULL
Joe Doe legal 990704 NULL
Joe Doe legal 991015
000617
Joe Doe legal 000329
010407
Joe Doe legal 010503 NULL
Any Suggestions
AjHello,
Thank you for including DDL, sample data and expected result.
However, there are a few problems:
1. Your DDL does not include primary keys (and other constraints)
2. You use char(6) instead of datetime. That's really bad, for (at
least) two reasons:
- performance: converting the values to datetime prevents SQL Server
from using indexes
- data integrity: in a char(6) you can store a value that is not a
valid date and you won't notice until it's too late
3. The expected result... is not quite what I expected. Either the
provided expected result is be wrong or I am unable to understand what
it should contain. If the expected result would have been this:
fname lname dept in_dt out_dt
-- -- -- -- --
Joe Doe legal 980622 990228
Joe Doe legal 990313 NULL
Joe Doe legal 990704 NULL
Joe Doe legal 991015 NULL
Joe Doe legal 000329 000617
Joe Doe legal 010503 NULL
Then a possible solution is this:
SELECT fname, lname, dept, in_dt, (
SELECT MIN(out_dt)
FROM work_day e
WHERE e.fname=c.fname and e.lname=c.lname
AND CONVERT(datetime,e.out_dt)>CONVERT(datetime,c.in_dt)
AND NOT EXISTS (
SELECT *
FROM employee d
WHERE d.fname=c.fname and d.lname=c.lname
AND CONVERT(datetime,d.in_dt)>CONVERT(datetime,c.in_dt)
AND CONVERT(datetime,d.in_dt)<CONVERT(datetime,e.out_dt)
)
) AS out_dt
FROM employee c
Razvan|||Thank you, I agree with the char date field but that is what the table was
initially created with and I am extracting data from it. Your script gave
me the output I will looking for.
Aj
"Razvan Socol" <rsocol@.gmail.com> wrote in message
news:1116436704.952637.73930@.g49g2000cwa.googlegroups.com...
> Hello,
> Thank you for including DDL, sample data and expected result.
> However, there are a few problems:
> 1. Your DDL does not include primary keys (and other constraints)
> 2. You use char(6) instead of datetime. That's really bad, for (at
> least) two reasons:
> - performance: converting the values to datetime prevents SQL Server
> from using indexes
> - data integrity: in a char(6) you can store a value that is not a
> valid date and you won't notice until it's too late
> 3. The expected result... is not quite what I expected. Either the
> provided expected result is be wrong or I am unable to understand what
> it should contain. If the expected result would have been this:
> fname lname dept in_dt out_dt
> -- -- -- -- --
> Joe Doe legal 980622 990228
> Joe Doe legal 990313 NULL
> Joe Doe legal 990704 NULL
> Joe Doe legal 991015 NULL
> Joe Doe legal 000329 000617
> Joe Doe legal 010503 NULL
> Then a possible solution is this:
> SELECT fname, lname, dept, in_dt, (
> SELECT MIN(out_dt)
> FROM work_day e
> WHERE e.fname=c.fname and e.lname=c.lname
> AND CONVERT(datetime,e.out_dt)>CONVERT(datetime,c.in_dt)
> AND NOT EXISTS (
> SELECT *
> FROM employee d
> WHERE d.fname=c.fname and d.lname=c.lname
> AND CONVERT(datetime,d.in_dt)>CONVERT(datetime,c.in_dt)
> AND CONVERT(datetime,d.in_dt)<CONVERT(datetime,e.out_dt)
> )
> ) AS out_dt
> FROM employee c
> Razvan
>