order by month

[mr hankey]

hi,

need some help with displaying months in the correct order on my chart.

here is my recordset:

SELECT MonthPaid AS XLABEL, SUM(Amount) AS YVALUEFROM monthlycommissionWHERE YearPaid= NOW( )GROUP BY MonthPaid

the column is a date column as the WHERE YEAR works fine but i have tried :

ORDER BY MonthPaidorder by MonthPaid
but no joy, also Tried



ORDER BY 'MonthName'ORDER BY 'MonthName'

and

ORDER BY MONTH Paid = 'MonthName'ORDER BY Month Paid = 'MonthName'

and does not work, it displays the data i need but just looks stupid going from June to March then to January instead of Jan feb etc

can you help impliment into the chart correctly?

thanks

[CraigR]

I haven't (yet) played with dynamic webcharts.

However, I was wondering about your month sorting as i don't know how your information is stored.

could you extract the month from the date paid as an integer variable in your sql select statement and sort by this ?

[mr hankey]

hi Craig R,

thanks for the repsonse, how would i extract to a integer variable? could you provide an exmple please?

thanks mate

[CraigR]

as i said, i haven't played with dynamic webcharts, so dont know if this will help.

However, to extract the month number from a date field, in your mySQL select statement, add the following...

MONTH(datefield) AS monthnumber

where datefield is the field in your table and monthnumber is just the alias you want to use for the result.

This will return an integer between 1 and 12

[mr hankey]

just to give you a quick background of setup:

i have 6 tables which all have a date column and i have created a view which stores all these due dates as 'DueDate', however when i order by month in the graph it does not work?

cheers

[mr hankey]

hi craig, i managed to sort it.

must have had dumb moment, i changed the view to include the date column as stand alone as well as the year and month and then orderby the date column which works.

i had create my view with the month and year as an AS statement but forgot to inlcude the date column itself along with them.

works now thank god.

cheers