Re: logic problem

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, 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 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, 110) BETWEEN '$startdate' and > > 'enddate' > > and type = '1' > > group by date > > > > In MySQL you don't need to specify a field for count(). And you should > > have to convert the eventime field twice, you should be able to > > reference the calc field in the group by. > > > > On Apr 21, 2004, at 2:19 PM, Adam Williams wrote: > > > >> 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 the distinct cardnums for each date and then spit it all into an > >> html table. thanks > >> > >> On Wed, 21 Apr 2004, Daniel > >> Clark wrote: > >> > >>> 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 > >