MySQL - select as fiscal year

Question

I have this SELECT:

SELECT 
    m.`maschine-name` AS byMaschine,
    q.`mname` AS byMName,
    SUM(YEAR(q.`created`) = YEAR(CURDATE())) AS total
FROM qualitaet q INNER JOIN
    maschinen m 
    ON m.maschine = q.maschine
WHERE
    q.`status`='1'
GROUP BY
    q.maschine, q.mname;

to get all results for the current year and it looks like this:

| maschine-name | mname | total |
|     TYP 1     |   0   |   4   |
|     TYP 2     |   3   |   4   |
|     TYP 2     |   4   |   4   |
|     TYP 3     |   0   |   4   |
|     TYP 4     |   0   |   4   |

see SQL Fiddle here

But i want to SELECT it as fiscal year (financial year) starting at >= Oct, 1 to get this result:

| maschine-name | mname | total |
|     TYP 1     |   0   |   3   |
|     TYP 2     |   3   |   2   |
|     TYP 2     |   4   |   0   |
|     TYP 3     |   0   |   2   |
|     TYP 4     |   0   |   2   |

i have different Date statements which work all, but the fiscal year drives me crazy :-(

show data for TODAY:

SUM(DATE(created) = CURDATE()) AS total

show data for CURRENT WEEK:

SUM(YEARWEEK(q.`created`, 1) = YEARWEEK(CURRENT_DATE, 1)) AS total

show data for CURRENT MONTH:

SUM(q.`created` >= CURDATE() - INTERVAL DAY(CURDATE())-1 DAY) AS total

show data for CURRENT YEAR:

SUM(YEAR(q.`created`) = YEAR(CURDATE())) AS total

Is there a way to get this result from above?

Best regards and a happy new year ;-)

Just use the `DATE_ADD` or `DATE_SUB` to substract or add 10 months? — Norbert, Dec 31 '15 at 22:51

score 2 · Accepted Answer · answered Jan 01 '16 at 00:50

I did it with MAKEDATE. Startdate is Oct, 1

SUM(q.`created` >= MAKEDATE(year(now()-interval 1 year),1) + interval 9 month) AS total

see SQLFiddle here

complete SELECT:

SELECT 
    m.`maschine-name` AS byMaschine,
    q.`mname` AS byMName,
    SUM(q.`created` >= MAKEDATE(year(now()-interval 1 year),1) + interval 9 month) AS total
FROM qualitaet q INNER JOIN
    maschinen m 
    ON m.maschine = q.maschine
WHERE
    q.`status`='1'
GROUP BY
    q.maschine, q.mname;

Now i receive this result:

| maschine-name | mname | total |
|     TYP 1     |   0   |   3   |
|     TYP 2     |   3   |   2   |
|     TYP 2     |   4   |   0   |
|     TYP 3     |   0   |   2   |
|     TYP 4     |   0   |   2   |

MySQL - select as fiscal year

1 Answers1