Nyckeltal för samlingarna Bibliotek Mellansjö

Nyttjandegrad

Nyttjandegrad=Lån per exemplar under det senaste året

SELECT 
branchname AS Bibliotek, Avdelning, Placering, Utlan, 
Exemplar AS 'Egna exemplar', 
ROUND(
(Utlan/Exemplar)
,1)
AS Nyttjandegrad

FROM
(SELECT statistics.branch, statistics.ccode AS Avdelning, authorised_values.lib AS Placering, COUNT(statistics.type) AS Utlan,
(SELECT COUNT (*)
FROM items

WHERE items.homebranch=statistics.branch
AND items.ccode=statistics.ccode
AND items.location=statistics.location) AS Exemplar

FROM statistics
LEFT JOIN authorised_values ON (statistics.location=authorised_values.authorised_value)

WHERE 
statistics.branch LIKE  <<Bibliotek|branches>> 
AND statistics.ccode LIKE  <<Avdelning|CCODE:all>> 
AND statistics.location LIKE  <<Placering|LOC:all>> 
AND statistics.itemtype LIKE  <<Exemplartyp |itemtypes:all>> 
AND statistics.type='issue'

GROUP BY branch,Avdelning,Placering
ORDER BY branch,ccode,location)t

LEFT JOIN branches ON (t.branch=branches.branchcode)

Diverse medelvärden

Säger förhoppningsvis något om hur aktuellt, använt och slitet beståndet är

SELECT branchname,ccode AS Avdelning,authorised_values.lib AS Placering,
SUBSTRING(FROM_UNIXTIME(AVG(UNIX_TIMESTAMP(dateaccessioned))),1,10) AS 'Förvärvad',
SUBSTRING(FROM_UNIXTIME(AVG(UNIX_TIMESTAMP(datelastborrowed))),1,10) AS 'Senast lånad',
ROUND(AVG(issues),1) AS 'Antal lån sedan boken köptes'


FROM items

LEFT JOIN branches ON (items.homebranch=branches.branchcode)
LEFT JOIN authorised_values ON (items.location=authorised_values.authorised_value)

WHERE dateaccessioned IS NOT NULL
AND datelastborrowed IS NOT NULL
AND homebranch LIKE  <<Bibliotek|branches>> 
AND ccode LIKE  <<Avdelning|CCODE:all>> 
AND location LIKE  <<Placering|LOC:all>> 
AND itype LIKE <<Exemplartyp |itemtypes:all>> 


GROUP BY homebranch,ccode,location
ORDER BY homebranch,ccode,location

Förändringar av beståndet

Antal nyförvärv och gallringar under det senaste året

SELECT branchname AS Bibliotek,ccode AS Avdelning,authorised_values.lib AS Placering, SUM(Forvarv) AS 'Förvärvat', SUM(Gallrat) AS 'Gallrat', SUM(Forvarv)-SUM(Gallrat) AS 'Förändring'
FROM
(
SELECT homebranch,ccode,location,COUNT(*) AS Forvarv, 0 AS Gallrat
FROM items
WHERE items.dateaccessioned >= curdate() - interval 1 year
AND items.homebranch LIKE <<Bibliotek|branches>> 
AND items.ccode LIKE  <<Avdelning|CCODE:all>> 
AND items.location LIKE  <<Placering|LOC:all>> 
AND items.itype LIKE  <<Exemplartyp |itemtypes:all>> 
GROUP BY homebranch,ccode,location

UNION

SELECT homebranch,ccode,location,0 'Förvärv', COUNT(*) AS Gallrat
FROM deleteditems
WHERE deleteditems.timestamp >= curdate() - interval 1 year
AND deleteditems.homebranch LIKE <<Bibliotek|branches>>
AND deleteditems.ccode LIKE  <<Avdelning|CCODE:all>> 
AND deleteditems.location LIKE  <<Placering|LOC:all>> 
AND deleteditems.itype LIKE  <<Exemplartyp |itemtypes:all>> 
GROUP BY homebranch,ccode,location
) t

LEFT JOIN branches ON (t.homebranch=branches.branchcode)
LEFT JOIN authorised_values ON (t.location=authorised_values.authorised_value)

GROUP BY homebranch,ccode,location
ORDER BY homebranch,ccode,location

Hyllvärmare och utlånade exemplar

Bibliotek Mellansjös medieplan säger att böcker ska gallras eller magasineras om de inte har varit utlånade på tre år - siffran i den kolumnen visar alltså hur mycket som blir kvar om man följer det som planen säger

SELECT branchname AS Bibliotek,ccode AS Avdelning,authorised_values.lib AS Placering,SUM(Exemplar) AS Exemplar, SUM(Hyllvarmare) AS 'Aldrig utlånat', CONCAT(ROUND(100*(SUM(Hyllvarmare)/SUM(Exemplar)),0), ' %') AS '', SUM(Utlyear) AS 'Utlånat senaste tre åren', CONCAT(ROUND(100*(SUM(Utlyear)/SUM(Exemplar)),0), ' %') AS '', SUM(Utlanat) AS 'Utlånat just nu', CONCAT(ROUND(100*(SUM(Utlanat)/SUM(Exemplar)),0), ' %') AS ''

FROM 
(SELECT homebranch,ccode,location,COUNT(*) AS Exemplar, 0 AS Hyllvarmare, 0 Utlyear, 0 AS Utlanat
FROM items
WHERE items.homebranch LIKE  <<Bibliotek|branches>> 
AND items.ccode LIKE  <<Avdelning|CCODE:all>> 
AND items.location LIKE  <<Placering|LOC:all>> 
AND items.itype LIKE  <<Exemplartyp |itemtypes:all>> 
GROUP BY homebranch,ccode,location

UNION 

SELECT homebranch,ccode,location,0 AS Exemplar, COUNT(*) AS Hyllvarmare, 0 Utlyear, 0 AS Utlanat
FROM items
WHERE items.homebranch LIKE  <<Bibliotek|branches>> 
AND items.ccode LIKE  <<Avdelning|CCODE:all>> 
AND items.location LIKE  <<Placering|LOC:all>> 
AND items.itype LIKE  <<Exemplartyp |itemtypes:all>> 
AND issues='0'
GROUP BY homebranch,ccode,location

UNION

SELECT homebranch,ccode,location,0 AS Exemplar,0 AS Hyllvarmare, COUNT(*) AS Utlyear, 0 AS Utlanat
FROM items
WHERE items.homebranch LIKE  <<Bibliotek|branches>> 
AND items.ccode LIKE  <<Avdelning|CCODE:all>> 
AND items.location LIKE  <<Placering|LOC:all>> 
AND items.itype LIKE  <<Exemplartyp |itemtypes:all>> 
AND items.datelastborrowed >= curdate() - interval 3 year 
GROUP BY homebranch,ccode,location

UNION 

SELECT homebranch,ccode,location,0 AS Exemplar,0 AS Hyllvarmare, 0 Utlyear, COUNT(*) AS Utlanat
FROM items
WHERE items.homebranch LIKE  <<Bibliotek|branches>> 
AND items.ccode LIKE  <<Avdelning|CCODE:all>> 
AND items.location LIKE  <<Placering|LOC:all>> 
AND items.itype LIKE  <<Exemplartyp |itemtypes:all>> 
AND onloan IS NOT NULL
GROUP BY homebranch,ccode,location) t

LEFT JOIN branches ON (t.homebranch=branches.branchcode)
LEFT JOIN authorised_values ON (t.location=authorised_values.authorised_value)

GROUP BY homebranch,ccode,location
ORDER BY homebranch,ccode,location