November 27, 2002 at 6:59 pm
The Below Select statement is querying an audit table.
The event_date column is datatime
data is stored in the event_date column like this 2002-08-19 13:17:15.427
SQL 2000
-- Query gets number of distinct users logging in Per day over past 200 days --
SELECT count(distinct(performed_by_user_id)) as 'No of distinct Users',
CONVERT (DATETIME,
CAST(event_date AS char(12)))
as 'Date'
FROM tablename
WHERE condition
The output is the following
num users date
12002-08-19 00:00:00.000
12002-10-10 00:00:00.000
22002-10-11 00:00:00.000
I would like the output to be in the form
1Aug 19 2002
1 Oct 10 2002
2 Oct 11 2002
But what i get is alphbatically ordered, ie the october data gets list further down the results
SELECT count(distinct(performed_by_user_id)) as 'No of distinct Users',CAST(event_date AS char(12)) as 'Date'
FROM table
WHERE condition
GROUP BY CAST(event_date AS char(12))
1Aug 19 2002
4Nov 1 2002
3Nov 4 2002
Any ideas ???
November 28, 2002 at 4:07 am
just add an orderby on the datecolumn something like this
select count(distinct(performed_by_userid)) as no_users,
convert(char(12),datecol,106) as auditdate
from tablename
group by convert(char(12),datecol,106)
order by datecol
November 28, 2002 at 4:11 am
Change GROUP BY clause to GROUP BY event_date
Far away is close at hand in the images of elsewhere.
Anon.
November 28, 2002 at 6:42 pm
i had tried that already but the problem i have problems as in message below
Column name 'tablename.event_date' is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP BY clause.
And if i include event_date in the select list or GROUP BY clause it changes my results. its not returning what i need anymore.
Any more ideas ?
November 28, 2002 at 9:52 pm
Add another event_date column to your output that is not converted, sort by the unconverted event_date and group by both (with the same value, it wouldn't change your grouping).
Or you could do it in a subquery, and do the convert in the outermost layer (group and order in the subquery).
Either would work, just depends on whether you can have the extra column or not. It would be better to do it the first way, but if you just can't have the extra column, go with the second.
November 29, 2002 at 1:32 am
Another, maybe simpler solution, is to put the event_date in your select in an Aggregate function (e.g. min). This will not change results, since you're grouping on the column anyway.
SELECT
count(distinct(performed_by_user_id)) as 'No of distinct Users',
CAST(min(event_date) AS char(12)) as 'Date'
FROM table
WHERE condition
GROUP BY CAST(event_date AS char(12))
November 29, 2002 at 2:11 am
The only way to be sure about the sort order of the resultset is to put an ORDER BY clause in your query. Group by might give a correct result, but is not guaranteed for ordering sequence.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
November 29, 2002 at 3:02 am
Yep, your right. Just saw another error in my statement, so here I go again...
I use the Convert function, taking a type to format as yymmdd. You can choose any other format as long as it gives you the possibility to order the way you want it.
SELECT
count(distinct(performed_by_user_id)) as 'No of distinct Users',
CAST(min(event_date) AS char(12)) as 'Date'
FROM table
WHERE condition
GROUP BY CONVERT(char(12), event_date, 7)
ORDER BY CONVERT(char(12), event_date, 7)
November 29, 2002 at 3:21 am
How about
SELECT count(distinct(performed_by_user_id)) as 'No of distinct Users',
CONVERT(varchar(11),event_date) as 'Date'
FROM tablename
WHERE condition
GROUP BY CONVERT(varchar(11),event_date),CONVERT(varchar(10),event_date,120)
ORDER BY CONVERT(varchar(10),event_date,120)
Far away is close at hand in the images of elsewhere.
Anon.
December 1, 2002 at 5:07 pm
Cheers Guys
Thanks for your help
David's Query works perfectly
SELECT count(distinct(performed_by_user_id)) as 'No of distinct Users',
CONVERT(varchar(11),event_date) as 'Date'
FROM tablename
WHERE condition
GROUP BY CONVERT(varchar(11),event_date),CONVERT(varchar(10),event_date,120)
ORDER BY CONVERT(varchar(10),event_date,120)
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply