65 lines
1.3 KiB
Transact-SQL
65 lines
1.3 KiB
Transact-SQL
CREATE PROCEDURE [GetLossPayoff]
|
|
@UserId bigint
|
|
|
|
|
|
AS
|
|
DECLARE @Practitioner TABLE (
|
|
idx int Primary Key IDENTITY(1,1)
|
|
, KnokId int
|
|
)
|
|
DECLARE @i int
|
|
DECLARE @numrows int
|
|
DECLARE @knokid int
|
|
DECLARE @knokrate int
|
|
DECLARE @profit int
|
|
DECLARE @knokentryto int
|
|
DECLARE @knokentryfrom int
|
|
DECLARE @sumprofit int
|
|
DECLARE @win int
|
|
DECLARE @res int
|
|
DECLARE @res2 int
|
|
|
|
|
|
SET @sumprofit = 0
|
|
--___________________--
|
|
|
|
INSERT @Practitioner
|
|
SELECT distinct KnokId FROM [Signals] Where CreatorUserId = @UserId AND [StopLossTouched] = 1
|
|
|
|
SET @i = 1
|
|
SET @numrows = (SELECT COUNT(*) FROM @Practitioner)
|
|
IF @numrows > 0
|
|
WHILE (@i <= (SELECT MAX(idx) FROM @Practitioner))
|
|
BEGIN
|
|
set @knokid = (SELECT KnokId FROM @Practitioner WHERE idx = @i)
|
|
|
|
set @knokrate = (SELECT 1 LowRate FROM [Signals] Where KnokId =@knokid )
|
|
set @knokentryto = (SELECT 1 LowRate FROM [Signals] Where KnokId =@knokid )
|
|
set @knokentryfrom = (SELECT 1 LowRate FROM [Signals] Where KnokId =@knokid )
|
|
|
|
set @profit = @knokrate - ((@knokentryfrom + @knokentryto)/2)
|
|
|
|
set @sumprofit = @sumprofit + @profit
|
|
|
|
SET @i = @i + 1
|
|
END
|
|
|
|
--___________________--
|
|
|
|
Set @win = (SELECT Count(*)
|
|
FROM [Signals] Where CreatorUserId = @UserId AND
|
|
[StopLossTouched] = 1)
|
|
|
|
SET @res = @sumprofit
|
|
if @win > 0
|
|
SET @res2 = @res/@win
|
|
else
|
|
SET @res2 = 0
|
|
|
|
RETURN @res2
|
|
|
|
|
|
|
|
|
|
|