/*
Monty Hall problem computer simulation.
By John Milton Bunch

Local temp table version.

*/
--Set @NumberOfRuns to the total number of simulated runs
--for each strategy. @NumberOfRuns must be greater than 2.
declare @NumberOfRuns bigint = 1000000;


declare @starttime datetime2 = sysdatetime();


--Suppress insert counts.
set nocount on;

--Drop our results table if it already exists
if object_id('tempdb..#Simulation') is not null
	drop table #Simulation;

--Create a table to store the results of each round of play
create table #Simulation
(
	RunID bigint identity(1,1) primary key,
	Door1 varchar(4),
	Door2 varchar(4),
	Door3 varchar(4),
	InitialChoice int,
	FinalChoice int,
	MontyChoice int,
	WinOrLose varchar(4),
	Strategy int
);

--Declare some variables we'll need
declare @Door1 varchar(4),@Door2 varchar(4),@Door3 varchar(4),
		@InitialChoice int,@FinalChoice int,@MontyChoice int,
		@ctr int, @CarDoor int, @RunID int;
/*
Declare a variable to keep track of which strategy we're using.
The first set of rounds will use the "Keep original choice"
strategy, the second set will use the "Change original choice" 
strategy.
*/
declare @KeepOrSwitch int = 0;

--This WHILE loop starts the #Simulation. 
--This outer WHILE loop runs once when @KeepOrSwitch = 0,
--and once when @KeepOrSwitch = 1.
while @KeepOrSwitch <= 1
begin

--This variable tracks which round we're on
set @ctr = 0;

/*
This inner WHILE loop will continue looping until we've
run through all the rounds.
*/
while @ctr < @NumberOfRuns
begin

/* 
Set up the the round by randomly assigning the car 
and two zonks to the three doors.
*/
	set @ctr += 1;
	--Select a random integer between 1 and 3.
	select @CarDoor=(ABS(CHECKSUM(NewId())) % 3) + 1;
	--Use the above random integer to assign cars and zonks to doors.
	set @Door1 = iif(@CarDoor=1,'Car','Zonk');
	set @Door2 = iif(@CarDoor=2,'Car','Zonk');
	set @Door3 = iif(@CarDoor=3,'Car','Zonk');

/*
Simulate playing a round and set values 
for @InitialChoice,@FinalChoice,@MontyChoice
*/
--Player makes the initial choice:
set @InitialChoice=(ABS(CHECKSUM(NewId())) % 3) + 1;
		--Now Monty chooses which zonk to reveal based on the 
		--player's original choice
		if @InitialChoice=1
			begin
				if (@Door2 = 'Car')
				--Show Door 2
					set @MontyChoice = 3
				else
				--Show Door 3
					set @MontyChoice = 2;
			end
		if @InitialChoice=2
			begin
				if (@Door1 = 'Car')
				--Show Door 2
					set @MontyChoice = 3
				else
				--Show Door 3
					set @MontyChoice = 1;
			end
		if @InitialChoice=3
			begin
				if (@Door1 = 'Car')
				--Show Door 2
					set @MontyChoice = 2
				else
				--Show Door 3
					set @MontyChoice = 1;
			end			

			--If this round is using the "Switch door" strategy,
			--we need to switch to the door Monty hasn't opened:
			if @KeepOrSwitch = 1
				begin
					if @InitialChoice = 1
					begin
						set @FinalChoice = 
						case @MontyChoice
							when 2 then 3
							when 3 then 2
						end
					end
					if @InitialChoice = 2
					begin
						set @FinalChoice = 
						case @MontyChoice
							when 3 then 1
							when 1 then 3
						end
					end
					if @InitialChoice = 3
					begin
						set @FinalChoice = 
						case @MontyChoice
							when 1 then 2
							when 2 then 1
						end
					end

				end
			else
				--If this round is using the "stick with the original 
				--door" strategy, we don't switch and the final choice 
				--is the original choice.
				begin
					set @FinalChoice = @InitialChoice
				end
/*
The round has now ended.  The original door choice is stored
in the variable @InitialChoice, Monty's choice in @MontyChoice
and the final choice in @FinalChoice.
*/
	--The round results are now stored in table #Simulation.
	insert into #Simulation (Door1,Door2,Door3,
				InitialChoice,FinalChoice,MontyChoice,WinOrLose,Strategy)
	values (@Door1,@Door2,@Door3,@InitialChoice,@FinalChoice,@MontyChoice
	,case	--This statement checks to see if the player won or lost this 
			--round, and stores this with the rest of the data for the 
			--round.
		when (@FinalChoice = 1 AND @Door1 = 'Car') then 'WIN'
		when (@FinalChoice = 2 AND @Door2 = 'Car') then 'WIN'
		when (@FinalChoice = 3 AND @Door3 = 'Car') then 'WIN'
		else 'LOSE' 
	end
	,@KeepOrSwitch
	);
end;
--Once we've run the game using the "Keep original choice" strategy, 
--we're going to run it again using the "Change original choice" strategy.
set @KeepOrSwitch += 1;
end;

/*
At this point we've played the game @NumberOfRuns times, using
both strategies.  The table #Simulation contains all of the
results.  The below statements report what happened.
*/
select  --This statement simply counts the number of runs using each
		--strategy. It's a check to ensure the correct number were run.
	(select count(*) from #Simulation where Strategy=0)
		 as [Total Number of Keep-Original-Choice Runs]
	,(select count(*) from #Simulation where Strategy=1) 
		as [Total Number of Change-Original-Choice Runs];

select Door1,Door2,Door3,WinOrLose
	,coalesce([Keep Door],0) 
		as [Number of events using Keep Door strategy]
	,coalesce([Change Door],0) 
		as [Number of events using Change Door strategy]
from
(
select
	Door1
	,Door2
	,Door3
	,WinOrLose
	,iif(Strategy=0,'Keep door','Change door') as [Strategy]
	,count(RunID) [Number of Runs]
from #Simulation
group by Strategy,Door1,Door2,Door3,WinOrLose
) Src 
pivot
(
	sum([Number of Runs]) for Strategy in ([Keep Door],[Change Door])
) as Piv
order by Door1,Door2,Door3,WinOrLose;

--This statement is the money shot. It calculates the probability of 
--winning vs. losing the car across all rounds using each of the two 
--strategies.
with cte_Lose0 as
(
	select count(*) as TotalRounds from #Simulation 
		where Strategy=0 and WinOrLose = 'LOSE'
),
cte_Win0 as
(
	select count(*) as TotalRounds from #Simulation 
		where Strategy=0 and WinOrLose = 'WIN'
),
cte_Lose1 as
(
	select count(*) as TotalRounds from #Simulation 
		where Strategy=1 and WinOrLose = 'LOSE'
),
cte_Win1 as
(
	select count(*) as TotalRounds from #Simulation 
		where Strategy=1 and WinOrLose = 'WIN'
)
select 
	--Calculate probability of WINNING using 
	--the "Keep the original choice" strategy.
	cast((cast((select * from cte_Win0) as decimal(25,5)) 
		/ @NumberOfRuns)  as decimal(5,3))
		as [Probability of Win with Keep]
	--Calculate probability of LOSING using 
	--the "Keep the original choice" strategy.
	,cast((cast((select * from cte_Lose0) as decimal(25,5)) 
		/ @NumberOfRuns)  as decimal(5,3))
		as [Probability of Loss with Keep]
	--Calculate probability of WINNING using 
	--the "Change the original choice" strategy.
	,cast((cast((select * from cte_Win1) as decimal(25,5)) 
		/ @NumberOfRuns)  as decimal(5,3))
	--Calculate probability of LOSING using 
	--the "Change the original choice" strategy.
		as [Probability of Win with Change]
	,cast((cast((select * from cte_Lose1) as decimal(25,5)) 
		/ @NumberOfRuns)  as decimal(5,3))
		as [Probability of Loss with Change]

--Run duration
select datediff(ss,@starttime,sysdatetime()) as 'Run Time (Seconds)';


--This statement shows the raw data collected.
--select * from #Simulation order by RunID;