Multiple Full outer join on store number

Question

I have a SQL Server query that shows each store number, sales for today vs. sales for last year. From two different tables.

Now I would like to add another table that shows the store name. How can I do that?

The store name is found in a table called [Company$Store] and the field "Name". I want it to Join on [Company$Store].No_ = [Company$Trans_ Sales Entry].[Store No_]

This is the query where i want to add it:

  Select t.Store, t.Sales, a.[Last year]
    From (Select [Company$Trans_ Sales Entry].[Store No_] As Store,
                 Sum([Company$Trans_ Sales Entry].[Net Amount] * -1) As Sales
            From [Company$Trans_ Sales Entry]
           Where [Company$Trans_ Sales Entry].Date = Convert(date,GetDate()) 
             And [Company$Trans_ Sales Entry].[Store No_] Not Like '5%'
           Group By [Company$Trans_ Sales Entry].[Store No_]
         ) t
    Full Outer Join
         (Select [Company$Archived Sales Entry].[Store No_] As Store,
                 Sum([Company$Archived Sales Entry].[Net Amount] * -1) As [Last year]
            From [Company$Archived Sales Entry]
           Where [Company$Archived Sales Entry].Date = Convert(date,DateAdd(week, -52, GetDate()))
           Group By [Company$Archived Sales Entry].[Store No_]
         ) a
      On a.Store = t.Store    
   Order By t.Store

Hope someone can help!

score 0 · Accepted Answer · answered Jul 12 '14 at 17:20

Select [Company$Trans_ Sales Entry].[Store No_] As Store, [Company$Store].[Name], 
       Sum([Company$Trans_ Sales Entry].[Net Amount] * -1) As Sales
  From [Company$Trans_ Sales Entry]
  JOIN [Company$Store] 
    ON [Company$Store].No_ = [Company$Trans_ Sales Entry].[Store No_]
 Where [Company$Trans_ Sales Entry].Date = Convert(date,GetDate()) 
   And [Company$Trans_ Sales Entry].[Store No_] Not Like '5%'
 Group By [Company$Trans_ Sales Entry].[Store No_], [Company$Store].[Name]

Multiple Full outer join on store number

1 Answers1