use DataScience
go

-----------------------------------------------------------------------------------------------
-- add lags for look-back periods and buy-signal and buy-action columns to
-- dbo.HA_values_w_candle_color_and_open_observed_price 
-- and populate the added columns for buy-signal and buy-action columns
-- then, add sell-signal and sell-action columns

-- test runs of the model are performed for just 1 symbol
declare @symbol nvarchar(10) = 'AMT'


-- create and populate table for assigning buy signal and buy action columns based 
-- on HA candle colors for a symbol, such as AMT, for beginning of each buy-sell cycle

drop table if exists dbo.HA_values_w_candle_color_and_w_buy_signal_buy_action

-- assign buy action to criteria-matching dates
-- criteria are: date after a green candle following 3 red candles
select 
 *,
case
 when (lag([3_red_buy_signal],1) over (order by date)) = cast('buy signal' as nvarchar(10))
 then cast('buy action' as nvarchar(10))
else null
end [buy_action]
into dbo.HA_values_w_candle_color_and_w_buy_signal_buy_action
from
(
--*/
-- assign buy signal to criteria-matching dates
-- criteria are: a green candle after 3 preceding red candles
select 
 date
,HA_open
,open_observed_price
,HA_close
,symbol
,candle_color
,candle_color_lag_1
,candle_color_lag_2
,candle_color_lag_3
,candle_color_lag_4
,
case
 when 
  candle_color_lag_1 = 'green' and
  candle_color_lag_2= 'red' and
  candle_color_lag_3 = 'red' and
  candle_color_lag_4 = 'red'
  then cast('buy signal' as nvarchar(10))
 else null
end [3_red_buy_signal]
from
(
-- dbo.HA_values_w_candle_color_and_open_observed_price and 
-- candle_color_lag_1, candle_color_lag_2, candle_color_lag_3, candle_color_lag_4
select 
 date date
,[open] HA_open
,[close] HA_close
,symbol
,open_observed_price
,candle_color
,lag(candle_color,1) over (order by date) candle_color_lag_1
,lag(candle_color,2) over (order by date) candle_color_lag_2
,lag(candle_color,3) over (order by date) candle_color_lag_3
,lag(candle_color,4) over (order by date) candle_color_lag_4
from dbo.HA_values_w_candle_color_and_open_observed_price
where symbol = @symbol
) for_buy_signal
) for_buy_action

-- add sell_signal and sell_action columns to 
-- dbo.HA_values_w_candle_color_and_w_buy_signal_buy_action
alter table dbo.HA_values_w_candle_color_and_w_buy_signal_buy_action
 add [sell_signal] nvarchar(20);
alter table dbo.HA_values_w_candle_color_and_w_buy_signal_buy_action
 add [sell_action] nvarchar(20);


-- optionally display source data columns for for all dates
-- buy-signal, buy-action, sell-signal, and sell-action columns
-- select * from dbo.HA_values_w_candle_color_and_w_buy_signal_buy_action


-----------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------


-- populate in #temp runs of green HA candles for a symbol for subset of rows
-- through '2000-07-02'
drop table if exists #temp

select *
into #temp
from (
select 
 [date]
,[HA_open]
,[open_observed_price]
,[HA_close]
,[symbol]
,[candle_color]
,[candle_color_lag_1]
,[candle_color_lag_2]
,[candle_color_lag_3]
,[candle_color_lag_4]
,[3_red_buy_signal]
,[buy_action]
,[sell_signal]
,[sell_action]
from dbo.HA_values_w_candle_color_and_w_buy_signal_buy_action
where 
 date <= '2000-07-02'
 ) for_foo

-- optionally display full subset of rows
-- select * from #temp

-----------------------------------------------------------------------------------------------

-- search for sell_signal (and sell_action) rows for the first buy-sell cycle

-- update for first sell signal
update #temp
 set [sell_signal] = 'sell signal'
where [candle_color_lag_1] = 'red' and
date = 
(
-- date for first sell signal
select min(date) next_sell_signal_date
from #temp 
where [candle_color_lag_1] = 'red' and 
date > (select min(date) from #temp where [buy_action] = 'buy action')
)

-- update for first sell action
update #temp
 set [sell_action] = 'sell action'
where date = 
(
-- date first sell action date
select min(date) first_sell_action_date
from #temp
where date >
(
select min(date) next_sell_signal_date
from #temp 
where [candle_color_lag_1] = 'red' and 
date > (select min(date) from #temp where [buy_action] = 'buy action')
)
)

------------------------------------------------------------------------------------

-- then search for sell_signal (and sell_action) rows 
-- for buy-sell cycles after the first buy-sell cycle
-- start with the statement having a label of
-- find_another_sell_action

-- update for next sell signal

find_another_sell_action:
update #temp
 set [sell_signal] = 'sell signal'
where date = (
 -- next sell signal date
select min(date) next_sell_signal_date
from #temp 
where [candle_color_lag_1] = 'red' and 
date > (
-- current buy_action date
select min(date) [current buy_action date] 
from #temp 
where [buy_action] = 'buy action' and 
 date > 
 (-- [prior sell_action date]
 select max(date) [prior sell_action date] 
 from #temp 
 where [sell_action] = 'sell action')
))

-- if there is no sell_signal row for the prior buy_action row, 
-- then the current buy-sell cycle cannot be completed
-- because there is no data with which to complete it
-- inform the user, then delete the current buy-sell cycle 
-- with incomplete data and display the buy-sell cycles through 
-- the last complete buy-sell cycle
if @@ROWCOUNT = 0
 begin
	-- diagnostic message
	select 'No more sell_signal rows available after:' [diagnostic message];
	select max(date) [last prior sell_action date] 
	from #temp 
	where [sell_action] = 'sell action';
	
	-- trim the full results of rows with an incomplete buy-sell cycle
	delete from #temp
	where date > 
	(
	 select max(date) [last prior sell_action date] 
	 from #temp 
	 where [sell_action] = 'sell action'
	);

	-- display results set through the last complete buy-sell cycle
	select * from #temp;
	
	-- transfer out of batch with goto loop
	return
 end

-- update for next sell action
update #temp
 set [sell_action] = 'sell action'
where date = (
select min(date) [current sell_action date]
from #temp
where date >
(
select max(date) [current sell_signal date]
from #temp
where [sell_signal] = 'sell signal'
)
)

-- continue search for any remaining sell_action rows
   goto find_another_sell_action;





