Nyckeltal för samlingarna Bibliotek Mellansjö

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