Amt Won Today at Site x

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

Amt Won Today at Site x

Postby bball44 » Thu Aug 13, 2009 5:54 pm

I have tried the configure stats without success to do the following: I would like to be able to display in the HUD the total amount won by hero for all sessions on a particular site for the current system date.

Is this possible? I thought I was getting close by creating a column that looked like this: sum(if[dateextract_date[datefix_fromutc[ holdem_table_session_summary.date_start ]]=SYSDATETIME ()
, holdem_table_session_summary.amt_won, 0]) - but this is apparently not valid sql. Not sure if I need to change the syntax for SYSDATETIME (), or if the whole thing is no good.

Actually, I'd like to be able to something very similar for mgr also. I should be able to figure this out once I can do the above though.

Thanks for any help you can offer, and sorry if I could of found this by searching more - I did try.
bball44
 
Posts: 38
Joined: Sat Jul 19, 2008 5:29 pm

Re: Amt Won Today at Site x

Postby bball44 » Thu Aug 13, 2009 5:58 pm

It appears I've posted this twice - sorry - please delete one. Thanks.
bball44
 
Posts: 38
Joined: Sat Jul 19, 2008 5:29 pm

Re: Amt Won Today at Site x

Postby kraada » Thu Aug 13, 2009 6:36 pm

I'm not certain this is going to work, but you want to use localtimestamp - you can dateextract_day[] from it, and that will get you the day in question, you can then compare to see if it matches.

For a bit more on localtimestamp see here.

Good luck.
kraada
Moderator
 
Posts: 54430
Joined: Wed Mar 05, 2008 2:32 am
Location: NY

Re: Amt Won Today at Site x

Postby WhiteRider » Fri Aug 14, 2009 4:59 am

I don't think you'll be able to do this in the HUD because hero's stats are always for the current session only - Kraada's workaround might just do it, but I doubt it.
WhiteRider
Moderator
 
Posts: 54025
Joined: Sat Jan 19, 2008 7:06 pm
Location: UK

Re: Amt Won Today at Site x

Postby bball44 » Fri Aug 14, 2009 5:37 pm

Thanks for trying to help me with this. I've decided to see if I can do it as a report instead.

I am choosing filter on the report page so I can make different filters for different reports and not have them effect the database as a whole. To get today's date, I am entering: date_played_date = #8/11/09# into the filter on the report page. It tells me that this is not a valid statement. Is there some way I can do this? Thanks.
bball44
 
Posts: 38
Joined: Sat Jul 19, 2008 5:29 pm

Re: Amt Won Today at Site x

Postby kraada » Fri Aug 14, 2009 5:59 pm

I'm unfortunately on my way out for the evening so I won't be check in after this for a bit. Regardless, you certainly should be able to do this as a report though. You can in fact run any select statement you'd like as a filter, which basically solves your problems.

Try this filter:
date_played_date = (select current_date;)
kraada
Moderator
 
Posts: 54430
Joined: Wed Mar 05, 2008 2:32 am
Location: NY

Re: Amt Won Today at Site x

Postby bball44 » Fri Aug 14, 2009 6:13 pm

I get not valid when I use that. I can't find any expression that is considered valid. Do you know one? Thanks.
bball44
 
Posts: 38
Joined: Sat Jul 19, 2008 5:29 pm

Re: Amt Won Today at Site x

Postby WhiteRider » Sat Aug 15, 2009 4:57 am

The simplest way to do this is to click the Simple Filters button and add a date filter there.

Alternatively:
date_played = '2009/08/15'
..will work. (assuming you're using the Holdem Cash Player Statistics section).
Correction - that will validate, but doesn't seem to show any results.
This:
date_played_day=15 AND date_played_month=8 AND date_played_year=2009
..does work, though.

I can't figure out how to get it to work with the postgres 'current date' functions, but Kraada may be able to when he's back later.
WhiteRider
Moderator
 
Posts: 54025
Joined: Sat Jan 19, 2008 7:06 pm
Location: UK

Re: Amt Won Today at Site x

Postby kraada » Sat Aug 15, 2009 2:34 pm

My apologies for taking so long to get back to you.

Use this filter and it will filter to the current date based on the date a hand was played:

0 < (SELECT count(holdem_hand_player_statistics.id_hand) FROM holdem_hand_player_statistics WHERE
holdem_hand_player_statistics.date_played::date = current_date)
kraada
Moderator
 
Posts: 54430
Joined: Wed Mar 05, 2008 2:32 am
Location: NY

Re: Amt Won Today at Site x

Postby bball44 » Mon Aug 17, 2009 6:55 pm

Thank you both for all the responses.

The last statement validated, and I've saved it as part of my report, but for some reason the filter seems not to be applied, as I get sums for all hands that have been played and not for any particular date/dates. Maybe I am not understanding something. I have 2 fields: MGR & Amount Won.

Is there somewhere I can query the database directly from within PT3?

Thanks again.
bball44
 
Posts: 38
Joined: Sat Jul 19, 2008 5:29 pm

Next

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

Who is online

Users browsing this forum: No registered users and 0 guests