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

Question

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 the extra ones from the @DateType table that dont match.....

Answer 1

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)

Answer 2

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]

Answer 3

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