Musik och teaterbiblioteket

Samlingsplats fr Musik- och teaterbibliotekets statistik.

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

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