Skillnad mellan versioner av "Lånestatistik Bibliotek Mellansjö"
Hoppa till navigering
Hoppa till sök
Rad 86: | Rad 86: | ||
GROUP BY homebranch | GROUP BY homebranch | ||
+ | </pre> | ||
+ | |||
+ | ==Fråga 11 Olika utlån== | ||
+ | |||
+ | <pre> | ||
+ | SELECT | ||
+ | branchname AS Bibliotek, | ||
+ | COUNT(CASE WHEN type IN ('issue','renew') AND itemtype IN ('BARNBOK','BARNKORT','BOKCDBARN','BOKDAISYBA','BOKMP3BARN','BARN LJUD','BARNMP3','BARNTAL') THEN 1 END) 'Utlån för barn', | ||
+ | COUNT(CASE WHEN type IN ('issue','renew') AND itemtype IN ('BARNTAL','BOKCD','BOKCDBARN','BOKDAISYBA','BOKMP3','BOKMP3BARN','DAISY','STORSTIL') THEN 1 WHEN type IN ('issue','renew') AND location IN ('Lattlast','Appelhyllan') THEN 1 END) 'Utlån läsnedsättning' | ||
+ | FROM statistics | ||
+ | |||
+ | LEFT JOIN branches ON (statistics.branch=branches.branchcode) | ||
+ | LEFT JOIN library_groups ON (statistics.branch=library_groups.branchcode) | ||
+ | |||
+ | WHERE statistics.branch = <<Bibliotek|branches>> AND statistics.itemtype IS NOT NULL AND statistics.datetime BETWEEN <<Datum från|date>>-INTERVAL 1 DAY AND <<Datum till |date>>+INTERVAL 1 DAY | ||
+ | |||
+ | |||
+ | |||
+ | GROUP BY branch | ||
+ | |||
+ | </pre> | ||
+ | |||
+ | ==Fråga 12 Svenska== | ||
+ | |||
+ | <pre> | ||
+ | SELECT | ||
+ | branchname AS Bibliotek, | ||
+ | COUNT(*) AS 'Antal svenska tilar' | ||
+ | |||
+ | FROM biblio_metadata | ||
+ | |||
+ | LEFT JOIN biblioitems ON (biblio_metadata.biblionumber=biblioitems.biblionumber) | ||
+ | LEFT JOIN items On (biblioitems.biblionumber=items.biblionumber) | ||
+ | LEFT JOIN branches ON (items.homebranch=branches.branchcode) | ||
+ | LEFT JOIN library_groups ON (items.homebranch=library_groups.branchcode) | ||
+ | LEFT JOIN library_groups kommun ON (library_groups.parent_id=kommun.id) | ||
+ | |||
+ | WHERE (SUBSTRING(ExtractValue(metadata, '//controlfield[@tag="008"]'), 36, 3) = 'swe' OR | ||
+ | SUBSTRING(ExtractValue(metadata, '//controlfield[@tag="008"]'), 36, 3) = '') AND | ||
+ | items.homebranch=<<Bibliotek|branches>> | ||
+ | |||
+ | </pre> | ||
+ | |||
+ | ==Fråga 12 Minoritetsspråk== | ||
+ | |||
+ | <pre> | ||
+ | SELECT | ||
+ | branchname AS Bibliotek, | ||
+ | COUNT(*) AS 'Antal titlar på minoritetsspråk' | ||
+ | |||
+ | FROM biblio_metadata | ||
+ | |||
+ | LEFT JOIN biblioitems ON (biblio_metadata.biblionumber=biblioitems.biblionumber) | ||
+ | LEFT JOIN items On (biblioitems.biblionumber=items.biblionumber) | ||
+ | LEFT JOIN branches ON (items.homebranch=branches.branchcode) | ||
+ | LEFT JOIN library_groups ON (items.homebranch=library_groups.branchcode) | ||
+ | LEFT JOIN library_groups kommun ON (library_groups.parent_id=kommun.id) | ||
+ | |||
+ | WHERE (SUBSTRING(ExtractValue(metadata, '//controlfield[@tag="008"]'), 36, 3) = 'fin' OR | ||
+ | SUBSTRING(ExtractValue(metadata, '//controlfield[@tag="008"]'), 36, 3) = 'smi' OR | ||
+ | SUBSTRING(ExtractValue(metadata, '//controlfield[@tag="008"]'), 36, 3) = '9mk' OR | ||
+ | SUBSTRING(ExtractValue(metadata, '//controlfield[@tag="008"]'), 36, 3) = 'rom' OR | ||
+ | SUBSTRING(ExtractValue(metadata, '//controlfield[@tag="008"]'), 36, 3) = 'yid' ) AND | ||
+ | items.homebranch=<<Bibliotek|branches>> | ||
+ | </pre> | ||
+ | |||
+ | ==Fråga 12 Utländska== | ||
+ | |||
+ | <pre> | ||
+ | SELECT | ||
+ | branchname AS Bibliotek, | ||
+ | COUNT(*) AS 'Antal utländska titlar' | ||
+ | |||
+ | FROM biblio_metadata | ||
+ | |||
+ | LEFT JOIN biblioitems ON (biblio_metadata.biblionumber=biblioitems.biblionumber) | ||
+ | LEFT JOIN items On (biblioitems.biblionumber=items.biblionumber) | ||
+ | LEFT JOIN branches ON (items.homebranch=branches.branchcode) | ||
+ | LEFT JOIN library_groups ON (items.homebranch=library_groups.branchcode) | ||
+ | LEFT JOIN library_groups kommun ON (library_groups.parent_id=kommun.id) | ||
+ | |||
+ | WHERE SUBSTRING(ExtractValue(metadata, '//controlfield[@tag="008"]'), 36, 3) != 'swe' AND | ||
+ | SUBSTRING(ExtractValue(metadata, '//controlfield[@tag="008"]'), 36, 3) != 'fin' AND | ||
+ | SUBSTRING(ExtractValue(metadata, '//controlfield[@tag="008"]'), 36, 3) != 'smi' AND | ||
+ | SUBSTRING(ExtractValue(metadata, '//controlfield[@tag="008"]'), 36, 3) != '9mk' AND | ||
+ | SUBSTRING(ExtractValue(metadata, '//controlfield[@tag="008"]'), 36, 3) != 'rom' AND | ||
+ | SUBSTRING(ExtractValue(metadata, '//controlfield[@tag="008"]'), 36, 3) != 'yid' AND | ||
+ | SUBSTRING(ExtractValue(metadata, '//controlfield[@tag="008"]'), 36, 3) != '' AND | ||
+ | items.homebranch=<<Bibliotek|branches>> | ||
+ | </pre> | ||
+ | |||
+ | ==Fråga 14 Lånestatistik== | ||
+ | |||
+ | <pre> | ||
+ | SELECT | ||
+ | branchname AS Bibliotek, | ||
+ | COUNT(CASE WHEN type = 'issue' AND itemtype IN ('BARNBOK','BARNKORT','BOK','BOKCD','BOKCDBARN','BOKDAISY','BOKDAISYBA','BOKMP3','BOKMP3BARN','KORTLON','LANGLAN','PAKET','REFERENS','SPRAKKURS','STORSTIL') THEN 1 END) 'Utlån tryckt bok', | ||
+ | COUNT(CASE WHEN type = 'renew' AND itemtype IN ('BARNBOK','BARNKORT','BOK','BOKCD','BOKCDBARN','BOKDAICY','BOKDAISYBA','BOKMP3','BOKMP3BARN','KORTLON','LANGLAN','PAKET','REFERENS','SPRAKKURS','STORSTIL') THEN 1 END) 'Omlån tryckt bok', | ||
+ | COUNT(CASE WHEN type = 'issue' AND itemtype IN ('LAROMDL','LAROMTERM') THEN 1 END) 'Utlån läromedel', | ||
+ | COUNT(CASE WHEN type = 'renew' AND itemtype IN ('LAROMDL','LAROMTERM') THEN 1 END) 'Omlån läromedel', | ||
+ | COUNT(CASE WHEN type = 'issue' AND itemtype IN ('BARN LJUD','BARNMP3','LJUDBOK','MP3') THEN 1 END) 'Utlån ljudböcker', | ||
+ | COUNT(CASE WHEN type = 'renew' AND itemtype IN ('BARN LJUD','BARNMP3','LJUDBOK','MP3') THEN 1 END) 'Omlån ljudböcker', | ||
+ | COUNT(CASE WHEN type = 'issue' AND itemtype IN ('BARNTAL','DAISY') THEN 1 END) 'Utlån talböcker daisy', | ||
+ | COUNT(CASE WHEN type = 'renew' AND itemtype IN ('BARNTAL','DAISY') THEN 1 END) 'Omlån talböcker daisy', | ||
+ | COUNT(CASE WHEN type = 'issue' AND itemtype IN ('BARN TIDSK','TIDSKRIFT') THEN 1 END) 'Utlån tidskrifter', | ||
+ | COUNT(CASE WHEN type = 'renew' AND itemtype IN ('BARN TIDSK','TIDSKRIFT') THEN 1 END) 'Omlån tidskrifter', | ||
+ | COUNT(CASE WHEN type = 'issue' AND itemtype IN ('MUSIKCD') THEN 1 END) 'Utlån musik', | ||
+ | COUNT(CASE WHEN type = 'renew' AND itemtype IN ('MUSIKCD') THEN 1 END) 'Omlån musik', | ||
+ | COUNT(CASE WHEN type = 'issue' AND itemtype IN ('BLURAY','FILM','VHS') THEN 1 END) 'Utlån film', | ||
+ | COUNT(CASE WHEN type = 'renew' AND itemtype IN ('BLURAY','FILM','VHS') THEN 1 END) 'Omlån film', | ||
+ | COUNT(CASE WHEN type = 'issue' AND itemtype IN ('KARTOR') THEN 1 END) 'Utlån kartor', | ||
+ | COUNT(CASE WHEN type = 'renew' AND itemtype IN ('KARTOR') THEN 1 END) 'Omlån kartor', | ||
+ | COUNT(CASE WHEN type = 'issue' AND itemtype IN ('MUSIK','MUSIKBARN') THEN 1 END) 'Utlån noter', | ||
+ | COUNT(CASE WHEN type = 'renew' AND itemtype IN ('MUSIK','MUSIKBARN') THEN 1 END) 'Omlån noter', | ||
+ | COUNT(CASE WHEN type = 'issue' AND itemtype IN ('TV-SPEL') THEN 1 END) 'Utlån TV-spel', | ||
+ | COUNT(CASE WHEN type = 'renew' AND itemtype IN ('TV-SPEL') THEN 1 END) 'Omlån TV-spel', | ||
+ | COUNT(CASE WHEN type = 'issue' AND itemtype IN ('ARTIKEL','BLANDAT','FOREMAL','SUFRPLATTA','VECKOLAN','X') THEN 1 END) 'Utlån övrigt', | ||
+ | COUNT(CASE WHEN type = 'renew' AND itemtype IN ('ARTIKEL','BLANDAT','FOREMAL','SUFRPLATTA','VECKOLAN','X') THEN 1 END) 'Omlån övrigt' | ||
+ | |||
+ | FROM statistics | ||
+ | |||
+ | LEFT JOIN branches ON (statistics.branch=branches.branchcode) | ||
+ | LEFT JOIN library_groups ON (statistics.branch=library_groups.branchcode) | ||
+ | |||
+ | WHERE statistics.branch=<<Bibliotek|branches>> AND statistics.itemtype IS NOT NULL AND statistics.datetime BETWEEN <<Datum från|date>>-INTERVAL 1 DAY AND <<Datum till |date>>+INTERVAL 1 DAY | ||
+ | |||
+ | |||
+ | GROUP BY branch | ||
+ | |||
+ | ORDER BY branchname | ||
+ | |||
+ | </pre> | ||
+ | |||
+ | ==Fråga 16 Fjärrlån== | ||
+ | |||
+ | <pre> | ||
+ | SELECT | ||
+ | branchname, | ||
+ | COUNT(CASE WHEN type = 'issue' AND borrowers.categorycode IN ('BIBLIOTEK') THEN 1 END) 'Fjärrutlån', | ||
+ | COUNT(CASE WHEN type = 'issue' AND itemtype IN ('FJARRLAN') THEN 1 END) 'Fjärrinlån' | ||
+ | |||
+ | FROM statistics | ||
+ | |||
+ | LEFT JOIN borrowers ON (statistics.borrowernumber=borrowers.borrowernumber) | ||
+ | LEFT JOIN branches ON (statistics.branch=branches.branchcode) | ||
+ | LEFT JOIN library_groups ON (statistics.branch=library_groups.branchcode) | ||
+ | |||
+ | WHERE statistics.branch=<<Bibliotek|branches>> AND statistics.itemtype IS NOT NULL AND statistics.datetime BETWEEN <<Datum från|date>>-INTERVAL 1 DAY AND <<Datum till |date>>+INTERVAL 1 DAY | ||
+ | |||
+ | |||
+ | GROUP BY branch | ||
+ | </pre> | ||
+ | |||
+ | ==Fråga 19 Kvinnor == | ||
+ | |||
+ | <pre> | ||
+ | SELECT | ||
+ | branches.branchname AS Bibliotek, | ||
+ | COUNT(DISTINCT statistics.borrowernumber) AS 'Aktiva låntagare kvinnor' | ||
+ | |||
+ | FROM statistics | ||
+ | LEFT JOIN borrowers ON (borrowers.borrowernumber=statistics.borrowernumber) | ||
+ | LEFT JOIN borrower_attributes ON (borrowers.borrowernumber=borrower_attributes.borrowernumber) | ||
+ | LEFT JOIN branches ON (statistics.branch=branches.branchcode) | ||
+ | |||
+ | WHERE statistics.datetime LIKE '2018%' AND code = 'PERSNUMMER' AND LEFT( RIGHT(attribute, 2), 1) % 2 = 0 AND statistics.branch=<<Bibliotek|branches>> | ||
+ | |||
+ | GROUP BY statistics.branch | ||
+ | </pre> | ||
+ | |||
+ | ==Fråga 19 Män== | ||
+ | |||
+ | <pre> | ||
+ | SELECT | ||
+ | branches.branchname AS Bibliotek, | ||
+ | COUNT(DISTINCT statistics.borrowernumber) AS 'Aktiva låntagare kvinnor' | ||
+ | |||
+ | FROM statistics | ||
+ | LEFT JOIN borrowers ON (borrowers.borrowernumber=statistics.borrowernumber) | ||
+ | LEFT JOIN borrower_attributes ON (borrowers.borrowernumber=borrower_attributes.borrowernumber) | ||
+ | LEFT JOIN branches ON (statistics.branch=branches.branchcode) | ||
+ | |||
+ | WHERE statistics.datetime LIKE '2018%' AND code = 'PERSNUMMER' AND LEFT( RIGHT(attribute, 2), 1) % 2 = 1 AND statistics.branch=<<Bibliotek|branches>> | ||
+ | |||
+ | GROUP BY statistics.branch | ||
</pre> | </pre> |
Versionen från 28 januari 2019 kl. 13.26
Statistikrapporter som Bibliotek Mellansjö skapat. Se dem som komplement/alternativ till Hyltes rapporter!
Fråga 10 Fysiskt bestånd
SELECT branchname, COUNT(CASE WHEN itype IN ('BARNBOK','BARNKORT','BOK','BOKCD','BOKCDBARN','BOKDAISY','BOKDAISYBA','BOKMP3','BOKMP3BARN','KORTLON','LANGLAN','PAKET','REFERENS','SPRAKKURS','STORSTIL') THEN 1 END) 'Bestånd tryckt bok', COUNT(CASE WHEN itype IN ('LAROMDL','LAROMTERM') THEN 1 END) 'Bestånd läromedel', COUNT(CASE WHEN itype IN ('BARN LJUD','BARNMP3','LJUDBOK','MP3') THEN 1 END) 'Bestånd ljudböcker', COUNT(CASE WHEN itype IN ('BARNTAL','DAISY') THEN 1 END) 'Bestånd talböcker daisy', COUNT(CASE WHEN itype IN ('MUSIKCD') THEN 1 END) 'Bestånd musik', COUNT(CASE WHEN itype IN ('BLURAY','FILM','VHS') THEN 1 END) 'Bestånd film', COUNT(CASE WHEN itype IN ('KARTOR') THEN 1 END) 'Bestånd kartor', COUNT(CASE WHEN itype IN ('MUSIK') THEN 1 END) 'Bestånd noter', COUNT(CASE WHEN itype IN ('TV-SPEL') THEN 1 END) 'Bestånd TV-spel', COUNT(CASE WHEN itype IN ('ARTIKEL','BLANDAT','FOREMAL','SUFRPLATTA','VECKOLAN','X') THEN 1 END) 'Bestånd övrigt' FROM items LEFT JOIN branches ON (items.homebranch=branches.branchcode) LEFT JOIN library_groups ON (items.homebranch=library_groups.branchcode) WHERE items.homebranch = <<Bibliotek|branches>>
Fråga 10 Fysiskt nyförvärv
SELECT branchname, COUNT(CASE WHEN itype IN ('BARNBOK','BARNKORT','BOK','BOKCD','BOKCDBARN','BOKDAISY','BOKDAISYBA','BOKMP3','BOKMP3BARN','KORTLON','LANGLAN','PAKET','REFERENS','SPRAKKURS','STORSTIL') THEN 1 END) 'Nyförvärv tryckt bok', COUNT(CASE WHEN itype IN ('LAROMDL','LAROMTERM') THEN 1 END) 'Nyförvärv läromedel', COUNT(CASE WHEN itype IN ('BARN LJUD','BARNMP3','LJUDBOK','MP3') THEN 1 END) 'Nyförvärv ljudböcker', COUNT(CASE WHEN itype IN ('BARNTAL','DAISY') THEN 1 END) 'Nyförvärv talböcker daisy', COUNT(CASE WHEN itype IN ('MUSIKCD') THEN 1 END) 'Nyförvärv musik', COUNT(CASE WHEN itype IN ('BLURAY','FILM','VHS') THEN 1 END) 'Nyförvärv film', COUNT(CASE WHEN itype IN ('KARTOR') THEN 1 END) 'Nyförvärv kartor', COUNT(CASE WHEN itype IN ('MUSIK') THEN 1 END) 'Nyförvärv noter', COUNT(CASE WHEN itype IN ('TV-SPEL') THEN 1 END) 'Nyförvärv TV-spel', COUNT(CASE WHEN itype IN ('ARTIKEL','BLANDAT','FOREMAL','SUFRPLATTA','VECKOLAN','X') THEN 1 END) 'Nyförvärv övrigt' FROM items LEFT JOIN branches ON (items.homebranch=branches.branchcode) LEFT JOIN library_groups ON (items.homebranch=library_groups.branchcode) WHERE items.homebranch = <<Bibliotek|branches>> AND items.dateaccessioned BETWEEN <<Datum från|date>>-INTERVAL 1 DAY AND <<Datum till |date>>+INTERVAL 1 DAY GROUP BY homebranch
Fråga 11 olika bestånd
SELECT COUNT(CASE WHEN itype IN ('BARNBOK','BARNKORT','BOKCDBARN','BOKDAISYBA','BOKMP3BARN','BARN LJUD','BARNMP3','BARNTAL','MUSIKBARN','MUSIKBARN') THEN 1 END) 'Bestånd för barn och unga', COUNT(CASE WHEN location IN ('Skonlitteratur','Deckare','Deckare & Thriller','Fantasy','Fantasy/SF','Noveller') THEN 1 END) 'Bestånd av skönlitteratur', COUNT(CASE WHEN location IN ('REFERENS') THEN 1 END) 'Antal referensexemplar', COUNT(CASE WHEN itype IN ('BARNTAL','BOKCD','BOKCDBARN','BOKDAISYBA','BOKMP3','BOKMP3BARN','DAISY','STORSTIL') THEN 1 WHEN location IN ('Lattlast','Appelhyllan') THEN 1 END) 'Bestånd läsnedsättning' FROM items LEFT JOIN branches ON (items.homebranch=branches.branchcode) LEFT JOIN library_groups ON (items.homebranch=library_groups.branchcode) WHERE items.homebranch = <<Bibliotek|branches>>
Fråga 11 Olika nyförvärv
SELECT branchname, COUNT(CASE WHEN itype IN ('BARN LJUD','BARN TIDSK','BARNBOK','BARNKORT','BOKCDBARN','BOKDAISYBA','BOKMP3BARN') THEN 1 END) 'Nyförvärv tryckt för barn' FROM items LEFT JOIN branches ON (items.homebranch=branches.branchcode) LEFT JOIN library_groups ON (items.homebranch=library_groups.branchcode) LEFT JOIN library_groups kommun ON (library_groups.parent_id=kommun.id) WHERE items.homebranch IS NOT NULL AND items.dateaccessioned BETWEEN <<Datum från|date>>-INTERVAL 1 DAY AND <<Datum till |date>>+INTERVAL 1 DAY AND items.homebranch = <<Bibliotek|branches>> GROUP BY homebranch
Fråga 11 Olika utlån
SELECT branchname AS Bibliotek, COUNT(CASE WHEN type IN ('issue','renew') AND itemtype IN ('BARNBOK','BARNKORT','BOKCDBARN','BOKDAISYBA','BOKMP3BARN','BARN LJUD','BARNMP3','BARNTAL') THEN 1 END) 'Utlån för barn', COUNT(CASE WHEN type IN ('issue','renew') AND itemtype IN ('BARNTAL','BOKCD','BOKCDBARN','BOKDAISYBA','BOKMP3','BOKMP3BARN','DAISY','STORSTIL') THEN 1 WHEN type IN ('issue','renew') AND location IN ('Lattlast','Appelhyllan') THEN 1 END) 'Utlån läsnedsättning' FROM statistics LEFT JOIN branches ON (statistics.branch=branches.branchcode) LEFT JOIN library_groups ON (statistics.branch=library_groups.branchcode) WHERE statistics.branch = <<Bibliotek|branches>> AND statistics.itemtype IS NOT NULL AND statistics.datetime BETWEEN <<Datum från|date>>-INTERVAL 1 DAY AND <<Datum till |date>>+INTERVAL 1 DAY GROUP BY branch
Fråga 12 Svenska
SELECT branchname AS Bibliotek, COUNT(*) AS 'Antal svenska tilar' FROM biblio_metadata LEFT JOIN biblioitems ON (biblio_metadata.biblionumber=biblioitems.biblionumber) LEFT JOIN items On (biblioitems.biblionumber=items.biblionumber) LEFT JOIN branches ON (items.homebranch=branches.branchcode) LEFT JOIN library_groups ON (items.homebranch=library_groups.branchcode) LEFT JOIN library_groups kommun ON (library_groups.parent_id=kommun.id) WHERE (SUBSTRING(ExtractValue(metadata, '//controlfield[@tag="008"]'), 36, 3) = 'swe' OR SUBSTRING(ExtractValue(metadata, '//controlfield[@tag="008"]'), 36, 3) = '') AND items.homebranch=<<Bibliotek|branches>>
Fråga 12 Minoritetsspråk
SELECT branchname AS Bibliotek, COUNT(*) AS 'Antal titlar på minoritetsspråk' FROM biblio_metadata LEFT JOIN biblioitems ON (biblio_metadata.biblionumber=biblioitems.biblionumber) LEFT JOIN items On (biblioitems.biblionumber=items.biblionumber) LEFT JOIN branches ON (items.homebranch=branches.branchcode) LEFT JOIN library_groups ON (items.homebranch=library_groups.branchcode) LEFT JOIN library_groups kommun ON (library_groups.parent_id=kommun.id) WHERE (SUBSTRING(ExtractValue(metadata, '//controlfield[@tag="008"]'), 36, 3) = 'fin' OR SUBSTRING(ExtractValue(metadata, '//controlfield[@tag="008"]'), 36, 3) = 'smi' OR SUBSTRING(ExtractValue(metadata, '//controlfield[@tag="008"]'), 36, 3) = '9mk' OR SUBSTRING(ExtractValue(metadata, '//controlfield[@tag="008"]'), 36, 3) = 'rom' OR SUBSTRING(ExtractValue(metadata, '//controlfield[@tag="008"]'), 36, 3) = 'yid' ) AND items.homebranch=<<Bibliotek|branches>>
Fråga 12 Utländska
SELECT branchname AS Bibliotek, COUNT(*) AS 'Antal utländska titlar' FROM biblio_metadata LEFT JOIN biblioitems ON (biblio_metadata.biblionumber=biblioitems.biblionumber) LEFT JOIN items On (biblioitems.biblionumber=items.biblionumber) LEFT JOIN branches ON (items.homebranch=branches.branchcode) LEFT JOIN library_groups ON (items.homebranch=library_groups.branchcode) LEFT JOIN library_groups kommun ON (library_groups.parent_id=kommun.id) WHERE SUBSTRING(ExtractValue(metadata, '//controlfield[@tag="008"]'), 36, 3) != 'swe' AND SUBSTRING(ExtractValue(metadata, '//controlfield[@tag="008"]'), 36, 3) != 'fin' AND SUBSTRING(ExtractValue(metadata, '//controlfield[@tag="008"]'), 36, 3) != 'smi' AND SUBSTRING(ExtractValue(metadata, '//controlfield[@tag="008"]'), 36, 3) != '9mk' AND SUBSTRING(ExtractValue(metadata, '//controlfield[@tag="008"]'), 36, 3) != 'rom' AND SUBSTRING(ExtractValue(metadata, '//controlfield[@tag="008"]'), 36, 3) != 'yid' AND SUBSTRING(ExtractValue(metadata, '//controlfield[@tag="008"]'), 36, 3) != '' AND items.homebranch=<<Bibliotek|branches>>
Fråga 14 Lånestatistik
SELECT branchname AS Bibliotek, COUNT(CASE WHEN type = 'issue' AND itemtype IN ('BARNBOK','BARNKORT','BOK','BOKCD','BOKCDBARN','BOKDAISY','BOKDAISYBA','BOKMP3','BOKMP3BARN','KORTLON','LANGLAN','PAKET','REFERENS','SPRAKKURS','STORSTIL') THEN 1 END) 'Utlån tryckt bok', COUNT(CASE WHEN type = 'renew' AND itemtype IN ('BARNBOK','BARNKORT','BOK','BOKCD','BOKCDBARN','BOKDAICY','BOKDAISYBA','BOKMP3','BOKMP3BARN','KORTLON','LANGLAN','PAKET','REFERENS','SPRAKKURS','STORSTIL') THEN 1 END) 'Omlån tryckt bok', COUNT(CASE WHEN type = 'issue' AND itemtype IN ('LAROMDL','LAROMTERM') THEN 1 END) 'Utlån läromedel', COUNT(CASE WHEN type = 'renew' AND itemtype IN ('LAROMDL','LAROMTERM') THEN 1 END) 'Omlån läromedel', COUNT(CASE WHEN type = 'issue' AND itemtype IN ('BARN LJUD','BARNMP3','LJUDBOK','MP3') THEN 1 END) 'Utlån ljudböcker', COUNT(CASE WHEN type = 'renew' AND itemtype IN ('BARN LJUD','BARNMP3','LJUDBOK','MP3') THEN 1 END) 'Omlån ljudböcker', COUNT(CASE WHEN type = 'issue' AND itemtype IN ('BARNTAL','DAISY') THEN 1 END) 'Utlån talböcker daisy', COUNT(CASE WHEN type = 'renew' AND itemtype IN ('BARNTAL','DAISY') THEN 1 END) 'Omlån talböcker daisy', COUNT(CASE WHEN type = 'issue' AND itemtype IN ('BARN TIDSK','TIDSKRIFT') THEN 1 END) 'Utlån tidskrifter', COUNT(CASE WHEN type = 'renew' AND itemtype IN ('BARN TIDSK','TIDSKRIFT') THEN 1 END) 'Omlån tidskrifter', COUNT(CASE WHEN type = 'issue' AND itemtype IN ('MUSIKCD') THEN 1 END) 'Utlån musik', COUNT(CASE WHEN type = 'renew' AND itemtype IN ('MUSIKCD') THEN 1 END) 'Omlån musik', COUNT(CASE WHEN type = 'issue' AND itemtype IN ('BLURAY','FILM','VHS') THEN 1 END) 'Utlån film', COUNT(CASE WHEN type = 'renew' AND itemtype IN ('BLURAY','FILM','VHS') THEN 1 END) 'Omlån film', COUNT(CASE WHEN type = 'issue' AND itemtype IN ('KARTOR') THEN 1 END) 'Utlån kartor', COUNT(CASE WHEN type = 'renew' AND itemtype IN ('KARTOR') THEN 1 END) 'Omlån kartor', COUNT(CASE WHEN type = 'issue' AND itemtype IN ('MUSIK','MUSIKBARN') THEN 1 END) 'Utlån noter', COUNT(CASE WHEN type = 'renew' AND itemtype IN ('MUSIK','MUSIKBARN') THEN 1 END) 'Omlån noter', COUNT(CASE WHEN type = 'issue' AND itemtype IN ('TV-SPEL') THEN 1 END) 'Utlån TV-spel', COUNT(CASE WHEN type = 'renew' AND itemtype IN ('TV-SPEL') THEN 1 END) 'Omlån TV-spel', COUNT(CASE WHEN type = 'issue' AND itemtype IN ('ARTIKEL','BLANDAT','FOREMAL','SUFRPLATTA','VECKOLAN','X') THEN 1 END) 'Utlån övrigt', COUNT(CASE WHEN type = 'renew' AND itemtype IN ('ARTIKEL','BLANDAT','FOREMAL','SUFRPLATTA','VECKOLAN','X') THEN 1 END) 'Omlån övrigt' FROM statistics LEFT JOIN branches ON (statistics.branch=branches.branchcode) LEFT JOIN library_groups ON (statistics.branch=library_groups.branchcode) WHERE statistics.branch=<<Bibliotek|branches>> AND statistics.itemtype IS NOT NULL AND statistics.datetime BETWEEN <<Datum från|date>>-INTERVAL 1 DAY AND <<Datum till |date>>+INTERVAL 1 DAY GROUP BY branch ORDER BY branchname
Fråga 16 Fjärrlån
SELECT branchname, COUNT(CASE WHEN type = 'issue' AND borrowers.categorycode IN ('BIBLIOTEK') THEN 1 END) 'Fjärrutlån', COUNT(CASE WHEN type = 'issue' AND itemtype IN ('FJARRLAN') THEN 1 END) 'Fjärrinlån' FROM statistics LEFT JOIN borrowers ON (statistics.borrowernumber=borrowers.borrowernumber) LEFT JOIN branches ON (statistics.branch=branches.branchcode) LEFT JOIN library_groups ON (statistics.branch=library_groups.branchcode) WHERE statistics.branch=<<Bibliotek|branches>> AND statistics.itemtype IS NOT NULL AND statistics.datetime BETWEEN <<Datum från|date>>-INTERVAL 1 DAY AND <<Datum till |date>>+INTERVAL 1 DAY GROUP BY branch
Fråga 19 Kvinnor
SELECT branches.branchname AS Bibliotek, COUNT(DISTINCT statistics.borrowernumber) AS 'Aktiva låntagare kvinnor' FROM statistics LEFT JOIN borrowers ON (borrowers.borrowernumber=statistics.borrowernumber) LEFT JOIN borrower_attributes ON (borrowers.borrowernumber=borrower_attributes.borrowernumber) LEFT JOIN branches ON (statistics.branch=branches.branchcode) WHERE statistics.datetime LIKE '2018%' AND code = 'PERSNUMMER' AND LEFT( RIGHT(attribute, 2), 1) % 2 = 0 AND statistics.branch=<<Bibliotek|branches>> GROUP BY statistics.branch
Fråga 19 Män
SELECT branches.branchname AS Bibliotek, COUNT(DISTINCT statistics.borrowernumber) AS 'Aktiva låntagare kvinnor' FROM statistics LEFT JOIN borrowers ON (borrowers.borrowernumber=statistics.borrowernumber) LEFT JOIN borrower_attributes ON (borrowers.borrowernumber=borrower_attributes.borrowernumber) LEFT JOIN branches ON (statistics.branch=branches.branchcode) WHERE statistics.datetime LIKE '2018%' AND code = 'PERSNUMMER' AND LEFT( RIGHT(attribute, 2), 1) % 2 = 1 AND statistics.branch=<<Bibliotek|branches>> GROUP BY statistics.branch