Join Two Tables

Transact-SQL
1

I have two tables, one of the tables has actual sales (sbSalesSummary) while the other has budget sales (sbSalesBudget). I want to create a few queries that let's me SUM the data from each table and compare the two. If sales exist in the actual table, but not the budget table, I want to show that. If sales show in the budget table, but none in the actual table, I want to show that.

Here is an example of the actual table:

'http://www.emp-corp.com/Actual.gif

Here is an example of the budget table:

'http://www.emp-corp.com/Budget.gif

There are multiple sites (4 in table - 2200, 5100, 8100, and 9100). There are multiple customer groups and multiple finance groups. Here are a few of the SQL queries I am wanting to write:

  1. Total actual sales vs. budget sales by customer for ALL sites for ALL finance groups.
  2. Total actual sales vs. budget sales by customer, by finance group, for ALL sites.

Any ideas on how to join these tables to SUM these up?

Thanks in advance!

2

Please post:

CREATE TABLE statements for the tables involved
INSERT INTO statements to populate the tables

desired output from the query.

3

Why do I need to post that? I am looking for a SQL query to display the data already in my tables.

4

Why? So that anyone who wants to help you has a head start. to answer your question properly, i would create test tables, populate them with data, then write the query against those tables. I don't want to make up tables that may be different from yours. I also want to provide a working solution that you can use right away.

Note that this is a commonly accepted practice. Here's a good reference: How to post a SQL question

5

Here is the SQL to CREATE the Budget table:

CREATE TABLE [dbo].[sbSalesBudget](
	[QadSite] [nchar](8) NOT NULL,
	[CustGroup] [varchar](25) NOT NULL,
	[FinanceGroup] [nchar](10) NOT NULL,
	[Year] [int] NOT NULL,
	[Quarter] [tinyint] NOT NULL,
	[Month] [tinyint] NOT NULL,
	[Volume] [decimal](18, 0) NULL,
	[Sales] [decimal](18, 0) NULL,
	[Margin] [decimal](18, 0) NULL,
	[Material] [decimal](18, 0) NULL,
	[Labor] [decimal](18, 0) NULL,
	[Burden] [decimal](18, 0) NULL,
	[Overhead] [decimal](18, 0) NULL,
 CONSTRAINT [PK_sbSalesBudget] PRIMARY KEY CLUSTERED 
(
	[QadSite] ASC,
	[CustGroup] ASC,
	[FinanceGroup] ASC,
	[Year] ASC,
	[Month] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]

GO

Here is the code to create the Actual table:

CREATE TABLE [dbo].[sbSalesSummary](
	[QadSite] [nchar](8) NOT NULL,
	[CustGroup] [varchar](25) NOT NULL,
	[FinanceGroup] [nchar](10) NOT NULL,
	[Year] [int] NOT NULL,
	[Quarter] [tinyint] NOT NULL,
	[Month] [tinyint] NOT NULL,
	[QtyInvoiced] [decimal](18, 0) NULL,
	[Sales] [decimal](18, 0) NULL,
	[Surcharge] [decimal](18, 0) NULL,
	[Margin] [decimal](18, 0) NULL,
	[MarginPerc] [int] NULL,
	[Cost] [decimal](18, 0) NULL,
	[Material] [decimal](18, 0) NULL,
	[Labor] [decimal](18, 0) NULL,
	[Burden] [decimal](18, 0) NULL,
	[Overhead] [decimal](18, 0) NULL,
 CONSTRAINT [PK_sbPrnSales] PRIMARY KEY CLUSTERED 
(
	[QadSite] ASC,
	[CustGroup] ASC,
	[FinanceGroup] ASC,
	[Year] ASC,
	[Month] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]

GO

Here is some data to INSERT into the Budget table:

INSERT INTO sbSalesBudget (QadSite, CustGroup, FinanceGroup, Year, Quarter, Month, Volume, Sales, Margin, Material, Labor, Burden, Overhead) VALUES ('5100','Caterpillar','WP',2015,1,1,500,5000,0,0,0,0,0)
INSERT INTO sbSalesBudget (QadSite, CustGroup, FinanceGroup, Year, Quarter, Month, Volume, Sales, Margin, Material, Labor, Burden, Overhead) VALUES ('2200','John Deere','OP',2015,1,2,50,2500,0,0,0,0,0)
INSERT INTO sbSalesBudget (QadSite, CustGroup, FinanceGroup, Year, Quarter, Month, Volume, Sales, Margin, Material, Labor, Burden, Overhead) VALUES ('2200','John Deere','WP',2015,1,1,175,3000,0,0,0,0,0)
INSERT INTO sbSalesBudget (QadSite, CustGroup, FinanceGroup, Year, Quarter, Month, Volume, Sales, Margin, Material, Labor, Burden, Overhead) VALUES ('5100','Cummins','Other',2015,1,2,50,25000,0,0,0,0,0)
INSERT INTO sbSalesBudget (QadSite, CustGroup, FinanceGroup, Year, Quarter, Month, Volume, Sales, Margin, Material, Labor, Burden, Overhead) VALUES ('8100','New Flyer','MH',2015,1,1,100,100000,0,0,0,0,0)
INSERT INTO sbSalesBudget (QadSite, CustGroup, FinanceGroup, Year, Quarter, Month, Volume, Sales, Margin, Material, Labor, Burden, Overhead) VALUES ('8100','NABI','P450',2015,1,2,500,125000,0,0,0,0,0)
INSERT INTO sbSalesBudget (QadSite, CustGroup, FinanceGroup, Year, Quarter, Month, Volume, Sales, Margin, Material, Labor, Burden, Overhead) VALUES ('8100','New Flyer','P450',2015,1,1,100,25000,0,0,0,0,0)
INSERT INTO sbSalesBudget (QadSite, CustGroup, FinanceGroup, Year, Quarter, Month, Volume, Sales, Margin, Material, Labor, Burden, Overhead) VALUES ('5100','Caterpillar','VVA',2015,1,2,1000,85000,0,0,0,0,0)

Here is some data for the Actual table:

INSERT INTO sbSalesSummary (QadSite, CustGroup, FinanceGroup, Year, Quarter, Month, Volume, Sales, Margin, Material, Labor, Burden, Overhead) VALUES ('5100','Caterpillar','VVA',2015,1,1,200,2500,0,0,0,0,0)
INSERT INTO sbSalesSummary (QadSite, CustGroup, FinanceGroup, Year, Quarter, Month, Volume, Sales, Margin, Material, Labor, Burden, Overhead) VALUES ('2200','John Deere','OP',2015,1,2,250,50000,0,0,0,0,0)
INSERT INTO sbSalesSummary (QadSite, CustGroup, FinanceGroup, Year, Quarter, Month, Volume, Sales, Margin, Material, Labor, Burden, Overhead) VALUES ('2200','John Deere','WP',2015,1,1,70,1500,0,0,0,0,0)
INSERT INTO sbSalesSummary (QadSite, CustGroup, FinanceGroup, Year, Quarter, Month, Volume, Sales, Margin, Material, Labor, Burden, Overhead) VALUES ('5100','Cummins','Other',2015,1,2,100,38000,0,0,0,0,0)
INSERT INTO sbSalesSummary (QadSite, CustGroup, FinanceGroup, Year, Quarter, Month, Volume, Sales, Margin, Material, Labor, Burden, Overhead) VALUES ('8100','New Flyer','MH',2015,1,1,150,150000,0,0,0,0,0)
INSERT INTO sbSalesSummary (QadSite, CustGroup, FinanceGroup, Year, Quarter, Month, Volume, Sales, Margin, Material, Labor, Burden, Overhead) VALUES ('8100','Gillig','P450',2015,1,1,200,50000,0,0,0,0,0)
INSERT INTO sbSalesSummary (QadSite, CustGroup, FinanceGroup, Year, Quarter, Month, Volume, Sales, Margin, Material, Labor, Burden, Overhead) VALUES ('8100','New Flyer','P450',2015,1,1,100,25000,0,0,0,0,0)
INSERT INTO sbSalesSummary (QadSite, CustGroup, FinanceGroup, Year, Quarter, Month, Volume, Sales, Margin, Material, Labor, Burden, Overhead) VALUES ('5100','Caterpillar','VVA',2015,1,2,950,80000,0,0,0,0,0)
6

Any help? I assume this is a fairly simple query.

7

Sorry about the delay. I couldn't run the second INSERT script,since the sbSalesSummary table does not have a Volume column. Should it?

8

Sorry, that table used 'QtyInvoiced' instead of 'Volume' for the column name.

9

for 2:(separate queries)

select QadSite, CustGroup, FinanceGroup, sum(sales)  as SalesBudget
from dbo.sbSalesBudget
group by QadSite, CustGroup, FinanceGroup

select QadSite, CustGroup, FinanceGroup, sum(sales)  as SalesActual
from dbo.sbSalesSummary
group by QadSite, CustGroup, FinanceGroup

Joining the queries:

with budget as
    (   
    select QadSite, CustGroup, FinanceGroup, sum(sales)  as SalesBudget
    from dbo.sbSalesBudget
    group by QadSite, CustGroup, FinanceGroup
    )
   , sales as 
   (
   select QadSite, CustGroup, FinanceGroup, sum(sales)  as SalesActual
    from dbo.sbSalesSummary
    group by QadSite, CustGroup, FinanceGroup

   )

select budget.*, sales.SalesActual from Sales
join budget 
on sales.QadSite = budget.QadSite
and sales.CustGroup = budget.CustGroup
and sales.FinanceGroup = budget.FinanceGroup

the first problem (Total actual sales vs. budget sales by customer for ALL sites for ALL finance groups) is the same, just remove FinanceGroup from the select lists, and the grouping and joining

10

I have come up with a working solution based upon your input. Thank you so much! I am trying to run this statement in ASP but am getting a syntax error:

Incorrect syntax near the keyword 'WITH'.

Any ideas as to why or is there an alternative I can use to run from an ASP script? Code below:

WITH
ss As (
SELECT CustGroup, FinanceGroup, SUM(Sales - Surcharge) As SalesActual
FROM [Finance].[dbo].[sbSalesSummary] ss
LEFT JOIN [Finance].[dbo].[sbQadSites] qad ON qad.QadSite = ss.QadSite
WHERE Quarter <= 2 AND Year = 2015 AND qad.SiteType = 'Conventional'
GROUP BY CustGroup, FinanceGroup
),
sb as (
SELECT CustGroup, FinanceGroup, SUM(Sales) As SalesBudget
FROM [Finance].[dbo].[sbSalesBudget] sb
LEFT JOIN [Finance].[dbo].[sbQadSites] qad ON qad.QadSite = sb.QadSite
WHERE Quarter <= 2 AND Year = 2015 AND qad.SiteType = 'Conventional'
GROUP BY CustGroup, FinanceGroup
)
SELECT
(CASE WHEN ss.CustGroup IS NULL THEN sb.CustGroup ELSE ss.CustGroup END) As CustGroup,
(CASE WHEN ss.FinanceGroup IS NULL THEN sb.FinanceGroup ELSE ss.FinanceGroup END) As FinanceGroup,
(CASE WHEN SalesActual IS NULL THEN 0 ELSE SalesActual END) As SalesActual,
(CASE WHEN SalesBudget IS NULL THEN 0 ELSE SalesBudget END) As SalesBudget,
(CASE WHEN SalesActual IS NULL THEN (0 - SalesBudget) WHEN SalesBudget IS NULL THEN (SalesActual - 0) ELSE (SalesActual - SalesBudget) END) As SalesDifference
FROM ss
FULL JOIN sb ON (ss.CustGroup = sb.CustGroup AND ss.FinanceGroup = sb.FinanceGroup)
WHERE (ss.CustGroup NOT LIKE 'EMP%' OR sb.CustGroup NOT LIKE 'EMP%')
ORDER BY SalesDifference DESC

11

Anything before the "WITH"? If so, be sure you have semicolon between the statements (that is, terminate the preceding statement)

If nothing before the WITH, are you running SQL SERVER 2005 or better?

12

There is nothing before the WITH. The SQL server is 2012. Here is the ASP code generating the SQL. It runs fine when I print to the screen and copy and paste in SQL server. It does not work running directly from my ASP page.

strSQL = "WITH " & _
		"ss AS (" & _
			"SELECT CustGroup, FinanceGroup, SUM(Sales - Surcharge) As SalesActual " & _
			"FROM [Finance].[dbo].[sbSalesSummary] ss " & _
			"LEFT JOIN [Finance].[dbo].[sbQadSites] qad ON qad.QadSite = ss.QadSite " & _
			"WHERE Quarter <= 2 AND Year = 2015 AND qad.SiteType = 'Conventional' " & _
			"GROUP BY CustGroup, FinanceGroup), " & _
		"sb AS (" & _
			"SELECT CustGroup, FinanceGroup, SUM(Sales) As SalesBudget " & _
			"FROM [Finance].[dbo].[sbSalesBudget] sb " & _
			"LEFT JOIN [Finance].[dbo].[sbQadSites] qad ON qad.QadSite = sb.QadSite " & _
			"WHERE Quarter <= 2 AND Year = 2015 AND qad.SiteType = 'Conventional' " & _
			"GROUP BY CustGroup, FinanceGroup" & _
		") " & _
	"SELECT " & _
		"(CASE WHEN ss.CustGroup IS NULL THEN sb.CustGroup ELSE ss.CustGroup END) As CustGroup, " & _
		"(CASE WHEN ss.FinanceGroup IS NULL THEN sb.FinanceGroup ELSE ss.FinanceGroup END) As FinanceGroup, " & _
		"(CASE WHEN SalesActual IS NULL THEN 0 ELSE SalesActual END) As SalesActual, " & _
		"(CASE WHEN SalesBudget IS NULL THEN 0 ELSE SalesBudget END) As SalesBudget, " & _
		"(CASE WHEN SalesActual IS NULL THEN (0 - SalesBudget) WHEN SalesBudget IS NULL THEN (SalesActual - 0) ELSE (SalesActual - SalesBudget) END) As SalesDifference " & _
	"FROM ss " & _
	"FULL JOIN sb ON (ss.CustGroup = sb.CustGroup AND ss.FinanceGroup = sb.FinanceGroup) " & _
	"WHERE (ss.CustGroup NOT LIKE 'EMP%' OR sb.CustGroup NOT LIKE 'EMP%') " & _
	"ORDER BY SalesDifference DESC"
13

OK that's weird. However, why not put the code into a stored procedure and call the proc from ASP.Net. It's a better way to go anyway.

14

I can do that but I will be passing several variables to the statement to create a dynamic SQL statement and it will be easier (for me anyways) to do it in ASP code rather than SQL. Here are some of the things I need to pass:

  • Conventional or Advanced sales (change Conventional with Advanced in above)
  • Include/remove surcharge (either remove from sales in the ss table or keep).
  • Show FinanceGroup or do not show FinanceGroup
  • Select Year, Quarter, Month, etc.
  • Select specific CustGroup
  • Select specific QadSite

These will all be selectable form items on the page that the user can use to drill down to get the information they need.

15

fine, but a stored proc is still a best practice. for now, I just want to see if it works for you as is. Not to mention enhanced security options, right now, your users need SELECT access on the tables. If you used a stored proc, they wouldn't and decoupling the SQL from C#/VB will give you more flexibility on both sides.

16

I have also tried to do this by selecting the queries, so that the database can be easily filled. But it seems to be very complicated.

Learn more about collective agreement here.

17

Tried what? Can you show your work?