Hi I'm PT3!
10-30-2010, 08:13 AM
I'm trying to make a report that's grouped into rows based on what action a player took. Like Bet, Bet/Call, Check/Raise etc.
I've gotten it to work writing a custom SQL query (see photo) but I could do a lot more with it if I could have it as a custom report in HEM.
http://i52.tinypic.com/6zn5g5.jpg
I've tried writing my own custom report that does this but I can't get it to show up in HEM.
I really don't know much about writing custom reports or SQL queries to be honest, but here's the code I used for each.
SQL Query
SELECT
(case
when ph.flopplayeractiontype_id <0 then '00) None'
when ph.flopplayeractiontype_id between 0 and 0 then '01) Fold'
when ph.flopplayeractiontype_id between 1 and 1 then '02) Check Fold'
when ph.flopplayeractiontype_id between 2 and 11 then '03) Check Call'
when ph.flopplayeractiontype_id between 12 and 21 then '04) Check Raise'
when ph.flopplayeractiontype_id between 12 and 22 then '05) Check Behind'
when ph.flopplayeractiontype_id between 23 and 23 then '06) Bet Fold'
when ph.flopplayeractiontype_id between 24 and 33 then '07) Bet Call'
when ph.flopplayeractiontype_id between 34 and 43 then '08) Bet Raise'
when ph.flopplayeractiontype_id between 44 and 44 then '09) Bet'
when ph.flopplayeractiontype_id between 45 and 66 then '10) Call'
when ph.flopplayeractiontype_id between 67 and 88 then '11) Raise' end),
count(ph.*) as totalhands,sum(ph.netamountwon)/100.0 as netamountwon,
sum(case when ph.maxstreetseen = 4 then 1 else 0 end) as sawshowdown,
sum(case when ph.maxstreetseen >= 1 then 1 else 0 end) as sawflop,
sum(case when ph.netamountwon > 0 and ph.maxstreetseen = 4 then 1 else 0 end) as wonshowdown,
sum(case when ph.netamountwon > 0 and ph.maxstreetseen >= 1 then 1 else 0 end) as wonhandwhensawflop
FROM playerhandscashkeycolumns_hero ph join players pl on (pl.player_id = ph.player_id) join gametypes gt on
gt.gametype_id = ph.gametype_id
WHERE (ph.player_id = 6) and (ph.gametype_id = 1 or ph.gametype_id = 2) and ph.maxstreetseen >= 1
and gt.pokergame = 1
group by (
case when ph.flopplayeractiontype_id <0 then '00) None'
when ph.flopplayeractiontype_id between 0 and 0 then '01) Fold'
when ph.flopplayeractiontype_id between 1 and 1 then '02) Check Fold'
when ph.flopplayeractiontype_id between 2 and 11 then '03) Check Call'
when ph.flopplayeractiontype_id between 12 and 21 then '04) Check Raise'
when ph.flopplayeractiontype_id between 12 and 22 then '05) Check Behind'
when ph.flopplayeractiontype_id between 23 and 23 then '06) Bet Fold'
when ph.flopplayeractiontype_id between 24 and 33 then '07) Bet Call'
when ph.flopplayeractiontype_id between 34 and 43 then '08) Bet Raise'
when ph.flopplayeractiontype_id between 44 and 44 then '09) Bet'
when ph.flopplayeractiontype_id between 45 and 66 then '10) Call'
when ph.flopplayeractiontype_id between 67 and 88 then '11) Raise' end)
ORDER BY 1 ASC
I attached my attempt at writing a HEM report that does this.
Can anyone help me get this to work?
I've gotten it to work writing a custom SQL query (see photo) but I could do a lot more with it if I could have it as a custom report in HEM.
http://i52.tinypic.com/6zn5g5.jpg
I've tried writing my own custom report that does this but I can't get it to show up in HEM.
I really don't know much about writing custom reports or SQL queries to be honest, but here's the code I used for each.
SQL Query
SELECT
(case
when ph.flopplayeractiontype_id <0 then '00) None'
when ph.flopplayeractiontype_id between 0 and 0 then '01) Fold'
when ph.flopplayeractiontype_id between 1 and 1 then '02) Check Fold'
when ph.flopplayeractiontype_id between 2 and 11 then '03) Check Call'
when ph.flopplayeractiontype_id between 12 and 21 then '04) Check Raise'
when ph.flopplayeractiontype_id between 12 and 22 then '05) Check Behind'
when ph.flopplayeractiontype_id between 23 and 23 then '06) Bet Fold'
when ph.flopplayeractiontype_id between 24 and 33 then '07) Bet Call'
when ph.flopplayeractiontype_id between 34 and 43 then '08) Bet Raise'
when ph.flopplayeractiontype_id between 44 and 44 then '09) Bet'
when ph.flopplayeractiontype_id between 45 and 66 then '10) Call'
when ph.flopplayeractiontype_id between 67 and 88 then '11) Raise' end),
count(ph.*) as totalhands,sum(ph.netamountwon)/100.0 as netamountwon,
sum(case when ph.maxstreetseen = 4 then 1 else 0 end) as sawshowdown,
sum(case when ph.maxstreetseen >= 1 then 1 else 0 end) as sawflop,
sum(case when ph.netamountwon > 0 and ph.maxstreetseen = 4 then 1 else 0 end) as wonshowdown,
sum(case when ph.netamountwon > 0 and ph.maxstreetseen >= 1 then 1 else 0 end) as wonhandwhensawflop
FROM playerhandscashkeycolumns_hero ph join players pl on (pl.player_id = ph.player_id) join gametypes gt on
gt.gametype_id = ph.gametype_id
WHERE (ph.player_id = 6) and (ph.gametype_id = 1 or ph.gametype_id = 2) and ph.maxstreetseen >= 1
and gt.pokergame = 1
group by (
case when ph.flopplayeractiontype_id <0 then '00) None'
when ph.flopplayeractiontype_id between 0 and 0 then '01) Fold'
when ph.flopplayeractiontype_id between 1 and 1 then '02) Check Fold'
when ph.flopplayeractiontype_id between 2 and 11 then '03) Check Call'
when ph.flopplayeractiontype_id between 12 and 21 then '04) Check Raise'
when ph.flopplayeractiontype_id between 12 and 22 then '05) Check Behind'
when ph.flopplayeractiontype_id between 23 and 23 then '06) Bet Fold'
when ph.flopplayeractiontype_id between 24 and 33 then '07) Bet Call'
when ph.flopplayeractiontype_id between 34 and 43 then '08) Bet Raise'
when ph.flopplayeractiontype_id between 44 and 44 then '09) Bet'
when ph.flopplayeractiontype_id between 45 and 66 then '10) Call'
when ph.flopplayeractiontype_id between 67 and 88 then '11) Raise' end)
ORDER BY 1 ASC
I attached my attempt at writing a HEM report that does this.
Can anyone help me get this to work?