Query to get current active players?

[iso_stat_ffs=/]

I've turned on database command logging, and then clicked "Launch Active Player Details Wndow...", but not query was logged.

Can anyone help with an idea how I can get that information from a query?

[phillitup]

Just saw your post, are you still looking for this? I wrote a query that returns the active players, if you haven't been able to do it yet.

[a.k.a.Also]

Just saw your post, are you still looking for this? I wrote a query that returns the active players, if you haven't been able to do it yet.

I am interested in this if you don't mind sharing.

[phillitup]

Here you go:
----------------------------------
select
localtimestamp as "time",
playername as player
from playerhandscashkeycolumns ph join players pl on (pl.player_id = ph.player_id)
where
pl.lastplayeddate > (localtimestamp - interval '6 hours')
--you might have to experiment with the above line, depending on your timezone.
--this interval works for GMT (my local timezone) and Stars. Try it and if the
--query returns empty, add or subtract an hour at a time until something shows.
--Of course, HEM has to import a hand from an active table first
--And I expect the timezone will get thrown off when Daylight Savings Time changes
--and the poker site is not in sync with the change


--and pl.playername <> 'hero name'
--uncomment the above line and insert your hero name if you don't want yourself to show up

and (ph.player_id > 0)
--and cashhands > 0
Group by playername

-----------------------------------------------

Once you have the timezone sorted, this leads to all kinds of stuff. Say we want to retrieve players, VPIP by position:

----------------------------------------------------

select
localtimestamp,
playername as player,
count(ph.*) as totalhands,
round(sum(case when didvpip = true AND positiontype_id = 0 then 1 else 0 end) / 1.00 / (sum(case when positiontype_id = 0 then 1 else 0 end)+.00001)*100,1) as SB,
round(sum(case when didvpip = true AND positiontype_id = 1 then 1 else 0 end) / 1.00 / (sum(case when positiontype_id = 1 then 1 else 0 end)+.00001)*100,1) as BB,
round(sum(case when didvpip = true AND positiontype_id = 2 then 1 else 0 end) / 1.00 / (sum(case when positiontype_id = 2 then 1 else 0 end)+.00001)*100,1) as UTG,
round(sum(case when didvpip = true AND positiontype_id = 3 then 1 else 0 end) / 1.00 / (sum(case when positiontype_id = 3 then 1 else 0 end)+.00001)*100,1) as UTG1,
round(sum(case when didvpip = true AND positiontype_id = 4 then 1 else 0 end) / 1.00 / (sum(case when positiontype_id = 4 then 1 else 0 end)+.00001)*100,1) as Co,
round(sum(case when didvpip = true AND positiontype_id = 5 then 1 else 0 end) / 1.00 / (sum(case when positiontype_id = 5 then 1 else 0 end)+.00001)*100,1) as Button,
round(avg(case when didvpip = true then 1 else 0 end)*100,1) as vpip
from playerhandscashkeycolumns ph join players pl on (pl.player_id = ph.player_id)
join pokerhands pkh on pkh.pokerhand_id = ph.pokerhand_id join playerhandscashmisc phmisc
on phmisc.playerhand_id = ph.playerhand_id join gametypes gt on gt.gametype_id = ph.gametype_id
left join playerhandsriver river on ph.playerhand_id = river.playerhand_id
where
pl.lastplayeddate > (localtimestamp - interval '6 hours')
and pl.playername <> 'insert hero name'
and (ph.player_id > 0)
Group by playername


-------------------------------------------

[phillitup]

Or say we have a long history with a couple of the players, and want to start to establish a PF range:

-------------------------------------------------------------

select
localtimestamp as "time",
PL.Playername as player,
count(ph.*) as totalhands,
sum(case when ph.holecard1int > 0 then 1 else 0 end) as KnownCards,
sum(case when ph.holecardvalue_id =1 then 1 else 0 end) as _AA,
sum(case when ph.holecardvalue_id =2 then 1 else 0 end) as _KK,
sum(case when ph.holecardvalue_id =3 then 1 else 0 end) as _QQ,
sum(case when ph.holecardvalue_id =4 then 1 else 0 end) as _JJ,
sum(case when ph.holecardvalue_id =5 then 1 else 0 end) as _TT,
sum(case when ph.holecardvalue_id =6 then 1 else 0 end) as _99,
sum(case when ph.holecardvalue_id =7 then 1 else 0 end) as _88,
sum(case when ph.holecardvalue_id =8 then 1 else 0 end) as _77,
sum(case when ph.holecardvalue_id =9 then 1 else 0 end) as _66,
sum(case when ph.holecardvalue_id =10 then 1 else 0 end) as _55,
sum(case when ph.holecardvalue_id =11 then 1 else 0 end) as _44,
sum(case when ph.holecardvalue_id =12 then 1 else 0 end) as _33,
sum(case when ph.holecardvalue_id =13 then 1 else 0 end) as _22,
sum(case when ph.holecardvalue_id =14 then 1 else 0 end) as _AKs,
sum(case when ph.holecardvalue_id =15 then 1 else 0 end) as _AQs,
sum(case when ph.holecardvalue_id =16 then 1 else 0 end) as _AJs,
sum(case when ph.holecardvalue_id =17 then 1 else 0 end) as _ATs,
sum(case when ph.holecardvalue_id =18 then 1 else 0 end) as _A9s,
sum(case when ph.holecardvalue_id =19 then 1 else 0 end) as _A8s,
sum(case when ph.holecardvalue_id =20 then 1 else 0 end) as _A7s,
sum(case when ph.holecardvalue_id =21 then 1 else 0 end) as _A6s,
sum(case when ph.holecardvalue_id =22 then 1 else 0 end) as _A5s,
sum(case when ph.holecardvalue_id =23 then 1 else 0 end) as _A4s,
sum(case when ph.holecardvalue_id =24 then 1 else 0 end) as _A3s,
sum(case when ph.holecardvalue_id =25 then 1 else 0 end) as _A2s,
sum(case when ph.holecardvalue_id =26 then 1 else 0 end) as _KQs,
sum(case when ph.holecardvalue_id =27 then 1 else 0 end) as _KJs,
sum(case when ph.holecardvalue_id =28 then 1 else 0 end) as _KTs,
sum(case when ph.holecardvalue_id =29 then 1 else 0 end) as _K9s,
sum(case when ph.holecardvalue_id =30 then 1 else 0 end) as _K8s,
sum(case when ph.holecardvalue_id =31 then 1 else 0 end) as _K7s,
sum(case when ph.holecardvalue_id =32 then 1 else 0 end) as _K6s,
sum(case when ph.holecardvalue_id =33 then 1 else 0 end) as _K5s,
sum(case when ph.holecardvalue_id =34 then 1 else 0 end) as _K4s,
sum(case when ph.holecardvalue_id =35 then 1 else 0 end) as _K3s,
sum(case when ph.holecardvalue_id =36 then 1 else 0 end) as _K2s,
sum(case when ph.holecardvalue_id =37 then 1 else 0 end) as _QJs,
sum(case when ph.holecardvalue_id =38 then 1 else 0 end) as _QTs,
sum(case when ph.holecardvalue_id =39 then 1 else 0 end) as _Q9s,
sum(case when ph.holecardvalue_id =40 then 1 else 0 end) as _Q8s,
sum(case when ph.holecardvalue_id =41 then 1 else 0 end) as _Q7s,
sum(case when ph.holecardvalue_id =42 then 1 else 0 end) as _Q6s,
sum(case when ph.holecardvalue_id =43 then 1 else 0 end) as _Q5s,
sum(case when ph.holecardvalue_id =44 then 1 else 0 end) as _Q4s,
sum(case when ph.holecardvalue_id =45 then 1 else 0 end) as _Q3s,
sum(case when ph.holecardvalue_id =46 then 1 else 0 end) as _Q2s,
sum(case when ph.holecardvalue_id =47 then 1 else 0 end) as _JTs,
sum(case when ph.holecardvalue_id =48 then 1 else 0 end) as _J9s,
sum(case when ph.holecardvalue_id =49 then 1 else 0 end) as _J8s,
sum(case when ph.holecardvalue_id =50 then 1 else 0 end) as _J7s,
sum(case when ph.holecardvalue_id =51 then 1 else 0 end) as _J6s,
sum(case when ph.holecardvalue_id =52 then 1 else 0 end) as _J5s,
sum(case when ph.holecardvalue_id =53 then 1 else 0 end) as _J4s,
sum(case when ph.holecardvalue_id =54 then 1 else 0 end) as _J3s,
sum(case when ph.holecardvalue_id =55 then 1 else 0 end) as _J2s,
sum(case when ph.holecardvalue_id =56 then 1 else 0 end) as _T9s,
sum(case when ph.holecardvalue_id =57 then 1 else 0 end) as _T8s,
sum(case when ph.holecardvalue_id =58 then 1 else 0 end) as _T7s,
sum(case when ph.holecardvalue_id =59 then 1 else 0 end) as _T6s,
sum(case when ph.holecardvalue_id =60 then 1 else 0 end) as _T5s,
sum(case when ph.holecardvalue_id =61 then 1 else 0 end) as _T4s,
sum(case when ph.holecardvalue_id =62 then 1 else 0 end) as _T3s,
sum(case when ph.holecardvalue_id =63 then 1 else 0 end) as _T2s,
sum(case when ph.holecardvalue_id =64 then 1 else 0 end) as _98s,
sum(case when ph.holecardvalue_id =65 then 1 else 0 end) as _97s,
sum(case when ph.holecardvalue_id =66 then 1 else 0 end) as _96s,
sum(case when ph.holecardvalue_id =67 then 1 else 0 end) as _95s,
sum(case when ph.holecardvalue_id =68 then 1 else 0 end) as _94s,
sum(case when ph.holecardvalue_id =69 then 1 else 0 end) as _93s,
sum(case when ph.holecardvalue_id =70 then 1 else 0 end) as _92s,
sum(case when ph.holecardvalue_id =71 then 1 else 0 end) as _87s,
sum(case when ph.holecardvalue_id =72 then 1 else 0 end) as _86s,


------------continues--------------

[phillitup]

---------------------continued------------------

sum(case when ph.holecardvalue_id =73 then 1 else 0 end) as _85s,
sum(case when ph.holecardvalue_id =74 then 1 else 0 end) as _84s,
sum(case when ph.holecardvalue_id =75 then 1 else 0 end) as _83s,
sum(case when ph.holecardvalue_id =76 then 1 else 0 end) as _82s,
sum(case when ph.holecardvalue_id =77 then 1 else 0 end) as _76s,
sum(case when ph.holecardvalue_id =78 then 1 else 0 end) as _75s,
sum(case when ph.holecardvalue_id =79 then 1 else 0 end) as _74s,
sum(case when ph.holecardvalue_id =80 then 1 else 0 end) as _73s,
sum(case when ph.holecardvalue_id =81 then 1 else 0 end) as _72s,
sum(case when ph.holecardvalue_id =82 then 1 else 0 end) as _65s,
sum(case when ph.holecardvalue_id =83 then 1 else 0 end) as _64s,
sum(case when ph.holecardvalue_id =84 then 1 else 0 end) as _63s,
sum(case when ph.holecardvalue_id =85 then 1 else 0 end) as _62s,
sum(case when ph.holecardvalue_id =86 then 1 else 0 end) as _54s,
sum(case when ph.holecardvalue_id =87 then 1 else 0 end) as _53s,
sum(case when ph.holecardvalue_id =88 then 1 else 0 end) as _52s,
sum(case when ph.holecardvalue_id =89 then 1 else 0 end) as _43s,
sum(case when ph.holecardvalue_id =90 then 1 else 0 end) as _42s,
sum(case when ph.holecardvalue_id =91 then 1 else 0 end) as _32s,
sum(case when ph.holecardvalue_id =92 then 1 else 0 end) as _AKo,
sum(case when ph.holecardvalue_id =93 then 1 else 0 end) as _AQo,
sum(case when ph.holecardvalue_id =94 then 1 else 0 end) as _AJo,
sum(case when ph.holecardvalue_id =95 then 1 else 0 end) as _ATo,
sum(case when ph.holecardvalue_id =96 then 1 else 0 end) as _A9o,
sum(case when ph.holecardvalue_id =97 then 1 else 0 end) as _A8o,
sum(case when ph.holecardvalue_id =98 then 1 else 0 end) as _A7o,
sum(case when ph.holecardvalue_id =99 then 1 else 0 end) as _A6o,
sum(case when ph.holecardvalue_id =100 then 1 else 0 end) as _A5o,
sum(case when ph.holecardvalue_id =101 then 1 else 0 end) as _A4o,
sum(case when ph.holecardvalue_id =102 then 1 else 0 end) as _A3o,
sum(case when ph.holecardvalue_id =103 then 1 else 0 end) as _A2o,
sum(case when ph.holecardvalue_id =104 then 1 else 0 end) as _KQo,
sum(case when ph.holecardvalue_id =105 then 1 else 0 end) as _KJo,
sum(case when ph.holecardvalue_id =106 then 1 else 0 end) as _KTo,
sum(case when ph.holecardvalue_id =107 then 1 else 0 end) as _K9o,
sum(case when ph.holecardvalue_id =108 then 1 else 0 end) as _K8o,
sum(case when ph.holecardvalue_id =109 then 1 else 0 end) as _K7o,
sum(case when ph.holecardvalue_id =110 then 1 else 0 end) as _K6o,
sum(case when ph.holecardvalue_id =111 then 1 else 0 end) as _K5o,
sum(case when ph.holecardvalue_id =112 then 1 else 0 end) as _K4o,
sum(case when ph.holecardvalue_id =113 then 1 else 0 end) as _K3o,
sum(case when ph.holecardvalue_id =114 then 1 else 0 end) as _K2o,
sum(case when ph.holecardvalue_id =115 then 1 else 0 end) as _QJo,
sum(case when ph.holecardvalue_id =116 then 1 else 0 end) as _QTo,
sum(case when ph.holecardvalue_id =117 then 1 else 0 end) as _Q9o,
sum(case when ph.holecardvalue_id =118 then 1 else 0 end) as _Q8o,
sum(case when ph.holecardvalue_id =119 then 1 else 0 end) as _Q7o,
sum(case when ph.holecardvalue_id =120 then 1 else 0 end) as _Q6o,
sum(case when ph.holecardvalue_id =121 then 1 else 0 end) as _Q5o,
sum(case when ph.holecardvalue_id =122 then 1 else 0 end) as _Q4o,
sum(case when ph.holecardvalue_id =123 then 1 else 0 end) as _Q3o,
sum(case when ph.holecardvalue_id =124 then 1 else 0 end) as _Q2o,
sum(case when ph.holecardvalue_id =125 then 1 else 0 end) as _JTo,
sum(case when ph.holecardvalue_id =126 then 1 else 0 end) as _J9o,
sum(case when ph.holecardvalue_id =127 then 1 else 0 end) as _J8o,
sum(case when ph.holecardvalue_id =128 then 1 else 0 end) as _J7o,
sum(case when ph.holecardvalue_id =129 then 1 else 0 end) as _J6o,
sum(case when ph.holecardvalue_id =130 then 1 else 0 end) as _J5o,
sum(case when ph.holecardvalue_id =131 then 1 else 0 end) as _J4o,
sum(case when ph.holecardvalue_id =132 then 1 else 0 end) as _J3o,
sum(case when ph.holecardvalue_id =133 then 1 else 0 end) as _J2o,
sum(case when ph.holecardvalue_id =134 then 1 else 0 end) as _T9o,
sum(case when ph.holecardvalue_id =135 then 1 else 0 end) as _T8o,
sum(case when ph.holecardvalue_id =136 then 1 else 0 end) as _T7o,
sum(case when ph.holecardvalue_id =137 then 1 else 0 end) as _T6o,
sum(case when ph.holecardvalue_id =138 then 1 else 0 end) as _T5o,
sum(case when ph.holecardvalue_id =139 then 1 else 0 end) as _T4o,
sum(case when ph.holecardvalue_id =140 then 1 else 0 end) as _T3o,
sum(case when ph.holecardvalue_id =141 then 1 else 0 end) as _T2o,
sum(case when ph.holecardvalue_id =142 then 1 else 0 end) as _98o,
sum(case when ph.holecardvalue_id =143 then 1 else 0 end) as _97o,
sum(case when ph.holecardvalue_id =144 then 1 else 0 end) as _96o,
sum(case when ph.holecardvalue_id =145 then 1 else 0 end) as _95o,
sum(case when ph.holecardvalue_id =146 then 1 else 0 end) as _94o,
sum(case when ph.holecardvalue_id =147 then 1 else 0 end) as _93o,
sum(case when ph.holecardvalue_id =148 then 1 else 0 end) as _92o,
sum(case when ph.holecardvalue_id =149 then 1 else 0 end) as _87o,
sum(case when ph.holecardvalue_id =150 then 1 else 0 end) as _86o,
sum(case when ph.holecardvalue_id =151 then 1 else 0 end) as _85o,
sum(case when ph.holecardvalue_id =152 then 1 else 0 end) as _84o,
sum(case when ph.holecardvalue_id =153 then 1 else 0 end) as _83o,
sum(case when ph.holecardvalue_id =154 then 1 else 0 end) as _82o,
sum(case when ph.holecardvalue_id =155 then 1 else 0 end) as _76o,
sum(case when ph.holecardvalue_id =156 then 1 else 0 end) as _75o,
sum(case when ph.holecardvalue_id =157 then 1 else 0 end) as _74o,
sum(case when ph.holecardvalue_id =158 then 1 else 0 end) as _73o,
sum(case when ph.holecardvalue_id =159 then 1 else 0 end) as _72o,
sum(case when ph.holecardvalue_id =160 then 1 else 0 end) as _65o,
sum(case when ph.holecardvalue_id =161 then 1 else 0 end) as _64o,
sum(case when ph.holecardvalue_id =162 then 1 else 0 end) as _63o,
sum(case when ph.holecardvalue_id =163 then 1 else 0 end) as _62o,
sum(case when ph.holecardvalue_id =164 then 1 else 0 end) as _54o,
sum(case when ph.holecardvalue_id =165 then 1 else 0 end) as _53o,
sum(case when ph.holecardvalue_id =166 then 1 else 0 end) as _52o,
sum(case when ph.holecardvalue_id =167 then 1 else 0 end) as _43o,
sum(case when ph.holecardvalue_id =168 then 1 else 0 end) as _42o,
sum(case when ph.holecardvalue_id =169 then 1 else 0 end) as _32o

from playerhandscashkeycolumns ph
join pokerhands pkh on ph.pokerhand_id = pkh.pokerhand_id
join players pl on (pl.player_id = ph.player_id)
join playerhandscashmisc phmisc on phmisc.playerhand_id = ph.playerhand_id
join gametypes gt on gt.gametype_id = ph.gametype_id
--left join playerhandsriver river on ph.playerhand_id = river.playerhand_id
where
pl.lastplayeddate > (localtimestamp - interval '6 hours')
and pl.playername <> 'insert hero name'
and (ph.player_id > 0)

Group by ( Pl.playername)

-------------------------------------------------------------

And so on.