Aaaarrrrggghhh

Forum for users that want to write their own custom queries against the PT database either via the Structured Query Language (SQL) or using the PT3 custom stats/reports interface.

Moderator: Moderators

Aaaarrrrggghhh

Postby phonix4 » Thu Sep 03, 2009 8:13 am

I've jst managed to get my custom report to work with the date filter so that i only see the players playing from a specified date. this is so i can quickly see certain stats about my current opponents rather than have to trawl through the lists to search each player individual.
This is working fine so i only see the players that have played since the date selected. The only problem with this is all of the stats in my report now only provide the stats for the date specified.
So.
How can i view the players that i have played since Say yesterday but view their all time stats rather than jst the stats since yesterday?????????????????
In otherwords is there a way to run the date stat on the player stat only in my report rather than all columns so i get the current players but the rest of the report runs for all time stats rather than also being run through the date stat.
phonix4
 
Posts: 60
Joined: Wed Sep 02, 2009 9:57 pm

Re: Aaaarrrrggghhh

Postby kraada » Thu Sep 03, 2009 9:09 am

LIkely you'll just need to change your filter definition.

What are you currently using?
kraada
Moderator
 
Posts: 54443
Joined: Wed Mar 05, 2008 2:32 am
Location: NY

Re: Aaaarrrrggghhh

Postby kraada » Mon Sep 07, 2009 12:15 pm

I answered your question via PM but just to put things on the record for everyone else, I thought I'd post here.

phonix4 was looking for a custom report that showed only players that have hands imported from them today, but he wanted the report to show their lifetime statistics.

So you won't be able to filter using the regular Simple Filters on the custom report tab as that will show you only data from today - so you wouldn't have lifetime statistics.

Instead, you need to create a custom report and add this as a filter:
player.id_player IN (SELECT p.id_player FROM player p, holdem_hand_player_statistics hhps WHERE p.id_player = hhps.id_player AND hhps.date_played::date = localtimestamp::date)

What's going on here? We're filter for times the id_player (that is, the unique identifier associated with each player) is in a certain list. This will limit players to a certain group but won't limit hands later. What are we looking for id_player to be in? We built it as a subquery:
SELECT p.id_player FROM player p, holdem_hand_player_statistics hhps WHERE p.id_player = hhps.id_player AND hhps.date_played::date = localtimestamp::date

So anytime the player ID matches one of the p.id_player values, we see data for that player. We also need the holdem_hand_player_statistics table as that table is the one we are going to use to see if the player played any hands today. So we also select from a holdem_hand_player_statistics table (called hhps). We make sure that the player_ids match (p.id_player = hhps.id_player) because we want data from the same player. Then we make one more check:

hhps.date_played::date = localtimestamp::date

This is checking that the date_played field in the hhps table when turned into a date (e.g. 9/5/2009) matches the localtimestamp when turned into a date. localtimestamp is a reserved local variable that is always available in PostgreSQL and it always returns the current time right now. So when we cast it as a date, it will return today's date.

Putting everything back together, the subquery pulls out the id_player values for players who played today, and the whole query tests for whether someone's player ID is in that list. If it is, we display them. If it isn't, we don't.

I've attached this report (and uploaded it to the repository) and have the current version just display a player's name and the number of hands we have on the player. You can of course add any other data you would like to see.
Attachments
Today's Players.zip
This report shows lifetime data only for players who have played today.
(694 Bytes) Downloaded 102 times
kraada
Moderator
 
Posts: 54443
Joined: Wed Mar 05, 2008 2:32 am
Location: NY

Re: Aaaarrrrggghhh

Postby phonix4 » Wed Sep 09, 2009 11:45 am

Hi. I've downloaded the file but i can't import it into my reports. it doesn't show up for import. Also i've tried to open the file but my computer doesn't recognise the extension.

What should i do????

Also I've recreated the summary report in my custom reports can i jst add the filter to that????
phonix4
 
Posts: 60
Joined: Wed Sep 02, 2009 9:57 pm

Re: Aaaarrrrggghhh

Postby WhiteRider » Wed Sep 09, 2009 12:05 pm

You need to extract the report file from the .zip before importing it.
Right click > Extract.
WhiteRider
Moderator
 
Posts: 52496
Joined: Sat Jan 19, 2008 7:06 pm
Location: UK

Re: Aaaarrrrggghhh

Postby jimmyfloyd » Sat Mar 20, 2010 3:27 pm

Kraada,

Does this file work for the Mac version?

Thanks.
jimmyfloyd
 
Posts: 5
Joined: Sat Mar 20, 2010 3:18 pm

Re: Aaaarrrrggghhh

Postby WhiteRider » Sat Mar 20, 2010 4:25 pm

Yes - all files are cross-platform.
WhiteRider
Moderator
 
Posts: 52496
Joined: Sat Jan 19, 2008 7:06 pm
Location: UK

Re: Aaaarrrrggghhh

Postby domakeufeelrandy » Fri Apr 08, 2011 10:38 pm

Quick question. If i wanted to make this report for omaha, how would I do so?

I'm assuming I would have to change holdem_hand_player_statistics to omaha_hand_player_statistics, however when I do so my report is blank yet it tells me my expression is valid.

Any thoughts? thanks
domakeufeelrandy
 
Posts: 2
Joined: Tue Mar 29, 2011 8:59 pm

Re: Aaaarrrrggghhh

Postby WhiteRider » Sat Apr 09, 2011 4:28 am

Did you remember to untick "Filter on Active Player"? in the report Filter window?
Did you create your report in the Omaha Cash Player Statistics section?
That filter with the change you mention is working for me.
WhiteRider
Moderator
 
Posts: 52496
Joined: Sat Jan 19, 2008 7:06 pm
Location: UK

Re: Aaaarrrrggghhh

Postby domakeufeelrandy » Sat Apr 09, 2011 5:26 am

Yes, I copied the expression in the Hold'em Report into a new report under Omaha Cash Player Statistics. I only changed the part where it said holdem so the "new" expression I have is this:

player.id_player IN (SELECT p.id_player FROM player p, omaha_hand_player_statistics hhps WHERE p.id_player = hhps.id_player AND hhps.date_played::date = localtimestamp::date)

The "Filter on Active Player" is unticked but my report signals 0 players. Where did I screw up?
domakeufeelrandy
 
Posts: 2
Joined: Tue Mar 29, 2011 8:59 pm

Next

Return to Custom Stats, Reports, and SQL [Read Only]

Who is online

Users browsing this forum: No registered users and 1 guest

cron