Nyckeltal för samlingarna Bibliotek Mellansjö - Versionshistorik

Klas den 8 mars 2023 kl. 14.51

2023-03-08T14:51:26Z

Klas den 8 mars 2023 kl. 14.44

2023-03-08T14:44:17Z

Klas: Skapade sidan med '

 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...'

2020-10-23T08:40:36Z

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...'

Ny sida

<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 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

</pre>

← Äldre version		Versionen från 8 mars 2023 kl. 14.51
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>