Re: [Koha] Problem with some SQL

2021-03-16 Thread Guillermo 
Hi Speak Spanish ?

Ing. Guillermo González

El lun., 15 de mar. de 2021 1:34 a. m., Carlos Lopez 
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 < (-mm-dd)|date>> AND <>
> 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 < (-mm-dd)|date>> AND <>
> 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 = <>
> SET @end_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  ext.1 | libr...@dml.vic.edu.au 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

Re: [Koha] Problem with some SQL

2021-03-16 Thread Carlos Lopez 
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  ext.1 | libr...@dml.vic.edu.au<mailto:libr...@dml.vic.edu.au> 
| library.dmlibrary.org.au



From: Koha  on behalf of Sebastian Krieg 

Sent: Monday, 15 March 2021 6:40 PM
To: koha 
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  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 <> 
> AND <>
> 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 <> 
> AND <>
> 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 = <>
> SET @end_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  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
___

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

Re: [Koha] Problem with some SQL

2021-03-15 Thread Sebastian Krieg 
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  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 <> 
> AND <>
> 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 <> 
> AND <>
> 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 = <>
> SET @end_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  ext.1 | 
> 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

[Koha] Problem with some SQL

2021-03-14 Thread Carlos Lopez 
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 <> 
AND <>
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 <> 
AND <>
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 = <>
SET @end_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  ext.1 | 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