Musik och teaterbiblioteket

Rapport från Musik- och teaterbiblioteket

Cirkulation

Framtagningslista Egengjord lista för att hämta fram reserverat material då vi har det mesta i magasin.

SELECT *
FROM (SELECT items.location, items.itemcallnumber, biblio.title AS 'Titel', biblio.author AS 'Upphov', biblio.copyrightdate,
      reserves.reservedate AS 'Reservationsdatum', reserves.reservenotes AS 'Kommentar',
      CONCAT_WS(' ', borrowers.firstname, borrowers.surname) AS 'Låntagare',
DENSE_RANK() OVER (PARTITION BY biblio.biblionumber ORDER BY reserves.reservedate ASC, reserves.reserve_id ASC) AS reserveenumeration,
DENSE_RANK() OVER (PARTITION BY biblio.biblionumber ORDER BY 
                   CASE WHEN items.location = 'PjäsHem' THEN '0'
                   WHEN items.location LIKE 'EMS%' THEN CONCAT('2', items.location)
                   WHEN items.location IN ('SVA', 'Referens', 'Rariteter') THEN CONCAT('3', items.location)
                   ELSE CONCAT('1', items.location) END
                   ASC, items.copynumber ASC,items.barcode) AS itemenumeration,
      waitortransit.itemnumber IS NOT NULL AS waitortransit
FROM borrowers INNER JOIN reserves ON borrowers.borrowernumber = reserves.borrowernumber
INNER JOIN biblio ON biblio.biblionumber = reserves.biblionumber
INNER JOIN items ON biblio.biblionumber = items.biblionumber
LEFT OUTER JOIN issues ON issues.itemnumber = items.itemnumber
LEFT OUTER JOIN
      (
      SELECT itemnumber FROM reserves WHERE found IN ('W','T')
      ) AS waitortransit
ON items.itemnumber = waitortransit.itemnumber
WHERE issues.itemnumber IS NULL
AND reserves.suspend = 0
AND (reserves.itemnumber = items.itemnumber OR reserves.itemnumber IS NULL)
AND items.itemlost = '0'
AND COALESCE(reserves.found,'') NOT IN ('W','T')) X
WHERE reserveenumeration = itemenumeration
AND NOT waitortransit
AND (location NOT LIKE 'EMS%' AND location NOT LIKE 'Orkester')
ORDER BY location, itemcallnumber

Statistik

Aktiva låntagare Baserat på att vi anger kön/institution som ett auktoriserat värde som förs in i låntagarposten som borrower attribute.

SELECT
	CASE
		WHEN attr IN ('m','man') THEN 'Man'
		WHEN attr IN ('k','kvinna') THEN 'Kvinna'
		WHEN attr IN ('i','Svensk institution') THEN 'Svensk Institution'
		WHEN attr IN ('u','Utländsk institution') THEN 'Utländsk Institution'
		WHEN attr IN ('-','Ospecificerat') THEN 'Ospecifierat'
		ELSE 'Okänt'
	END AS 'Kategori',
	mdt AS 'Året som lånt senast var aktiv',
	COUNT(*) AS 'Antal låntagare'
FROM
	(
	SELECT
		borrowers.borrowernumber,
		MAX(borrower_attributes.attribute) attr,
		MAX(LEFT(statistics.datetime,4)) mdt
	FROM
		statistics
	INNER JOIN
		borrowers
	ON
		statistics.borrowernumber = borrowers.borrowernumber
	INNER JOIN
		borrower_attributes
	ON
		borrower_attributes.borrowernumber=borrowers.borrowernumber
	WHERE 
		borrower_attributes.code = 'INST'
	GROUP BY 
		borrowernumber
	) borrowerstats
GROUP BY 1,2 ORDER BY 1,2

Registrerade låntagare Baserat på att vi anger kön/institution som ett auktoriserat värde som förs in i låntagarposten som borrower attribute.

SELECT
	CASE
		WHEN attr IN ('m','man') THEN 'Man'
		WHEN attr IN ('k','kvinna') THEN 'Kvinna'
		WHEN attr IN ('i','Svensk institution') THEN 'Svensk Institution'
		WHEN attr IN ('u','Utländsk institution') THEN 'Utländsk Institution'
		WHEN attr IN ('-','Ospecificerat') THEN 'Ospecifierat'
		ELSE 'Okänt'
	END AS 'Kategori',
    COUNT(*) AS 'Antal låntagare'
FROM
	(
	SELECT
		borrowers.borrowernumber,
		MAX(borrower_attributes.attribute) attr
	FROM
		borrowers
	LEFT OUTER JOIN
		borrower_attributes
	ON
		borrower_attributes.borrowernumber=borrowers.borrowernumber
	WHERE 
		COALESCE(borrower_attributes.code = 'INST', borrower_attributes.borrowernumber IS NULL)
	GROUP BY 
		borrowernumber
	) borrowerstats
GROUP BY 1 ORDER BY 1

Nya låntagare

SELECT COUNT(borrowernumber) AS 'Nya låntagare'
FROM borrowers
WHERE YEAR(dateenrolled) = <<Välj år ÅÅÅÅ>> 

Lån fördelat på län Baserat på att vi anger län som ett auktoriserat värde som förs in i låntagarposten som borrower attribute.

SELECT COALESCE(cat," Total") AS cat,loans,loans/MAX(loans) OVER (ORDER BY loans DESC)*100 FROM
(
SELECT
	CASE
		WHEN attr IN ('k') THEN 'Blekinge'
		WHEN attr IN ('w') THEN 'Dalarna'
		WHEN attr IN ('i') THEN 'Gotland'
		WHEN attr IN ('x') THEN 'Gävleborg'
		WHEN attr IN ('n') THEN 'Halland'
		WHEN attr IN ('z') THEN 'Jämtland'
		WHEN attr IN ('f') THEN 'Jönköping'
		WHEN attr IN ('h') THEN 'Kalmar'
		WHEN attr IN ('g') THEN 'Kronoberg'
		WHEN attr IN ('b') THEN 'Norrbotten'
		WHEN attr IN ('m') THEN 'Skåne'
		WHEN attr IN ('a') THEN 'Stockholm'
		WHEN attr IN ('d') THEN 'Södermanland'
		WHEN attr IN ('c') THEN 'Uppsala'
		WHEN attr IN ('s') THEN 'Värmland'
		WHEN attr IN ('1') THEN 'Västerbotten'
		WHEN attr IN ('y') THEN 'Västernorrland'
		WHEN attr IN ('u') THEN 'Västmanland'
		WHEN attr IN ('o') THEN 'Västra Götaland'
		WHEN attr IN ('t') THEN 'Örebro'
		WHEN attr IN ('e') THEN 'Östergötland'
		ELSE 'Okänt'
	END AS cat,
	COUNT(*) AS loans
FROM
	(
	SELECT
		borrowers.borrowernumber,
		MAX(borrower_attributes.attribute) attr
	FROM
		statistics
	INNER JOIN
		borrowers
	ON
		statistics.borrowernumber = borrowers.borrowernumber
	INNER JOIN
		borrower_attributes
	ON
		borrower_attributes.borrowernumber=borrowers.borrowernumber
	WHERE 
		borrower_attributes.code = 'LÄN'
      	AND statistics.datetime BETWEEN <<Välj startdatum|date>> AND <<och slutdatum|date>>
	GROUP BY 
		borrowernumber
	) borrowerstats
GROUP BY 1 WITH ROLLUP
) x
ORDER BY 1

Tertialstatistik Lån grupperat per tertial

SELECT
(MONTH(datetime) DIV 4)+1 AS 'Tertial för år 2019', 
SUM( IF(type = 'issue', 1, 0 )) AS Utlån,
SUM( IF(type = 'renew', 1, 0 )) AS Omlån,
SUM( IF(type = 'onsite_checkout', 1, 0 )) AS Läsesalslån,
SUM( IF(type IN ('issue','renew','onsite_checkout'), 1, 0 )) AS 'Totalt antal transaktioner'
FROM  statistics
WHERE YEAR(datetime)=2019
GROUP BY 1