Hw To Match Stock Holdings with any Stock Model Using SQL Full Outer Join

Problem
Here's a nice SQL challenge that I have done recently.
There are 2 friends Jack and Julie.
Jack has a list of holdings that represents stock name and amount he owns.
Julie has a Model that represents stock name and percentage she owns of the stock.
for the holding table, the Total amount is $1000.
For the Model table, the Total percentage is 100%


We would like to map Jack's holdings to Julie's Model.
We have a table of orders represented by stock name, amount, and operation type.
We are looking for a list of stocks that we need to buy or sell in order to match holdings with any given model.
Here's an example.

Solution

Using full outer Join we can provide a simple, and easy to understand solution.  We use left or right outer join most of the time, and It's very rare to use Full outer join. 

Full outer join provides you with all records from both tables a long with the matching records of each other. It's like a union of Left join and right join.

Simply Left join and Union concepts are all what we need to resolve this problem in a single SQL query.

	USE [TESTDB]
	GO
	/****** Object: Table [dbo].[Holding] Script Date: 12/16/2015 6:31:51 PM ******/
	SET ANSI_NULLS ON
	GO
	SET QUOTED_IDENTIFIER ON
	GO
	CREATE TABLE [dbo].[Holding](
	[Holdingid] [int] NOT NULL,
	[Name] [nvarchar](50) NOT NULL,
	[Amount] [decimal](18, 0) NULL,
	CONSTRAINT [PK_Holding] PRIMARY KEY CLUSTERED
	(
	[Holdingid] ASC
	)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
	) ON [PRIMARY]
	GO
	/****** Object: Table [dbo].[Model] Script Date: 12/16/2015 6:31:51 PM ******/
	SET ANSI_NULLS ON
	GO
	SET QUOTED_IDENTIFIER ON
	GO
	CREATE TABLE [dbo].[Model](
	[ModelID] [int] NOT NULL,
	[Name] [nvarchar](50) NOT NULL,
	[Percentage] [decimal](18, 0) NULL,
	CONSTRAINT [PK_Model] PRIMARY KEY CLUSTERED
	(
	[ModelID] ASC
	)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
	) ON [PRIMARY]
	GO
	/****** Object: Table [dbo].[Order] Script Date: 12/16/2015 6:31:51 PM ******/
	SET ANSI_NULLS ON
	GO
	SET QUOTED_IDENTIFIER ON
	GO
	SET ANSI_PADDING ON
	GO
	CREATE TABLE [dbo].[Order](
	[OrderID] [int] IDENTITY(1,1) NOT NULL,
	[Name] [nvarchar](100) NOT NULL,
	[OperationType] [varchar](20) NOT NULL,
	[Amount] [decimal](18, 2) NOT NULL CONSTRAINT [DF_Order_Amount] DEFAULT ((0)),
	CONSTRAINT [PK_Order] PRIMARY KEY CLUSTERED
	(
	[OrderID] ASC
	)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
	) ON [PRIMARY]
	GO
	SET ANSI_PADDING OFF
	GO
	INSERT [dbo].[Holding] ([Holdingid], [Name], [Amount]) VALUES (1, N'GOOG', CAST(100 AS Decimal(18, 0)))
	INSERT [dbo].[Holding] ([Holdingid], [Name], [Amount]) VALUES (2, N'MDRX', CAST(300 AS Decimal(18, 0)))
	INSERT [dbo].[Holding] ([Holdingid], [Name], [Amount]) VALUES (3, N'BOOK', CAST(100 AS Decimal(18, 0)))
	INSERT [dbo].[Holding] ([Holdingid], [Name], [Amount]) VALUES (4, N'Hello', CAST(500 AS Decimal(18, 0)))
	INSERT [dbo].[Model] ([ModelID], [Name], [Percentage]) VALUES (1, N'GOOG', CAST(20 AS Decimal(18, 0)))
	INSERT [dbo].[Model] ([ModelID], [Name], [Percentage]) VALUES (2, N'MDRX', CAST(50 AS Decimal(18, 0)))
	INSERT [dbo].[Model] ([ModelID], [Name], [Percentage]) VALUES (3, N'BOOK', CAST(20 AS Decimal(18, 0)))
	INSERT [dbo].[Model] ([ModelID], [Name], [Percentage]) VALUES (4, N'AMZN', CAST(10 AS Decimal(18, 0)))

view raw Holdings Model Mapping tables structure and data.SQL hosted with ❤ by GitHub

	--SOLUTION
	SELECT * FROM Holding
	SELECT * FROM Model
	DELETE FROM [Order]
	INSERT INTO [ORDER] (Name, Amount , OperationType)
	Select M.Name , ((M.Percentage/100 * 1000) -
	CASE WHEN H.Amount is NULL THEN 0 ELSE H.Amount END)
	, CASE WHEN ((M.Percentage/100 * 1000) -
	CASE WHEN H.Amount is NULL THEN 0 ELSE H.Amount END) > 0
	THEN 'BUY'
	ELSE 'SELL'
	END
	from Model M
	LEFT JOIN Holding H ON H.Name = M.Name
	UNION
	SELECT H.Name , -1 * H.Amount , 'SELL'
	from Holding H
	WHERE H.Name NOT IN (Select Name FROM Model )
	Select Name, Amount , OperationType from [Order]

view raw Map Holding to any Model.sql hosted with ❤ by GitHub