Cant get data in right order!!

[jstar7]

Hi there,

I have been struggling with a SQL statement for a while now.
I cant seem to get it into the right order. Here is the SQL...

select left(supp_registrationdatetime, 11), count(left(supp_registrationdatetime, 11))
from supporter_details sd, supporter_registered_emailadd sr
where sd.supp_number = sr.supp_number
and supp_registrationdatetime > 'Jan 26 2003'
group by left(supp_registrationdatetime, 11)
order by left(supp_registrationdatetime, 11) asc

..it returns a list of dates and a count of how many times they occurred. The only problem is that the order of the record set is in alphabetical order with the Date..example:

Feb 26 2003 2
Feb 27 2003 1
Feb 28 2003 1
Jan 26 2003 3
Jan 27 2003 5
Jan 28 2003 5
Jan 29 2003 2
Jan 30 2003 6
Jan 31 2003 3
Mar 3 2003 2

I want the order to be as follows....

Jan 26 2003 3
Jan 27 2003 5
Jan 28 2003 5
Jan 29 2003 2
Jan 30 2003 6
Jan 31 2003 3
Feb 26 2003 2
Feb 27 2003 1
Feb 28 2003 1
Mar 3 2003 2

can anyone help me? cheers! jstar7

-------------------------------------
...what rhymes with month?

 

[sguslan]

Try converting supp_registrationdatetime to datetime using either Cast or Convert functions.
More ifo at:

http://www.msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ca-co_2f3o.asp

________________________________________________________________________________
If you do not like change, get out of the IT business...

 

[millerk]

Is supp_registrationdatetime a datetime or varchar field?

If it is a varchar, it's not working because varchars can't be treated as a date in your order by clause. In that case, sguslan's suggestions should work.

If the field is a datetime type, it's not working because your left statement implicitly converts it to a varchar, and again, a varchar can't be treated as a date in an order by clause. So in this case, remove the left() from your order by clause.

 

[MeanGreen]

Try this:

select left(supp_registrationdatetime, 11), count(left(supp_registrationdatetime, 11))
from supporter_details sd, supporter_registered_emailadd sr
where sd.supp_number = sr.supp_number
and supp_registrationdatetime > 'Jan 26 2003'
group by left(supp_registrationdatetime, 11),supp_registrationdatetime
order by supp_registrationdatetime asc

Hope this helps.

 

[jstar7]

Yeah that works to get them in date order but the count doesnt work then...

Jan 30 2003 1
Jan 30 2003 1
Jan 31 2003 1
Jan 31 2003 1
Jan 31 2003 1
Feb 1 2003 1
Feb 1 2003 1
Feb 1 2003 1
Feb 2 2003 1
Feb 2 2003 1

should be...

Jan 30 2003 2
Jan 31 2003 3
Feb 1 2003 3
Feb 2 2003 2

any other ideas? Thanks by the way! -------------------------------------
...what rhymes with month?

 

[JamesLean]

OK, so you're using LEFT() to strip off the time so you can group properly, but you still want it ordered by the date. I think you can use a double convert to do this:

select CONVERT(datetime, CONVERT(varchar(12), @dt, 107), 107), count(*) 
from supporter_details sd, supporter_registered_emailadd sr
where sd.supp_number = sr.supp_number
  and supp_registrationdatetime > 'Jan 26 2003'
group by CONVERT(datetime, CONVERT(varchar(12), @dt, 107), 107)
order by CONVERT(datetime, CONVERT(varchar(12), @dt, 107), 107)

--James

 

[JamesLean]

Oops, sorry - substitute @dt for your column name in my query. --James

 

[jstar7]

Thats it!! Excellent mate, cheers!!

-------------------------------------
...what rhymes with month?