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 |
|
jake
Starting Member
1 Post |
 Posted - 2002-02-15 : 00:31:58
|
| Judging by the complexity of the other postings, this one is going to seem like an overly stupid question!!I have a mySQL table (members) that contains a datetime stamp field (lastlogin) holding each members previous login datetime and another datetime stamp field (joinedon) which holds each members joining date and time.I have got minor success (+ some problems) in counting the number of new members in the members table that have joined since my last login datetime.To find my last login datetime I use...set MyLastLogin = ("SELECT lastlogin FROM members WHERE memberno = 1") (ie finding me!!)To show how many new members have joined since my last login I use...set NewMembers = ("SELECT COUNT(*) as NewJoiners from members WHERE joinedon > '" & MyLastLogin("lastlogin") & "'")Then to then list all new members who have joined since my last login I use...set NewMembersList = ("SELECT * FROM members WHERE joinedon > '" & MyLastLogin("lastlogin")& "'")Can anyone offer a more professional method. My NewMembers "count(*)" field tells me I have say 10 new members, but when I use the NewMemberList querie, it does not show me all 10 members. It seems to have a problem with the time comparrison.I can convert the datetime to datevalue's etc and get the mnumbers to match, but then I lose the time-part of the comparrison which loses the whole point of finding the new members who joined a few hours after I last logged in ?Can you help please ?? |
|
|
Merkin
Funky Drop Bear Fearing SQL Dude!
4970 Posts |
Posted - 2002-02-15 : 00:48:25
|
| It looks like you are losing something in the date comparison. SQL server has some good functions for comparing dates, like DateDiff. But you say you are using mySQL ?I am not sure what mySQL offers (other than no referential integrity YUK, no stored procs except in perl YUK and no transactions) so you might be better off looking in the mySQL documentation for ideas on date comparison.If you are using SQL Server however, there are some good ways we can help you out. This is basically an SQL Server site though.Damian |
 |
|
|
|
|
|
SQL Query - This is a bit too simple