SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Count Condition, Is it possible?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

wbauk2002
Starting Member

5 Posts

Posted - 06/25/2013 :  06:26:40  Show Profile  Reply with Quote
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

India
52325 Posts

Posted - 06/25/2013 :  06:32:16  Show Profile  Reply with Quote
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 - 06/25/2013 :  06:40:45  Show Profile  Reply with Quote
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

India
52325 Posts

Posted - 06/25/2013 :  07:01:12  Show Profile  Reply with Quote
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 - 06/25/2013 :  07:30:42  Show Profile  Reply with Quote
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 - 06/25/2013 :  07:38:08  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
2223 Posts

Posted - 06/25/2013 :  07:49:09  Show Profile  Reply with Quote
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 - 06/25/2013 :  07:53:21  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
2223 Posts

Posted - 06/25/2013 :  07:56:08  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
2223 Posts

Posted - 06/25/2013 :  08:12:55  Show Profile  Reply with Quote
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

India
52325 Posts

Posted - 06/25/2013 :  10:22:39  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000