Re: logic problem

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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


--
Brent Baisley
Systems Architect
Landover Associates, Inc.
Search & Advisory Services for Advanced Technology Environments
p: 212.759.6400/800.759.0577

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php


[Index of Archives]     [PHP Home]     [PHP Users]     [Postgresql Discussion]     [Kernel Newbies]     [Postgresql]     [Yosemite News]

  Powered by Linux