Help with query

Hello I have query which is pulling the winnings for each player by month and year, I total the winnings but I would only like to see the top winner for each month.

Here is the query I have, any insight would be great thanks in advance.

SELECT TOP (100) PERCENT dbo.Players.Player_name, SUM(dbo.Event_data.Transaction_value) AS Winnings, DATEPART(year, dbo.Events.starttime) AS Year,
DATEPART(month, dbo.Events.starttime) AS Month
FROM dbo.Event_data INNER JOIN
dbo.Players ON dbo.Event_data.Player_id = dbo.Players.Player_id INNER JOIN
dbo.Events ON dbo.Event_data.Event_id = dbo.Events.id
GROUP BY dbo.Players.Player_name, dbo.Event_data.Transaction_type, DATEPART(year, dbo.Events.starttime), DATEPART(month, dbo.Events.starttime)
HAVING (dbo.Event_data.Transaction_type = 1)
ORDER BY Year, Month, Winnings DESC

Hi.

If you want to get the maximum value for each month, you must use the MAX function instead of SUM.

If you want the maximum for each month but, wants the total too, you can't see them in the same query because for getting the total you need to group the months too. May you coudl get the maximum for ecah month in the .net code:

Dim rw() As DataRow

rw = dataTable.Select("MAX(Winnings) AND Month = 1")

|||

Well first you could put this data you have in the above querry of the totals of all players per month in a table:

Table tempPlayerData

(

Player name,

PlayerScore,

Month etc...

)

then get the max from this temp table for each month.

|||

Sorry can you provide your answer using the query I provided.

thanks

|||

Sorry can you provide your answer using the query I provided.

thanks

|||

You want it in separate query or same query ?

So how will you want the result like below ?

[Player_Name] - [Winnings] - [Year] - [Month] - [Top Winner Name]

Note the [Top Winner Name] will be repeated for every record

|||

In the end I would like

Player name Winnings Month Year

george 24,000 1 2007

fred 12,000 2 2007

etc

only one record for each month

Help with Max Query

Hello I have query which is pulling the winnings for each player by month and year, I total the winnings but I would only like to see the top winner for each month.

Here is the query I have, any insight would be great thanks in advance.

SELECT TOP (100) PERCENT dbo.Players.Player_name, SUM(dbo.Event_data.Transaction_value) AS Winnings, DATEPART(year, dbo.Events.starttime) AS Year,
DATEPART(month, dbo.Events.starttime) AS Month
FROM dbo.Event_data INNER JOIN
dbo.Players ON dbo.Event_data.Player_id = dbo.Players.Player_id INNER JOIN
dbo.Events ON dbo.Event_data.Event_id = dbo.Events.id
GROUP BY dbo.Players.Player_name, dbo.Event_data.Transaction_type, DATEPART(year, dbo.Events.starttime), DATEPART(month, dbo.Events.starttime)
HAVING (dbo.Event_data.Transaction_type = 1)
ORDER BY Year, Month, Winnings DESC

Is this what you need?:

SELECT TOP (100) PERCENT dbo.Players.Player_name, MAX(dbo.Event_data.Transaction_value) AS Winnings, DATEPART(year, dbo.Events.starttime) AS Year,
DATEPART(month, dbo.Events.starttime) AS Month
FROM dbo.Event_data INNER JOIN
dbo.Players ON dbo.Event_data.Player_id = dbo.Players.Player_id INNER JOIN
dbo.Events ON dbo.Event_data.Event_id = dbo.Events.id
GROUP BY dbo.Players.Player_name, dbo.Event_data.Transaction_type, DATEPART(year, dbo.Events.starttime), DATEPART(month, dbo.Events.starttime)
HAVING (dbo.Event_data.Transaction_type = 1)
ORDER BY Year, Month, Winnings DESC

|||

Hi Mariop

no I need to sum the values first so I can get the monthly total of each player, then I want to select the highest monthly total for each month.

help with join query

I am writing a query to find missing numbers. The query works, but is very very slow due to the number of records it is currently pulling.

I want to limit the records to only search where "records.event_year = 2007" but when I try to insert that after the "from dbo.records" it gives me an error.

Where exactly do I need to add this and is there anything else I can do to make the query run faster? The event_year, state_file_number, and isactive are all indexed.

================================================== =======
ALTER procedure [Migrate].[Chk_Missing_SFN]

@.beg as int,
@.end as int

AS
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
select BeforeSkip+1 as gapStart, NextValue-1 as gapEnd from (
select
a.state_file_number as BeforeSkip,
min(b.state_file_number) as NextValue
from dbo.records A join dbo.records b
on a.state_file_number < b.state_file_number

where a.state_file_number between @.beg and @.end and a.isactive = 'T' and b.isactive = 'T'

group by a.state_file_number
having min(b.state_file_number) > a.state_file_number + 1
) Xwhat error are you getting?

I imagine that the "A.event_year = 2007" needs to go after the "on A.state_file_number < b.state_file_number"|||Remember that this snippet creates a million row test set before you judge speed.-- ptp 20071130 See http://www.dbforums.com/showthread.php?t=1624988

CREATE TABLE patp (
patpId INT NOT NULL
CONSTRAINT XPKpatp
PRIMARY KEY CLUSTERED (patpId)
)

INSERT INTO patp (
patpID
) SELECT 0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9

INSERT INTO patp (
patpID
) SELECT d0.patpID + d1.patpId + d2.patpId + d3.patpId + d4.patpId + d5.patpId
FROM patp AS d0
CROSS JOIN (SELECT 10 * patpId AS patpID FROM patp) AS d1
CROSS JOIN (SELECT 100 * patpId AS patpID FROM patp) AS d2
CROSS JOIN (SELECT 1000 * patpId AS patpID FROM patp) AS d3
CROSS JOIN (SELECT 10000 * patpId AS patpID FROM patp) AS d4
CROSS JOIN (SELECT 100000 * patpId AS patpID FROM patp) AS d5
WHERE 0 < d1.patpId + d2.patpId + d3.patpId + d4.patpId + d5.patpId
ORDER BY 1

DECLARE @.i INT
SET @.i = 1

WHILE @.i < (SELECT Max(patpID) FROM patp)
BEGIN
DELETE FROM patp WHERE patpID = @.i
SET @.i = 2 * @.i
END

SELECT a.patpID AS block_begin
, (SELECT Min(b.patpID)
FROM patp AS b
WHERE a.patpID <= b.patpID
AND NOT EXISTS (SELECT *
FROM patp AS c
WHERE c.patpID = 1 + b.patpID)) AS block_end
FROM patp AS a
WHERE NOT EXISTS (SELECT *
FROM patp AS b
WHERE b.patpId = a.patpID - 1)

DROP TABLE patp-PatP|||Thank you very much. That worked as needed.