2. PHPRunner General questions
  3. [SOLVED]  Monthly Graph From Daily Data
This topic is locked
[SOLVED]

 Monthly Graph From Daily Data

 		12/5/2019 14:14:30
PHPRunner General questions
Spider Webster author

I'm trying to get a monthly graph from my table as below.
Date Machine Category Minutes

1/10/19 1 Production 900

1/10/19 2 Production 840

1/10/19 3 Production 915

2/10/19 1 Production 920

2/10/19 2 Production 860

2/10/19 3 Production 905

ect

ect

1/11/19 1 Production 950

ect

4/12/10 1 Production 900
My SQL:-

SELECT

Machine,

Category,

Minutes,

SUM(Minutes) AS STotal,

Date,

ownerid

FROM UtiData

GROUP by Date

Order by Date
This is the graph I am getting which shows every day.


And I would like something like this showing totals for each month:-


I am using PHPRunner 10.3 build 34159
Thank you very glad,.,. <img src='https://asprunner.com/forums/file.php?topicimage=1&fieldname=question&id=26944&image=3&table=forumtopics' class='bbc_emoticon' alt=':D' />
Sergey Kornilov admin 12/5/2019

It can be done. Instead of grouping data by the date field you need to extract month/year value and group data by that.
The syntax will be different for different database but here is what you can try for MySQL:

SELECT

Machine,

Category,

Minutes,

SUM(Minutes) AS STotal,

EXTRACT(YEAR_MONTH FROM Date) as MonthYear,

ownerid

FROM UtiData

GROUP by EXTRACT(YEAR_MONTH FROM Date)

Order by EXTRACT(YEAR_MONTH FROM Date)


More info:

https://xlinesoft.com/dss/thread_view.asp?folder=xlinesoft&category=45&tid=1360419
Spider Webster author 12/6/2019

Simples


Thankyou Sergey works a treat,.,,.
Is there a way I could get Oct 2019 Nov 2019 etc?
M
 		Mark Kramer 12/8/2019



Simples

Thankyou Sergey works a treat,.,,.
Is there a way I could get Oct 2019 Nov 2019 etc?


On your "Date" field (Designer page) click the "View As/Edit As" button then go to "Filter as" tab and use the "Interval list". You can set you paremeters there.
Spider Webster author 12/9/2019

On your "Date" field (Designer page) click the "View As/Edit As" button then go to "Filter as" tab and use the "Interval list". You can set you paremeters there.

[/quote]
Hi Mark, It was the chart date that I wanted formating |Jan 2019|Feb 2019|Mar 2019| instead of 201901 201902 201903 that it has.

I think you explained filtering the chart?
M
 		Mark Kramer 12/11/2019



On your "Date" field (Designer page) click the "View As/Edit As" button then go to "Filter as" tab and use the "Interval list". You can set you paremeters there.


Hi Mark, It was the chart date that I wanted formating |Jan 2019|Feb 2019|Mar 2019| instead of 201901 201902 201903 that it has.

I think you explained filtering the chart?

[/quote]
Yep you are right.. Here is how to do it in mysql SELECT DATE_FORMAT("2017-06-15", "%M %d %Y");