alexoxol
04-02-2010, 11:16 AM
Hello.
I created SQL query from HM DB, it shows our coinflip's luck in tournaments. But I've never created custom reports in HM. Can someone create report by my SQL?
SELECT date(ph.handtimestamp) as date,
ph.numberofplayers as players,
cast(hc.holecardstring as char(3)) as cards,
gt.smallblind/100+gt.bigblind/100+(gt.ante/100*ph.numberofplayers) as M,
CASE
WHEN gt.smallblind/100+gt.bigblind/100+(gt.ante/100*ph.numberofplayers) between 0 and 150 THEN 'M = (0-150)'
WHEN gt.smallblind/100+gt.bigblind/100+(gt.ante/100*ph.numberofplayers) between 151 and 700 THEN 'M = (151-700)'
WHEN gt.smallblind/100+gt.bigblind/100+(gt.ante/100*ph.numberofplayers) between 701 and 2000 THEN 'M = (701-2000)'
WHEN gt.smallblind/100+gt.bigblind/100+(gt.ante/100*ph.numberofplayers) between 2001 and 5000 THEN 'M = (2001-5000)'
WHEN gt.smallblind/100+gt.bigblind/100+(gt.ante/100*ph.numberofplayers) >= 5001 THEN 'M = (5001+)'
ELSE NULL
END as Tourn_level,
allin.equitypct/10 as equity,
CASE
WHEN (((ph.netamountwon/100.00)/(gt.smallblind/100.00+gt.bigblind/100.00+(gt.ante/100.00*ph.numberofplayers)))-((allin.sklanskybucks/100.00)/(gt.smallblind/100.00+gt.bigblind/100.00+(gt.ante/100.00*ph.numberofplayers)))) > 0 THEN 1
ELSE 0
END as Won,
CASE
WHEN (((ph.netamountwon/100.00)/(gt.smallblind/100.00+gt.bigblind/100.00+(gt.ante/100.00*ph.numberofplayers)))-((allin.sklanskybucks/100.00)/(gt.smallblind/100.00+gt.bigblind/100.00+(gt.ante/100.00*ph.numberofplayers)))) < 0 THEN 1
ELSE 0
END as Lost
FROM playerhandstourneykeycolumns_hero ph left join allinsituations_hero allin on ph.playerhand_id = allin.playerhand_id left join holecards hc
on ph.holecardvalue_id = hc.holecard_id left join gametypes gt on ph.gametype_id = gt.gametype_id left join pokerhands_hero pokh on ph.pokerhand_id = pokh.pokerhand_id
where allin.equitypct/10 between 43 and 57 and ph.streetwentallin = 1 and pokh.numberofplayerssawflop = 2 and (allin.equitypct/10) > -100
and (((ph.netamountwon/100.00)/(gt.smallblind/100.00+gt.bigblind/100.00+(gt.ante/100.00*ph.numberofplayers)))-((allin.sklanskybucks/100.00)/(gt.smallblind/100.00+gt.bigblind/100.00+(gt.ante/100.00*ph.numberofplayers)))) not between -0.5 and 0.5
order by ph.handtimestamp
Columns:
date, players at the table, your cards, M digit, M range, Won (1 if won coinflip, 0 otherwise), Lost (1 if lost coinflip, 0 otherwise). WHERE clause includes just situations where you went allin on preflop with only one opponent, had from 43 to 57 percent on win and result is not like sklanskybucks (because it is split pot situations).
Maybe someone need this script.
I created SQL query from HM DB, it shows our coinflip's luck in tournaments. But I've never created custom reports in HM. Can someone create report by my SQL?
SELECT date(ph.handtimestamp) as date,
ph.numberofplayers as players,
cast(hc.holecardstring as char(3)) as cards,
gt.smallblind/100+gt.bigblind/100+(gt.ante/100*ph.numberofplayers) as M,
CASE
WHEN gt.smallblind/100+gt.bigblind/100+(gt.ante/100*ph.numberofplayers) between 0 and 150 THEN 'M = (0-150)'
WHEN gt.smallblind/100+gt.bigblind/100+(gt.ante/100*ph.numberofplayers) between 151 and 700 THEN 'M = (151-700)'
WHEN gt.smallblind/100+gt.bigblind/100+(gt.ante/100*ph.numberofplayers) between 701 and 2000 THEN 'M = (701-2000)'
WHEN gt.smallblind/100+gt.bigblind/100+(gt.ante/100*ph.numberofplayers) between 2001 and 5000 THEN 'M = (2001-5000)'
WHEN gt.smallblind/100+gt.bigblind/100+(gt.ante/100*ph.numberofplayers) >= 5001 THEN 'M = (5001+)'
ELSE NULL
END as Tourn_level,
allin.equitypct/10 as equity,
CASE
WHEN (((ph.netamountwon/100.00)/(gt.smallblind/100.00+gt.bigblind/100.00+(gt.ante/100.00*ph.numberofplayers)))-((allin.sklanskybucks/100.00)/(gt.smallblind/100.00+gt.bigblind/100.00+(gt.ante/100.00*ph.numberofplayers)))) > 0 THEN 1
ELSE 0
END as Won,
CASE
WHEN (((ph.netamountwon/100.00)/(gt.smallblind/100.00+gt.bigblind/100.00+(gt.ante/100.00*ph.numberofplayers)))-((allin.sklanskybucks/100.00)/(gt.smallblind/100.00+gt.bigblind/100.00+(gt.ante/100.00*ph.numberofplayers)))) < 0 THEN 1
ELSE 0
END as Lost
FROM playerhandstourneykeycolumns_hero ph left join allinsituations_hero allin on ph.playerhand_id = allin.playerhand_id left join holecards hc
on ph.holecardvalue_id = hc.holecard_id left join gametypes gt on ph.gametype_id = gt.gametype_id left join pokerhands_hero pokh on ph.pokerhand_id = pokh.pokerhand_id
where allin.equitypct/10 between 43 and 57 and ph.streetwentallin = 1 and pokh.numberofplayerssawflop = 2 and (allin.equitypct/10) > -100
and (((ph.netamountwon/100.00)/(gt.smallblind/100.00+gt.bigblind/100.00+(gt.ante/100.00*ph.numberofplayers)))-((allin.sklanskybucks/100.00)/(gt.smallblind/100.00+gt.bigblind/100.00+(gt.ante/100.00*ph.numberofplayers)))) not between -0.5 and 0.5
order by ph.handtimestamp
Columns:
date, players at the table, your cards, M digit, M range, Won (1 if won coinflip, 0 otherwise), Lost (1 if lost coinflip, 0 otherwise). WHERE clause includes just situations where you went allin on preflop with only one opponent, had from 43 to 57 percent on win and result is not like sklanskybucks (because it is split pot situations).
Maybe someone need this script.