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

Från Svenska kohanätverkets wiki
Hoppa till navigering Hoppa till sök
(Skapade sidan med '<pre> SELECT CASE WHEN ds.ccode IS NULL THEN '--' ELSE ds.ccode END AS Avdelning, CASE WHEN authorised_values.lib IS NULL THEN '--' ELSE authorised_values.lib END AS Placer...')
 
Rad 1: Rad 1:
 +
 +
== Nyttjandegrad ==
 +
 +
Nyttjandegrad=Lån per exemplar under det senaste året
 +
 
<pre>
 
<pre>
 
SELECT  
 
SELECT  
−
CASE
+
branchname AS Bibliotek, Avdelning, Placering, Utlan,  
−
WHEN ds.ccode IS NULL
+
Exemplar AS 'Egna exemplar',  
−
THEN '--'
+
ROUND(
−
ELSE
+
(Utlan/Exemplar)
−
ds.ccode
+
,1)
−
END
+
AS Nyttjandegrad
−
AS Avdelning,
+
 
−
CASE
+
FROM
−
WHEN authorised_values.lib IS NULL
+
(SELECT statistics.branch, statistics.ccode AS Avdelning, authorised_values.lib AS Placering, COUNT(statistics.type) AS Utlan,
−
THEN '--'
 
−
ELSE
 
−
authorised_values.lib
 
−
END
 
−
AS Placering,
 
−
SUM(issue1) AS 'Utl på hembibl',
 
−
SUM(issue2) AS 'Utl på annat bibl',
 
−
SUM(issue3) AS 'Utl totalt',
 
−
SUM(exemplar) AS 'Exemplar',
 
−
ROUND(SUM(issue3) / sum(exemplar),2)
 
−
as Kvot,
 
−
ROUND(100*(SUM(issue3) /
 
−
(SELECT COUNT(*)
 
−
FROM statistics
 
−
LEFT JOIN items ON (items.itemnumber=statistics.itemnumber)
 
−
WHERE type='issue'
 
−
AND items.homebranch LIKE <<Bibliotek|branches:all>>
 
−
AND statistics.location LIKE <<Placering|LOC:all>>
 
−
AND statistics.ccode LIKE <<Avdelning (barn/vuxen/magasin)|ccode:all>>
 
−
AND statistics.datetime >= curdate() - interval 1 year )),2) AS '% av utlån',
 
−
ROUND(100*(SUM(exemplar) /
 
 
(SELECT COUNT (*)
 
(SELECT COUNT (*)
−
FROM items  
+
FROM items
−
WHERE
 
−
items.homebranch LIKE <<Bibliotek|branches:all>>
 
−
AND items.location LIKE <<Placering|LOC:all>>
 
−
AND items.ccode LIKE <<Avdelning (barn/vuxen/magasin)|ccode:all>>
 
−
)),2)
 
−
AS '% av exemplar',
 
−
ROUND(100*(SUM(issue3) /
 
−
(SELECT COUNT(*)
 
−
FROM statistics
 
−
LEFT JOIN items ON (items.itemnumber=statistics.itemnumber)
 
−
WHERE type='issue'
 
−
AND items.homebranch LIKE <<Bibliotek|branches:all>>
 
−
AND items.location LIKE <<Placering|LOC:all>>
 
−
AND items.ccode LIKE <<Avdelning (barn/vuxen/magasin)|ccode:all>>
 
−
AND statistics.datetime >= curdate() - interval 1 year ))
 
−
-
 
−
100*(SUM(exemplar) /
 
−
(SELECT COUNT (*)
 
−
FROM items
 
−
WHERE
 
−
items.homebranch LIKE <<Bibliotek|branches:all>>
 
−
AND items.location LIKE <<Placering|LOC:all>>
 
−
AND items.ccode LIKE <<Avdelning (barn/vuxen/magasin)|ccode:all>>
 
−
)),2)
 
−
AS Performance,
 
−
ROUND(100*(SUM(ckoinperiod)/SUM(exemplar)),2) '% ex lånade senaste året',
 
−
ROUND(100*(SUM(ckonow)/SUM(exemplar)),2) AS '% Utlånat just nu',
 
−
ROUND(100*((SUM(ckoinperiod)-SUM(ckonow))/SUM(exemplar)),2) AS '% ex tillg just nu men lånade senaste året',
 
−
ROUND(SUM(issue3) / sum(ckoinperiod),2)
 
−
as 'Kvot för lånade ex',
 
−
SUM(acqinperiod) AS 'Antal förvärvade senaste året',
 
−
ROUND(100*(SUM(acqinperiod)/SUM(exemplar)),2) AS '% ex förvärvade senaste året',
 
−
SUM(weedinperiod) AS 'Antal gallrade senaste året',
 
−
ROUND(100*(SUM(weedinperiod)/(SUM(exemplar)+SUM(weedinperiod)-SUM(acqinperiod))),2) AS '% ex gallrat av beståndet för ett år sedan',
 
−
SUM(acqinperiod)-SUM(weedinperiod) AS 'Förändring',
 
−
FORMAT(AVG(totissues),2) AS 'Genomsnittligt antal lån sedan förvärv',
 
−
SUBSTRING(AVG(accyear),1,4) AS 'Genomsnittligt förvärvsår'
 
  
 +
WHERE items.homebranch=statistics.branch
 +
AND items.ccode=statistics.ccode
 +
AND items.location=statistics.location) AS Exemplar
  
−
FROM
 
−
((SELECT statistics.ccode, statistics.location, 1 as issue1, 0 as issue2, 1 as issue3, 0 as exemplar, 0 as ckoinperiod, 0 as acqinperiod, NULL as totissues, NULL as accyear, 0 as ckonow, 0 as weedinperiod
 
−
FROM statistics
 
−
LEFT JOIN items ON (items.itemnumber=statistics.itemnumber)
 
−
WHERE type='issue' AND items.homebranch LIKE <<Bibliotek|branches:all>>
 
−
AND statistics.location LIKE <<Placering|LOC:all>>
 
−
AND statistics.ccode LIKE <<Avdelning (barn/vuxen/magasin)|ccode:all>>
 
−
AND items.homebranch=statistics.branch
 
−
AND items.itype LIKE <<Exemplartyp|itemtypes:all>>
 
−
AND statistics.datetime >= curdate() - interval 1 year
 
−
)
 
−
UNION ALL
 
−
(SELECT items.ccode, statistics.location, 0 as issue1, 1 as issue2, 1 as issue3, 0 as exemplar, 0 as ckoinperiod, 0 as acqinperiod, NULL as totissues, NULL as accyear, 0 as ckonow, 0 as weedinperiod
 
 
FROM statistics
 
FROM statistics
−
LEFT JOIN items ON (items.itemnumber=statistics.itemnumber)
+
LEFT JOIN authorised_values ON (statistics.location=authorised_values.authorised_value)
−
WHERE type='issue' AND items.homebranch LIKE <<Bibliotek|branches:all>>
 
−
AND statistics.location LIKE <<Placering|LOC:all>>
 
−
AND statistics.ccode LIKE <<Avdelning (barn/vuxen/magasin)|ccode:all>>
 
−
AND items.homebranch!=statistics.branch
 
−
AND items.itype LIKE <<Exemplartyp|itemtypes:all>>
 
−
AND statistics.datetime >= curdate() - interval 1 year
 
−
)
 
−
UNION ALL
 
−
(SELECT items.ccode,items.location, 0 as issue1, 0 as issue2, 0 as issue3, 1 as exemplar, 0 as ckoinperiod, 0 as acqinperiod, items.issues as totissues, items.dateaccessioned as accyear, 0 as ckonow, 0 as weedinperiod
 
−
FROM items
 
−
WHERE homebranch LIKE <<Bibliotek|branches:all>>
 
−
AND items.location LIKE <<Placering|LOC:all>>
 
−
AND items.ccode LIKE <<Avdelning (barn/vuxen/magasin)|ccode:all>>
 
−
AND items.itype LIKE <<Exemplartyp|itemtypes:all>>
 
−
)
 
−
UNION ALL
 
−
(SELECT items.ccode,items.location, 0 as issue1, 0 as issue2, 0 as issue3, 0 as exemplar, 1 as ckoinperiod, 0 as acqinperiod, NULL as totissues, NULL as accyear, 0 as ckonow, 0 as weedinperiod
 
−
FROM items
 
−
WHERE homebranch LIKE <<Bibliotek|branches:all>>
 
−
AND items.location LIKE <<Placering|LOC:all>>
 
−
AND items.ccode LIKE <<Avdelning (barn/vuxen/magasin)|ccode:all>>
 
−
AND items.itype LIKE <<Exemplartyp|itemtypes:all>>
 
−
AND items.datelastborrowed >= curdate() - interval 1 year
 
−
)
 
−
UNION ALL
 
−
(SELECT items.ccode,items.location, 0 as issue1, 0 as issue2, 0 as issue3, 0 as exemplar, 0 as ckoinperiod, 1 as acqinperiod, NULL as totissues, NULL as accyear, 0 as ckonow, 0 as weedinperiod
 
−
FROM items
 
−
WHERE homebranch LIKE <<Bibliotek|branches:all>>
 
−
AND items.location LIKE <<Placering|LOC:all>>
 
−
AND items.ccode LIKE <<Avdelning (barn/vuxen/magasin)|ccode:all>>
 
−
AND items.itype LIKE <<Exemplartyp|itemtypes:all>>
 
−
AND items.dateaccessioned >= curdate() - interval 1 year
 
−
)
 
  
−
UNION ALL
+
WHERE
−
(SELECT items.ccode,items.location, 0 as issue1, 0 as issue2, 0 as issue3, 0 as exemplar, 0 as ckoinperiod, 0 as acqinperiod, NULL as totissues, NULL as accyear, 1 as ckonow, 0 as weedinperiod
+
statistics.branch LIKE <<Bibliotek|branches>>  
−
FROM items
+
AND statistics.ccode LIKE <<Avdelning|CCODE:all>>  
−
WHERE homebranch LIKE <<Bibliotek|branches:all>>
+
AND statistics.location LIKE <<Placering|LOC:all>>  
−
AND items.location LIKE <<Placering|LOC:all>>  
+
AND statistics.itemtype LIKE <<Exemplartyp |itemtypes:all>>  
−
AND items.ccode LIKE <<Avdelning (barn/vuxen/magasin)|ccode:all>>
+
AND statistics.type='issue'
−
AND items.itype LIKE <<Exemplartyp|itemtypes:all>>
 
−
AND items.onloan IS NOT NULL
 
−
)
 
  
−
UNION ALL
+
GROUP BY branch,Avdelning,Placering
−
(SELECT deleteditems.ccode,deleteditems.location, 0 as issue1, 0 as issue2, 0 as issue3, 0 as exemplar, 0 as ckoinperiod, 0 as acqinperiod, NULL as totissues, NULL as accyear, 0 as ckonow, 1 as weedinperiod
+
ORDER BY branch,ccode,location)t
−
FROM deleteditems
 
−
WHERE homebranch LIKE <<Bibliotek|branches:all>>
 
−
AND deleteditems.location LIKE <<Placering|LOC:all>>
 
−
AND deleteditems.ccode LIKE <<Avdelning (barn/vuxen/magasin)|ccode:all>>
 
−
AND deleteditems.itype LIKE <<Exemplartyp|itemtypes:all>>
 
−
AND deleteditems.timestamp >= curdate() - interval 1 year
 
−
)
 
−
) ds
 
−
LEFT JOIN authorised_values ON (authorised_values.authorised_value=ds.location)
 
−
WHERE ds.location LIKE <<Placering|LOC:all>>
 
−
AND ds.ccode LIKE <<Avdelning (barn/vuxen/magasin)|ccode:all>>
 
−
GROUP BY ds.ccode,authorised_values.lib WITH ROLLUP
 
  
 +
LEFT JOIN branches ON (t.branch=branches.branchcode)
 
</pre>
 
</pre>

Versionen från 8 mars 2023 kl. 16.44

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