|
Re:Year dropdown menu above the table 8 Years, 2 Months ago
|
Karma: 760
|
|
Set init value as:
SELECT @r := 12 * ({$REQUEST:chartYearStart|empty:'2011'} - 2011)
This is what you need?
Regards,
ARI Soft
|
|
|
|
|
|
|
Re:Year dropdown menu above the table 8 Years, 2 Months ago
|
Karma: 0
|
No, this is the query that calculates the value (and it works OK on its own, but it gives me the error with the CASE WHEN statement:
| Code: |
(SELECT @runtot :=(
CASE WHEN {$REQUEST:chartYearTotal}='2011' THEN 0 ELSE /*IF IT STARTS AT 2011, IT IS ZERO AND IT ADDS UP THE MONTHLY VALUES*/
(/*IF IT STARTS AT 2012+, FIRST CALCULATE HOW MUCH IS THE TOTAL LAST YEAR */
SELECT (@risultato := calcolo.assun - calcolo.licen) AS totale
FROM(
SELECT
SUM(statistica_assunti_n.assunzioni) AS assun,
SUM(statistica_licenziati_n.licenziamenti) AS licen
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) BETWEEN 2011 AND {$REQUEST:chartYearTotal}-1)/*I REQUEST 2012, BUT IT WILL SEE HOW MUCH WAS IT IN 2011 AND ONLY THEN ADD THE MONTHLY VALUE OF JAN,FEB,MAR... OF 2012*/
AS calcolo
)
) AS zero
|
I know that the answer is right in front of me, but I don't see it  |
|
|
|
Last Edit: 2016/09/10 10:42 By vladimir84.
|
|
|
Re:Year dropdown menu above the table 8 Years, 2 Months ago
|
Karma: 0
|
Yes, I found the mistake  However when I select the 2011, it shows null on all values for that year. I think it's just a matter of small correction.
Here is the complete query, which works fine for all the other years except the base year of 2011:
| Code: |
SELECT
CONCAT(q1.d,'-',LPAD(q1.m,2,0)) AS Periodo,
(@runtot := @runtot + q1.c - q1.l) AS `N. dipendenti`
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:chartYearTotal} AND datefield <= DATE(NOW())
GROUP BY d, m
ORDER BY d, m) AS q1,
(SELECT (
CASE @runtot
WHEN {$REQUEST:chartYearTotal}='2011' THEN @runtot :='0' ELSE @runtot :=
(
SELECT (@risultato := calcolo.assun - calcolo.licen) AS totale
FROM(
SELECT
SUM(statistica_assunti_n.assunzioni) AS assun,
SUM(statistica_licenziati_n.licenziamenti) AS licen
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) BETWEEN 2011 AND {$REQUEST:chartYearTotal}-1)
AS calcolo
) END)
) AS zero
|
|
|
|
|
Last Edit: 2016/09/10 11:58 By vladimir84.
|
|
|
Re:Year dropdown menu above the table 8 Years, 2 Months ago
|
Karma: 0
|
got it!!! 
it is redundant as hell, but luckily there aren't are only 1000-2000 records to analyze, it won't be that slow
| Code: |
SELECT
CONCAT(q1.d,'-',LPAD(q1.m,2,0)) AS Periodo,
(@runtot := @runtot + q1.c - q1.l) AS `N. dipendenti`
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:chartYearTotal|empty:'2016'} AND datefield <= DATE(NOW())
GROUP BY d, m
ORDER BY d, m) AS q1,
(SELECT (
CASE @runtot
WHEN {$REQUEST:chartYearTotal|empty:'2016'}='2011' THEN '@runtot :=0' ELSE @runtot :=
(CASE WHEN {$REQUEST:chartYearTotal|empty:'2016'}='2011' THEN '@runtot :=0' ELSE
(
SELECT (@risultato := calcolo.assun - calcolo.licen) AS totale
FROM(
SELECT
SUM(statistica_assunti_n.assunzioni) AS assun,
SUM(statistica_licenziati_n.licenziamenti) AS licen
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) BETWEEN 2011 AND (CASE WHEN {$REQUEST:chartYearTotal|empty:'2016'}='2011' THEN '2011' ELSE {$REQUEST:chartYearTotal|empty:'2016'}-1 END))
AS calcolo
)END) END)
) AS zero
|
|
|
|
|
|
|
|
