Re: [Koha] [EXTERNAL] Re: Report help needed

[Hernandez, Heather H]

Hi, Michael--

That's fantastic!!  Thank you so much for sharing it!

Best,
h2

~~~

Ms. Heather Hernandez (she, her, hers)
Technical Services Librarian
San Francisco Maritime National Historical Park Research Center
2 Marina Blvd., Bldg. E, 3rd floor, San Francisco, CA  94123-1284
415-561-7032, 
heather_hernan...@nps.gov
Library catalog: http://keys.bywatersolutions.com/
___

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

Re: [Koha] SQL help?

[Myka Kennedy Stephens]

Hi Kerrie,

The workflow that we've developed utilizes patron lists. When the list of
prospective graduates is prepared by the registrar's office, I put them all
into a patron list. Yes, this involves looking up individual students by
name, but I only have to do this once. After they are in the list, I use a
report to see who has an outstanding account balance and/or books still
checked out. I use this information to email the students to let them know
what they need to pay and/or return by our deadline for graduation. When it
is time to convert their accounts to alumni status, I use the batch patron
modification tool. This is super easy to use with patron lists and is
linked both from the patron list tool and the batch patron modification
tool. This year I also wrote a new report that gives me the email addresses
for all the patrons on the list, which made it easier to email them all
when their accounts were converted to congratulate them and let them know
about their alumni library privileges.

I will paste my SQL reports below in case you decide to go this route and
use patron lists.

Good luck,
Myka

Graduating Students Account Status
SELECT
CONCAT('', borrowernumber, '' ) AS borrowernumber,
cardnumber,
surname,
firstname,
email,
COALESCE(i.CO, 0) AS checkouts,
f.Owes
FROM
borrowers b
JOIN
patron_list_patrons USING (borrowernumber)
JOIN
patron_lists ON (patron_list_patrons.patron_list_id =
patron_lists.patron_list_id)
LEFT JOIN
(SELECT
borrowernumber, COUNT(issue_id) AS CO
FROM
issues
GROUP BY borrowernumber) i USING (borrowernumber)
LEFT JOIN
(SELECT
borrowernumber,
FORMAT(SUM(accountlines.amountoutstanding), 2) AS Owes
FROM
accountlines
GROUP BY accountlines.borrowernumber) f USING (borrowernumber)
WHERE
patron_lists.patron_list_id = <> AND
(COALESCE(i.CO, 0) > 0 OR f.Owes > 0)

Email Addresses from a Patron List
SELECT
CONCAT('', borrowernumber, '' ) AS borrowernumber,
cardnumber,
surname,
firstname,
email
FROM
borrowers b
JOIN
patron_list_patrons USING (borrowernumber)
JOIN
patron_lists ON (patron_list_patrons.patron_list_id =
patron_lists.patron_list_id)
WHERE
patron_lists.patron_list_id = <>

On Tue, May 26, 2020 at 8:24 PM Kerrie Stevens 
wrote:

> Every year, we need to confirm graduating students have no outstanding
> loans so they can graduate. And every year I think I must ask for some help
> in doing it more efficiently than searching borrower numbers one by one...
> I'd like to be able to copy & paste a number of borrower numbers into
> search box and get back a table showing those with outstanding loans:
> (borrower number, name, and number of current loans)
> Can cutting & pasting or searching on multiple borrower numbers be done in
> SQL reports?
> This is way beyond my basic level of SQL understanding, so any
> advice/assistance would be greatly appreciated.
>
> Thank you and have a great day wherever you may be!
>
>
> Kerrie Stevens  AALIA(CP), MAppSci(LibMgt), BBus(Info)
> Director of Library Services, Alphacrucis College
> Librarian - Melbourne Campus
> HDR Liaison Librarian
> ___
>
> Koha mailing list  http://koha-community.org
> Koha@lists.katipo.co.nz
> Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha
>


-- 
Deaconess Myka Kennedy Stephens, MDiv, MSLIS
Seminary Librarian
Associate Professor of Theological Bibliography

555 West James Street
Lancaster, PA 17603
717-290-8704
mksteph...@lancasterseminary.edu
https://library.lancasterseminary.edu
___

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

Re: [Koha] Report help needed

[Mark Alexander]

Excerpts from Michael Sutherland's message of 2020-05-27 16:59:50 -0400:
> CASE SUBSTR(metadata,282,2)

I tried this myself, and it does seem to work.  But I worry that
the 282 value might not be correct in the future, should the XML
format of the metadata change even slightly.  In particular,
the XML header has a field called xsi:schemaLocation that contains
a URL that might change someday.  I could very wrong about this,
though; perhaps the format really is stable.

This does the same job and seems less obscure (to me, anyway):

  substring(ExtractValue(metadata, '//leader'), 7, 2)

But perhaps it's not as fast?  I'm not a MySQL expert by any means.
___

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

Re: [Koha] Report help needed

[Michael Sutherland]

Thank you. I'm unfamiliar with that particular bug and will check it out
for our records.

Best,
Michael
_
*Michael J. Sutherland*
University Libraries
Virginia Tech
sudrl...@vt.edu | 540.231.9669 <+15402319669>



On Wed, May 27, 2020 at 7:05 PM Elaine Bradtke  wrote:

> Not condescending at all, the things I don't know about reports would fill
> a book.
> Interesting report you have there by the way.
> The purpose for mine is to double check the position 06 against the 942 $c
> because there is a bug 19419 that seems to cause trouble with the 008 if
> the Leader 06 is not set correctly for the format (still trying to verify
> that's what's going on).  I wanted to find any records that didn't have the
> appropriate Leader 06 for a given format as specified in the 942.
> Elaine Bradtke
> VWML
> English Folk Dance and Song Society | http://www.efdss.org
> Cecil Sharp House, 2 Regent's Park Road, London NW1 7AY
> Tel+44 (0) 20 7485 2206 (This number is for the English Folk Dance and
> Song Society in London, England. If you wish to phone me personally, send
> an e-mail first. I work off site)
> --
> Registered Company No. 297142
> Charity Registered in England and Wales No. 305999
>
>
> On Wed, May 27, 2020 at 2:00 PM Michael Sutherland 
> wrote:
>
> > Elaine,
> >
> > Maybe this would be helpful for those codes -
> > https://www.loc.gov/marc/bibliographic/bdleader.html
> >
> > And, to break it down for those that do not know and are interested, (at
> > the risk of condescending, which is not my intention, please, please, I
> > hope you do not take it that way)
> >
> > in the expression - SUBSTRING(ExtractValue(metadata,'//leader'),7,1) AS
> > "Position06"
> >
> > you are wanting to extract a piece (substring) from the entire leader
> > string - metadata,'//leader' - the 7 is the value that specifies the
> > initial position from which the characters can be extracted. The first
> > position of the expression starts with 1. But, in MARC the first position
> > is 0 (zero). The next value is a positive integer value that specifies
> the
> > ending limit and determines how many characters are going to be
> > extracted from the given expression in this case '1', which will give you
> > the single letter code in the leader 06, or the seventh position, however
> > you choose to look at it.
> >
> > So, in collaboration with ByWater Solutions, I have co-written the
> > following report, which I use to report collection statistics by itemtype
> > to ACRL and ARL on our collection sans suppressed records from the OPAC.
> It
> > takes the combination of the leader 06 and 07 to determine itemtype and
> > count them.
> >
> > SELECT
> > CASE SUBSTR(metadata,282,2)
> >   WHEN ' m' THEN 'Book'
> >   WHEN '2m' THEN 'Book'
> >   WHEN 'am' THEN 'Book'
> >   WHEN 'aa' THEN 'Book'
> >   WHEN 'ac' THEN 'Book'
> >   WHEN 'ad' THEN 'Book'
> >   WHEN 'ai' THEN 'Book'
> >   WHEN 'as' THEN 'Journal'
> >   WHEN 'cc' THEN 'Music Score'
> >   WHEN 'cm' THEN 'Music Score'
> >   WHEN 'dm' THEN 'Music Score'
> >   WHEN 'ei' THEN 'Map'
> >   WHEN 'em' THEN 'Map'
> >   WHEN 'es' THEN 'Map'
> >   WHEN 'ga' THEN 'Video recording and motion pictures'
> >   WHEN 'gs' THEN 'Video recording and motion pictures'
> >   WHEN 'gc' THEN 'Video recording and motion pictures'
> >   WHEN 'gi' THEN 'Video recording and motion pictures'
> >   WHEN 'gm' THEN 'Video recording and motion pictures'
> >   WHEN 'ic' THEN 'Non-music sound recording'
> >   WHEN 'im' THEN 'Non-music sound recording'
> >   WHEN 'is' THEN 'Non-music sound recording'
> >   WHEN 'jm' THEN 'Music sound recording'
> >   WHEN 'jc' THEN 'Music sound recording'
> >   WHEN 'ji' THEN 'Music sound recording'
> >   WHEN 'kc' THEN '2D image'
> >   WHEN 'km' THEN '2D image'
> >   WHEN 'ma' THEN 'Computer file'
> >   WHEN 'mi' THEN 'Computer file'
> >   WHEN 'mm' THEN 'Computer file'
> >   WHEN 'ms' THEN 'Computer file'
> >   WHEN 'oc' THEN 'Kit'
> >   WHEN 'om' THEN 'Kit'
> >   WHEN 'pc' THEN 'Mixed material'
> >   WHEN 'pm' THEN 'Mixed material'
> >   WHEN 'rc' THEN '3D Object'
> >   WHEN 'rm' THEN '3D Object'
> >   WHEN 'tc' THEN 'Manuscript'
> >   WHEN 'tm' THEN 'Manuscript'
> >   ELSE 'unknown' END AS Type,
> > COUNT(DISTINCT biblionumber) AS Titles,
> > COUNT(itemnumber) AS Volumes
> > FROM biblio_metadata
> > LEFT JOIN items USING (biblionumber)
> > WHERE
> >  ExtractValue(metadata,'//datafield[@tag="942"]/subfield[@code="n"]')
> > != 1
> > GROUP BY Type
> > WITH ROLLUP
> >
> > which generates the following
> >
> > TypeTitlesVolumes
> > 2D image 65 92
> > 3D Object 21 21
> > Book 1193934 1329489
> > Computer file 630 1555
> > Journal 38568 542424
> > Kit 41 66
> > Manuscript 5461 14120
> > Map 4754 5770
> > Mixed 

Re: [Koha] Report help needed

[Elaine Bradtke]

Not condescending at all, the things I don't know about reports would fill
a book.
Interesting report you have there by the way.
The purpose for mine is to double check the position 06 against the 942 $c
because there is a bug 19419 that seems to cause trouble with the 008 if
the Leader 06 is not set correctly for the format (still trying to verify
that's what's going on).  I wanted to find any records that didn't have the
appropriate Leader 06 for a given format as specified in the 942.
Elaine Bradtke
VWML
English Folk Dance and Song Society | http://www.efdss.org
Cecil Sharp House, 2 Regent's Park Road, London NW1 7AY
Tel+44 (0) 20 7485 2206 (This number is for the English Folk Dance and
Song Society in London, England. If you wish to phone me personally, send
an e-mail first. I work off site)
--
Registered Company No. 297142
Charity Registered in England and Wales No. 305999


On Wed, May 27, 2020 at 2:00 PM Michael Sutherland  wrote:

> Elaine,
>
> Maybe this would be helpful for those codes -
> https://www.loc.gov/marc/bibliographic/bdleader.html
>
> And, to break it down for those that do not know and are interested, (at
> the risk of condescending, which is not my intention, please, please, I
> hope you do not take it that way)
>
> in the expression - SUBSTRING(ExtractValue(metadata,'//leader'),7,1) AS
> "Position06"
>
> you are wanting to extract a piece (substring) from the entire leader
> string - metadata,'//leader' - the 7 is the value that specifies the
> initial position from which the characters can be extracted. The first
> position of the expression starts with 1. But, in MARC the first position
> is 0 (zero). The next value is a positive integer value that specifies the
> ending limit and determines how many characters are going to be
> extracted from the given expression in this case '1', which will give you
> the single letter code in the leader 06, or the seventh position, however
> you choose to look at it.
>
> So, in collaboration with ByWater Solutions, I have co-written the
> following report, which I use to report collection statistics by itemtype
> to ACRL and ARL on our collection sans suppressed records from the OPAC. It
> takes the combination of the leader 06 and 07 to determine itemtype and
> count them.
>
> SELECT
> CASE SUBSTR(metadata,282,2)
>   WHEN ' m' THEN 'Book'
>   WHEN '2m' THEN 'Book'
>   WHEN 'am' THEN 'Book'
>   WHEN 'aa' THEN 'Book'
>   WHEN 'ac' THEN 'Book'
>   WHEN 'ad' THEN 'Book'
>   WHEN 'ai' THEN 'Book'
>   WHEN 'as' THEN 'Journal'
>   WHEN 'cc' THEN 'Music Score'
>   WHEN 'cm' THEN 'Music Score'
>   WHEN 'dm' THEN 'Music Score'
>   WHEN 'ei' THEN 'Map'
>   WHEN 'em' THEN 'Map'
>   WHEN 'es' THEN 'Map'
>   WHEN 'ga' THEN 'Video recording and motion pictures'
>   WHEN 'gs' THEN 'Video recording and motion pictures'
>   WHEN 'gc' THEN 'Video recording and motion pictures'
>   WHEN 'gi' THEN 'Video recording and motion pictures'
>   WHEN 'gm' THEN 'Video recording and motion pictures'
>   WHEN 'ic' THEN 'Non-music sound recording'
>   WHEN 'im' THEN 'Non-music sound recording'
>   WHEN 'is' THEN 'Non-music sound recording'
>   WHEN 'jm' THEN 'Music sound recording'
>   WHEN 'jc' THEN 'Music sound recording'
>   WHEN 'ji' THEN 'Music sound recording'
>   WHEN 'kc' THEN '2D image'
>   WHEN 'km' THEN '2D image'
>   WHEN 'ma' THEN 'Computer file'
>   WHEN 'mi' THEN 'Computer file'
>   WHEN 'mm' THEN 'Computer file'
>   WHEN 'ms' THEN 'Computer file'
>   WHEN 'oc' THEN 'Kit'
>   WHEN 'om' THEN 'Kit'
>   WHEN 'pc' THEN 'Mixed material'
>   WHEN 'pm' THEN 'Mixed material'
>   WHEN 'rc' THEN '3D Object'
>   WHEN 'rm' THEN '3D Object'
>   WHEN 'tc' THEN 'Manuscript'
>   WHEN 'tm' THEN 'Manuscript'
>   ELSE 'unknown' END AS Type,
> COUNT(DISTINCT biblionumber) AS Titles,
> COUNT(itemnumber) AS Volumes
> FROM biblio_metadata
> LEFT JOIN items USING (biblionumber)
> WHERE
>  ExtractValue(metadata,'//datafield[@tag="942"]/subfield[@code="n"]')
> != 1
> GROUP BY Type
> WITH ROLLUP
>
> which generates the following
>
> TypeTitlesVolumes
> 2D image 65 92
> 3D Object 21 21
> Book 1193934 1329489
> Computer file 630 1555
> Journal 38568 542424
> Kit 41 66
> Manuscript 5461 14120
> Map 4754 5770
> Mixed material 253 2517
> Music Score 11948 15757
> Music sound recording 9193 9766
> Non-music sound recording 613 818
> unknown 1018 214
> Video recording and motion pictures 15246 17090
> 1281745 1939699
> titles is the number of bib records and volumes are the number of items
> attached to a bib record.
>
> Best regards,
> Michael
> _
> *Michael J. Sutherland*
> University Libraries
> Virginia Tech
> sudrl...@vt.edu | 540.231.9669 <+15402319669>
>
>
>
> On Wed, May 27, 2020 at 2:33 PM Elaine Bradtke  

Re: [Koha] Report help needed

[Michael Sutherland]

Elaine,

Maybe this would be helpful for those codes -
https://www.loc.gov/marc/bibliographic/bdleader.html

And, to break it down for those that do not know and are interested, (at
the risk of condescending, which is not my intention, please, please, I
hope you do not take it that way)

in the expression - SUBSTRING(ExtractValue(metadata,'//leader'),7,1) AS
"Position06"

you are wanting to extract a piece (substring) from the entire leader
string - metadata,'//leader' - the 7 is the value that specifies the
initial position from which the characters can be extracted. The first
position of the expression starts with 1. But, in MARC the first position
is 0 (zero). The next value is a positive integer value that specifies the
ending limit and determines how many characters are going to be
extracted from the given expression in this case '1', which will give you
the single letter code in the leader 06, or the seventh position, however
you choose to look at it.

So, in collaboration with ByWater Solutions, I have co-written the
following report, which I use to report collection statistics by itemtype
to ACRL and ARL on our collection sans suppressed records from the OPAC. It
takes the combination of the leader 06 and 07 to determine itemtype and
count them.

SELECT
CASE SUBSTR(metadata,282,2)
  WHEN ' m' THEN 'Book'
  WHEN '2m' THEN 'Book'
  WHEN 'am' THEN 'Book'
  WHEN 'aa' THEN 'Book'
  WHEN 'ac' THEN 'Book'
  WHEN 'ad' THEN 'Book'
  WHEN 'ai' THEN 'Book'
  WHEN 'as' THEN 'Journal'
  WHEN 'cc' THEN 'Music Score'
  WHEN 'cm' THEN 'Music Score'
  WHEN 'dm' THEN 'Music Score'
  WHEN 'ei' THEN 'Map'
  WHEN 'em' THEN 'Map'
  WHEN 'es' THEN 'Map'
  WHEN 'ga' THEN 'Video recording and motion pictures'
  WHEN 'gs' THEN 'Video recording and motion pictures'
  WHEN 'gc' THEN 'Video recording and motion pictures'
  WHEN 'gi' THEN 'Video recording and motion pictures'
  WHEN 'gm' THEN 'Video recording and motion pictures'
  WHEN 'ic' THEN 'Non-music sound recording'
  WHEN 'im' THEN 'Non-music sound recording'
  WHEN 'is' THEN 'Non-music sound recording'
  WHEN 'jm' THEN 'Music sound recording'
  WHEN 'jc' THEN 'Music sound recording'
  WHEN 'ji' THEN 'Music sound recording'
  WHEN 'kc' THEN '2D image'
  WHEN 'km' THEN '2D image'
  WHEN 'ma' THEN 'Computer file'
  WHEN 'mi' THEN 'Computer file'
  WHEN 'mm' THEN 'Computer file'
  WHEN 'ms' THEN 'Computer file'
  WHEN 'oc' THEN 'Kit'
  WHEN 'om' THEN 'Kit'
  WHEN 'pc' THEN 'Mixed material'
  WHEN 'pm' THEN 'Mixed material'
  WHEN 'rc' THEN '3D Object'
  WHEN 'rm' THEN '3D Object'
  WHEN 'tc' THEN 'Manuscript'
  WHEN 'tm' THEN 'Manuscript'
  ELSE 'unknown' END AS Type,
COUNT(DISTINCT biblionumber) AS Titles,
COUNT(itemnumber) AS Volumes
FROM biblio_metadata
LEFT JOIN items USING (biblionumber)
WHERE
 ExtractValue(metadata,'//datafield[@tag="942"]/subfield[@code="n"]')
!= 1
GROUP BY Type
WITH ROLLUP

which generates the following

TypeTitlesVolumes
2D image 65 92
3D Object 21 21
Book 1193934 1329489
Computer file 630 1555
Journal 38568 542424
Kit 41 66
Manuscript 5461 14120
Map 4754 5770
Mixed material 253 2517
Music Score 11948 15757
Music sound recording 9193 9766
Non-music sound recording 613 818
unknown 1018 214
Video recording and motion pictures 15246 17090
1281745 1939699
titles is the number of bib records and volumes are the number of items
attached to a bib record.

Best regards,
Michael
_
*Michael J. Sutherland*
University Libraries
Virginia Tech
sudrl...@vt.edu | 540.231.9669 <+15402319669>



On Wed, May 27, 2020 at 2:33 PM Elaine Bradtke  wrote:

> Yes, I think I've got it now.  Thanks for your help.  The hard part was
> figuring out where to find the itemtype in the bib record.
> SELECT biblio.biblionumber,
> SUBSTRING(ExtractValue(metadata,'//leader'),7,1) AS "Position06"
> FROM biblio
> LEFT JOIN biblio_metadata USING (biblionumber)
> WHERE ExtractValue( metadata, '//datafield[@tag="942"]/subfield[@code="c"]'
> ) = <>
> Elaine Bradtke
> VWML
> English Folk Dance and Song Society | http://www.efdss.org
> Cecil Sharp House, 2 Regent's Park Road, London NW1 7AY
> Tel+44 (0) 20 7485 2206 (This number is for the English Folk Dance and
> Song Society in London, England. If you wish to phone me personally, send
> an e-mail first. I work off site)
> --
> Registered Company No. 297142
> Charity Registered in England and Wales No. 305999
>
>
> On Wed, May 27, 2020 at 10:50 AM Elaine Bradtke  wrote:
>
> > It doesn't throw up error messages, but what I really need is a list of
> > biblio numbers, with the info in position 6 of the leader and the
> itemtype
> > from the bib record, but also choose a specific itemtype (not books).
> I'm
> > going to try to mash up what you did with another 

Re: [Koha] Report help needed

[Elaine Bradtke]

Yes, I think I've got it now.  Thanks for your help.  The hard part was
figuring out where to find the itemtype in the bib record.
SELECT biblio.biblionumber,
SUBSTRING(ExtractValue(metadata,'//leader'),7,1) AS "Position06"
FROM biblio
LEFT JOIN biblio_metadata USING (biblionumber)
WHERE ExtractValue( metadata, '//datafield[@tag="942"]/subfield[@code="c"]'
) = <>
Elaine Bradtke
VWML
English Folk Dance and Song Society | http://www.efdss.org
Cecil Sharp House, 2 Regent's Park Road, London NW1 7AY
Tel+44 (0) 20 7485 2206 (This number is for the English Folk Dance and
Song Society in London, England. If you wish to phone me personally, send
an e-mail first. I work off site)
--
Registered Company No. 297142
Charity Registered in England and Wales No. 305999


On Wed, May 27, 2020 at 10:50 AM Elaine Bradtke  wrote:

> It doesn't throw up error messages, but what I really need is a list of
> biblio numbers, with the info in position 6 of the leader and the itemtype
> from the bib record, but also choose a specific itemtype (not books).  I'm
> going to try to mash up what you did with another report and see what
> happens.
> Elaine Bradtke
> VWML
> English Folk Dance and Song Society | http://www.efdss.org
> Cecil Sharp House, 2 Regent's Park Road, London NW1 7AY
> Tel+44 (0) 20 7485 2206 (This number is for the English Folk Dance and
> Song Society in London, England. If you wish to phone me personally, send
> an e-mail first. I work off site)
> --
> Registered Company No. 297142
> Charity Registered in England and Wales No. 305999
>
>
> On Wed, May 27, 2020 at 1:51 AM Jonathan Druart <
> jonathan.dru...@bugs.koha-community.org> wrote:
>
>> Hi Elaine,
>>
>> The biblioitems.marcxml field has been moved to biblio_metadata.metadata.
>>
>> Does this query work as you want:
>> SELECT CONCAT('> href=\"/cgi-bin/koha/catalogue/detail.pl?biblionumber=
>> ',b.biblionumber,'\">',b.biblionumber,'')
>>  AS biblionumber
>> FROM biblio b
>> LEFT JOIN biblioitems bi ON b.biblionumber= bi.biblionumber
>> LEFT JOIN biblio_metadata bm ON b.biblionumber=bm.biblionumber
>> WHERE bi.itemtype = 'BK' AND SUBSTR(bm.metadata,INSTR(bm.metadata,
>> "")+8+6,1) = 'a';
>>
>> Regards,
>> Jonathan
>>
>> Le mar. 26 mai 2020 à 20:35, Elaine Bradtke  a écrit :
>> >
>> > What I'd like it to do - give a list of biblio numbers for records with
>> a
>> > particular itemtype, and the information found in the Leader *06 - Type
>> of
>> > record *field.
>> > I found the following in the reports library as something that could be
>> > modified to fit my needs.  But it doesn't work  I get the following
>> > message: Unknown column 'marcxml' in 'field list'
>> > Please check the log for further details.
>> > Also, I don't understand how to indicate Leader position 06
>> >
>> >  SELECT CONCAT('> detail.pl?biblionumber=
>> > ',biblionumber,'\">',biblionumber,'')
>> >
>> > AS biblionumber
>> >
>> > FROM biblioitems,
>> >
>> > (SELECT biblioitemnumber, SUBSTR(marcxml,INSTR(marcxml,
>> "")+8+6,1)
>> >
>> > AS leader6 FROM biblioitems)
>> >
>> > AS leaders
>> >
>> > WHERE biblioitems.biblioitemnumber=leaders.biblioitemnumber AND
>> > leaders.leader6 = 'a'
>> >
>> > AND itemtype = <>
>> >
>> > Any help would be appreciated
>> >
>> > stay safe,
>> >
>> > Elaine Bradtke
>> > VWML
>> > English Folk Dance and Song Society | http://www.efdss.org
>> > Cecil Sharp House, 2 Regent's Park Road, London NW1 7AY
>> > Tel+44 (0) 20 7485 2206 (This number is for the English Folk Dance
>> and
>> > Song Society in London, England. If you wish to phone me personally,
>> send
>> > an e-mail first. I work off site)
>> >
>> --
>> > Registered Company No. 297142
>> > Charity Registered in England and Wales No. 305999
>> > ___
>> >
>> > 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

[Koha] SQL Help

[Himanshu Aggarwal]

Can someone suggest report for getting accession number with a range from
and to

-- 
Assistant Librarian,
___

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

Re: [Koha] SQL help?

[Holger Meissner]

Hi Kerrie,

if you don't mind hardcoding the borrowernumbers this should do the trick.

SELECT
b.borrowernumber AS 'Borrowernumber',
CONCAT(b.firstname, ' ', b.surname) AS 'Name',
COUNT(i.issue_id) AS 'Outstanding loans'
FROM borrowers b
LEFT JOIN issues i ON b.borrowernumber = i.borrowernumber
WHERE b.borrowernumber IN (
123,
456,
789
)
GROUP BY b.borrowernumber

Alternatively, you could use a BETWEEN in the WHERE clause, if the 
borrowernumbers are consecutive.

Regards,
Holger

-Ursprüngliche Nachricht-
Von: Koha  Im Auftrag von Jonathan Druart
Gesendet: Mittwoch, 27. Mai 2020 11:00
An: Kerrie Stevens 
Cc: koha@lists.katipo.co.nz
Betreff: Re: [Koha] SQL help?

Hello Kerrie,

I am not sure this is what you want, but if you create a SQL report with this 
query you will see the patrons with outstanding loans:

SELECT b.borrowernumber, concat(b.surname, ' ', b.firstname), count(*) FROM 
issues LEFT JOIN borrowers b on b.borrowernumber=issues.borrowernumber
GROUP BY b.borrowernumber;

Regards,
Jonathan

Le mer. 27 mai 2020 à 02:24, Kerrie Stevens  a écrit :
>
> Every year, we need to confirm graduating students have no outstanding loans 
> so they can graduate. And every year I think I must ask for some help in 
> doing it more efficiently than searching borrower numbers one by one...
> I'd like to be able to copy & paste a number of borrower numbers into search 
> box and get back a table showing those with outstanding loans:
> (borrower number, name, and number of current loans) Can cutting & 
> pasting or searching on multiple borrower numbers be done in SQL reports?
> This is way beyond my basic level of SQL understanding, so any 
> advice/assistance would be greatly appreciated.
>
> Thank you and have a great day wherever you may be!
>
>
> Kerrie Stevens  AALIA(CP), MAppSci(LibMgt), BBus(Info) Director 
> of Library Services, Alphacrucis College Librarian - Melbourne Campus 
> HDR Liaison Librarian ___
>
> 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
___

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

Re: [Koha] SQL help?

[Jonathan Druart]

Hello Kerrie,

I am not sure this is what you want, but if you create a SQL report
with this query you will see the patrons with outstanding loans:

SELECT b.borrowernumber, concat(b.surname, ' ', b.firstname), count(*)
FROM issues
LEFT JOIN borrowers b on b.borrowernumber=issues.borrowernumber
GROUP BY b.borrowernumber;

Regards,
Jonathan

Le mer. 27 mai 2020 à 02:24, Kerrie Stevens  a écrit :
>
> Every year, we need to confirm graduating students have no outstanding loans 
> so they can graduate. And every year I think I must ask for some help in 
> doing it more efficiently than searching borrower numbers one by one...
> I'd like to be able to copy & paste a number of borrower numbers into search 
> box and get back a table showing those with outstanding loans:
> (borrower number, name, and number of current loans)
> Can cutting & pasting or searching on multiple borrower numbers be done in 
> SQL reports?
> This is way beyond my basic level of SQL understanding, so any 
> advice/assistance would be greatly appreciated.
>
> Thank you and have a great day wherever you may be!
>
>
> Kerrie Stevens  AALIA(CP), MAppSci(LibMgt), BBus(Info)
> Director of Library Services, Alphacrucis College
> Librarian - Melbourne Campus
> HDR Liaison Librarian
> ___
>
> 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] Report help needed

[Jonathan Druart]

Hi Elaine,

The biblioitems.marcxml field has been moved to biblio_metadata.metadata.

Does this query work as you want:
SELECT CONCAT('',b.biblionumber,'')
 AS biblionumber
FROM biblio b
LEFT JOIN biblioitems bi ON b.biblionumber= bi.biblionumber
LEFT JOIN biblio_metadata bm ON b.biblionumber=bm.biblionumber
WHERE bi.itemtype = 'BK' AND SUBSTR(bm.metadata,INSTR(bm.metadata,
"")+8+6,1) = 'a';

Regards,
Jonathan

Le mar. 26 mai 2020 à 20:35, Elaine Bradtke  a écrit :
>
> What I'd like it to do - give a list of biblio numbers for records with a
> particular itemtype, and the information found in the Leader *06 - Type of
> record *field.
> I found the following in the reports library as something that could be
> modified to fit my needs.  But it doesn't work  I get the following
> message: Unknown column 'marcxml' in 'field list'
> Please check the log for further details.
> Also, I don't understand how to indicate Leader position 06
>
>  SELECT CONCAT(' ',biblionumber,'\">',biblionumber,'')
>
> AS biblionumber
>
> FROM biblioitems,
>
> (SELECT biblioitemnumber, SUBSTR(marcxml,INSTR(marcxml, "")+8+6,1)
>
> AS leader6 FROM biblioitems)
>
> AS leaders
>
> WHERE biblioitems.biblioitemnumber=leaders.biblioitemnumber AND
> leaders.leader6 = 'a'
>
> AND itemtype = <>
>
> Any help would be appreciated
>
> stay safe,
>
> Elaine Bradtke
> VWML
> English Folk Dance and Song Society | http://www.efdss.org
> Cecil Sharp House, 2 Regent's Park Road, London NW1 7AY
> Tel+44 (0) 20 7485 2206 (This number is for the English Folk Dance and
> Song Society in London, England. If you wish to phone me personally, send
> an e-mail first. I work off site)
> --
> Registered Company No. 297142
> Charity Registered in England and Wales No. 305999
> ___
>
> 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