Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
wbauk2002
Starting Member
5 Posts |
Posted - 2013-06-25 : 06:26:40
|
Hi Wonder if anyone can help meTyped up this query which works fineSELECT COUNT(DISTINCT PlayerAccounts.AccountID), SUM(TransactionDepositAmount)/100,SUM(TransactionWithdrawAmount)/100FROM PlayerAccounts, TransactionLogs, PlayerBookkeepingWHERE PlayerAccounts.AccountID = TransactionLogs.AccountIDAND PlayerAccounts.AccountID = PlayerBookkeeping.AccountIDAND FirstDepositDateTime BETWEEN '2011-10-01' AND '2011-11-01'AND TransactionRequestDateTime >= (FirstDepositDateTime + interval 30 day) and TransactionRequestDateTime <= (FirstDepositDateTime + interval 60 day) We want to to sum depositors and withdrawals, which its does, and count account id's, which is does, and all works fine But the ID's we actually want to count is the sum of depositors, as we are trying to track who deposited after 30 days and so on, but with this query it counts people who have depositied and withdrawn, so if a person didn't deposit but withdrew they would be counted, so what im trying to say is the first line counts all the players activity, and we just want to count if they depositedThanksCraig |
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-06-25 : 06:32:16
|
do you mean this?SELECT COUNT(DISTINCT CASE WHEN TransactionDepositAmount>0 THEN PlayerAccounts.AccountID END), SUM(TransactionDepositAmount)/100,SUM(TransactionWithdrawAmount)/100FROM PlayerAccounts, TransactionLogs, PlayerBookkeepingWHERE PlayerAccounts.AccountID = TransactionLogs.AccountIDAND PlayerAccounts.AccountID = PlayerBookkeeping.AccountIDAND FirstDepositDateTime BETWEEN '2011-10-01' AND '2011-11-01'AND TransactionRequestDateTime >= (FirstDepositDateTime + interval 30 day) and TransactionRequestDateTime <= (FirstDepositDateTime + interval 60 day) ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
wbauk2002
Starting Member
5 Posts |
Posted - 2013-06-25 : 06:40:45
|
Visakh that worked perfect, thank you very much another question if I may, we have been pasting this query below each other and running it for different periods, and its outputting it in different tabs and I have to go to each tab to get my data, is there a way to output it into one table? im using workbenchquote: Originally posted by visakh16 do you mean this?SELECT COUNT(DISTINCT CASE WHEN TransactionDepositAmount>0 THEN PlayerAccounts.AccountID END), SUM(TransactionDepositAmount)/100,SUM(TransactionWithdrawAmount)/100FROM PlayerAccounts, TransactionLogs, PlayerBookkeepingWHERE PlayerAccounts.AccountID = TransactionLogs.AccountIDAND PlayerAccounts.AccountID = PlayerBookkeeping.AccountIDAND FirstDepositDateTime BETWEEN '2011-10-01' AND '2011-11-01'AND TransactionRequestDateTime >= (FirstDepositDateTime + interval 30 day) and TransactionRequestDateTime <= (FirstDepositDateTime + interval 60 day) ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-06-25 : 07:01:12
|
yep do like thisSELECT DATEADD(mm,DATEDIFF(mm,0,FirstDepositDateTime),0) AS PeriodStart,DATEADD(mm,DATEDIFF(mm,0,FirstDepositDateTime)+1,-1) AS PeriodEnd,COUNT(DISTINCT CASE WHEN TransactionDepositAmount>0 THEN PlayerAccounts.AccountID END), SUM(TransactionDepositAmount)/100,SUM(TransactionWithdrawAmount)/100FROM PlayerAccounts, TransactionLogs, PlayerBookkeepingWHERE PlayerAccounts.AccountID = TransactionLogs.AccountIDAND PlayerAccounts.AccountID = PlayerBookkeeping.AccountIDAND FirstDepositDateTime BETWEEN put your whole interval startdate here AND put your whole interval enddate hereAND TransactionRequestDateTime >= (FirstDepositDateTime + interval 30 day) and TransactionRequestDateTime <= (FirstDepositDateTime + interval 60 day) GROUP BY DATEADD(mm,DATEDIFF(mm,0,FirstDepositDateTime),0),DATEADD(mm,DATEDIFF(mm,0,FirstDepositDateTime)+1,-1) ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
wbauk2002
Starting Member
5 Posts |
Posted - 2013-06-25 : 07:30:42
|
You are a starquote: Originally posted by visakh16 yep do like thisSELECT DATEADD(mm,DATEDIFF(mm,0,FirstDepositDateTime),0) AS PeriodStart,DATEADD(mm,DATEDIFF(mm,0,FirstDepositDateTime)+1,-1) AS PeriodEnd,COUNT(DISTINCT CASE WHEN TransactionDepositAmount>0 THEN PlayerAccounts.AccountID END), SUM(TransactionDepositAmount)/100,SUM(TransactionWithdrawAmount)/100FROM PlayerAccounts, TransactionLogs, PlayerBookkeepingWHERE PlayerAccounts.AccountID = TransactionLogs.AccountIDAND PlayerAccounts.AccountID = PlayerBookkeeping.AccountIDAND FirstDepositDateTime BETWEEN put your whole interval startdate here AND put your whole interval enddate hereAND TransactionRequestDateTime >= (FirstDepositDateTime + interval 30 day) and TransactionRequestDateTime <= (FirstDepositDateTime + interval 60 day) GROUP BY DATEADD(mm,DATEDIFF(mm,0,FirstDepositDateTime),0),DATEADD(mm,DATEDIFF(mm,0,FirstDepositDateTime)+1,-1) ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs
|
|
|
wbauk2002
Starting Member
5 Posts |
Posted - 2013-06-25 : 07:38:08
|
im getting errorError Code: 1582. Incorrect parameter count in the call to native function 'DATEDIFF' 0.031 secquote: Originally posted by wbauk2002 You are a starquote: Originally posted by visakh16 yep do like thisSELECT DATEADD(mm,DATEDIFF(mm,0,FirstDepositDateTime),0) AS PeriodStart,DATEADD(mm,DATEDIFF(mm,0,FirstDepositDateTime)+1,-1) AS PeriodEnd,COUNT(DISTINCT CASE WHEN TransactionDepositAmount>0 THEN PlayerAccounts.AccountID END), SUM(TransactionDepositAmount)/100,SUM(TransactionWithdrawAmount)/100FROM PlayerAccounts, TransactionLogs, PlayerBookkeepingWHERE PlayerAccounts.AccountID = TransactionLogs.AccountIDAND PlayerAccounts.AccountID = PlayerBookkeeping.AccountIDAND FirstDepositDateTime BETWEEN put your whole interval startdate here AND put your whole interval enddate hereAND TransactionRequestDateTime >= (FirstDepositDateTime + interval 30 day) and TransactionRequestDateTime <= (FirstDepositDateTime + interval 60 day) GROUP BY DATEADD(mm,DATEDIFF(mm,0,FirstDepositDateTime),0),DATEADD(mm,DATEDIFF(mm,0,FirstDepositDateTime)+1,-1) ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs
|
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-06-25 : 07:49:09
|
quote: Originally posted by wbauk2002 im getting errorError Code: 1582. Incorrect parameter count in the call to native function 'DATEDIFF' 0.031 sec
I think you are using MySQL, but not the SQL Server....Refer respective MySQL Forums to post relevant queries... Check this link for Date Time functions in MySQLhttp://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html--Chandu |
|
|
wbauk2002
Starting Member
5 Posts |
Posted - 2013-06-25 : 07:53:21
|
Hi Yes it is MySQL, using workbench, we are basically pulling sales data off the server to analyse to give us some sales data Will that query ever work? or could it work with a tweek?ThanksCraig |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-06-25 : 07:56:08
|
quote: Originally posted by wbauk2002 Hi Yes it is MySQL, using workbench, we are basically pulling sales data off the server to analyse to give us some sales data Will that query ever work? or could it work with a tweek?ThanksCraig
You can tweek.... I think you have to tweek towards the DateDiff and DateAdd fucntions.. that is enough--Chandu |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-06-25 : 08:12:55
|
May be these are the equivalent expressions for first and last dates for the month of FirstDepositDateTimeDATEADD(mm,DATEDIFF(mm,0,FirstDepositDateTime),0) == DATE_ADD(date,INTERVAL -DAYOFMONTH(FirstDepositDateTime) DAYS) -- first day of a monthDATEADD(mm,DATEDIFF(mm,0,FirstDepositDateTime)+1,-1) == LAST_DAY(FirstDepositDateTime) -- Last day of a month--Chandu |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-06-25 : 10:22:39
|
quote: Originally posted by wbauk2002 Hi Yes it is MySQL, using workbench, we are basically pulling sales data off the server to analyse to give us some sales data Will that query ever work? or could it work with a tweek?ThanksCraig
You should have specified this in your postThis is MS SQL Server forum and obviously I was assuming you're on SQL Server.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
|
|
|
|