query help

[silverpearl]

Greetings Everyone,

Below is my query that i'm trying to get it to work.

I want the query to select only the unique rows. In my example, the unique feild is the date field. The result doesnt fetch the unique fields. It simply spits out all fields! what am i doing wrong?

Dim strSQL2 as String = "select DISTINCT count(*) as rec, convert(char(6),dateEntered,13) as dateEntered from EmailTracker where convert(char(12),dateEntered,13) >= '"& stdate &"' AND convert(char(12),dateEntered,13) <= '"& endate &"' group by convert(char(6),dateEntered,13)"


The fields are used in a chart control. I just want to track unique clicks and draw up a chart on the dates. any help is very much appreciatd. Thanks.

[silverpearl]

ok that problem was solved by creating a view and then selecting distinct rows from that view.

Now, the count function wont work rite.

I added another field, username, to the query. now when i draw the chart, because of the group by clause, it takes each count with a matched username as a new count and draws separate lines for them. what they really should do is, they should combine all the matched counts in to one line.

For ex, if there are three unique clicks from three users

bob 11/01/05
george 11/01/05
adam 11/01/05

I want the query to count them all as unique clicks and draw one single line on the chart marking the count. Now it paints a line for each one of the records. It was doing right before i changed the query to include username in. I assume this is due to the group by clause which groups both username and date field. is there anyways to get around this problem?

below is my modified query.

Dim strSQL2 as String = "select DISTINCT count(*) as rec, convert(char(6),dateEntered,13) as dateEntered, ekey from trackerview where dateEntered >= '"& stdate &"' AND dateEntered <= '"& endate &"' group by convert(char(6),dateEntered,13), ekey"

i hope i havent confused no one. pls help if u can asap... much appreciated!

[r937]

yes, you have to remove ekay from your query

select count(*) as rec
     , convert(char(6),dateEntered,13) as dateEntered
  from trackerview 
 where dateEntered >= '"& stdate &"' 
   AND dateEntered <= '"& endate &"' 
group 
    by convert(char(6),dateEntered,13)

FYI, style 13 used with char(6) will give you totals for dd mon without the year -- is that what you want?

[silverpearl]

yep, i wanted that style so that on the x axsis i can display just the date and month.

i solved the problem. thanks for ur help anyways Rudy.