Hi everyone I have the following reports that I would like to combine into one:
SELECT categories.description, COUNT(borrowers.borrowernumber) FROM borrowers, categories WHERE borrowers.categorycode = categories.categorycode AND borrowers.date_renewed BETWEEN <<Expiry date BETWEEN (yyyy-mm-dd)|date>> AND <<and (yyyy-mm-dd)|date>> GROUP BY borrowers.categorycode (count the number of borrower renewals between 2 given dates and provide a breakdown by borrower category) And SELECT categories.description, COUNT(borrowers.borrowernumber) FROM borrowers, categories WHERE borrowers.categorycode = categories.categorycode AND borrowers.dateenrolled BETWEEN <<Expiry date BETWEEN (yyyy-mm-dd)|date>> AND <<and (yyyy-mm-dd)|date>> GROUP BY borrowers.categorycode (same thing but for new enrolments rather than renewals) What I would like is a report that will give me 3 columns: Borrower category (from categories.description), a count of newly-enrolled borrowers, and a count of renewed borrowers, with a breakdown by borrower category, but I only want the user to have to enter a single starting date and a single end date (not 2 identical date ranges). I tried to run this one but it looks as though we're not allowed to set variables before the SELECT statement: SET @start_date = <<Start of date range (yyyy-mm-dd)|date>> SET @end_date = <<End of rate range (yyyy-mm-dd)|date>> SELECT categories.description AS "BORROWER CATEGORY", SUM(CASE WHEN borrowers.dateenrolled BETWEEN @start_date AND @end_date THEN 1 ELSE 0) NewBorrowers, SUM(CASE WHEN borrowers.date_renewed BETWEEN @start_date AND @end_date THEN 1 ELSE 0) RenewedBorrowers FROM borrowers, categories WHERE borrowers.categorycode = categories.categorycode GROUP BY borrowers.categorycode Is something like what I'm trying to do even possible on Koha, or should I be happy with my 2 reports? With kind regards from the Dalton McCaughey Library Team Carlos Lopez Dalton McCaughey Library | 29 College Crescent, Parkville, VICTORIA 3052 Ph: 03 9340 8888 ext.1 | libr...@dml.vic.edu.au<mailto:libr...@dml.vic.edu.au> | library.dmlibrary.org.au _______________________________________________ Koha mailing list http://koha-community.org Koha@lists.katipo.co.nz Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha
