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.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Count Condition, Is it possible?

Author  Topic 

wbauk2002
Starting Member

5 Posts

Posted - 2013-06-25 : 06:26:40
Hi

Wonder if anyone can help me

Typed up this query which works fine

SELECT COUNT(DISTINCT PlayerAccounts.AccountID),
SUM(TransactionDepositAmount)/100,
SUM(TransactionWithdrawAmount)/100
FROM PlayerAccounts, TransactionLogs, PlayerBookkeeping
WHERE PlayerAccounts.AccountID = TransactionLogs.AccountID
AND PlayerAccounts.AccountID = PlayerBookkeeping.AccountID
AND 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 deposited

Thanks
Craig

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)/100
FROM PlayerAccounts, TransactionLogs, PlayerBookkeeping
WHERE PlayerAccounts.AccountID = TransactionLogs.AccountID
AND PlayerAccounts.AccountID = PlayerBookkeeping.AccountID
AND FirstDepositDateTime BETWEEN '2011-10-01' AND '2011-11-01'
AND TransactionRequestDateTime >= (FirstDepositDateTime + interval 30 day) and TransactionRequestDateTime <= (FirstDepositDateTime + interval 60 day)


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

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 workbench



quote:
Originally posted by visakh16

do you mean this?

SELECT COUNT(DISTINCT CASE WHEN TransactionDepositAmount>0 THEN PlayerAccounts.AccountID END),
SUM(TransactionDepositAmount)/100,
SUM(TransactionWithdrawAmount)/100
FROM PlayerAccounts, TransactionLogs, PlayerBookkeeping
WHERE PlayerAccounts.AccountID = TransactionLogs.AccountID
AND PlayerAccounts.AccountID = PlayerBookkeeping.AccountID
AND FirstDepositDateTime BETWEEN '2011-10-01' AND '2011-11-01'
AND TransactionRequestDateTime >= (FirstDepositDateTime + interval 30 day) and TransactionRequestDateTime <= (FirstDepositDateTime + interval 60 day)


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs


Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-25 : 07:01:12
yep do like this


SELECT 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)/100
FROM PlayerAccounts, TransactionLogs, PlayerBookkeeping
WHERE PlayerAccounts.AccountID = TransactionLogs.AccountID
AND PlayerAccounts.AccountID = PlayerBookkeeping.AccountID
AND FirstDepositDateTime BETWEEN put your whole interval startdate here AND put your whole interval enddate here
AND 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 MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

wbauk2002
Starting Member

5 Posts

Posted - 2013-06-25 : 07:30:42
You are a star


quote:
Originally posted by visakh16

yep do like this


SELECT 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)/100
FROM PlayerAccounts, TransactionLogs, PlayerBookkeeping
WHERE PlayerAccounts.AccountID = TransactionLogs.AccountID
AND PlayerAccounts.AccountID = PlayerBookkeeping.AccountID
AND FirstDepositDateTime BETWEEN put your whole interval startdate here AND put your whole interval enddate here
AND 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 MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs


Go to Top of Page

wbauk2002
Starting Member

5 Posts

Posted - 2013-06-25 : 07:38:08
im getting error

Error Code: 1582. Incorrect parameter count in the call to native function 'DATEDIFF' 0.031 sec



quote:
Originally posted by wbauk2002

You are a star


quote:
Originally posted by visakh16

yep do like this


SELECT 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)/100
FROM PlayerAccounts, TransactionLogs, PlayerBookkeeping
WHERE PlayerAccounts.AccountID = TransactionLogs.AccountID
AND PlayerAccounts.AccountID = PlayerBookkeeping.AccountID
AND FirstDepositDateTime BETWEEN put your whole interval startdate here AND put your whole interval enddate here
AND 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 MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs




Go to Top of Page

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-06-25 : 07:49:09
quote:
Originally posted by wbauk2002

im getting error
Error 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 MySQL
http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html

--
Chandu
Go to Top of Page

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?

Thanks
Craig
Go to Top of Page

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?

Thanks
Craig


You can tweek....
I think you have to tweek towards the DateDiff and DateAdd fucntions.. that is enough

--
Chandu
Go to Top of Page

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 FirstDepositDateTime

DATEADD(mm,DATEDIFF(mm,0,FirstDepositDateTime),0) == DATE_ADD(date,INTERVAL -DAYOFMONTH(FirstDepositDateTime) DAYS) -- first day of a month
DATEADD(mm,DATEDIFF(mm,0,FirstDepositDateTime)+1,-1) == LAST_DAY(FirstDepositDateTime) -- Last day of a month

--
Chandu
Go to Top of Page

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?

Thanks
Craig



You should have specified this in your post
This is MS SQL Server forum and obviously I was assuming you're on SQL Server.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
   

- Advertisement -