How about SELECT distinct badge.id, convert( varchar,eventime,110) as date Shows all the badge numbers IN on that date. If the badge number is not there, they didn't check in at ALL that day. > 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
