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
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
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(
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
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-.
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.
Yeah I basically had that with my previous SQL statement, I was grouping
by event.cardnum instead of counting the cardnums by date. I think what
I'm trying to do is beyond the scope of SQL and I'll have to write some
PHP to take the SQL statement results and feed them into an array and
count
This seems too easy to not be able to do it with SQL. There must be
something we're missing in the query.
Try this:
select distinct badge.id,convert( varchar,eventime,110) as date,count(*)
from events, badge where
events.cardnum = badge.id and devid = '1' and
convert( varchar, events.eventime,
Re-read my original email, I'm using mssql and not mysql. I'm sort
of close to having it working with the sql statement i posted earlier and
using PHP to figure out if the date has changed or not and to count the
cardnum if the date hasn't changed. I'm getting a number that is
different then
How about
SELECT distinct badge.id, convert( varchar,eventime,110) as date
Shows all the badge numbers IN on that date. If the badge number is not
there, they didn't check in at ALL that day.
This seems too easy to not be able to do it with SQL. There must be
something we're missing in the
Still doesn't work the way I want it. My boss is taking a look at it, she
knows SQL really well, I was trying to do this without her assistance, but
its just harder then I was expecting. thanks for the help tho
On Wed, 21 Apr 2004, Daniel Clark wrote:
How about
SELECT distinct badge.id,
10 matches
Mail list logo
