Ok, I know there is a cleaner way to do this and I am just too new and too dumb to figure it out. Time for some help from the experts.
I have a SQL Database with multiple tables. Most of the tables are just lists in order to connect the two main data tables with like identifiers. The problem I am having is that I can't seem to pull the data from both tables correctly to correlate one with another.
The first table pulls qty and sales by zip code, item number and thus are filtered by the lists in order to get the market that correlates with the zip code and variety that correlates with the item number along with the weight multiplier so as to convert qty to pounds. I have that down and working well.
When I try and add the second table that already has the correct market, the correct pounds and correct variety in it - but I still need to pull the sales and pounds out to compare the two data sets is when I get into trouble. I can't seem to get the two table to pull the correct information.
In the example it should basically return 52 rows - one for each market with the correct dollars and pounds from one table (MarketShare) and dollars and pounds from the other table (CognosData).
Using MS SQL Server 2005 and Visual Studio 2005
Can anyone help me get this right?
SELECT Markets.[IRI Market], Varieties.Variety, SUM(MarketShare.[POS Qty] * Varieties.Weight) AS WMTlbs, SUM(MarketShare.[POS Sales]) AS WMTDollars,
SUM(CognosData.Dollars) AS IRIDollars, SUM(CognosData.Pounds) AS IRIlbs,
FROM MarketShare INNER JOIN
Markets ON MarketShare.[Zip Code] = Markets.[Zip Code] INNER JOIN
CognosMonths ON MarketShare.[WM Week] = CognosMonths.[Walmart Weeks] INNER JOIN
Qualifier ON MarketShare.[Store Nbr] = Qualifier.Stores INNER JOIN
Varieties ON MarketShare.[Item Nbr] = Varieties.[Item No] INNER JOIN
CognosData ON Markets.[IRI Market] = CognosData.Market
WHERE (Qualifier.Qualifier = N'Yes') AND (Varieties.Variety = N'Red') AND (CognosData.Variety = N'Red') AND (CognosData.Year = N'2009')
GROUP BY Varieties.Variety, Markets.[IRI Market], CognosData.Market, CognosData.Variety, CognosData.Year
ORDER BY Markets.[IRI Market]