Bank_of_SQL/Populate_SIGNAL_AllStocks.sql at master · Ramanthan/Bank_of_SQL · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
CREATE DEFINER=`root`@`localhost` PROCEDURE `SIGNAL_PRODUCE_ALL_STOCKS`()
BEGIN

drop table if exists assignment.bajaj2;

create table assignment.bajaj2 as
SELECT DATE ,`Close_Price`,
case
        WHEN `20DayAverage` > `50DayAverage` AND LAG(`20DayAverage`) over() < LAG(`50DayAverage`) over() THEN 'BUY'
		WHEN `20DayAverage` < `50DayAverage` AND (LAG(`20DayAverage`) over() > LAG(`50DayAverage`) over())  THEN 'SELL'
		ELSE 'HOLD'
end as `SIGNAL` from assignment.bajaj1
order by Date ;

drop table if exists assignment.eicher2;
create table assignment.eicher2 as
SELECT DATE ,`Close_Price`,
case
        WHEN `20DayAverage` > `50DayAverage` AND LAG(`20DayAverage`) over() < LAG(`50DayAverage`) over() THEN 'BUY'
		WHEN `20DayAverage` < `50DayAverage` AND (LAG(`20DayAverage`) over() > LAG(`50DayAverage`) over())  THEN 'SELL'
		ELSE 'HOLD'
end as `SIGNAL` from assignment.eicher1
order by Date ;

drop table if exists assignment.hero2;
create table assignment.hero2 as
SELECT DATE ,`Close_Price`,
case
        WHEN `20DayAverage` > `50DayAverage` AND LAG(`20DayAverage`) over() < LAG(`50DayAverage`) over() THEN 'BUY'
		WHEN `20DayAverage` < `50DayAverage` AND (LAG(`20DayAverage`) over() > LAG(`50DayAverage`) over())  THEN 'SELL'
		ELSE 'HOLD'
end as `SIGNAL` from assignment.hero1
order by Date ;

drop table if exists assignment.infosys2;
create table assignment.infosys2 as
SELECT DATE ,`Close_Price`,
case
        WHEN `20DayAverage` > `50DayAverage` AND LAG(`20DayAverage`) over() < LAG(`50DayAverage`) over() THEN 'BUY'
		WHEN `20DayAverage` < `50DayAverage` AND (LAG(`20DayAverage`) over() > LAG(`50DayAverage`) over())  THEN 'SELL'
		ELSE 'HOLD'
end as `SIGNAL` from assignment.infosys1
order by Date ;


drop table if exists assignment.tcs2;
create table assignment.tcs2 as
SELECT DATE ,`Close_Price`,
case
        WHEN `20DayAverage` > `50DayAverage` AND LAG(`20DayAverage`) over() < LAG(`50DayAverage`) over() THEN 'BUY'
		WHEN `20DayAverage` < `50DayAverage` AND (LAG(`20DayAverage`) over() > LAG(`50DayAverage`) over())  THEN 'SELL'
		ELSE 'HOLD'
end as `SIGNAL` from assignment.tcs1
order by Date ;

drop table if exists assignment.tvs2;
create table assignment.tvs2 as
SELECT DATE ,`Close_Price`,
case
        WHEN `20DayAverage` > `50DayAverage` AND LAG(`20DayAverage`) over() < LAG(`50DayAverage`) over() THEN 'BUY'
		WHEN `20DayAverage` < `50DayAverage` AND (LAG(`20DayAverage`) over() > LAG(`50DayAverage`) over())  THEN 'SELL'
		ELSE 'HOLD'
end as `SIGNAL` from assignment.tvs1
order by Date ;


END