Help with union then unmatched

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.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Help with union then unmatched

Author

Topic

benkennedy
Starting Member

5 Posts

Posted - 2008-10-02 : 10:29:55

Hello all.
Needing some help.

I have 3 tables.

Drill 1 Table:
Date Employee
1/1/08 12344
1/2/08 23459
1/7/08 34568
etc.

Drill 2 Table:
Date Employee
1/2/08 45672
1/4/08 90124
1/6/08 56788
etc.

Employee Table:
EmpNum Name
12344 John B Green
23348 Johnny S Red
23459 Sam A Brown
34568 Jack C Blue
45672 Jess L Yellow
59535 Mason J Orange
90124 Rich G Black
56788 Matt R Gray

So if i enter StartDate: 1/1/08 and EndDate: 1/4/08

I would like the output of:

EmployeeNum EmployeeName
23348 Johnny S Red
34568 Jack C Blue
59535 Mason J Orange
56788 Matt R Gray

This query is for a report in SQL Server Business Intelligence Development Studio on SQL Server 2005

Thanks!

Ben

bjoerns
Posting Yak Master

154 Posts

Posted - 2008-10-02 : 10:35:45

select EmployeeNum, EmployeeName from Employee
where EmployeeNum in(23348, 34568, 59535, 56788)
order by EmployeeNum asc

What's the logic behind? Those who do not have an entry between 1/1/08 and 1/4/08? Hard to guess from expected output.

benkennedy
Starting Member

5 Posts

Posted - 2008-10-02 : 10:37:58

Basically I need to report those employees who haven't had a drill in the time period specified

bjoerns
Posting Yak Master

154 Posts

Posted - 2008-10-02 : 10:42:02

And the datatypes of Date and EmployeeNum?

benkennedy
Starting Member

5 Posts

Posted - 2008-10-02 : 10:48:16

Date is DateTime And EmployeeNum nchar(5)

bjoerns
Posting Yak Master

154 Posts

Posted - 2008-10-02 : 11:04:32

[code]select EmpNum, Name
from Employee
left join(
select Date, Employee
from Drill1
union all
select Date, Employee
from Drill2
) Drills
on Employee.Empnum = Drills.Employee
and Drills.Date between '01/01/2008' and '01/04/2008'
where Drills.Employee IS NULL[/code]

benkennedy
Starting Member

5 Posts

Posted - 2008-10-02 : 11:17:27

Thanks!

I'll try that, but in the meantime I have come up w/ this:

SELECT EmpNum, Name
FROM Employee
WHERE (NOT EXISTS
(SELECT Employee
FROM Drill1
WHERE (Employee.EmpNum = Employee) AND (Date BETWEEN '01/01/2008' AND '01/04/2008')
UNION
SELECT Employee
FROM Drill2
WHERE (Employee.EmpNum = Employee) AND (Date BETWEEN '01/01/2008' AND '01/04/2008')))

What do you think?

bjoerns
Posting Yak Master

154 Posts

Posted - 2008-10-02 : 11:24:43

Sure, should be equivalent :)

benkennedy
Starting Member

5 Posts

Posted - 2008-10-02 : 11:26:33

It figures that I work on this query for 3 days, and when I ask for help the answer comes to me. But Thanks anyway.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources