Yes eventtime is a timestamp field (whatever mssql's version of mysql's 
NOW() is) and the convert( varchar,eventime,110) converts the timestamp to 
a date MM-DD-YYYY.

my problem is that with the count, it counts each card swipe, and i only 
need to count one card swipe from each patron per day.  so if they swipe 
their card multiple times, in my php script when i return the results, it 
only returns one visit for each patron each day even if they have visited 
two or more times.

On Wed, 21 Apr 2004, Brent Baisley wrote:

> I think your problem is that you are using time, when you are not 
> interested in time, just the date. I'm assuming your eventime column is 
> a timestamp field. Your goal is to select the badge id and the date, 
> distinct will then eliminate multiple visits and then you can group by 
> date to get the visitor count. The way I see it, the hardest part is 
> converting the eventime to a date or some unique string to represent 
> the date.
> 
> 
> On Apr 21, 2004, at 1:13 PM, Adam Williams wrote:
> 
> > 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