16 July 2015

This kata was inspired by the join series found here

Given the above data,

Part 1

For each Customer, we want to return the total number of orders, the number of items ordered, the total order amount, and the total shipping cost.

Customer ItemCount OrderAmount OrderCount
ABC 6 725.00 3
DEF 3 475.00 2

Part 2

Now, display the total shipping cost per customer.

Customer customerName City STATE OrderCount ShippingTotal ItemCount OrderAmount
ABC ABC Corporation Boston MA 3 95.00 6 725.00
DEF The DEF Foundation New York City NY 2 20.00 3 475.00  

Code to create the data…

CREATE TABLE Orders (
	OrderID INT PRIMARY KEY
	,Customer VARCHAR(10)
	,OrderDate DATETIME
	,ShippingCost MONEY
	)

CREATE TABLE OrderDetails (
	DetailID INT PRIMARY KEY
	,OrderID INT REFERENCES Orders(OrderID)
	,Item VARCHAR(10)
	,Amount MONEY
	) 

CREATE TABLE Customers (
	Customer VARCHAR(10) PRIMARY KEY
	,CustomerName VARCHAR(100) NOT NULL
	,City VARCHAR(100) NOT NULL
	,STATE VARCHAR(2) NOT NULL
	)

go


INSERT INTO Orders  
select 1,'ABC', '2007-01-01', 40 union all 
select 2,'ABC', '2007-01-02', 30 union all 
select 3,'ABC', '2007-01-03', 25 union all 
select 4,'DEF', '2007-01-02', 10 union all
select 5,'DEF', '2007-01-04' ,10


insert into OrderDetails 
select 1, 1, 'Item A', 100 union all 
select 2, 1, 'Item B', 150 union all 
select 3, 2, 'Item C', 125 union all 
select 4, 2, 'Item B', 50 union all 
select 5, 2, 'Item H', 200 union all 
select 6, 3, 'Item X', 100 union all 
select 7, 4, 'Item Y', 50 union all 
select 8, 4, 'Item Z', 300 union all
select 9 ,5 ,'Item J', 125

INSERT INTO Customers 
SELECT 'ABC' ,'ABC Corporation' ,'Boston' ,'MA' UNION ALL 
SELECT 'DEF' ,'The DEF Foundation' ,'New York City' ,'NY'