asked on

Two SQL tables combined using SQL view not showing correct data

Hi Experts,

We have two SQL tables in Microsoft SQL server 2008. One named salesanalysisbud for budget data and one named: salesanalysisactual for actual data.

The structure and field names of the two tables is very similar, and what we want to do is combine the data from both tables for query/reporting. We have tried to first create a view and then other SQL code to use the View to see both sets of tables so that the data can be collectively queried together in the one crystal 2008 report.

However the problem we are having is all the budget data in the salesanalysisactual table seems to be correct but the data in the salesanalysisbud is wrong – The budget figures are to high when running the SQL code in comparison to the figures when just viewing the same data in the salesanalysisbud table on its own. Any suggestions on this would be a great help.

Ive attached the SQL code for both the querys and also a screenshot of a design view of both tables - (SalesAnalysisActual table is the one on the left)

Thanks

First we run the below SQL code to index the tabkes and create a view using a union join.

create index analysisKey on salesanalysisbud
(year,quarter,period,week,repcode,customer,partcode  )

create index analysisKey on salesanalysisactual
(year,quarter,period,week,repcode,customer,partcode  )

create view AllBudgetsAndActualsKeys as
select dbo.salesanalysisbud.YEAR,
       dbo.salesanalysisbud.quarter,
       dbo.salesanalysisbud.period,
       dbo.salesanalysisbud.week,
       dbo.salesanalysisbud.repcode,
       dbo.salesanalysisbud.customer,
       dbo.salesanalysisbud.partcode
       from salesanalysisbud
       union
select dbo.salesanalysisactual.YEAR,
       dbo.salesanalysisactual.quarter,
       dbo.salesanalysisactual.period,
       dbo.salesanalysisactual.week,
       dbo.salesanalysisactual.repcode,
       dbo.salesanalysisactual.customer,
       dbo.salesanalysisactual.partcode    
       from salesanalysisactual


Then we have the following SQL code to select the fields need and making use of the above view.
select T1.year 
      ,T1.quarter
      ,T1.period
      ,T1.week
      ,T1.repcode
      ,Min(IsNull(T2.RepName, T3.RepName)) AS RepName
      ,Min(IsNull(T2.AreaCode, T3.AreaCode)) AS AreaCode
      ,Min(IsNull(T2.AreaName, T3.AreaName)) AS AreaName
      ,T1.customer 
      ,Min(IsNull(T2.CustomerName, T3.CustomerName)) AS CustomerName
      ,T1.partcode
      ,Min(IsNull(T2.PartName, T3.PartName)) AS CustomerName
      ,Sum(IsNull(T2.BudgetCases,0)) AS BudgetCases
      ,Sum(IsNull(T3.ActualCases,0)) AS ActualCases
      ,Sum(IsNull(T2.BudgetValue,0)) AS BudgetValue
      ,Sum(IsNull(T3.ActualValue,0)) AS ActualValue
      ,Sum(IsNull(T2.BudgetMargin,0)) AS BudgetMargin
      ,Sum(IsNull(T3.ActualMargin,0)) AS ActualMargin
      ,Min(IsNull(T2.FamilyCode, T3.FamilyCode)) AS FamilyCode
      ,Min(IsNull(T2.FamilyCodeDesc, T3.FamilyCodeDesc)) AS FamilyCodeDesc

from AllBudgetsAndActualsKeys T1

left outer join salesanalysisbud T2 on
       T1.year = T2.year and
       T1.quarter = T2.quarter and
       T1.period = T2.period and
       T1.week = T2.week and
       T1.repcode = T2.repcode and
       T1.customer = T2.customer and
       T1.partcode = T2.partcode
      
left outer join salesanalysisactual T3 on
       T1.year = T3.year and
       T1.quarter = T3.quarter and
       T1.period = T3.period and
       T1.week = T3.week and
       T1.repcode = T3.repcode and
       T1.customer = T3.customer and
       T1.partcode = T3.partcode

/* where T1.customer = 'COL001' and T1.year = 2010
  
------------------------------------------------- */

group by T1.year
        ,T1.quarter
        ,T1.period
        ,T1.week 
        ,T1.repcode
        ,T1.customer
        ,T1.partcode

Open in new window

SalesAnalysisTabledesigns.jpg

dwe761

Try UNION ALL instead of UNION.

Does Partcode tell you the difference between whether the data is coming from the budget vs actual? Or how do you distinguish the difference between the two in your results of the union?

Jacobfw

Appears you are joining the tables together twice, once in the view and again in the query.
The view however, joins the tables without combining the keys. This is resulting in two many records in the second query.

Not sure why you are using the view, becuase this is really messing you up.