61 lines
1.4 KiB
Transact-SQL
61 lines
1.4 KiB
Transact-SQL
CREATE PROCEDURE [GetWinPayoff]
|
|
@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 [EntryPriceTouched] = 1 OR [ExitPriceTouched]=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 [HighRate] FROM [Signals] Where KnokId =@knokid )
|
|
set @knokentryto = (SELECT 1 [HighRate] FROM [Signals] Where KnokId =@knokid )
|
|
set @knokentryfrom = (SELECT 1 [HighRate] 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 [EntryPriceTouched] = 1
|
|
OR [ExitPriceTouched]=1)
|
|
|
|
SET @res = @sumprofit
|
|
IF @win>0
|
|
SET @res2 = @res/@win
|
|
ELSE
|
|
SET @res2 = 0
|
|
RETURN @res2
|