Solved
SQL Query
Posted on 2010-08-17
I have this requirement to write a SQL query:
Players get one entry for every 250 they deposit and then play. So if they deposit 250 one day and wager 500, they still only get 1 entry that day.
For each entry they must have deposited 250 and wagered 250. If they deposit 500 in one go and then wager 500 then its 2 entries.
So when updating daily, it must take into consideration the previous days activity (any previous promotion days) if they deposit 500 on day 1 and only wager 250, they would get 1 entry on day 1. But if then on day 2 the player wagers another 250, they would get another entry following on from previous activity. So entry count at the end of day 2 would be 2.
There is a table that captures deposits made by each player( AmountPTY), there is a playeraccount with account id for each player, there is a table that captures play for each player (BetAmtPTY).
I have done the query but its running for too long within my stored procedure.
Please feel free to correct what I have done. The important bit is the T-SQL that tracts deposit and play.
Thanks
[CASE
WHEN FLOOR(SUM(BetAmtPTY) /250 * 1) > 0 AND FLOOR(SUM(AmountPTY) / 250) * 1 > 0
AND FLOOR(SUM(BetAmtPTY) /250 * 1) = FLOOR(SUM(AmountPTY) / 250) * 1 THEN (SUM(BetAmtPTY)/250) * 1
WHEN FLOOR(SUM(BetAmtPTY) /250 * 1) > 0 AND FLOOR(SUM(AmountPTY) / 250) * 1 > 0
AND FLOOR(SUM(BetAmtPTY) /250 * 1) < FLOOR(SUM(AmountPTY) / 250) * 1 THEN (SUM(BetAmtPTY)/250) * 1
WHEN FLOOR(SUM(BetAmtPTY) /250 * 1) > 0 AND FLOOR(SUM(AmountPTY) / 250) * 1 > 0
AND FLOOR(SUM(BetAmtPTY) /250 * 1) > FLOOR(SUM(AmountPTY) / 250) * 1 THEN (SUM(AmountPTY)/250) * 1
WHEN FLOOR(SUM(BetAmtPTY) /250 * 1) > 0 AND FLOOR(SUM(AmountPTY) / 250) * 1 < 0 THEN '0'
WHEN FLOOR(SUM(BetAmtPTY) /250 * 1) < 0 AND FLOOR(SUM(AmountPTY) / 250) * 1 > 0 THEN '0'
WHEN FLOOR(SUM(BetAmtPTY) /250 * 1) < 0 AND FLOOR(SUM(AmountPTY) / 250) * 1 < 0 THEN '0'
ELSE 0
END ]