Hi Speak Spanish ? Ing. Guillermo González
El lun., 15 de mar. de 2021 1:34 a. m., Carlos Lopez <clo...@dml.vic.edu.au> escribió: > 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 > _______________________________________________ Koha mailing list http://koha-community.org Koha@lists.katipo.co.nz Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha