Skillnad mellan versioner av "Nyckeltal för samlingarna Bibliotek Mellansjö"

Från Svenska kohanätverkets wiki
Hoppa till navigering Hoppa till sök
 
Rad 36: Rad 36:
  
 
LEFT JOIN branches ON (t.branch=branches.branchcode)
 
LEFT JOIN branches ON (t.branch=branches.branchcode)
 +
</pre>
 +
 +
 +
== Diverse medelvärden ==
 +
 +
Säger förhoppningsvis något om hur aktuellt, använt och slitet beståndet är
 +
 +
<pre>
 +
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
 +
</pre>
 +
 +
 +
== Förändringar av beståndet ==
 +
 +
Antal nyförvärv och gallringar under det senaste året
 +
 +
<pre>
 +
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
 +
</pre>
 +
 +
 +
== 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
 +
 +
<pre>
 +
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
 
</pre>
 
</pre>

Nuvarande version från 8 mars 2023 kl. 15.51

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
Hämtad från "https://koha.se/wiki/index.php?title=Nyckeltal_för_samlingarna_Bibliotek_Mellansjö&oldid=986"