HELP PLZ.. - 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 2000 Forums

Transact-SQL (2000)

HELP PLZ..

Author

Topic

azmark30
Starting Member

11 Posts

Posted - 2007-11-30 : 12:01:35

OK here's what Im trying to do..
ROOM Points for -OUT OF TOWN PLAYERS-
only last 10 days of tracked play count in room point calculation.
we look at the last 18 months for the tracked 10 days of play.
a player must earn a full 100 points in 24 hours to get 1 room point.
if player earns 500 he then gets 5 room points in that 24 hours..
the most a player can earn is 30 points per 2 month period.

ROOM POINTS for LOCAL PLAYERS -
10 days tracked play we look at the last 3 months for the tracked 10 days.
a local will earn half a point for every 100 points earned in 24 hours. if he earns 500 points he will get 21 1/2 room points.

i tried to write something but i can not get it right.. when i try this sql query it shows what i need but when it does the (c.ptsearned) /100 /2 it makes it a 0 it rounds the .49 to a 0..

playerid first last local points date roompoints
209802 MARIA MACKENZIE Y 119 2007-04-19 00:00:00.000 0

heres my sql query..

SELECT
primary_ID AS 'PLAYERID',
FIRSTNAME AS 'FIRST',
LASTNAME AS 'LAST',
local,
c.ptsearned as 'pointsEarned',
gamingdate as 'date',
roompoints =
case
when local='n'
then (c.ptsearned) /100
when local='y'
then (c.ptsearned) /100 /2
else '0'
end
FROM cdS_PLAYER A, CDS_ACCOUNT B, CDS_STATDAY C
WHERE
B.primary_ID=A.player_ID AND
B.primary_ID*=C.META_ID AND
C.IDTYPE='P' AND
c.STATTYPE='SLOT' and
gamingdate between getdate()-547 and getdate()
group BY
primary_ID,
FIRSTNAME,
LASTNAME,
local,
c.ptsearned,
gamingdate
ORDER BY primary_ID ASC

thanks for anyones help on this.. any ideas or anything would be great help..

azmark30
Starting Member

11 Posts

Posted - 2007-11-30 : 12:16:32

I also need this to total up the roompoints for the last 10 days.
i get this
playerid first last local points date roompoints
208440 HARRIET ALBERTSON N 167 2006-11-28 00:00:00.000 1
208440 HARRIET ALBERTSON N 235 2007-01-23 00:00:00.000 2
208440 HARRIET ALBERTSON N 293 2007-03-06 00:00:00.000 2
208440 HARRIET ALBERTSON N 294 2007-11-13 00:00:00.000 2
208440 HARRIET ALBERTSON N 295 2007-01-16 00:00:00.000 2
208440 HARRIET ALBERTSON N 305 2007-11-11 00:00:00.000 3
208440 HARRIET ALBERTSON N 449 2007-11-25 00:00:00.000 4
i would like it to just add up the roompoints
playerid first last roompoints
208440 HARRIET ALBERTSON 16

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2007-11-30 : 12:55:56

Ok I was too lazy to understand your whole post but regarding:
>>it does the (c.ptsearned) /100 /2 it makes it a 0

try this:
(c.ptsearned) /100.00 /2.00

regarding your total by person try:

select playerid, first, last, sum(roompoints) as totalRoomPoints
from <your tables>
group by playerid, first, last

Be One with the Optimizer
TG

azmark30
Starting Member

11 Posts

Posted - 2007-11-30 : 13:28:37

ok the first part works..
(c.ptsearned) /100 /2.

second part i need this -
im guessing -

order by date desc and then i need it to take the top 10 days and sum(roompoints) and theres a lot of accounts.. so i need it to do it for every account in the database..

not sure if that make sense..

azmark30
Starting Member

11 Posts

Posted - 2007-11-30 : 14:17:50

I have a table with
playerid, first , last , local, points, date, roompoints..

I need to write something that will sum(roompoints) by the first 10 dates from date..
example

playerid first last date roompoints

272012 ETHEL SWANSON 2007-06-14 00:00:00.000 6
27012 ETHEL SWANSON 2007-06-11 00:00:00.000 8
87450 JANE DOE 2007-02-12 00:00:00.000 5
87450 JANE DOE 2007-01-13 00:00:00.000 6

this is what i want -

272012 ETHEL SWANSON 14
87450 JANE DOE 11

and so no with the other accounts...

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2007-11-30 : 17:22:52

is this what you are looking for?


select	playerid
	, first 
	, last 
	, sum(roompoints) roompoints
from	(
	select	playerid
		, first 
		, last 
		, roompoints
	from	yourTable yt
	where	date in (select top 10 date 
			from yourtable
			where playerid = yt.playerid
			order by date desc)
	) a
group by playerid
	, first 
	, last

Be One with the Optimizer
TG

azmark30
Starting Member

11 Posts

Posted - 2007-11-30 : 17:26:29

yup thats it.. thanks again

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources