Re: [Koha] Problem with some SQL

16 Mar 2021

      Thanks Sebastian but I'm afraid I'm still a little lost: How would you feed the same single expression (a user-supplied date range) to 2 separate fields?

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 | library@dml.vic.edu.au<mailto:library@dml.vic.edu.au> | library.dmlibrary.org.au

________________________________
From: Koha <koha-bounces@lists.katipo.co.nz> on behalf of Sebastian Krieg <sebastian.krieg@tuz-eisenach.de>
Sent: Monday, 15 March 2021 6:40 PM
To: koha <koha@lists.katipo.co.nz>
Subject: Re: [Koha] Problem with some SQL

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 <clopez@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 | library@dml.vic.edu.au<mailto:library@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.krieg@tuz-eisenach.de
_______________________________________________

Koha mailing list  http://koha-community.org
Koha@lists.katipo.co.nz
Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha