Geek Answers Handbook

SQL Server stored procedure return table with uniques for each user

I found here a little and a little, but nothing that really touches on the question that I have here. I ordered a book from Amazon, but it will not be here for another week, and I really need it as soon as possible.

I have two tables that contain basically the following.

Table A has a user id number, username, winnings, losses, links

Table B has the user ID number, when the game ends, the state of the game

I want to create a stored procedure that will return the top ten wins in the last week.

 Loginname | total wins, last 7 days | all wins | all losses | all ties Name1 | 10 | 40 | 8 | 6 Name2 | 9 | 96 | 76 | 19

etc....

What I still have:

 SELECT A.login, A.draws_count, A.losses_count, A.wins_count FROM [TableB] AS B INNER JOIN [TableA] AS A ON B.won_by_id = A.id WHERE B.win_defined_time > (GETDATE() - 7) AND B.state = 'OVER';

From there, I do not know how to return the table that I need. Any help would be greatly appreciated. (also keep in mind that “total winnings for the last 7 days” do not exist in any table.)

+4
source share
1 answer

Assuming schema and sample data, such as:

 CREATE TABLE [dbo].[Competitors] ( [id] INT NOT NULL, [login_name] VARCHAR (50) NOT NULL, [wins] INT NOT NULL, [losses] INT NOT NULL, [ties] INT NOT NULL ) ON [PRIMARY] CREATE TABLE [dbo].[Events] ( [id] INT NOT NULL, [Competitorid] VARCHAR (50) NOT NULL, [EventDateTime] DATETIME NOT NULL, [winner] BIT NOT NULL, [EventStatus] VARCHAR (50) NOT NULL ) ON [PRIMARY] INSERT INTO Competitors (id, login_name, wins, losses, ties) VALUES (1, 'Player 1', 40, 8, 6), (2, 'Player 2', 96, 76, 19), (3, 'Player 3', 1, 0, 0) INSERT INTO Events (id, Competitorid, EventDateTime, winner, EventStatus) VALUES (1, 1, '2013-01-25 01:05:25.000', 1, 'OVER'), (2, 1, '2013-01-26 01:05:25.000', 1, 'OVER'), (3, 1, '2013-01-27 14:05:25.000', 1, 'OVER'), (4, 1, '2013-01-28 01:05:25.000', 1, 'OVER'), (5, 1, '2013-01-29 15:05:25.000', 1, 'OVER'), (6, 1, '2013-01-30 01:05:25.000', 1, 'OVER'), (7, 1, '2013-01-31 22:05:25.000', 1, 'OVER'), (8, 1, '2013-02-01 01:05:25.000', 1, 'OVER'), (9, 1, '2013-02-02 21:05:25.000', 1, 'OVER'), (10, 1, '2013-01-02 11:05:25.000', 0, 'INPROGRESS'), (11, 1, '2013-01-30 01:05:25.000', 1, 'OVER'), (12, 2, '2013-01-25 11:05:25.000', 1, 'OVER'), (13, 2, '2013-01-26 01:05:25.000', 1, 'OVER'), (14, 2, '2013-01-27 11:25:25.000', 1, 'OVER'), (15, 2, '2013-01-28 01:05:25.000', 1, 'OVER'), (16, 2, '2013-01-29 11:45:25.000', 1, 'OVER'), (17, 2, '2013-01-30 01:45:25.000', 1, 'OVER'), (18, 2, '2013-01-31 12:15:25.000', 1, 'OVER'), (19, 2, '2013-02-01 01:05:25.000', 1, 'OVER'), (20, 2, '2013-02-02 22:25:25.000', 1, 'OVER'), (21, 2, '2013-02-02 15:05:25.000', 0, 'INPROGRESS'), (22, 2, '2013-01-25 01:05:25.000', 1, 'OVER'), (23, 1, '2013-01-30 01:05:25.000', 0, 'OVER'), (24, 2, '2013-01-30 01:05:25.000', 0, 'OVER'), (25, 3, '2012-01-30 01:05:25.000', 1, 'OVER')

You can return the names and get data for the ten people with the most victories in the last 7 days using the following query:

 SELECT TOP 10 login_name, recent_wins, wins AS 'All Wins', losses AS 'All losses', ties AS 'All Ties' FROM Competitors INNER JOIN (SELECT COUNT(*) AS recent_wins, Competitorid FROM events WHERE winner = 1 AND eventdatetime BETWEEN GetDate() - 7 AND GetDate() AND EventStatus = 'OVER' GROUP BY Competitorid) AS recent_event_winners ON Competitors.ID = recent_event_winners.Competitorid; ORDER BY recent_wins DESC

This query works by combining the data in the competitors table with the subquery according to the data in the events table, which pump out the number of last wins and then take the first 10 results. For users with a win over the past seven days, the subquery returns a counter of the number of winnings that the user had for events that were during this time period.

Note. users without any winnings will not return a single request over a period of time so that the results can have less than 10 results.

The SQL script with the above creation scripts and SQL queries can be found at http://sqlfiddle.com/#!3/0ebc8/2

+1
source

More articles:


All Articles