Skip to content

Instantly share code, notes, and snippets.

@chriskeene
Last active Dec 21, 2015
Embed
What would you like to do?
Select all rows for a Financial year grouped by month, with a running total for the given year, and a all time running total. The underlying table stores dd/mm/yy in separate int fields.
SELECT Count(*) as "new items", e.datestamp_month, e.datestamp_year,
(select count(*) from eprint
where ((e.datestamp_year >= datestamp_year AND e.datestamp_month >= datestamp_month)
OR (e.datestamp_year > datestamp_year))
and `eprint_status` = "archive"
) AS "all time running total",
(select count(*) from eprint
where ((e.datestamp_year = 2012 AND e.datestamp_year = datestamp_year AND datestamp_month > 7 AND datestamp_month <= e.datestamp_month )
OR (e.datestamp_year = 2013 AND ((datestamp_year = 2012 AND datestamp_month > 7)
OR (datestamp_year = 2013 AND datestamp_month <= e.datestamp_month))
)
)
and `eprint_status` = "archive"
) AS "this year running total"
FROM `eprint` e
WHERE e.`eprint_status` = "archive"
and ((e.datestamp_year = 2013 and e.datestamp_month < 8 )
OR (e.datestamp_year = 2012 and e.datestamp_month > 7 ))
group by e.datestamp_month, e.datestamp_year
order by e.datestamp_year, datestamp_month;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment