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
