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

Filter dates in a date range

Problem
We need to come with a list of dates where our service is online. Considering another list of date range(From through dates) where our service is down for maintenance.
We are looking for a date range of days, months, and may be years. So we might get a huge list of dates.
The 3 blocks on the image below represents down time of a service.

Solution

There are multiple ways to approach this problem but I need a smart way to implement it in O(1) or O(n) in the worst case.

Since I'm using C#. I can use a dictionary of datetime as a key and bool as It's value.

The dictionary keys will be all available dates with default value of true(service is available).

Since It's a dictionary, the insert operation is O(1).

Then we will go through each down time period and update the value of the Date key to true if the date is a key in our dictionary. 

Then we will return a list of Keys where the value is false.

	public class DateRange
	{
	public struct ServiceDownHistory
	{
	public DateTime ServiceDownDate;
	public DateTime ServiceUpDate;
	}
	public List<DateTime> GetServiceOnlineDates(DateTime StartDate, DateTime EndDate, List<ServiceDownHistory> ServiceDownHistoryList)
	{
	Dictionary<DateTime, bool> ServiceAvailabilityFromThrough = new Dictionary<DateTime, bool>();
	DateTime currentDate = StartDate;
	while (currentDate.Date < EndDate)
	{
	if (ServiceAvailabilityFromThrough.ContainsKey(currentDate.Date) == false)
	{
	ServiceAvailabilityFromThrough.Add(currentDate.Date, true);
	}
	currentDate = currentDate.AddDays(1);
	}
	UpdateServiceAvailabilityFromThroughDictionaryByServiceDownHistory(ServiceAvailabilityFromThrough, ServiceDownHistoryList);
	if (ServiceAvailabilityFromThrough.Any(kv => kv.Value == false))
	{
	return ServiceAvailabilityFromThrough.Where(kv => kv.Value == false).Select(kv => kv.Key).ToList<DateTime>();
	}
	return null;
	}
	private void UpdateServiceAvailabilityFromThroughDictionaryByServiceDownHistory
	(Dictionary<DateTime, bool> ServiceAvailabilityFromThrough, IList<ServiceDownHistory> ServiceDownHistoryList)
	{
	foreach (var item in ServiceDownHistoryList)
	{
	var start = item.ServiceDownDate;
	var end = item.ServiceUpDate;
	if (start.Date > DateTime.MinValue)
	{
	DateTime currentDate = start.Date;
	while (currentDate.Date <= end.Date)
	{
	if (ServiceAvailabilityFromThrough.ContainsKey(currentDate.Date) == true)
	{
	ServiceAvailabilityFromThrough[currentDate.Date] = false;
	}
	currentDate = currentDate.AddDays(1);
	}
	}
	}//end of foreach
	}
	}

view raw OnlinseServiceDates hosted with ❤ by GitHub

Results

List of dates where our service is online and functioning.