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

Reply via email to