Re: [Koha] Catalogue - SQL Report Help (Paul A)

2014-12-11 Thread Matthew Charlesworth, S.J. 
Dear Paul, Heather and Kerrie,

Thank you for the report. I've also tweaked it slightly to allow direct
interaction with the records in the Staff Interface (using example of
CONCAT with A HREF and TITLE codes). (NB Distinguish between ' and ).

I attach it here in case you might find it useful. I'm stuck with trying to
get a version of the report that only shows 0 copies or copies greater than
1 (or to use the input technique to specify by x number of Copies)... I've
tried the WHERE Copies=0 clause but that doesn't seem to work...

SELECT DISTINCT CONCAT('a title=Search for Title with Biblionumber:
',b.biblionumber,'
href=/cgi-bin/koha/catalogue/search.pl?q=ti%3A',REPLACE (b.title, '
', '+'),'Search/a') AS Search for Title, b.biblionumber,
CONCAT('a 
href=/cgi-bin/koha/catalogue/detail.pl?biblionumber=',b.biblionumber,'',b.title,'/a')
AS Item Title, b.author, t.editionstatement, t.publishercode,
t.isbn, count(i.itemnumber) AS Copies FROM biblio b LEFT JOIN
biblioitems t USING(biblionumber) LEFT JOIN items i USING(biblionumber)
GROUP BY b.biblionumber ORDER BY Copies ASC


Regards

Matthew.


--


 Message: 4
 Date: Thu, 11 Dec 2014 01:19:06 +
 From: Kerrie Stevens kstev...@harvest.edu.au
 Subject: Re: [Koha] Catalogue - SQL Report Help (Paul A)

 SELECT DISTINCT b.biblionumber, b.title, b.author, t.editionstatement,
 t.publishercode, t.isbn, i.ccode, count(i.itemnumber) FROM biblio b LEFT
 JOIN
 biblioitems t USING(biblionumber) LEFT JOIN items i USING(biblionumber)
 WHERE i.ccode=Enter collection code GROUP BY b.biblionumber

 Thanks so much for your sharing of this report.

 Kerrie Stevens

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

Re: [Koha] Catalogue - SQL Report Help (Paul A)

2014-12-11 Thread Liz Rea 
Hi everyone,

Would definitely like to point out the SQL report library at
http://wiki.koha-community.org/wiki/SQL_Reports_Library

It is much better for everyone if you put your contributed reports
there, even if they are duplicate (or slightly duplicate!).

What I would like to suggest, is if you get a report done for you on the
list, that you, the recipient, take the 10 minutes to put that on the
report wiki, crediting your helper, so that everyone can benefit.

It's what community is all about, ya?

Cheers,
Liz
On 12/12/14 13:18, Matthew Charlesworth, S.J. wrote:
 Dear Paul, Heather and Kerrie,

 Thank you for the report. I've also tweaked it slightly to allow direct
 interaction with the records in the Staff Interface (using example of
 CONCAT with A HREF and TITLE codes). (NB Distinguish between ' and ).

 I attach it here in case you might find it useful. I'm stuck with trying to
 get a version of the report that only shows 0 copies or copies greater than
 1 (or to use the input technique to specify by x number of Copies)... I've
 tried the WHERE Copies=0 clause but that doesn't seem to work...

 SELECT DISTINCT CONCAT('a title=Search for Title with Biblionumber:
 ',b.biblionumber,'
 href=/cgi-bin/koha/catalogue/search.pl?q=ti%3A',REPLACE (b.title, '
 ', '+'),'Search/a') AS Search for Title, b.biblionumber,
 CONCAT('a 
 href=/cgi-bin/koha/catalogue/detail.pl?biblionumber=',b.biblionumber,'',b.title,'/a')
 AS Item Title, b.author, t.editionstatement, t.publishercode,
 t.isbn, count(i.itemnumber) AS Copies FROM biblio b LEFT JOIN
 biblioitems t USING(biblionumber) LEFT JOIN items i USING(biblionumber)
 GROUP BY b.biblionumber ORDER BY Copies ASC


 Regards

 Matthew.


 --

 Message: 4
 Date: Thu, 11 Dec 2014 01:19:06 +
 From: Kerrie Stevens kstev...@harvest.edu.au
 Subject: Re: [Koha] Catalogue - SQL Report Help (Paul A)

 SELECT DISTINCT b.biblionumber, b.title, b.author, t.editionstatement,
 t.publishercode, t.isbn, i.ccode, count(i.itemnumber) FROM biblio b LEFT
 JOIN
 biblioitems t USING(biblionumber) LEFT JOIN items i USING(biblionumber)
 WHERE i.ccode=Enter collection code GROUP BY b.biblionumber

 Thanks so much for your sharing of this report.

 Kerrie Stevens

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

-- 
--
Liz Rea
Catalyst.Net Limited
Level 6, Catalyst House, 
150 Willis Street, Wellington.
P.O Box 11053, Manners Street, 
Wellington 6142

GPG: B149 A443 6B01 7386 C2C7 F481 B6c2 A49D 3726 38B7

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

Re: [Koha] Catalogue - SQL Report Help (Paul A)

2014-12-11 Thread Paul A 

At 07:18 PM 12/11/2014 -0500, Matthew Charlesworth, S.J. wrote:
[snip]

I attach it here in case you might find it useful. I'm stuck with trying to
get a version of the report that only shows 0 copies or copies greater than
1 (or to use the input technique to specify by x number of Copies)... I've
tried the WHERE Copies=0 clause but that doesn't seem to work...


No, the logic breaks down counting itemnumbers -- count the 
items.biblionumbers instead:


SELECT DISTINCT CONCAT('a title=Search for Title with Biblionumber:
',b.biblionumber,'
href=/cgi-bin/koha/catalogue/search.pl?q=ti%3A',REPLACE (b.title, '
', '+'),'Search/a') AS Search for Title, b.biblionumber,
CONCAT('a 
href=/cgi-bin/koha/catalogue/detail.pl?biblionumber=',b.biblionumber,'',b.title,'/a')

AS Item Title, b.author, t.editionstatement, t.publishercode,
t.isbn, count(i.biblionumber) AS Copies FROM biblio b LEFT JOIN
biblioitems t USING(biblionumber) LEFT JOIN items i USING(biblionumber)
GROUP BY b.biblionumber ORDER BY Copies ASC;

Best -- Paul







SELECT DISTINCT CONCAT('a title=Search for Title with Biblionumber:
',b.biblionumber,'
href=/cgi-bin/koha/catalogue/search.pl?q=ti%3A',REPLACE (b.title, '
', '+'),'Search/a') AS Search for Title, b.biblionumber,
CONCAT('a 
href=/cgi-bin/koha/catalogue/detail.pl?biblionumber=',b.biblionumber,'',b.title,'/a')

AS Item Title, b.author, t.editionstatement, t.publishercode,
t.isbn, count(i.itemnumber) AS Copies FROM biblio b LEFT JOIN
biblioitems t USING(biblionumber) LEFT JOIN items i USING(biblionumber)
GROUP BY b.biblionumber ORDER BY Copies ASC


Regards

Matthew.


--


 Message: 4
 Date: Thu, 11 Dec 2014 01:19:06 +
 From: Kerrie Stevens kstev...@harvest.edu.au
 Subject: Re: [Koha] Catalogue - SQL Report Help (Paul A)

 SELECT DISTINCT b.biblionumber, b.title, b.author, t.editionstatement,
 t.publishercode, t.isbn, i.ccode, count(i.itemnumber) FROM biblio b LEFT
 JOIN
 biblioitems t USING(biblionumber) LEFT JOIN items i USING(biblionumber)
 WHERE i.ccode=Enter collection code GROUP BY b.biblionumber

 Thanks so much for your sharing of this report.

 Kerrie Stevens


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

Re: [Koha] Catalogue - SQL Report Help (Paul A)

2014-12-10 Thread Kerrie Stevens 
Hi Paul  Heather,

The report you developed for Satish below is very helpful, but I'd like to 
tweak it a little differently -  I've tried every variation I can think of with 
no luck (I don't have very much SQL experience)... how can I get it to select 
by branch, rather than collection code?

SELECT DISTINCT b.biblionumber, b.title, b.author, t.editionstatement,
t.publishercode, t.isbn, i.ccode, count(i.itemnumber) FROM biblio b LEFT JOIN
biblioitems t USING(biblionumber) LEFT JOIN items i USING(biblionumber)
WHERE i.ccode=Enter collection code GROUP BY b.biblionumber

Thanks so much for your sharing of this report.

Kerrie Stevens
Harvest Bible College
Melbourne, Australia
___
Koha mailing list  http://koha-community.org
Koha@lists.katipo.co.nz
http://lists.katipo.co.nz/mailman/listinfo/koha

Re: [Koha] Catalogue - SQL Report Help (Paul A)

2014-12-10 Thread Heather Braum (NEKLS) 
Kerrie, to select by homebranch, you just need to add a branch parameter,
i.homebranch=choose branch|branches

So the statement will now look like

SELECT DISTINCT b.biblionumber, b.title, b.author, t.editionstatement,
t.publishercode, t.isbn, i.ccode, count(i.itemnumber) FROM biblio b LEFT
JOIN
biblioitems t USING(biblionumber) LEFT JOIN items i USING(biblionumber)
WHERE i.homebranch=Choose branch|branches GROUP BY b.biblionumber

In case anyone is interested in learning more, parameters are further
explained on the Koha community wiki (
http://wiki.koha-community.org/wiki/SQL_Reports_Library#Runtime_Parameters).
There's two basic kinds of parameters (that I at least use a lot with Koha
report writing on my system) -- and you can add multiple parameters to your
reports:

1. Add option to add text manually -- Examples: i.ccode=Enter collection
code OR i.homebranch=Enter homebranch

You then would type the corresponding branchcode or collection code into
the text box when running the report (the two examples mentioned use
branchcode or collection code, but this method works for lots of other
fields in Koha, too)

OR

2. Add option to Choose from dropdown list -- Examples: i.ccode=Choose
collection code|CCODE OR i.homebranch=Choose homebranch|branches
(what you use after the | character may vary a little bit, depending on
what field you're setting up the parameter for).

Hope this helps!

Heather Braum
NExpress Coordinator
Resource Sharing Librarian
Northeast Kansas Library System
hbr...@nekls.org

The illiterate of the 21st century will not be those who cannot read
and write, but those who cannot learn, unlearn, and relearn. ~Alvin
Toffler, *Rethinking the Future*




On Wed, Dec 10, 2014 at 7:19 PM, Kerrie Stevens kstev...@harvest.edu.au
wrote:

 Hi Paul  Heather,

 The report you developed for Satish below is very helpful, but I'd like to
 tweak it a little differently -  I've tried every variation I can think of
 with no luck (I don't have very much SQL experience)... how can I get it to
 select by branch, rather than collection code?

 SELECT DISTINCT b.biblionumber, b.title, b.author, t.editionstatement,
 t.publishercode, t.isbn, i.ccode, count(i.itemnumber) FROM biblio b LEFT
 JOIN
 biblioitems t USING(biblionumber) LEFT JOIN items i USING(biblionumber)
 WHERE i.ccode=Enter collection code GROUP BY b.biblionumber

 Thanks so much for your sharing of this report.

 Kerrie Stevens
 Harvest Bible College
 Melbourne, Australia
 ___
 Koha mailing list  http://koha-community.org
 Koha@lists.katipo.co.nz
 http://lists.katipo.co.nz/mailman/listinfo/koha

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