Solved

Two SQL tables combined using SQL view not showing correct data

Posted on 2011-09-26
55
244 Views
Last Modified: 2012-05-12
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
0
Comment
Question by:kevin1983
  • 31
  • 14
  • 9
  • +1
55 Comments
 
LVL 10

Expert Comment

by:dwe761
ID: 36598723
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
 
LVL 7

Expert Comment

by:Jacobfw
ID: 36598735
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
 

Author Comment

by:kevin1983
ID: 36598817
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
 

Author Comment

by:kevin1983
ID: 36598853
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
 

Author Comment

by:kevin1983
ID: 36598926
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
 
LVL 10

Expert Comment

by:dwe761
ID: 36598944
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
 
LVL 7

Expert Comment

by:Jacobfw
ID: 36598958
reformat your view to this format:

SELECT DISTINCT col FROM (SELECT * FROM tbl1 UNION ALL SELECT * FROM tbl2)
0
 

Author Comment

by:kevin1983
ID: 36598977
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
 

Author Comment

by:kevin1983
ID: 36598987
dwe761:not to sure how to do the sum before the Union but ill give it a try
0
 
LVL 7

Expert Comment

by:Jacobfw
ID: 36599062
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
 

Author Comment

by:kevin1983
ID: 36599084
Thanks - tried to run that but says: Msg 102, Level 15, State 1, Procedure AllBudgetsAndActualsKeys, Line 26
Incorrect syntax near ')'.
0
 
LVL 15

Expert Comment

by:tim_cs
ID: 36599115
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
 
LVL 7

Expert Comment

by:Jacobfw
ID: 36599119
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
 
LVL 7

Expert Comment

by:Jacobfw
ID: 36599133
sorry, that last one should have union (not union all)
0
 

Author Comment

by:kevin1983
ID: 36599144
tim_cs: ok ill get some samples - screenshots OK?
Jacobfw: thanks for new code - im giving this a try now
0
 
LVL 10

Expert Comment

by:dwe761
ID: 36599165
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
 

Author Comment

by:kevin1983
ID: 36599201
I can put sample data from both the orginal tables into Excel files, would this help?
0
 

Author Comment

by:kevin1983
ID: 36599210
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
 
LVL 10

Expert Comment

by:dwe761
ID: 36599240
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
 

Author Comment

by:kevin1983
ID: 36599410
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
 
LVL 7

Expert Comment

by:Jacobfw
ID: 36599432
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
 

Author Comment

by:kevin1983
ID: 36599448
dwe761: thanks reading through your sample trying to understand it
0
 

Author Comment

by:kevin1983
ID: 36599457
Jacobfw: tried what youve suggested so far but no joy yet
0
 
LVL 7

Expert Comment

by:Jacobfw
ID: 36599611
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
 

Author Comment

by:kevin1983
ID: 36599667
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
 
LVL 7

Expert Comment

by:Jacobfw
ID: 36599674
Missing this at the end:

)
group by year
        ,quarter
        ,period
        ,week
        ,repcode
        ,customer
        ,partcode
0
 

Author Comment

by:kevin1983
ID: 36599709
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
Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

 

Author Comment

by:kevin1983
ID: 36599720
does it need a table name before each field name?
0
 
LVL 7

Expert Comment

by:Jacobfw
ID: 36599740
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
 

Author Comment

by:kevin1983
ID: 36599814
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
 

Author Comment

by:kevin1983
ID: 36599818
...second part compiles ok
0
 
LVL 10

Expert Comment

by:dwe761
ID: 36599822
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
 

Author Comment

by:kevin1983
ID: 36599860
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
 
LVL 7

Expert Comment

by:Jacobfw
ID: 36599872
yes, you will need to put the MIN's back on the non group columns
0
 
LVL 10

Expert Comment

by:dwe761
ID: 36599889
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
 

Author Comment

by:kevin1983
ID: 36599911
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
 
LVL 7

Expert Comment

by:Jacobfw
ID: 36600001
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
 

Author Comment

by:kevin1983
ID: 36600033
Jacobfw: that just run fine
0
 

Author Comment

by:kevin1983
ID: 36600165
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
 
LVL 10

Expert Comment

by:dwe761
ID: 36600173
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
 

Author Comment

by:kevin1983
ID: 36600276
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
 
LVL 10

Expert Comment

by:dwe761
ID: 36600295
If those errors are in the GROUP By clause then change them each to:

,Source
0
 

Author Comment

by:kevin1983
ID: 36600319
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
 
LVL 10

Assisted Solution

by:dwe761
dwe761 earned 250 total points
ID: 36600441
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
 
LVL 7

Expert Comment

by:Jacobfw
ID: 36600496
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
 
LVL 7

Accepted Solution

by:
Jacobfw earned 250 total points
ID: 36600526
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
 

Author Comment

by:kevin1983
ID: 36602597
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
 

Author Comment

by:kevin1983
ID: 36602620
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
 

Author Comment

by:kevin1983
ID: 36603774
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
 
LVL 7

Expert Comment

by:Jacobfw
ID: 36605716
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
 

Author Comment

by:kevin1983
ID: 36708372
Ok ill check the code again and the query im using  - ill post back soon
0
 

Author Comment

by:kevin1983
ID: 36709080
Jacob- looks like your latest code is spot on after checking again more carefully, doing few other checks to confirm
0
 

Author Comment

by:kevin1983
ID: 36709090
Dwe- checking your code again
0
 

Author Comment

by:kevin1983
ID: 36713306
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
 

Author Closing Comment

by:kevin1983
ID: 36720591
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

Featured Post

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Join & Write a Comment

Suggested Solutions

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now