select * from CobraEmployee where [Employee Social Security No#] not in (Select SocialSecurityNo from employee)

select E.statuschangedate,CE.[Employee Termination Date],* from employee E inner join CobraEmployee CE on E.SocialSecurityNo = CE.[Employee Social Security No#]
WHERE DATEDIFF(day,E.statuschangedate,CE.[Employee Termination Date]) <> 0 or CE.[Employee Termination Date] is null


Select [Employee Social Security No#],
[Employee EIN],
[Employee  ID],
NULL AS EmployeeType,
CONVERT(VARCHAR(10),[Employee Effective date],101) AS EffectiveDate,
[Employee Status],
CONVERT(VARCHAR(10),[Employee Date of employment],101) AS OriginalHireDate,
CONVERT(VARCHAR(10),[Employee Most Recent Hire Date],101) AS MostRecentHireDate,
CONVERT(VARCHAR(10),[Employee Termination Date],101) AS Terminationdate,
[Employee First Name],
[Employee Last Name],
[Employee Middle Name /Initial],
[Employee Address1 Street Address 1],
[Employee Address1 Address Line 2 ],
[Employee Address1 City],
[Employee Address1 State],
[Employee Address1 ZIP],
[Employee Address1 Country],
[Employee Email Address],
CONVERT(VARCHAR(10),[Employee Date Of Birth],101) AS DateOfBirth,
CASE WHEN [Employee Gender] ='F' THEN 'Female'
ELSE 'Male' END AS Gender,
[Employee Current Job Title],
CONVERT(VARCHAR(10),[Payroll Information Pay Date],101) AS PayDate,
CONVERT(VARCHAR(10),[Payroll Information Pay Period Start Date],101) as PayPeriodStartDate,
CONVERT(VARCHAR(10),[Payroll Information Pay Period End Date],101) AS PayPeriodEndDate,
[Enrollment Gross Salary Per Pay Cycle Frequency],
[Employee Gross annual Salary],
[Payroll Information Benefit Salary],
[Payroll Information W2 wages],
[Employee Class1]
FROM CobraEmployee where [Employee Social Security No#] not in (Select SocialSecurityNo from employee)



---------------------------------- Spouse
select * from COBRASpouse CS inner join Employee E on CS.[Employee Social Security No#] = E.SocialSecurityNo 
where CS.[Spouse information Social Security No#] in(
select SocialSecurityNo from Employee_DependentSpouse where EmployeeID = E.ID)

select * from COBRASpouse CS inner join Employee_DependentSpouse ES on CS.[Spouse information Social Security No#] = ES.SocialSecurityNo 


-------------------- Child
select * from COBRAChild CS inner join Employee E on CS.[Employee Social Security No#] = E.SocialSecurityNo 
where CS.[Child Information Social Security No# SSN] in( 
select SocialSecurityNo from Employee_DependentChild where EmployeeID = E.ID)

select * from COBRAChild CS inner join Employee_DependentChild ES on CS.[Child Information Social Security No# SSN] = ES.SocialSecurityNo 

select * from employee where SocialSecurityNo = 523717003
select * from Employee_DependentChild where EmployeeID = 19447