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
kevin1983Asked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
JacobfwConnect With a Mentor Commented:
Not matching the groups in both:

Try this one:

create view AllBudgetsAndActualsKeys as
select T2.year
      ,T2.quarter
      ,T2.period
      ,T2.week
      ,T2.repcode
      ,MIN(T2.RepName) as RepName
      ,MIN(T2.AreaCode) as AreaCode
      ,MIN(T2.AreaName) as AreaName
      ,T2.customer
      ,MIN(T2.CustomerName) as CustomerName
      ,T2.partcode
      ,MIN(T2.PartName) as PartName      
      ,Sum(IsNull(T2.BudgetCases,0)) AS BudgetCases
      ,0 As ActualCases
      ,Sum(IsNull(T2.BudgetValue,0)) AS BudgetValue
      ,0 As ActualValue
      ,Sum(IsNull(T2.BudgetMargin,0)) AS BudgetMargin
      ,0 As ActualMargin
      ,MIN(T2.FamilyCode) as FamilyCode
      ,MIN(T2.FamilyCodeDesc) as FamilyCodeDesc
from  salesanalysisbud T2
 group by T2.year
        ,T2.quarter
        ,T2.period
        ,T2.week
        ,T2.repcode
        ,T2.customer
        ,T2.partcode
Union all
select T3.year
      ,T3.quarter
      ,T3.period
      ,T3.week
      ,T3.repcode
      ,MIN(T3.RepName) as RepName
      ,MIN(T3.AreaCode) as AreaCode
      ,MIN(T3.AreaName) as AreaName
      ,T3.customer
      ,MIN(T3.CustomerName) as CustomerName
      ,T3.partcode
      ,MIN(T3.PartName) as PartName
      ,0 As BudgetCases
      ,Sum(IsNull(T3.ActualCases,0)) AS ActualCases
      ,0 As BudgetValue
      ,Sum(IsNull(T3.ActualValue,0)) AS ActualValue
      ,0 As BudgetMargin
      ,Sum(IsNull(T3.ActualMargin,0)) AS ActualMargin
      ,MIN(T3.FamilyCode) as FamilyCode
      ,MIN(T3.FamilyCodeDesc) as FamilyCodeDesc
from  salesanalysisactual T3
 group by T3.year
        ,T3.quarter
        ,T3.period
        ,T3.week
        ,T3.repcode
        ,T3.customer
        ,T3.partcode


and then


select select year
      ,quarter
      ,period
      ,week
      ,repcode
      ,Min(RepName) AS RepName
      ,Min(AreaCode) AS AreaCode
      ,Min(AreaName) AS AreaName
      ,customer
      ,Min(CustomerName) AS CustomerName
      ,partcode
      ,Min(PartName) AS CustomerName
      ,Sum(BudgetCases) AS BudgetCases
      ,Sum(ActualCases) AS ActualCases
      ,Sum(BudgetValue) AS BudgetValue
      ,Sum(ActualValue) AS ActualValue
      ,Sum(BudgetMargin) AS BudgetMargin
      ,Sum(ActualMargin) AS ActualMargin
      ,Min(FamilyCode) AS FamilyCode
      ,Min(FamilyCodeDesc) AS FamilyCodeDesc
from AllBudgetsAndActualsKeys T1
group by year
        ,quarter
        ,period
        ,week
        ,repcode
        ,customer
        ,partcode
0
 
dwe761Software EngineerCommented:
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?
0
 
JacobfwCommented:
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.
0
[Webinar] Improve your customer journey

A positive customer journey is important in attracting and retaining business. To improve this experience, you can use Google Maps APIs to increase checkout conversions, boost user engagement, and optimize order fulfillment. Learn how in this webinar presented by Dito.

 
kevin1983Author Commented:
dwe761: ok ill try a union all a try- i guess you mean in the first query?

Jacobfw: ok, do you suggest a different method?, and not using the first view?
Just using one view would be preferable or one sql query
0
 
kevin1983Author Commented:
dwe761: The same partcode may appear in both tables or it may only appear in one table - the same with customers - Most appear in both table but sometimes only in one table.

We have tried to factor this into the SQL code such as this:
  ,T1.customer
      ,Min(IsNull(T2.CustomerName, T3.CustomerName)) AS CustomerName

So that if theres not a customer in one table it can still use the customer name in the other table but should only display budget and/or actual sales data accordingly

0
 
kevin1983Author Commented:
dwe761: The union all did not work Unfortunately  - The budget figures are now even more different than before (much higher figures)

Maybe its duplicating the partcode
0
 
dwe761Software EngineerCommented:
Ah, then you don't want UNION ALL because you want the duplicates removed.

Have you considered doing your Summing of each table first  and then doing the UNION after that?
0
 
JacobfwCommented:
reformat your view to this format:

SELECT DISTINCT col FROM (SELECT * FROM tbl1 UNION ALL SELECT * FROM tbl2)
0
 
kevin1983Author Commented:
Jacobfw: do you mean the below?

create view AllBudgetsAndActualsKeys as
select distinct dbo.salesanalysisbud.YEAR,
       dbo.salesanalysisbud.quarter,
       dbo.salesanalysisbud.period,
       dbo.salesanalysisbud.week,
       dbo.salesanalysisbud.repcode,
       dbo.salesanalysisbud.customer,
       dbo.salesanalysisbud.partcode
       from salesanalysisbud
       union all
select dbo.salesanalysisactual.YEAR,
       dbo.salesanalysisactual.quarter,
       dbo.salesanalysisactual.period,
       dbo.salesanalysisactual.week,
       dbo.salesanalysisactual.repcode,
       dbo.salesanalysisactual.customer,
       dbo.salesanalysisactual.partcode    
       from salesanalysisactual
0
 
kevin1983Author Commented:
dwe761:not to sure how to do the sum before the Union but ill give it a try
0
 
JacobfwCommented:
No, like this:

create view AllBudgetsAndActualsKeys as
select distinct YEAR,
       quarter,
       period,
       week,
       repcode,
       customer,
       partcode
from
 ( 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 all
select dbo.salesanalysisactual.YEAR,
       dbo.salesanalysisactual.quarter,
       dbo.salesanalysisactual.period,
       dbo.salesanalysisactual.week,
       dbo.salesanalysisactual.repcode,
       dbo.salesanalysisactual.customer,
       dbo.salesanalysisactual.partcode    
       from salesanalysisactual )
0
 
kevin1983Author Commented:
Thanks - tried to run that but says: Msg 102, Level 15, State 1, Procedure AllBudgetsAndActualsKeys, Line 26
Incorrect syntax near ')'.
0
 
tim_csCommented:
Kevin1983,

Your first view should be fine for now.  The examples provided by Jacobfw would provide the same result.  Whenver you use UNION instead of UNION ALL it's already doing a distinct.  

Can we get some examples of what your data looks like in the tables?  
0
 
JacobfwCommented:
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
group by dbo.salesanalysisbud.YEAR,
       dbo.salesanalysisbud.quarter,
       dbo.salesanalysisbud.period,
       dbo.salesanalysisbud.week,
       dbo.salesanalysisbud.repcode,
       dbo.salesanalysisbud.customer,
       dbo.salesanalysisbud.partcode

       union all
select dbo.salesanalysisactual.YEAR,
       dbo.salesanalysisactual.quarter,
       dbo.salesanalysisactual.period,
       dbo.salesanalysisactual.week,
       dbo.salesanalysisactual.repcode,
       dbo.salesanalysisactual.customer,
       dbo.salesanalysisactual.partcode    
       from salesanalysisactual
group by dbo.salesanalysisactual.YEAR,
       dbo.salesanalysisactual.quarter,
       dbo.salesanalysisactual.period,
       dbo.salesanalysisactual.week,
       dbo.salesanalysisactual.repcode,
       dbo.salesanalysisactual.customer,
       dbo.salesanalysisactual.partcode    
0
 
JacobfwCommented:
sorry, that last one should have union (not union all)
0
 
kevin1983Author Commented:
tim_cs: ok ill get some samples - screenshots OK?
Jacobfw: thanks for new code - im giving this a try now
0
 
dwe761Software EngineerCommented:
With all due respect, the previous query would not produce anything different than the original UNION query because that is what UNION means.  It combines both tables (or in this case queries) and removes all duplicates (i.e. DISTINCT).  

So I believe your problem lies in distinguishing where the values are coming from in your sums and giving the correct credit the appropriate party.  Since I don't have your data, I'm trying to create a simpler example to show proof of concept...
0
 
kevin1983Author Commented:
I can put sample data from both the orginal tables into Excel files, would this help?
0
 
kevin1983Author Commented:
Jacobfw: tried your latest SQL but still not correct , ah just read you last post  - i did union - will try to change to union all
0
 
dwe761Software EngineerCommented:
Please look at my simpler example to explain what I've been suggesting.  I added a field to the sum query so that you know whether the data is coming from actual or budget.  

Try applying this concept to your situation.


CREATE TABLE [dbo].[tblBudget](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[Year] [int] NULL,
	[Month] [int] NULL,
	[repName] [varchar](50) NULL,
	[partCode] [varchar] (50) NULL,
	[Sales] [money] NULL
) ON [PRIMARY]

GO


CREATE TABLE [dbo].[tblActual](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[Year] [int] NULL,
	[Month] [int] NULL,
	[repName] [varchar](50) NULL,
	[partCode] [varchar] (50) NULL,
	[Sales] [money] NULL
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

insert tblBudget(Year, Month, repName, partCode, Sales) select 2011, 1, 'John', 'Widget', 1000
insert tblBudget(Year, Month, repName, partCode, Sales) select 2011, 2, 'John', 'Widget', 1100
insert tblBudget(Year, Month, repName, partCode, Sales) select 2011, 1, 'John', 'hammer', 1200
insert tblBudget(Year, Month, repName, partCode, Sales) select 2011, 1, 'Peter', 'Widget', 1000

go

insert tblActual(Year, Month, repName, partCode, Sales) select 2011, 1, 'John', 'Widget', 900
insert tblActual(Year, Month, repName, partCode, Sales) select 2011, 2, 'John', 'Widget', 955
insert tblActual(Year, Month, repName, partCode, Sales) select 2011, 3, 'Sally', 'map', 1600
insert tblActual(Year, Month, repName, partCode, Sales) select 2011, 1, 'Carl', 'wrench', 1000

go

select [Year], repName, 'Budget' As Source, Sum(Sales) As SalesYrRep FROM tblBudget GROUP BY [Year], repName
UNION ALL
SELECT [Year], repName, 'Actual' As Source, Sum(Sales) As SalesYrRep FROM tblActual GROUP BY [Year], repName
ORDER by Year, repName, Source

Open in new window

0
 
kevin1983Author Commented:
Ive attached exported data into excel from both each of the source SQL tables - filtered the data by one customer otherwise would have been a bit large. Actualsmff.xlsx Budgetmff.xlsx
0
 
JacobfwCommented:
Thanks dwe761, I can see what you are talking about, the problem is actually in the second querey and not the view.  The sum on outer joined table is producing the larger amounts than expected becuase of join of rows between two tables.
0
 
kevin1983Author Commented:
dwe761: thanks reading through your sample trying to understand it
0
 
kevin1983Author Commented:
Jacobfw: tried what youve suggested so far but no joy yet
0
 
JacobfwCommented:
How about this then:

select select year
      ,quarter
      ,period
      ,week
      ,repcode
      ,Min(RepName) AS RepName
      ,Min(AreaCode) AS AreaCode
      ,Min(AreaName) AS AreaName
      ,customer
      ,Min(CustomerName) AS CustomerName
      ,partcode
      ,Min(PartName) AS CustomerName
      ,Sum(BudgetCases) AS BudgetCases
      ,Sum(ActualCases) AS ActualCases
      ,Sum(BudgetValue) AS BudgetValue
      ,Sum(ActualValue) AS ActualValue
      ,Sum(BudgetMargin) AS BudgetMargin
      ,Sum(ActualMargin) AS ActualMargin
      ,Min(FamilyCode) AS FamilyCode
      ,Min(FamilyCodeDesc) AS FamilyCodeDesc
from (
select T2.year
      ,T2.quarter
      ,T2.period
      ,T2.week
      ,T2.repcode
      ,T2.RepName
      ,T2.AreaCode,
      ,T2.AreaName
      ,T2.customer
      ,T2.CustomerName
      ,T2.partcode
      ,T2.PartName
      ,Sum(IsNull(T2.BudgetCases,0)) AS BudgetCases
      ,0
      ,Sum(IsNull(T2.BudgetValue,0)) AS BudgetValue
      ,0
      ,Sum(IsNull(T2.BudgetMargin,0)) AS BudgetMargin
      ,0
      ,T2.FamilyCode
      ,T2.FamilyCodeDesc
from  salesanalysisbud T2
 group by T2.year
        ,T2.quarter
        ,T2.period
        ,T2.week
        ,T2.repcode
        ,T2.customer
        ,T2.partcode
Union all
select T3.year
      ,T3.quarter
      ,T3.period
      ,T3.week
      ,T3.repcode
      ,T3.RepName
      ,T3.AreaCode,
      ,T3.AreaName
      ,T3.customer
      ,T3.CustomerName
      ,T3.partcode
      ,T3.PartName
      ,0
      ,Sum(IsNull(T3.ActualCases,0)) AS ActualCases
      ,0
      ,Sum(IsNull(T3.ActualValue,0)) AS ActualValue
      ,0
      ,Sum(IsNull(T3.ActualMargin,0)) AS ActualMargin
      ,T3.FamilyCode
      ,T3.FamilyCodeDesc
from  salesanalysisactual T3
 group by T3.year
        ,T3.quarter
        ,T3.period
        ,T3.week
        ,T3.repcode
        ,T3.customer
        ,T3.partcode
     
/* where T1.customer = 'COL001' and T1.year = 2010
)
group by year
        ,quarter
        ,period
        ,week
        ,repcode
        ,customer
        ,partcode
0
 
kevin1983Author Commented:
Thanks, just trying now i found few minor erros in the code which ive fixed. I currently have the below:
with 1 error: Msg 102, Level 15, State 1, Line 78
Incorrect syntax near 'partcode'.


seems to refer to the last partcode, any ideas?
select year 
      ,quarter
      ,period
      ,week
      ,repcode
      ,Min(RepName) AS RepName
      ,Min(AreaCode) AS AreaCode
      ,Min(AreaName) AS AreaName
      ,customer 
      ,Min(CustomerName) AS CustomerName
      ,partcode
      ,Min(PartName) AS CustomerName
      ,Sum(BudgetCases) AS BudgetCases
      ,Sum(ActualCases) AS ActualCases
      ,Sum(BudgetValue) AS BudgetValue
      ,Sum(ActualValue) AS ActualValue
      ,Sum(BudgetMargin) AS BudgetMargin
      ,Sum(ActualMargin) AS ActualMargin
      ,Min(FamilyCode) AS FamilyCode
      ,Min(FamilyCodeDesc) AS FamilyCodeDesc
from (
select T2.year 
      ,T2.quarter
      ,T2.period
      ,T2.week
      ,T2.repcode
      ,T2.RepName
      ,T2.AreaCode
      ,T2.AreaName
      ,T2.customer 
      ,T2.CustomerName
      ,T2.partcode
      ,T2.PartName
      ,Sum(IsNull(T2.BudgetCases,0)) AS BudgetCases
      ,0
      ,Sum(IsNull(T2.BudgetValue,0)) AS BudgetValue
      ,0
      ,Sum(IsNull(T2.BudgetMargin,0)) AS BudgetMargin
      ,0
      ,T2.FamilyCode
      ,T2.FamilyCodeDesc
from  salesanalysisbud T2
 group by T2.year
        ,T2.quarter
        ,T2.period
        ,T2.week 
        ,T2.repcode
        ,T2.customer
        ,T2.partcode 
Union all
select T3.year 
      ,T3.quarter
      ,T3.period
      ,T3.week
      ,T3.repcode
      ,T3.RepName
      ,T3.AreaCode
      ,T3.AreaName
      ,T3.customer 
      ,T3.CustomerName
      ,T3.partcode
      ,T3.PartName
      ,0
      ,Sum(IsNull(T3.ActualCases,0)) AS ActualCases
      ,0
      ,Sum(IsNull(T3.ActualValue,0)) AS ActualValue
      ,0
      ,Sum(IsNull(T3.ActualMargin,0)) AS ActualMargin
      ,T3.FamilyCode
      ,T3.FamilyCodeDesc
from  salesanalysisactual T3
 group by T3.year
        ,T3.quarter
        ,T3.period
        ,T3.week 
        ,T3.repcode
        ,T3.customer
        ,T3.partcode

Open in new window

0
 
JacobfwCommented:
Missing this at the end:

)
group by year
        ,quarter
        ,period
        ,week
        ,repcode
        ,customer
        ,partcode
0
 
kevin1983Author Commented:
ok ive added that to the end but now have:
Msg 156, Level 15, State 1, Line 80
Incorrect syntax near the keyword 'group'.
0
 
kevin1983Author Commented:
does it need a table name before each field name?
0
 
JacobfwCommented:
Sorry, I work more with Oracle than SQL Server syntax, but I am guessing that we need to split this into the view and query that you had before like this:

create view AllBudgetsAndActualsKeys as
select T2.year
      ,T2.quarter
      ,T2.period
      ,T2.week
      ,T2.repcode
      ,T2.RepName
      ,T2.AreaCode
      ,T2.AreaName
      ,T2.customer
      ,T2.CustomerName
      ,T2.partcode
      ,T2.PartName
      ,Sum(IsNull(T2.BudgetCases,0)) AS BudgetCases
      ,0
      ,Sum(IsNull(T2.BudgetValue,0)) AS BudgetValue
      ,0
      ,Sum(IsNull(T2.BudgetMargin,0)) AS BudgetMargin
      ,0
      ,T2.FamilyCode
      ,T2.FamilyCodeDesc
from  salesanalysisbud T2
 group by T2.year
        ,T2.quarter
        ,T2.period
        ,T2.week
        ,T2.repcode
        ,T2.customer
        ,T2.partcode
Union all
select T3.year
      ,T3.quarter
      ,T3.period
      ,T3.week
      ,T3.repcode
      ,T3.RepName
      ,T3.AreaCode
      ,T3.AreaName
      ,T3.customer
      ,T3.CustomerName
      ,T3.partcode
      ,T3.PartName
      ,0
      ,Sum(IsNull(T3.ActualCases,0)) AS ActualCases
      ,0
      ,Sum(IsNull(T3.ActualValue,0)) AS ActualValue
      ,0
      ,Sum(IsNull(T3.ActualMargin,0)) AS ActualMargin
      ,T3.FamilyCode
      ,T3.FamilyCodeDesc
from  salesanalysisactual T3
 group by T3.year
        ,T3.quarter
        ,T3.period
        ,T3.week
        ,T3.repcode
        ,T3.customer
        ,T3.partcode



and then

select select year
      ,quarter
      ,period
      ,week
      ,repcode
      ,Min(RepName) AS RepName
      ,Min(AreaCode) AS AreaCode
      ,Min(AreaName) AS AreaName
      ,customer
      ,Min(CustomerName) AS CustomerName
      ,partcode
      ,Min(PartName) AS CustomerName
      ,Sum(BudgetCases) AS BudgetCases
      ,Sum(ActualCases) AS ActualCases
      ,Sum(BudgetValue) AS BudgetValue
      ,Sum(ActualValue) AS ActualValue
      ,Sum(BudgetMargin) AS BudgetMargin
      ,Sum(ActualMargin) AS ActualMargin
      ,Min(FamilyCode) AS FamilyCode
      ,Min(FamilyCodeDesc) AS FamilyCodeDesc
from AllBudgetsAndActualsKeys T1
group by year
        ,quarter
        ,period
        ,week
        ,repcode
        ,customer
        ,partcode
0
 
kevin1983Author Commented:
ok tried running first code  - ie the view and have 1 error:
Msg 8120, Level 16, State 1, Procedure AllBudgetsAndActualsKeys, Line 7
Column 'salesanalysisbud.RepName' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.


guess repname needs to be added to each of the group by
0
 
kevin1983Author Commented:
...second part compiles ok
0
 
dwe761Software EngineerCommented:
In SQL Server, the field names must match for the union to work.

So to get the above query to work, you'll probably have to change the first part :

      ,Sum(IsNull(T2.BudgetCases,0)) AS BudgetCases
      ,0 As ActualCases
      ,Sum(IsNull(T2.BudgetValue,0)) AS BudgetValue
      ,0 As ActualValue
      ,Sum(IsNull(T2.BudgetMargin,0)) AS BudgetMargin
      ,0 As ActualMargin

And change the second part of the union to this:

      ,0 As BudgetCases
      ,Sum(IsNull(T3.ActualCases,0)) AS ActualCases
      ,0 As BudgetValue
      ,Sum(IsNull(T3.ActualValue,0)) AS ActualValue
      ,0 As BudgetMargin
      ,Sum(IsNull(T3.ActualMargin,0)) AS ActualMargin
0
 
kevin1983Author Commented:
ok so now i have the below:

and have 1 error: Msg 8120, Level 16, State 1, Procedure AllBudgetsAndActualsKeys, Line 8
Column 'salesanalysisbud.AreaCode' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.


create view AllBudgetsAndActualsKeys as
select T2.year 
      ,T2.quarter
      ,T2.period
      ,T2.week
      ,T2.repcode
      ,T2.RepName
      ,T2.AreaCode
      ,T2.AreaName
      ,T2.customer 
      ,T2.CustomerName
      ,T2.partcode
      ,T2.PartName
      
      ,Sum(IsNull(T2.BudgetCases,0)) AS BudgetCases
      ,0 As ActualCases
      ,Sum(IsNull(T2.BudgetValue,0)) AS BudgetValue
      ,0 As ActualValue
      ,Sum(IsNull(T2.BudgetMargin,0)) AS BudgetMargin
      ,0 As ActualMargin
      ,T2.FamilyCode
      ,T2.FamilyCodeDesc
from  salesanalysisbud T2
 group by T2.year
        ,T2.quarter
        ,T2.period
        ,T2.week 
        ,T2.repcode
        ,T2.repname
        ,T2.customer
        ,T2.partcode 
Union all
select T3.year 
      ,T3.quarter
      ,T3.period
      ,T3.week
      ,T3.repcode
      ,T3.RepName
      ,T3.AreaCode
      ,T3.AreaName
      ,T3.customer 
      ,T3.CustomerName
      ,T3.partcode
      ,T3.PartName
      ,0 As BudgetCases
      ,Sum(IsNull(T3.ActualCases,0)) AS ActualCases
      ,0 As BudgetValue
      ,Sum(IsNull(T3.ActualValue,0)) AS ActualValue
      ,0 As BudgetMargin
      ,Sum(IsNull(T3.ActualMargin,0)) AS ActualMargin 
      ,T3.FamilyCode
      ,T3.FamilyCodeDesc
from  salesanalysisactual T3
 group by T3.year
        ,T3.quarter
        ,T3.period
        ,T3.week 
        ,T3.repcode
        ,T3.repname
        ,T3.customer
        ,T3.partcode

Open in new window

0
 
JacobfwCommented:
yes, you will need to put the MIN's back on the non group columns
0
 
dwe761Software EngineerCommented:
For whatever it's worth, here is my suggestion following my previous simpler example.  But Jacob's idea may work as well.  But give this a try if you'd like.
SELECT 
	a.year 
      ,a.quarter
      ,a.period
      ,a.week
      ,a.repcode
      ,a.RepName
      ,a.AreaCode
      ,a.AreaName
      ,a.customer 
      ,a.CustomerName
      ,a.partcode
      ,a.PartName AS CustomerName
      , case when Source='Budget' THEN Cases Else 0 END as BudgetCases
      , case when Source='Actual' THEN Cases Else 0 END as ActualCases
      , case when Source='Budget' THEN Value Else 0 END as BudgetValue
      , case when Source='Actual' THEN Value Else 0 END as ActualValue
      , case when Source='Budget' THEN Margin Else 0 END as BudgetMargin
      , case when Source='Actual' THEN Margin Else 0 END as ActualMargin
      ,a.FamilyCode
      ,a.FamilyCodeDesc

FROM 

(
select budget.year 
      ,budget.quarter
      ,budget.period
      ,budget.week
      ,budget.repcode
      ,budget.RepName
      ,budget.AreaCode
      ,budget.AreaName
      ,budget.customer 
      ,budget.CustomerName
      ,budget.partcode
      ,budget.PartName AS CustomerName
      ,Sum(IsNull(budget.BudgetCases,0)) AS Cases
      ,Sum(IsNull(budget.BudgetValue,0)) AS Value
      ,Sum(IsNull(budget.BudgetMargin,0)) AS Margin
      ,budget.FamilyCode
      ,budget.FamilyCodeDesc
      ,'BUDGET' As Source

from salesanalysisbud budget

GROUP BY 
		budget.year 
      ,budget.quarter
      ,budget.period
      ,budget.week
      ,budget.repcode
      ,budget.RepName
      ,budget.AreaCode
      ,budget.AreaName
      ,budget.customer 
      ,budget.CustomerName
      ,budget.partcode
      ,budget.PartName AS CustomerName
      ,budget.FamilyCode
      ,budget.FamilyCodeDesc
      ,'BUDGET' As Source
      
UNION
      
select actual.year 
      ,actual.quarter
      ,actual.period
      ,actual.week
      ,actual.repcode
      ,actual.RepName
      ,actual.AreaCode
      ,actual.AreaName
      ,actual.customer 
      ,actual.CustomerName
      ,actual.partcode
      ,actual.PartName AS CustomerName
      ,Sum(IsNull(actual.BudgetCases,0)) AS Cases
      ,Sum(IsNull(actual.BudgetValue,0)) AS Value
      ,Sum(IsNull(actual.BudgetMargin,0)) AS Margin
      ,actual.FamilyCode
      ,actual.FamilyCodeDesc
      ,'ACTUAL' As Source

from salesanalysisactual actual

GROUP BY 
		actual.year 
      ,actual.quarter
      ,actual.period
      ,actual.week
      ,actual.repcode
      ,actual.RepName
      ,actual.AreaCode
      ,actual.AreaName
      ,actual.customer 
      ,actual.CustomerName
      ,actual.partcode
      ,actual.PartName AS CustomerName
      ,actual.FamilyCode
      ,actual.FamilyCodeDesc
      ,'ACTUAL' As Source

) a

Open in new window

0
 
kevin1983Author Commented:
Thanks dwe761: trying your code, currently have these couple of errors: Msg 156, Level 15, State 1, Line 59
Incorrect syntax near the keyword 'AS'.
Msg 156, Level 15, State 1, Line 99
Incorrect syntax near the keyword 'AS'.

both refer to the customer name lines:

  ,budget.PartName AS CustomerName

dont understand why customname is used as label for partname?
0
 
JacobfwCommented:
Let me know if this has syntax errors?

create view AllBudgetsAndActualsKeys as
select T2.year
      ,T2.quarter
      ,T2.period
      ,T2.week
      ,T2.repcode
      ,MIN(T2.RepName) as RepName
      ,MIN(T2.AreaCode) as AreaCode
      ,MIN(T2.AreaName) as AreaName
      ,T2.customer
      ,MIN(T2.CustomerName) as CustomerName
      ,T2.partcode
      ,MIN(T2.PartName) as PartName      
      ,Sum(IsNull(T2.BudgetCases,0)) AS BudgetCases
      ,0 As ActualCases
      ,Sum(IsNull(T2.BudgetValue,0)) AS BudgetValue
      ,0 As ActualValue
      ,Sum(IsNull(T2.BudgetMargin,0)) AS BudgetMargin
      ,0 As ActualMargin
      ,MIN(T2.FamilyCode) as FamilyCode
      ,MIN(T2.FamilyCodeDesc) as FamilyCodeDesc
from  salesanalysisbud T2
 group by T2.year
        ,T2.quarter
        ,T2.period
        ,T2.week
        ,T2.repcode
        ,T2.repname
        ,T2.customer
        ,T2.partcode
Union all
select T3.year
      ,T3.quarter
      ,T3.period
      ,T3.week
      ,T3.repcode
      ,MIN(T3.RepName) as RepName
      ,MIN(T3.AreaCode) as AreaCode
      ,MIN(T3.AreaName) as AreaName
      ,T3.customer
      ,MIN(T3.CustomerName) as CustomerName
      ,T3.partcode
      ,MIN(T3.PartName) as PartName
      ,0 As BudgetCases
      ,Sum(IsNull(T3.ActualCases,0)) AS ActualCases
      ,0 As BudgetValue
      ,Sum(IsNull(T3.ActualValue,0)) AS ActualValue
      ,0 As BudgetMargin
      ,Sum(IsNull(T3.ActualMargin,0)) AS ActualMargin
      ,MIN(T3.FamilyCode) as FamilyCode
      ,MIN(T3.FamilyCodeDesc) as FamilyCodeDesc
from  salesanalysisactual T3
 group by T3.year
        ,T3.quarter
        ,T3.period
        ,T3.week
        ,T3.repcode
        ,T3.repname
        ,T3.customer
        ,T3.partcode
0
 
kevin1983Author Commented:
Jacobfw: that just run fine
0
 
kevin1983Author Commented:
Jacobfw: to confrim you latest post for the initial view runs fine, i then tried to run the second part of your SQL code ie the below:

but a number of records for budget related data dont match up with the same figures in the orginal salesanalysisbud table, i thought that might have done the trick, but seem to be getting closer to solution now.
select select year 
      ,quarter
      ,period
      ,week
      ,repcode
      ,Min(RepName) AS RepName
      ,Min(AreaCode) AS AreaCode
      ,Min(AreaName) AS AreaName
      ,customer 
      ,Min(CustomerName) AS CustomerName
      ,partcode
      ,Min(PartName) AS CustomerName
      ,Sum(BudgetCases) AS BudgetCases
      ,Sum(ActualCases) AS ActualCases
      ,Sum(BudgetValue) AS BudgetValue
      ,Sum(ActualValue) AS ActualValue
      ,Sum(BudgetMargin) AS BudgetMargin
      ,Sum(ActualMargin) AS ActualMargin
      ,Min(FamilyCode) AS FamilyCode
      ,Min(FamilyCodeDesc) AS FamilyCodeDesc
from AllBudgetsAndActualsKeys T1
group by year
        ,quarter
        ,period
        ,week 
        ,repcode
        ,customer
        ,partcode

Open in new window

0
 
dwe761Software EngineerCommented:
dont understand why customname is used as label for partname?

I wondered the same thing but I took it directly out of your second original query.
    ,Min(IsNull(T2.PartName, T3.PartName)) AS CustomerName

The errors are because I've got "CustomerName" in the results twice because of the error I just noted in your original query.

See updated query:

SELECT 
	a.year 
      ,a.quarter
      ,a.period
      ,a.week
      ,a.repcode
      ,a.RepName
      ,a.AreaCode
      ,a.AreaName
      ,a.customer 
      ,a.CustomerName
      ,a.partcode
      ,a.PartName
      , case when Source='Budget' THEN Cases Else 0 END as BudgetCases
      , case when Source='Actual' THEN Cases Else 0 END as ActualCases
      , case when Source='Budget' THEN Value Else 0 END as BudgetValue
      , case when Source='Actual' THEN Value Else 0 END as ActualValue
      , case when Source='Budget' THEN Margin Else 0 END as BudgetMargin
      , case when Source='Actual' THEN Margin Else 0 END as ActualMargin
      ,a.FamilyCode
      ,a.FamilyCodeDesc

FROM 

(
select budget.year 
      ,budget.quarter
      ,budget.period
      ,budget.week
      ,budget.repcode
      ,budget.RepName
      ,budget.AreaCode
      ,budget.AreaName
      ,budget.customer 
      ,budget.CustomerName
      ,budget.partcode
      ,budget.PartName
      ,Sum(IsNull(budget.BudgetCases,0)) AS Cases
      ,Sum(IsNull(budget.BudgetValue,0)) AS Value
      ,Sum(IsNull(budget.BudgetMargin,0)) AS Margin
      ,budget.FamilyCode
      ,budget.FamilyCodeDesc
      ,'BUDGET' As Source

from salesanalysisbud budget

GROUP BY 
		budget.year 
      ,budget.quarter
      ,budget.period
      ,budget.week
      ,budget.repcode
      ,budget.RepName
      ,budget.AreaCode
      ,budget.AreaName
      ,budget.customer 
      ,budget.CustomerName
      ,budget.partcode
      ,budget.PartName
      ,budget.FamilyCode
      ,budget.FamilyCodeDesc
      ,'BUDGET' As Source
      
UNION
      
select actual.year 
      ,actual.quarter
      ,actual.period
      ,actual.week
      ,actual.repcode
      ,actual.RepName
      ,actual.AreaCode
      ,actual.AreaName
      ,actual.customer 
      ,actual.CustomerName
      ,actual.partcode
      ,actual.PartName
      ,Sum(IsNull(actual.BudgetCases,0)) AS Cases
      ,Sum(IsNull(actual.BudgetValue,0)) AS Value
      ,Sum(IsNull(actual.BudgetMargin,0)) AS Margin
      ,actual.FamilyCode
      ,actual.FamilyCodeDesc
      ,'ACTUAL' As Source

from salesanalysisactual actual

GROUP BY 
		actual.year 
      ,actual.quarter
      ,actual.period
      ,actual.week
      ,actual.repcode
      ,actual.RepName
      ,actual.AreaCode
      ,actual.AreaName
      ,actual.customer 
      ,actual.CustomerName
      ,actual.partcode
      ,actual.PartName
      ,actual.FamilyCode
      ,actual.FamilyCodeDesc
      ,'ACTUAL' As Source

) a

Open in new window

0
 
kevin1983Author Commented:
thanks dwe761, ok that seems bit odd included in orginal, my fault it seems on that one.

Just tried to run your query and couple of errors

Msg 156, Level 15, State 1, Line 63
Incorrect syntax near the keyword 'As'.
Msg 156, Level 15, State 1, Line 103
Incorrect syntax near the keyword 'As'

highlights these 2 lines:
      ,'BUDGET' As Source
,'ACTUAL' As Source
0
 
dwe761Software EngineerCommented:
If those errors are in the GROUP By clause then change them each to:

,Source
0
 
kevin1983Author Commented:
do you mean change to: ,'Source' As Source  ?

and do i need the a at the end of the code like you have:    ) a
0
 
dwe761Connect With a Mentor Software EngineerCommented:
do you mean change to: ,'Source' As Source  ?
No

and do i need the a at the end of the code like you have:    ) a
Yes

Run this and see if it gives you what you need:
SELECT 
	a.year 
      ,a.quarter
      ,a.period
      ,a.week
      ,a.repcode
      ,a.RepName
      ,a.AreaCode
      ,a.AreaName
      ,a.customer 
      ,a.CustomerName
      ,a.partcode
      ,a.PartName
      , case when Source='Budget' THEN Cases Else 0 END as BudgetCases
      , case when Source='Actual' THEN Cases Else 0 END as ActualCases
      , case when Source='Budget' THEN Value Else 0 END as BudgetValue
      , case when Source='Actual' THEN Value Else 0 END as ActualValue
      , case when Source='Budget' THEN Margin Else 0 END as BudgetMargin
      , case when Source='Actual' THEN Margin Else 0 END as ActualMargin
      ,a.FamilyCode
      ,a.FamilyCodeDesc

FROM 

(
select budget.year 
      ,budget.quarter
      ,budget.period
      ,budget.week
      ,budget.repcode
      ,budget.RepName
      ,budget.AreaCode
      ,budget.AreaName
      ,budget.customer 
      ,budget.CustomerName
      ,budget.partcode
      ,budget.PartName
      ,Sum(IsNull(budget.BudgetCases,0)) AS Cases
      ,Sum(IsNull(budget.BudgetValue,0)) AS Value
      ,Sum(IsNull(budget.BudgetMargin,0)) AS Margin
      ,budget.FamilyCode
      ,budget.FamilyCodeDesc
      ,'BUDGET' As Source

from salesanalysisbud budget

GROUP BY 
		budget.year 
      ,budget.quarter
      ,budget.period
      ,budget.week
      ,budget.repcode
      ,budget.RepName
      ,budget.AreaCode
      ,budget.AreaName
      ,budget.customer 
      ,budget.CustomerName
      ,budget.partcode
      ,budget.PartName
      ,budget.FamilyCode
      ,budget.FamilyCodeDesc
      ,Source
      
UNION
      
select actual.year 
      ,actual.quarter
      ,actual.period
      ,actual.week
      ,actual.repcode
      ,actual.RepName
      ,actual.AreaCode
      ,actual.AreaName
      ,actual.customer 
      ,actual.CustomerName
      ,actual.partcode
      ,actual.PartName
      ,Sum(IsNull(actual.ActualCases,0)) AS Cases
      ,Sum(IsNull(actual.ActualValue,0)) AS Value
      ,Sum(IsNull(actual.ActualMargin,0)) AS Margin
      ,actual.FamilyCode
      ,actual.FamilyCodeDesc
      ,'ACTUAL' As Source

from salesanalysisactual actual

GROUP BY 
		actual.year 
      ,actual.quarter
      ,actual.period
      ,actual.week
      ,actual.repcode
      ,actual.RepName
      ,actual.AreaCode
      ,actual.AreaName
      ,actual.customer 
      ,actual.CustomerName
      ,actual.partcode
      ,actual.PartName
      ,actual.FamilyCode
      ,actual.FamilyCodeDesc
      ,Source

) a

ORDER BY 	a.year 
      ,a.quarter
      ,a.period
      ,a.week
      ,a.repcode
      ,a.RepName
      ,a.AreaCode
      ,a.AreaName
      ,a.customer 
      ,a.CustomerName
      ,a.partcode
      ,a.PartName

Open in new window

0
 
JacobfwCommented:
kevin1983, can you be more specific about the following:
      but a number of records for budget related data dont match up with the same figures in the orginal salesanalysisbud table

Is the budget values that are still to high or some other match up that you are refering to?
0
 
kevin1983Author Commented:
Jacobfw: thanks for new code - Ive tried with the following results,
Some data seems to match up fine but other reocrds do not - at the start a number of records seems correct but then gets different.

Both the budget and actual sales data is different for many records to the orginal source tables. Ive attached an excel with colums comparing the version in your SQL code to the 2 orginal source tables - ive named the colums accordingly - does this help? Jacobfw-SQLTest.xlsx
0
 
kevin1983Author Commented:
dwe761: just tried your code but seems even more different to the data in the 2 source tables:

please see attached excel file with colum names with your name in them for actual data and budget data comparison.
Jacobfw--dwe761SQLTest.xlsx
0
 
kevin1983Author Commented:
seems like this is quite a tricky one, I guess there must be a way some how,

Jacobfw: oddly your latest code produces data that is half if not bit more than half correct but half way down the excel in then starts getting different,

seems perhaps still not matching certain groups, any ideas?
0
 
JacobfwCommented:
Kevin,

Looks like your spreadsheet in not alligning the correct columns for actuals (since your source data doesn't have all the same rows for actuals that exist for budget).

Budget numbers are close as you indicate, but seem to differ for a segment.

What query are you using to summarize the data for comparison and extract the columns for comparison?

Jacob
0
 
kevin1983Author Commented:
Ok ill check the code again and the query im using  - ill post back soon
0
 
kevin1983Author Commented:
Jacob- looks like your latest code is spot on after checking again more carefully, doing few other checks to confirm
0
 
kevin1983Author Commented:
Dwe- checking your code again
0
 
kevin1983Author Commented:
The total figures for the data filtered by 2 salesman codes doesnt total correctly even though all others are spot on & customers totals look ok, sorry about delay - trying to isolate where the issue may be, will aim to post findings in the morning. Perhaps its related for particular items
0
 
kevin1983Author Commented:
Thanks all experts for your help of finding a solution to this.

Especially dwe761 & Jacob - Both you most recent SQL posts seem work a great. Sorry it took bit of time to confirm back to you & Accept solution, was out of the office and took bit of time to check figures. The first few checks looked wrong but then reliased checking methods were not quite right.

Only minor thing to note is both solutions take bit of time to run (1minute:30 approx) but guess its becuase quite a lot of records we have in each of the tables for SQL to process so probably its fine & not to complain.

Thanks again. Kevin
0
All Courses

From novice to tech pro — start learning today.