Re: assignment of data into a 2 dimensional array

On Mon, 3 May 2004, Mattias Thorslund wrote: > On DISTINCT: > > Did you make sure your SELECT list contained only the columns that > contain date and card number? > > Does your date string include hours, minutes, seconds? In that case, you > need to clean those off. You could you do something like: > > DATE_FORMAT(date,'%Y %m %d') AS formatted_date > > or > > LEFT(date, 10) AS formatted_date > > > > /Mattias > > Adam Williams wrote: > > >I've tried doing this in SQL, and it does not do discrete counts properly. > >DISTINCT still returns each return visit by a person on the same day as a > >new visit. and I just need to have it put each discrete visit into the > >loop so i can count up the discrete visits > > > >On Mon, 3 May 2004, Mattias Thorslund wrote: > > > > > > > >>For the rest of the list I should mention that you say a "visit" is the > >>same as one or more occurrences of one card number in a particular day. > >> > >>That could be easier done in the SQL statement... After all, this is > >>the power of SQL :-) > >> > >>Try something along the lines of > >> > >>SELECT > >> date, > >> cardnum > >>FROM > >> your_table > >>WHERE > >> [from-to conditions] > >>GROUP BY > >> date, cardnum > >> > >>or > >> > >>SELECT DISTINCT > >> date, > >> cardnum > >>FROM > >> your_table > >>WHERE > >> [from-to conditions] > >> > >>Note that if you needed it, you could also get the number of card swipes > >>in the former case, by adding COUNT(cardnum) as NumSwipes to the select > >>clause. > >> > >>/Mattias > >> > >> > >> > >> > > > > > > > > Doesn't work, mssql doesn't have a DATE_FORMAT function. you have to use mssql's convert() function.