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 ]

I would like to rewrite this as the following.

Also *1 does not achieve much and can be dropped.

You can try this as well as CTE, depending on what is the remaining portion of your query.

```
CASE
WHEN BAmt > 0 AND AAmt > 0 AND BAmt <= AAmt THEN SBAmt
WHEN BAmt > 0 AND AAmt > 0 AND BAmt > AAmt THEN SAAmt
ELSE 0
END
Where
FLOOR(SUM(BetAmtPTY) /250 * 1) BAmt
FLOOR(SUM(AmountPTY) / 250) * 1 AAmt
(SUM(BetAmtPTY)/250) * 1 SBAmt
(SUM(AmountPTY)/250) * 1 SAAmt
```

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.