Nyckeltal för samlingarna Bibliotek Mellansjö
Version från den 23 oktober 2020 kl. 09.40 av Klas (diskussion | bidrag) (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...')
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 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 (*) 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' 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 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,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 (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 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.onloan IS NOT NULL ) UNION ALL (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 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