Hello,
Try this to combine the query and exp2 and exp3:
SELECT []
FROM []
WHERE
exp1
AND
(exp2 OR exp3)
ORDER by []
;
I hope this works, I got no error executing this query in my database...
Thanks
Sebastian
> Carlos Lopez <clo...@dml.vic.edu.au> hat am 15.03.2021 06:33 geschrieben:
>
>
> 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
___
mailto:sebastian.kr...@tuz-eisenach.de
_______________________________________________
Koha mailing list http://koha-community.org
Koha@lists.katipo.co.nz
Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha
