Link to home
Start Free TrialLog in
Avatar of sakvk
sakvk

asked on

sql query.

i have 2 tables...  client and games.....

in client i have many columns, of which 2 are client id and user id

in games i have many columns , of which one is client id

now i need to get to know how many games are generated by the user within 30 days.......... from that userid.



Avatar of Kevin Cross
Kevin Cross
Flag of United States of America image

Try something like this substituting my made up column names with real ones.
SELECT Count(g.*) As NumGamesPlayed
FROM client c INNER JOIN games g
ON c.clientID = g.clientID
WHERE c.userID = 'u1234'
AND g.datePlayed >= DateAdd(dd, -30, DateDiff(dd, 0, getdate()))

Open in new window

select client.userid, count(*)
from client inner join games on client.clientid = games.cliented
group by client.userid
Ignore my suggestion... it was incomplete, and mwvisa1's looks good!
Avatar of sakvk
sakvk

ASKER

hi mwvisa1,

when u say
g.datePlayed >= DateAdd(dd, -30, DateDiff(dd, 0, getdate())), whts dd? in that... why do we need to do DateAdd and then Datediff....

Can I just do a DateDiff(timestamp,g.dateplayed) < '45'
 
please let me know...

the other part of the query seems good.
>>now i need to get to know how many games are generated by the user within 30 days.......... from that userid.

g.datePlayed >= DateAdd(dd, -30, DateDiff(dd, 0, getdate()))

dd = day interval
-30 = goes back 30 days from getdate() (i.e. today)
DateDiff = used to ensure that we go back to 30 days ago at midnight, so that all games for that day are included regardless of time.  If you just do DateAdd it will add x number of days with current timestamp which in a >= statement will exclude records for that day with timestamps earlier than current time the query is run.  

Hopefully that makes sense.
g.datePlayed ==> column name in your games or whichever table is used to determine when a game was generated/played/etc.  This is field you use to determine what is 30 days ago, so whatever the name is change that here.  
Avatar of sakvk

ASKER

so can i say dd is CURTIMESTAMP()

DateAdd(CURRENT_TIMESTAMP, -30, DateDiff(CURTIMESTAMP(), 0, getdate()))

as all the time is in date and time format
i think.. its DATE_ADD or ADDDATE......

if i am abl to get this , then the whole query works......
I am sorry, that was bad of me too assume.  What SQL platform are you on?  CURRENT_TIMESTAMP() is MySQL I believe, am I correct on that?  

If so, please use this as reference for functions I am about to use:
http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html
(specifically) http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html#function_date-add

DATE_ADD(NOW(), INTERVAL -30 DAY)

NOW() - INTERVAL 30 DAY

For SQL server, the DateAdd(dd, -30, DateDiff(dd, 0, getdate())) was already implemented code, no need to change anything unless you want to adjust the 30 to go more/less days in past.
ASKER CERTIFIED SOLUTION
Avatar of Kevin Cross
Kevin Cross
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of sakvk

ASKER

yes boss this worked.. thanku
Avatar of sakvk

ASKER

very happy with the solution....
You are welcome.