Liat

Hi,

I would like to create a graph that its x-axis is the last 12 months from the current date.
I thought maybe I should group by the date field year and month.
How can I do that? (my date fiedl if `Call Details`.Call_Date ). I don't know how to retrieve Call_Date's month

Or should I convert it to value field? how can I do that?

Thanks a lot,
Liat

Azkaban

You need to use MySQL's date and time functions.

Liat

Yes, I know how to refer to the month and year of the date field mow - thank you.

If I need to create agraph that contains the following:
In x-axis - we have the months of the date field
In y-axis - we have the % of the claims that were higher than 1000 (for example).

Since its a graph, I need to create a column that contains this percent for each month. I tried to use Group By month(date), year(date) - but then it doesn't allow me to do any calculation...

Is there any way to solve this?? I have ben trying for so long time...

Thanks a lot,
Liat

Azkaban

Can you write out the statement you are using and the error you are getting. It will be much easier to help by modifying what's not working than to come up with it from scratch.

Liat

Hi,

You can see below the code (it is working now). I want to put in the x-axis the year, month (for example, 2005, 5). As you can see, I have a column for the month (month1) and a column for the year (year1). How can I do that?

SELECT

month(`Primary_Call_Details`.`Primary_Call_Date`) AS month1,
year(`Primary_Call_Details`.`Primary_Call_Date`) AS year1,
(sum( `Claim_Details`.`Outcome` = 1 AND `Claim_Details`.`Date_Closed` < `Primary_Call_Details`.`Primary_Call_Date`) * 100 / COUNT( * )) AS withdrawnBeforeP


FROM `Claim Details` `Claim_Details`
INNER JOIN `Primary Call Details` `Primary_Call_Details` ON
(`Primary_Call_Details`.`Claim_Reference(Primary)` = `Claim_Details`.`Claim_Reference`)
INNER JOIN `Secondary Call Details` `Secondary_Call_Details` ON
(`Secondary_Call_Details`.`Claims_Reference(Second ary)` = `Primary_Call_Details`.`Claim_Reference(Primary)`)

GROUP BY month(`Primary_Call_Details`.`Primary_Call_Date`), year(`Primary_Call_Details`.`Primary_Call_Date`)


Thanks a lot for you help,
Liat

Azkaban

You said it's working? So what is it still not doing that you want it to do?

I hope you're not trying to use mysql to create a graph directly? As far as I know it doesn't do that. I've assumed all along you understood the data needs to be exported to another tool to create the graph.

Liat

ohh, no. I use Navicat - I created a report that its query is used as an input for the graph - DBChart, isn't it right to do it this way??

Azkaban

I didn't even notice that it was in the Navicat section. Sorry, don't really know Navicat. Hopefully someone else will be able to help.