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
