30 lines
2.0 KiB
Transact-SQL
30 lines
2.0 KiB
Transact-SQL
CREATE VIEW [dbo].[VW_ActiveKnoks]
|
|
AS SELECT k.Duration, k.CreateDate, k.KnokId KnokId, k.Currency1 Currency1, k.Currency2 Currency2, k.Currency, t.TickerDisplayName as MarketDisplayName, k.ExchangeId, k.CreatorUserId as UserId,
|
|
k.EntryPriceFrom, k.EntryPriceTo, k.ExitPriceFrom, k.ExitPriceTo, k.StopLoss, e.ExchangeName, k.TickerId,
|
|
k.PotentialProfitValue, k.PotentialProfit,
|
|
k.Price, k.Price/.51*1 DollarPrice, ISNULL(k.EntryPriceTouched, 0) EntryPriceTouched,
|
|
u.HasAvatar, u.UserName, u.FirstName, u.LastName, k.PublishedDate, k.KnokStatusId, ISNULL(follow.UserFollowers, 0) UserFollowers, u.[Rank],
|
|
(SELECT lr.Rate FROM GetLastRate(k.Duration, t.Base, t.NonBase, k.ExchangeId) lr) AS CurrentPrice,
|
|
(SELECT count(1) FROM UserKnoks uk WHERE uk.KnokId = k.KnokId) AS Purchases,
|
|
((ISNULL((SELECT lr.Rate FROM GetLastRate(k.Duration, t.Base, t.NonBase, k.ExchangeId) lr), 0) -
|
|
((k.EntryPriceFrom + EntryPriceTo) / 2)) /
|
|
((k.EntryPriceFrom + EntryPriceTo) / 2)) AS CurrentProfit,
|
|
DATEDIFF(HOUR, GETUTCDATE(), DATEADD(day, k.Duration, k.CreateDate)) AS TimeLeft,
|
|
-- amount of purchases * knok price * knokser commission (70%)
|
|
(SELECT SUM(pk.Price) FROM UserKnoks uk
|
|
INNER JOIN Signals pk ON pk.KnokId = uk.KnokId
|
|
WHERE pk.knokId = k.knokId
|
|
GROUP BY uk.knokId) AS PricePoolData,
|
|
k.DistanceFromTarget,
|
|
k.HighRate, k.LowRate,
|
|
ISNULL(k.UserReview, 0) AS UserReview,
|
|
CAST(ISNULL(s.Decimals, 8) AS smallint) AS Precision
|
|
|
|
FROM [dbo].[Signals] k with(nolock)
|
|
LEFT JOIN [dbo].[_KnokStatuses] status on k.KnokStatusId = status.KnokStatusId
|
|
LEFT JOIN [dbo].[Users] u on u.UserId = k.CreatorUserId
|
|
LEFT JOIN [dbo].[Exchanges] e on e.ExchangeId = k.ExchangeId
|
|
LEFT JOIN [dbo].[Tickers] t on t.TickerId = k.TickerId
|
|
LEFT JOIN (select count(f.UserId) UserFollowers, f.FollowUserId userId from [dbo].[UserFollows] f group by f.FollowUserId) follow on follow.userId = k.CreatorUserId
|
|
left join [dbo].[Symbols] s on s.Symbol = k.Currency2
|
|
Where (status.KnokStatusId = 3 OR status.KnokStatusId = 4) --3- Available, 4-On-Going |