ARI Soft - [SOLVED] Second dataset bar charts

Home

News

Forum

About Us

Demos

Products

F.A.Q.

Featured Demos

ARI Smart Content demo

ARI Quiz demo

WordPress Quiz plugin

WordPress Lightbox plugin

e-mail: info@ari-soft.com

Shopping Cart

You currently have 0 items in your cart.

Show Cart

Recent Events

23/11/2024 Black Friday 2024

BIG SALE, 30% discount for all our extensions. Use BF24 coupon code. Hurry up the discount is valid till 3 December.

more...
31/12/2023 New Year SALE

We are glad to announce New Year SALE. 25% discount for all our extensions. Use NY24 coupon code. Hurry up the discount is valid till 7 January.

more...

2Checkout.com, Inc. is an authorized retailer of goods and services provided by ARI Soft.

Welcome, Guest
Please Login or Register. Lost Password?

ARI Soft Forum

Joomla components

ARI Data Tables

[SOLVED] Second dataset bar charts

(1 viewing) (1) Guest

Page: 1

TOPIC: [SOLVED] Second dataset bar charts

Forum Tools

#58459

[SOLVED] Second dataset bar charts 8 Years, 2 Months ago Karma: 0

I have a query that counts the people hired, and it displays correctly as a single bar, and a second query that displays the people who left in the second query. I have a second table that only has a calendar (datefield for each day to 2050) How do I represent both of these in a single bar chart that displays months that had zero values?

People hired (first dataset)

Code:

SELECT  

CONCAT(year(calendar.datefield),'-', LPAD(month(calendar.datefield), 2, '0')) AS Periodo,

  IFNULL(count(con_inizio_contratto),0) AS `<font color="blue">Assunzioni</font>`,

FROM datipersonali RIGHT JOIN calendar ON (DATE(datipersonali.con_inizio_contratto) = calendar.datefield)

WHERE YEAR(calendar.datefield) = {$REQUEST:chartYearStart|empty:'2011'}

GROUP BY year(calendar.datefield), month(calendar.datefield), Periodo

People who left (second dataset)

Code:

SELECT  

CONCAT(year(calendar.datefield),'-', LPAD(month(calendar.datefield), 2, '0')) AS Periodo,

  IFNULL(count(con_licenziamento_data),0) AS `<font color="red">Licenziamenti</font>`,

FROM datipersonali RIGHT JOIN calendar ON (DATE(datipersonali.con_inizio_contratto) = calendar.datefield)

WHERE YEAR(calendar.datefield) = {$REQUEST:chartYearStart|empty:'2011'}

GROUP BY year(calendar.datefield), month(calendar.datefield), Periodo

vladimir84

Last Edit: 2016/09/09 14:06 By vladimir84.

The administrator has disabled public write access.

#58460

Re: Second dataset bar charts 8 Years, 2 Months ago	Karma: 0
any news?

vladimir84

The administrator has disabled public write access.

#58462

Re: Second dataset bar charts 8 Years, 2 Months ago Karma: 0

I managed to find a solution, even though I am sure there is a less redundant way to do this. I created separate views for each query, where the time period and calndar.datefield would be joined. Then I created a query that takes those 2 views and sums up the results like this:

Code:

SELECT

      CONCAT(q1.d,'-',LPAD(q1.m,2,0)) AS Periodo,

  q1.c AS `<font color="blue">Assunzioni</font>`,

  q1.l AS `<font color="red">Licenziamenti</font>`

  FROM

   (SELECT

       YEAR(calendar.datefield) AS d,

       month(calendar.datefield) AS m,

       SUM(statistica_assunti_n.assunzioni) AS c,

	   SUM(statistica_licenziati_n.licenziamenti) AS l

    FROM  calendar

	INNER JOIN statistica_assunti_n

    ON calendar.datefield = statistica_assunti_n.data_assunzioni

  INNER JOIN statistica_licenziati_n

    ON calendar.datefield = statistica_licenziati_n.data_licenziamenti

WHERE YEAR(datefield) ={$REQUEST:chartYearStart|empty:'2011'} AND datefield <= DATE(NOW())

    GROUP  BY d, m

    ORDER  BY d, m) AS q1

vladimir84

The administrator has disabled public write access.

Page: 1

Useful Links