20 lines
1.6 KiB
Transact-SQL
20 lines
1.6 KiB
Transact-SQL
CREATE VIEW [dbo].[VW_PublicKnoks]
|
|
AS
|
|
SELECT k.KnokId KnokId, k.Currency1 Currency1, k.Currency2 Currency2, k.Currency, k.ExchangeId, k.CreatorUserId as UserId,
|
|
k.HasFundamental, k.HasTechnical, k.CreateDate, k.PublishedDate, t.TickerDisplayName "MarketDisplayName", e.ExchangeName,
|
|
k.PotentialProfitValue, k.PotentialProfit, DATEADD(day, k.Duration, k.CreateDate) FinishTime,
|
|
k.Price,
|
|
ISNULL(k.EntryPriceTouched, 0) EntryPriceTouched,
|
|
u.HasAvatar, u.UserName, u.FirstName, u.LastName, k.SellFinishDate, k.TickerId, k.Duration, k.Ranks, purchases.UserKnoks, NULL as Rank,
|
|
ISNULL(follow.UserFollowers, 0) UserFollowers, u.Rank as KnokserRank, ISNULL(purchases.UserKnoks, 0) Purchases, ISNULL(activeKnoks.Knoks, 0) UserActiveKnoks
|
|
FROM [dbo].[Signals] k with(nolock)
|
|
LEFT JOIN [dbo].[Exchanges] e on e.ExchangeId = k.ExchangeId
|
|
LEFT JOIN [dbo].[Users] u on u.UserId = k.CreatorUserId
|
|
LEFT JOIN [dbo].[Tickers] t on t.TickerId=k.TickerId
|
|
LEFT JOIN [dbo].[_KnokStatuses] status on k.KnokStatusId = status.KnokStatusId
|
|
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 (select count(f.UserId) UserKnoks, f.KnokId KnokId from [dbo].[UserKnoks] f group by f.KnokId) purchases on purchases.KnokId = k.KnokId
|
|
LEFT JOIN (select count(f.KnokId) Knoks, f.CreatorUserId UserId from [dbo].[Signals] f left join [_KnokStatuses] s
|
|
on s.KnokStatusId=f.KnokStatusId where s.IsPublished=1 and s.HasResult = 0 group by f.CreatorUserId) activeKnoks on activeKnoks.UserId = k.CreatorUserId
|
|
WHERE
|
|
status.KnokStatusId = 3 --3-Available |