SQL Server 2005 insert from select with conditionals and joins

Ok, so given the cluster fudge of a database below (SQL Server 2005), I need to do the following:

• Get the users.id for the temp.empid that are not duplicate (easy ids = 3, 4 with a simple join)
• Get the users.id for the temp.empid that are duplicate but exist in the oldLog (easy ids = 1, 7 easy enough with 2 joins)
• Get the users.id for the temp.empid that is duplicate and missing in the oldLog (wtf should be id = 8) The Mother Load => This user is not on the oldLog and is a duplicate, so I have to check both dbo.firstCriteria and dbo.secondCriteria. _if amount is 200 I check dbo.firstCriteria for having completed count >= 3._ if amount is 100 I check dbo.secondCriteria for a completed.
• insert into newLog

dbo.users

id  |   empid
=============
1   |   1234
2   |   2345
3   |   3456
4   |   4567 (Missing log table)
5   |   5678 (Missing temp table)
6   |   1234 (Duplicate empid)
7   |   2345 (Duplicate empid)
8   |   6789 (The Mother Load Missing from oldLog and duplicate empid)
9   |   6789
10  |   1111 (The Mother Load Missing from oldLog and duplicate empid)
11  |   1111

dbo.temp

empid         | amount
========================
1234 (id 1)   | 200
2345 (id 7)   | 200
3456 (id 3)   | 100
4567 (id 4)   | 100
6789 (id 8)   | 200
1111 开发者_运维百科(id 11)  | 100

dbo.oldLog

id
==
1
3
7

dbo.firstCriteria

id  |   task    |   status
===========================
1   |   task1   |   completed
1   |   task2   |   completed
1   |   task3   |   completed
2   |   task1   |   completed
3   |   task1   |   completed
8   |   task1   |   completed
8   |   task2   |   completed
8   |   task3   |   completed

dbo.secondCriteria

id  |   status
==============
1   |   completed
7   |   completed
3   |   completed
11  |   completed

dbo.newLog

BLANK

My results should be as follows:

id  |   empid
=============
1   |   1234
7   |   2345
3   |   3456
4   |   4567
8   |   6789
11  |   1111

And this is what I was attempting and got stuck:

SELECT users.id
FROM   TEMP
       JOIN users
         ON users.empid = TEMP.empid
WHERE  users.empid NOT IN (SELECT users.empid
                           FROM   users
                           GROUP  BY users.empid
                           HAVING COUNT(users.empid) > 1)
UNION ALL
SELECT users.id
FROM   TEMP
       JOIN users
         ON users.empid = TEMP.empid
WHERE  users.empid IN (SELECT users.empid
                       FROM   users
                       GROUP  BY users.empid
                       HAVING COUNT(users.empid) > 1)
       AND users.id IN (SELECT oldlog.id
                        FROM   oldlog)  
UNION ALL
--????

This would be your Query after UNION ALL

SELECT users.id
FROM   TEMP
       JOIN users
         ON users.empid = TEMP.empid
WHERE  users.empid IN (SELECT users.empid
                       FROM   users
                       GROUP  BY users.empid
                       HAVING COUNT(users.empid) > 1)
       AND users.id NOT IN (SELECT oldlog.id  FROM   oldlog)
       AND 1 = CASE TEMP.amount
                    WHEN 200 THEN
                        SELECT 1 FROM dbo.firstCriteria WHERE id = TEMP.id AND status = 'completed' AND COUNT(*) >= 3
                    WHEN 100 THEN
                        SELECT 1 FROM dbo.secondCriteria WHERE id = TEMP.id AND status = 'completed'
                END