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.
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.
select client.userid, count(*)
from client inner join games on client.clientid = games.cliented
group by client.userid
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!
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.datep layed) < '45'
please let me know...
the other part of the query seems good.
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.datep
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 >= 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.
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......
DateAdd(CURRENT_TIMESTAMP,
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.
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
yes boss this worked.. thanku
ASKER
very happy with the solution....
You are welcome.
Open in new window