SQL UNION on 2 table variables where date is same but other data is not

I have 2 table variable @Data and @DateTable

@Data conatains the following:

Company   Date        Type      Select
Altec    12-1-2010     d           2
Altec    12-1-2010     a           2
Alect   12-3-2010     d            3

@DateTable contains the following:

Company Date          Type Select
Altec    12-1-2010     a    0
Altec    12-3-2010     a    0
Altec    12-5-2010     a    0
Altec    12-6-2010     a    0

I need to have both d and a show up in the same query. So if there is no d that matches the a in the @data table it needs to pull it from the @DateTable.

So basically the results I want are following

Company DATE      Type Select
ALTEC   12-1-10     d    2
ALTEC   12-1-10     a    2
ALTEC   12-3-10     d    3
ALTEC   12-3-10     a    0 (This is pulled from @DateTable)

How would I Union these 2 tables without getting the ones that are already in the @Data table but also not gettig 开发者_StackOverflow社区the extra ones from the @DateType table that dont match.....

Here's my take:

SELECT dt.company,
       dt.date,
       COALESCE(d.type, dt.type) AS type,
       COALESCE(d.select, dt.select) AS select
FROM @DateTable dt 
     LEFT JOIN @Data d ON d.company = dt.company
                       AND d.date = dt.date

Using EXISTS / NOT EXISTS:

 SELECT Company, Date, Type, Select FROM DataTable
 UNION ALL
 SELECT Company, Date, Type, Select FROM DateTable
    WHERE EXISTS (SELECT * FROM DataTable WHERE Company = DateTable.Company AND Date = DateTable.Date)
    AND NOT EXISTS (SELECT * FROM DataTable WHERE Company = DateTable.Company AND Date = DateTable.Date AND Type = DateTable.Type)

Using Union:

select [Company], [Date], [Type], sum([Select]) as [Select]
from (select [Company], [Date], [Type], [Select] from @Data union all
      select [Company], [Date], [Type], [Select] from @DateTable) sq
group by [Company], [Date], [Type]