SQL question

[Biglop]

I'm trying to make a query for a cfchart... here's my code

SELECT convert(varchar(10),Date,101) AS enroll_date, COUNT(user_type) AS user_count
FROM IBS_apps
WHERE user_type = 'new user'
GROUP BY convert(varchar(10),Date,101)
ORDER BY convert(varchar(10),Date,101)

DB is SQL Server. What I'm trying to do is get a count of enrollments by date(some days might have more than one). I converted the date field to get a short date format (12/06/2004). This code works fine, except the order by doesn't work with data from more than one year. It's only sorting by day/month, not year. If I order by "date" I get an error because "date" is not in group by or aggregate clause...

Any ideas?

 

[r937]

you can either choose a format that sorts nicely, like yyyy-mm-dd

   SELECT convert(varchar(10),Date,120) 
             AS enroll_date
        , COUNT(user_type) AS user_count 
     FROM IBS_apps 
    WHERE user_type = 'new user'
 GROUP BY convert(varchar(10),Date,120)
 ORDER BY convert(varchar(10),Date,120)

or else include the Date in the query --

   SELECT convert(varchar(10),Date,101) 
             AS enroll_date
        , Date as sort_date
        , COUNT(user_type) AS user_count 
     FROM IBS_apps 
    WHERE user_type = 'new user'
 GROUP BY convert(varchar(10),Date,101)
        , Date
 ORDER BY Date

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (course starts January 9 2005)

 

[Biglop]

Thanks. I used the first option and it sorts fine. The second option (include date in query) doesn't work because it no longer groups on the converted date. Just curious...why doesn't mm-dd-yyyy(110) sort correctly??

 

[r937]

mm-dd-yyyy doesn't sort into calendrical sequence because the yyyy is in low-order position

01-01-1999
01-01-2000
01-01-2001
01-02-1999
01-02-2000
01-02-2001
01-03-1999
01-03-2000
01-03-2001
...
12-31-1999
12-31-2000
12-31-2001

whenever you use CONVERT() on a datetime column, the result of the function is a string



rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (course starts January 9 2005)