March 21, 2011 at 10:44 am
Hello all,
Hope this is in the right place! Apologises if its not.
I have the following code that should be returning the following columns
Alias, FreshPointsBalance, PercentPoints
But instead is giving me
Alias, FreshPointsBalance, (no column name)
I'm using this sql statement...
;with cte AS (
SELECT t.User_ID, t.FreshPointsBalance,
PercentPoints = CONVERT(DECIMAL, (
CONVERT(FLOAT, t.FreshPointsBalance) /
CONVERT(FLOAT, SUM(t.FreshPointsBalance) OVER() )
) * 100
) + '%'
FROM
(SELECT TOP(10) PERCENT WITH TIES Users.User_ID, DailyUsers.FreshPointsBalance
FROM Users
INNER JOIN DailyUsers
ON DailyUsers.User_ID = Users.User_ID
WHERE DailyUsers.DailyID = 13
ORDER BY DailyUsers.FreshPointsBalance desc
) t
)
SELECT u.Alias, du.FreshPointsBalance, COALESCE(cte.PercentPoints,0)
FROM Users u
INNER JOIN DailyUsers du
ON du.User_ID = u.User_ID
LEFT OUTER JOIN cte ON u.User_ID=cte.User_ID
WHERE du.DailyID = 13
ORDER BY du.FreshPointsBalance desc
Any ideas what I'm doing wrong here?
Thanks!
John
March 21, 2011 at 10:49 am
. . .
SELECT u.Alias, du.FreshPointsBalance, COALESCE(cte.PercentPoints,0) AS PercentPoints
. . .
March 21, 2011 at 10:51 am
sweet! thank you
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply