Joining multiple tables - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Joining multiple tables

Author

Topic

Ambiance44
Starting Member

16 Posts

Posted - 2007-04-27 : 12:31:33

Hello all,

I'm working on a program that is powered by SQL Server 2000. I need to fetch data from several different tables, summarized below:

Item

ItemKey
ItemDescription
etc...

SalesPerson

SperKey
SperName
etc...

SalesActivity

ActivityKey
SperKey
CustKey
ItemKey
SalesAmount
SalesQty
SaleDate
etc...

Customer

CustKey
CustName
etc...

I want to generate a report which looks like the following:

|------------------|-----------|---------------|----------|----------|
| SalesPerson Name | Item Desc | Customer Name | YTD 2006 | YTD 2007 |
|------------------|-----------|---------------|----------|----------|
| Name 1 | Item 1 | Cust 1 | xxx.xx | yyy.yy |
| Name 1 | Item 2 | Cust 2 | xxx.xx | yyy.yy |
| Name 2 | Item 1 | Cust 1 | xxx.xx | yyy.yy |
| Name 2 | Item 3 | Cust 2 | xxx.xx | yyy.yy |
| Name 3 | Item 25 | Cust 1 | xxx.xx | yyy.yy |
| Name 3 | Item 25 | Cust 2 | xxx.xx | yyy.yy |
| Name 3 | Item 25 | Cust 3 | xxx.xx | yyy.yy |
| ... | ... | ... | ... | ... |
|------------------|-----------|---------------|----------|----------|

This report allows a manager to see all items that each salesperson has sold to their customers.

I have enough knowledge to write a script to fetch data to get each salesperson's sales to customers for YTD2006 or YTD2007, but I don't know how I can put the columns beside each other to compare the salesperson's sales of YTD2006 vs YTD2007.

I will be grateful for any help I can get.

Thanks, Vishal

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-04-27 : 12:45:49

You can use a CASE Statement.


SELECT [SaleSPerson Name], [Item Desc],...
       ,[YTD 2006], [YTD 2007],
      ,SalesCompared = CASE WHEN [YTD 2007] >  [YTD 2006] THEN 'PROFIT' WHEN [YTD 2007] =  [YTD 2006] THEN 'NO PROFIT NO LOSS' ELSE 'LOSS. GO HOME' END
FROM
  table

Dinakar Nethi
SQL Server MVP
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

Ambiance44
Starting Member

16 Posts

Posted - 2007-04-27 : 12:53:00

I am sure I need to join the tables somewhere or the other. There are several joins required to get even the YTD data for a single year, so comparing the figures for both years will surly be more complicated.

I don't need a "summary" of the year in terms of profit or no profit. I need to get the total sales for year 2006 vs total sales for year 2007 for each salesperson's sales of each item to each customer.

Thanks, Vishal

Ambiance44
Starting Member

16 Posts

Posted - 2007-04-28 : 16:09:48

Can anyone help with this please?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-04-28 : 17:38:57

can you please post the table DDL (only the relevant columns) and some sample data

Ambiance44
Starting Member

16 Posts

Posted - 2007-04-30 : 11:23:23

Khtan,

I have only posted the relevant columns in the initial post. The Item table, for example, has only the item key and description, which are the only relevant ones... none of the irrelevant ones are included. My first post also has an example of the data that I want to get from the tables. I have already provided all that you have said. I don't understand what more I can include.

Thanks, Vishal

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-04-30 : 11:32:13

I was expecting the sample data for each of the tables (Item, SalesPerson ... etc) and the corresponding result that you want.
xxx.xx, yyy.yy does not help us to understand what you want.

Ambiance44
Starting Member

16 Posts

Posted - 2007-04-30 : 12:35:21

If the tables are like this:
Item

ItemKey = 1; ItemDescription = Monitor

ItemKey = 2; ItemDescription = CPU

ItemKey = 3; ItemDescription = Mouse

SalesPerson

SperKey = 1; SperName = Vishal

SperKey = 2; SperName = Jack

SperKey = 3; SperName = Andrew

Customer

CustKey = 1; CustName = Best Buy

CustKey = 2; CustName = FutureShop

CustKey = 3; CustName = Ma&Pa IT Shop

SalesActivity

ActivityKey = 1; SperKey = 1; CustKey = 2; ItemKey = 3; SalesAmount = 450.00; SalesQty = 15; SaleDate = 01/06/2006 14:30

ActivityKey = 2; SperKey = 1; CustKey = 1; ItemKey = 1; SalesAmount = 250.00; SalesQty = 2; SaleDate = 02/01/2006 14:30

ActivityKey = 3; SperKey = 2; CustKey = 1; ItemKey = 3; SalesAmount = 400.00; SalesQty = 30; SaleDate = 02/10/2006 11:00

ActivityKey = 4; SperKey = 2; CustKey = 1; ItemKey = 3; SalesAmount = 400.00; SalesQty = 30; SaleDate = 03/10/2006 12:30

ActivityKey = 5; SperKey = 1; CustKey = 3; ItemKey = 2; SalesAmount = 300.00; SalesQty = 1; SaleDate = 03/11/2006 16:15

ActivityKey = 6; SperKey = 3; CustKey = 2; ItemKey = 2; SalesAmount = 250.00; SalesQty = 2; SaleDate = 03/11/2006 16:20

ActivityKey = 7; SperKey = 2; CustKey = 3; ItemKey = 1; SalesAmount = 500.00; SalesQty = 3; SaleDate = 04/12/2006 14:25

ActivityKey = 8; SperKey = 1; CustKey = 2; ItemKey = 3; SalesAmount = 350.00; SalesQty = 10; SaleDate = 01/04/2007 08:40

ActivityKey = 9; SperKey = 2; CustKey = 1; ItemKey = 3; SalesAmount = 15.00; SalesQty = 1; SaleDate = 01/15/2007 11:00

ActivityKey = 10; SperKey = 3; CustKey = 3; ItemKey = 2; SalesAmount = 450.00; SalesQty = 3; SaleDate = 02/01/2007 13:30

ActivityKey = 11; SperKey = 1; CustKey = 1; ItemKey = 1; SalesAmount = 275.00; SalesQty = 2; SaleDate = 02/01/2007 14:30

ActivityKey = 12; SperKey = 1; CustKey = 1; ItemKey = 3; SalesAmount = 25.00; SalesQty = 2; SaleDate = 03/01/2007 14:32

ActivityKey = 13; SperKey = 3; CustKey = 2; ItemKey = 2; SalesAmount = 2000.00; SalesQty = 14; SaleDate = 03/12/2007 16:20

ActivityKey = 14; SperKey = 3; CustKey = 3; ItemKey = 2; SalesAmount = 450.00; SalesQty = 3; SaleDate = 04/02/2007 13:30

ActivityKey = 15; SperKey = 1; CustKey = 1; ItemKey = 2; SalesAmount = 200.00; SalesQty = 1; SaleDate = 04/12/2007 09:30

The report should be as follows:

|------------------|-----------|---------------|----------|----------|
| SalesPerson Name | Item Desc | Customer Name | YTD 2006 | YTD 2007 |
|------------------|-----------|---------------|----------|----------|
| Vishal | Monitor | Best Buy | 250.00 | 275.00 |
| Vishal | CPU | Best Buy | 0.00 | 200.00 |
| Vishal | Mouse | FutureShop | 450.00 | 350.00 |
| Vishal | Mouse | Best Buy | 0.00 | 25.00 |
| Vishal | CPU | Ma&Pa IT Shop | 300.00 | 0.00 |
| Jack | Monitor | Ma&Pa IT Shop | 500.00 | 0.00 |
| Jack | Mouse | Best Buy | 800.00 | 15.00 |
| Andrew | CPU | FutureShop | 250.00 | 2000.00 |
| Andrew | CPU | Ma&Pa IT Shop | 0.00 | 900.00 |
|------------------|-----------|---------------|----------|----------|

Ambiance44
Starting Member

16 Posts

Posted - 2007-05-02 : 14:46:04

Please help

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-05-02 : 15:03:31

What is the formula to calculate YTD2006? Do you sum up by saledate by product or by sales person or by customer?

Dinakar Nethi
SQL Server MVP
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

Ambiance44
Starting Member

16 Posts

Posted - 2007-05-02 : 15:21:58

To get 2006 sales, I run:


SELECT SalesPerson.SperName, Item.ItemDescription, Customer.CustomerName, 
	SUM(SalesActivity.SalesAmount) AS TotalAmt2006
FROM SalesActivity 
LEFT JOIN Item ON Item.ItemKey = SalesActivity.ItemKey
LEFT JOIN SalesPerson ON Salesperson.SperKey = SalesActivity.SperKey
LEFT JOIN Customer ON Customer.CustKey = SalesActivity.CustKey
WHERE SalesActivity.SaleDate BETWEEN '01/01/2006' AND '05/01/2006'
GROUP BY Salesperson.SperName, Item.ItemDescription, Customer.CustomerName

I run a similar query with date range '01/01/2007' and '05/01/2007' to get 2007 sales.

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-05-02 : 16:52:30

[code]
Declare @item table (ItemKey int, ItemDescription varchar(50))
insert into @item
select 1, 'Monitor' union all
select 2, 'CPU' union all
select 3, 'Mouse'

Declare @SalesPerson table (SperKey int, SperName varchar(100))
insert into @SalesPerson
select 1, 'Vishal' union all
select 2, 'Jack' union all
select 3, 'Andrew'

Declare @Customer table (CustKey int, CustomerName varchar(100))
insert into @Customer
select 1, 'Best Buy' union all
select 2, 'FutureShop' union all
select 3, 'Ma&Pa IT Shop'

Declare @SalesActivity table (ActivityKey int, SperKey int, CustKey int, ItemKey int, SalesAmount decimal(10,2), SalesQty int, SaleDate datetime)
insert into @SalesActivity
select 1, 1, 2, 3, 450.00, 15, '01/06/2006 14:30' union all
select 2, 1, 1, 1, 250.00, 2, '02/01/2006 14:30' union all
select 3, 2, 1, 3, 400.00, 30, '02/10/2006 11:00' union all
select 4, 2, 1, 3, 400.00, 30, '03/10/2006 12:30' union all
select 5, 1, 3, 2, 300.00, 1, '03/11/2006 16:15' union all
select 6, 3, 2, 2, 250.00, 2, '03/11/2006 16:20' union all
select 7, 2, 3, 1, 500.00, 3, '04/12/2006 14:25' union all
select 8, 1, 2, 3, 350.00, 10, '01/04/2007 08:40' union all
select 9, 2, 1, 3, 15.00, 1, '01/15/2007 11:00' union all
select 10, 3, 3, 2, 450.00, 3, '02/01/2007 13:30' union all
select 11, 1, 1, 1, 275.00, 2, '02/01/2007 14:30' union all
select 12, 1, 1, 3, 25.00, 2, '03/01/2007 14:32' union all
select 13, 3, 2, 2, 2000.00, 14, '03/12/2007 16:20' union all
select 14, 3, 3, 2, 450.00, 3, '04/02/2007 13:30' union all
select 15, 1, 1, 2, 200.00, 1, '04/12/2007 09:30'

SELECT sp.SperName, ItemDescription, CustomerName,
sum(case when SA.SaleDate BETWEEN '01/01/2006' AND '05/01/2006' Then SalesAmount Else 0 End) as YTD2006,
sum(Case When SA.SaleDate BETWEEN '01/01/2007' AND '05/01/2007' Then SalesAmount Else 0 end) as YTD2007
FROM @SalesActivity Sa
LEFT JOIN @Item I ON I.ItemKey = SA.ItemKey
LEFT JOIN @SalesPerson SP ON SP.SperKey = SA.SperKey
LEFT JOIN @Customer C ON C.CustKey = SA.CustKey
GROUP BY sp.SperName, I.ItemDescription, C.CustomerName

SperName ItemDescription CustomerName YTD2006 YTD2007
Andrew CPU FutureShop 250.00 2000.00
Andrew CPU Ma&Pa IT Shop 0.00 900.00
Jack Monitor Ma&Pa IT Shop 500.00 0.00
Jack Mouse Best Buy 800.00 15.00
Vishal CPU Best Buy 0.00 200.00
Vishal CPU Ma&Pa IT Shop 300.00 0.00
Vishal Monitor Best Buy 250.00 275.00
Vishal Mouse Best Buy 0.00 25.00
Vishal Mouse FutureShop 450.00 350.00
[/code]

Dinakar Nethi
SQL Server MVP
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

Ambiance44
Starting Member

16 Posts

Posted - 2007-05-03 : 13:17:15

Thank you very much. This works.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources