How do I get a single row inserted in a table when comparing one table(s) with multiple rows?

Question

I asked a similar question about SQL UPDATE, but now I have to load data to the table and there are multiple scenarios. One scenario is shown in the following tables:

History table:

Company	EmplNo	Seq	Status	Title	Level	DESC	EntryDate	LevelDate
900	324778	1	B	31117	0	Temp Worker	1997-12-15	1997-12-14
500	324778	1	B	31117	0	Temp Worker	1997-12-15	2010-06-20
500	324778	1	C	31117	0	Temp Werker	1997-12-15	1997-12-14

Current table:

Company	EmplNo	Seq	Status	Title	Level	EntryDate	FName	Lname	Salary
900	324778	1	B	31117	0	15-DEC-1977	James	Dohn	47826.00

The result should be:

Company	EmplNo	Status	Title	Level	DESC	EntryDate	LevelDate
900	324778	B	31117	0	Temp Worker	1997-12-15	1997-12-14

Instead I get this:

Company	EmplNo	Status	Title	Level	DESC	EntryDate	LevelDate
900	324778	B	31117	0	Temp Worker	1997-12-15	1997-12-14
900	324778	B	31117	0	Temp Worker	1997-12-15	1997-12-14

My SQL script is as follows:

INSERT INTO DatesTable (Company, EmplNo, Status, Title, DESC, Level, EntryDate)
    (SELECT 
         C.Company, C.EmplNo, C.Status, C.Title, H.DESC, C.Level, 
         C.EntryDate, CONVERT(DATETIME, C.EntryDate, 112) 
     FROM 
         CurrentTable C 
     INNER JOIN 
         HistoryTable H ON C.EmplNo = H.EmplNo 
     WHERE 
         C.Company = H.Company
         AND C.Seq = H.Seq
         AND C.Status = H.Status
         AND C.Title = H.Title  
         AND CONVERT(DATETIME, C.EntryDate, 112) = H.EntryDate
         AND C.EmplNo = 324778)

What am I doing wrong?

There are other combinations, like different Status, Seq, Titles and EntryDate and LevelDate in the History table that should match to 2 combinations of Status, Seq, Titles and EntryDate in the CurrentTable, that should yield 2 records with matching DESC and minimum LevelDates per combination.

But I will mention that later as I get to test the simpler scripts.

Thanks.

Just so you know, I am already updating the resulting table with the following SQL I received earlier. I match the first 4 positions of Title, because some Title codes have a alpha in the 5th column but they are the same titles at different dates, and I want to get the minimum LevelDates.

UPDATE D
SET [LevelDate]= (SELECT MIN(T.LevelDate) as LevelDate 
                  FROM [HistoryTable] H 
                  WHERE H.Company = D.Company
                    AND H.EmplNo = D.EmplNo 
                    AND H.Status = D.Status
                    AND LEFT(H.Title, 4) = LEFT(D.Title, 4))
FROM [DatesTable] D
WHERE EmplNo = 324778 

Answer 1

I was working late last night, and did the see the answer staring me in the face. The issue is I did not include a Group By clause. The SQL script should be:

INSERT INTO DatesTable (Company, EmplNo, Status, Title, DESC, Level, EntryDate)
    (SELECT 
         C.Company, C.EmplNo, C.Status, C.Title, H.DESC, C.Level, 
         C.EntryDate, CONVERT(DATETIME, C.EntryDate, 112) 
     FROM 
         CurrentTable C 
     INNER JOIN 
         HistoryTable H ON C.EmplNo = H.EmplNo 
     WHERE 
         C.Company = H.Company
         AND C.Seq = H.Seq
         AND C.Status = H.Status
         AND C.Title = H.Title  
         AND CONVERT(DATETIME, C.EntryDate, 112) = H.EntryDate
         AND C.EmplNo = 324778
     GROUP BY C.Company, C.EmplNo, C.Status, C.Title, H.DESC, C.Level, 
         C.EntryDate, C.EntryDate 
    )

Thanks a mil for both your inputs. However, if you have a better way to do this, please feel free to share. Thanks again.