Re-read my original email, I'm using mssql and not mysql. I'm sort of close to having it working with the sql statement i posted earlier and using PHP to figure out if the date has changed or not and to count the cardnum if the date hasn't changed. I'm getting a number that is different then the total number multiple visits by patrons, haven't checked with the registration person if the # I am getting is right or not, because for some of the very early dates when we were testing the equipment, it returns a count of 0 visits by type 1 card users, even though they have a count of 15 multiple entries that day, so something weird is still going on. i think i'm going to have to end up using a multidimensional array and having PHP loop through it or something...
On Wed, 21 Apr 2004, Brent Baisley wrote: > This seems too easy to not be able to do it with SQL. There must be > something we're missing in the query. > Try this: > > select distinct badge.id,convert( varchar,eventime,110) as date,count(*) > from events, badge where > events.cardnum = badge.id and devid = '1' and > convert( varchar, events.eventime, 110) BETWEEN '$startdate' and > 'enddate' > and type = '1' > group by date > > In MySQL you don't need to specify a field for count(). And you should > have to convert the eventime field twice, you should be able to > reference the calc field in the group by. > > On Apr 21, 2004, at 2:19 PM, Adam Williams wrote: > > > Yeah I basically had that with my previous SQL statement, I was > > grouping > > by event.cardnum instead of counting the cardnums by date. I think > > what > > I'm trying to do is beyond the scope of SQL and I'll have to write some > > PHP to take the SQL statement results and feed them into an array and > > count the distinct cardnums for each date and then spit it all into an > > html table. thanks > > > > On Wed, 21 Apr 2004, Daniel > > Clark wrote: > > > >> AND: any count >=1 shows they came in that day. > >> > >> How about: > >> > >> SELECT convert( varchar,eventime,110) as date from events, badge, > >> count(convert( varchar,eventime, 110)) as count > >> WHERE events.cardnum = badge.id and devid = '1' and > >> convert( varchar, events.eventime, 110) BETWEEN '$startdate' and > >> 'enddate' > >> AND type = '1' > >> GROUP BY convert( varchar,events.eventime, 110), badge > >> > >> > >>> I'm using some proprietary software/hardware where when a visitor > >>> swipes > >>> their entry card, it is recorded in a mssql 2000 server. My boss > >>> wants a > >>> count of unique vistors for a range of dates. So, I need to have it > >>> give > >>> a count of unique vistors, meaning that I need to count all vists > >>> for a > >>> day as one visit (because if they go outside to smoke and come back > >>> and > >>> swipe their card again to get in, each one is a separate visit, but > >>> i need > >>> to count all visits by each person as one visit since i just want to > >>> know > >>> if they came at all each day, not how many times they came in). > >>> > >>> This is my SQL statement: > >>> > >>> select distinct count(convert( varchar,eventime, 110)) as count, > >>> convert( varchar,eventime,110) as date from events, badge wher > >>> events.cardnum = badge.id and devid = '1' and > >>> convert( varchar, events.eventime, 110) BETWEEN '$startdate' and > >>> 'enddate' > >>> and type = '1' group by convert( varchar,events.eventime, 110) > >>> > >>> for reference, devid = '1' is the hardware device, where everytime it > >>> triggers, it means someone swiped their card to get in, and type = > >>> '1' > >>> means patron (because we have a type = 2 that is for staff and we > >>> jsut > >>> want to know how many patrons visited) > >>> > >>> When I execute this statement, its returning the result for each > >>> date of > >>> the total number of card swipes (so if a person comes in twice on a > >>> date, > >>> its recording it as 2 swipes, but I just need to know that they came > >>> to > >>> the building at all on this date, so I just need it to register that > >>> there > >>> was a count of atleast one for this card that was swiped) > >>> > >>> any suggestions? thanks > >>> > >>> -- > >>> PHP Database Mailing List (http://www.php.net/) > >>> To unsubscribe, visit: http://www.php.net/unsub.php > >>> > >>> > >> > >> > > > > -- > > PHP Database Mailing List (http://www.php.net/) > > To unsubscribe, visit: http://www.php.net/unsub.php > > > > > -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
