Give Reward to Top 5 - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Give Reward to Top 5

Author

Topic

damdeok
Starting Member

39 Posts

Posted - 2011-03-23 : 03:14:55

Someone help me make a query?

I want to give reward to top 5 of each month.

top 1 = 500 credits
top 2 = 400 credits
top 3 = 300 credits
top 4 = 200 credits
top 5 = 100 credits

I have this table with the following data as an example:

CREATE TABLE [dbo].[LuckyCoinsRank](
[id] [int] IDENTITY(1,1) NOT NULL,
[Account] [nvarchar](10) NULL,
[LuckyCoins] [int] NULL,
[Date] [smalldatetime] NULL
) ON [PRIMARY]

ID Account LuckyCoins Date
1 acnamexx1 256 10/1/2010 0:00
2 acnamexx2 276 12/1/2010 0:00
3 acnamexx3 272 12/1/2010 0:00
4 acnamexx4 260 12/1/2010 0:00
5 acnamexx5 255 12/1/2010 0:00
6 acnamexx6 276 12/1/2010 0:00
7 acnamexx7 272 12/1/2010 0:00
8 acnamexx8 260 12/1/2010 0:00
9 acnamexx9 255 12/1/2010 0:00
10 acnamexx10 584 1/1/2011 0:01
11 acnamexx11 487 1/1/2011 0:01
12 acnamexx12 460 1/1/2011 0:01
13 acnamexx13 438 1/1/2011 0:01
14 acnamexx14 383 1/1/2011 0:01
15 acnamexx15 2825 2/1/2011 0:00
16 acnamexx16 2512 2/1/2011 0:00
17 acnamexx17 1411 2/1/2011 0:00
18 acnamexx18 1238 2/1/2011 0:00
19 acnamexx19 1105 2/1/2011 0:00
20 acnamexx20 2825 2/1/2011 0:00
21 acnamexx21 2512 2/1/2011 0:00
22 acnamexx22 1411 2/1/2011 0:00
23 acnamexx23 1238 2/1/2011 0:00
24 acnamexx24 1105 2/1/2011 0:00
25 acnamexx25 793 3/1/2011 0:00
26 acnamexx26 333 3/1/2011 0:00
27 acnamexx27 269 3/1/2011 0:00
28 acnamexx28 258 3/1/2011 0:00
29 acnamexx29 235 3/1/2011 0:00

the reward will go to this table

CREATE TABLE [dbo].[MEMB_CREDITS](
[memb___id] [varchar](10) NOT NULL,
[credits] [int] NOT NULL
) ON [PRIMARY]

Account from LuckyCoinsRank = memb___id from MEMB_CREDITS

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2011-03-23 : 03:47:16

[code]
UPDATE MEMB_CREDITS SET credits = credits + Reward
FROM MEMB_CREDITS M
INNER JOIN
(
SELECT Account, LuckyCoins, Date, RowNo,
CASE RowNo WHEN 1 THEN 500
WHEN 2 THEN 400
WHEN 3 THEN 300
WHEN 4 THEN 200
WHEN 5 THEN 100
END AS Reward
FROM
(
SELECT *, ROW_NUMBER() OVER( PARTITION BY DATE ORDER BY LuckyCoins DESC, id) RowNo FROM LuckyCoinsRank
) A
WHERE RowNo <= 5
) A ON M.memb___id = A.Account
[/code]

Vaibhav T

If I cant go back, I want to go fast...

damdeok
Starting Member

39 Posts

Posted - 2011-03-23 : 06:13:00

Thanks for the quick reply. I will check.

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2011-03-23 : 07:06:38

You are welcome

Vaibhav T

If I cant go back, I want to go fast...

damdeok
Starting Member

39 Posts

Posted - 2011-03-26 : 04:18:32

Can you please make a query for Top 5 of the previous 2 months?

Example 1: if this month is March, make a Top 5 for February and also make a Top 5 for January.
Example 2: if this month is April, make a Top 5 for March and also make a Top 5 for February.

Thanks.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources