Home News Contact Us Forum About Us Demos Products F.A.Q.
Shopping Cart
You currently have 0 items in your cart.


Recent Events
  • 05/09/2017 BIG Autumn SALE

    Use AUTUMN17 coupon code to buy our WordPress or Joomla! extensions with 25% discount. Hurry up! The offer is valid till 11 September.

  • 14/04/2017 Happy Easter

    Wish a Happy Easter and give 30% discount for all our WordPress and Joomla! extensions. Use EASTER17 coupon code. The offer is valid till 17 April.


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




Follow us on twitter



Welcome, Guest
Please Login or Register.    Lost Password?

[SOLVED] Second dataset bar charts
(1 viewing) (1) Guest
Go to bottomPage: 1
TOPIC: [SOLVED] Second dataset bar charts
#58459
[SOLVED] Second dataset bar charts 1 Year, 1 Month 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

Last Edit: 2016/09/09 08:06 By vladimir84.
The administrator has disabled public write access.
 
#58460
Re: Second dataset bar charts 1 Year, 1 Month ago Karma: 0
any news?
The administrator has disabled public write access.
 
#58462
Re: Second dataset bar charts 1 Year, 1 Month 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 

The administrator has disabled public write access.
 
Go to topPage: 1