Solved

SQL Query

Posted on 2010-08-17
3
223 Views
Last Modified: 2012-05-10


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 ]
0
Comment
Question by:marvo2010
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 13

Expert Comment

by:sameer2010
ID: 33456079
Hi,

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

Open in new window

0
 

Author Comment

by:marvo2010
ID: 33466924
how can I configure this case statement or use any other construct to select only one and only one of the outcomes fro each player. the two columns I am comparing are BetAmountpty  (wagering) and AmountPTY (deposit)

Thanks
0
 
LVL 13

Accepted Solution

by:
sameer2010 earned 500 total points
ID: 33467382
Do GROUP BY on player_id and use SUM before these CASE statement
0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
tempdb log keep growing 7 45
point in time restore in SQL server 26 46
How come this XML node is not read? 3 31
Can a Trigger trigger a Trigger? 4 22
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question