BRAINBUSTER FOLLOW-UP - 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)

BRAINBUSTER FOLLOW-UP

Author

Topic

joemama
Posting Yak Master

113 Posts

Posted - 2007-12-02 : 19:24:18

OK...the help i got on this was amazing and now it works exactly as the client wanted...key word..WANTED..yep the client just informed me of on small change...

below is the stored procedure

what i need to change is these two fields (if either field totals more that 40 then the sum should only be 40..if the total sum is less than 40 there the sum should be whatever it is)

SUM(TRMTGPoints) AS MTGPoints,
SUM(TRDeBriefPoints) AS DeBriefPoints,

is there any way to do it?

**********************

AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here
;WITH cte
AS
(
SELECT dbo.WTournament_Results.TRUserID,
dbo.WUsers.Suffix,
dbo.WUsers.UserFirstName,
dbo.WUsers.UserLastName,
dbo.WTournament_Points.Points AS Points,
Row_Number() OVER (partition BY dbo.WTournament_Results.TRUserID ORDER BY dbo.WTournament_Points.Points DESC) AS RowID,
dbo.WTournament_Results.TRMTGPoints,
dbo.WTournament_Results.TRDeBriefPoints
FROM dbo.WTournaments
INNER JOIN dbo.WTournament_Results ON dbo.WTournaments.tournID = dbo.WTournament_Results.TID
INNER JOIN dbo.WUsers ON dbo.WTournament_Results.TRUserID = dbo.WUsers.UserID
INNER JOIN dbo.WTournament_Points ON dbo.WTournament_Results.TRPlace = dbo.WTournament_Points.Place
WHERE dbo.WTournaments.TournYear = @TournYear
AND dbo.WTournaments.TournYearInc = 1
)

SELECT TRUserID,
Suffix,
UserFirstName,
UserLastName,
SUM(TRMTGPoints) AS MTGPoints,
SUM(CASE WHEN RowID > 8 THEN 0 ELSE Points END) AS [YTD Points],
SUM(TRDeBriefPoints) AS DeBriefPoints,
SUM(TRMTGPoints) + SUM(TRDeBriefPoints)+ SUM(CASE WHEN RowID > 8 THEN 0 ELSE Points END) AS TOTALPOINTS
FROM cte
GROUP BY TRUserID,
Suffix,
UserFirstName,
UserLastName
ORDER BY TOTALPOINTS DESC

END

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-12-02 : 19:27:21

[code]case when SUM(TRMTGPoints) >= 40 then 40 else sum(TRMTGPoints) AS MTGPoints,[/code]

KH
[spoiler]Time is always against us[/spoiler]

joemama
Posting Yak Master

113 Posts

Posted - 2007-12-02 : 21:13:14

got it...thanks

quote:
Originally posted by khtan

case when SUM(TRMTGPoints) >= 40 then 40 else sum(TRMTGPoints) AS MTGPoints,

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources