Låntagarrörlighet

Låntagarrörlighet

Hur många låntagare använder andra bibliotek än sitt hembibliotek, och hur många låna gör de?

SELECT hembibl.branchname AS 'Låntagarens hembibliotek',utlbibl.branchname AS 'Utlånande bibliotek',COUNT(*) AS 'Antal lån',COUNT(DISTINCT statistics.borrowernumber) AS 'Antal låntagare'

FROM statistics
LEFT JOIN borrowers ON (borrowers.borrowernumber=statistics.borrowernumber)
LEFT JOIN branches hembibl ON (hembibl.branchcode=borrowers.branchcode)
LEFT JOIN branches utlbibl ON (utlbibl.branchcode=statistics.branch)

WHERE statistics.branch LIKE <<Utlånande bibliotek|branches:all>>
AND borrowers.branchcode LIKE <<Låntagarens hembibliotek|branches:all>> 
AND borrowers.categorycode LIKE <<Låntagarkategori|categorycode:all>>
AND statistics.datetime BETWEEN <<Datum från|date>>-INTERVAL 1 DAY AND <<Datum till |date>>+INTERVAL 1 DAY AND statistics.branch IS NOT NULL

GROUP BY borrowers.branchcode,statistics.branch

ORDER BY 2,4

Låntagarrörlighet med postnummer

Samma som ovan, men sorterat på låntagarens postnummer så man kan se vilka områden man täcker in

select 
hembibl.branchname AS 'Hembibliotek',utlbibl.branchname AS 'Utlånande bibliotek',
CONCAT('<a href=\"https://kartor.eniro.se/sok/', REPLACE(zipcode, ' ', '' ), '\" target="_blank">', REPLACE(zipcode, ' ', '' ), '</a>' ) AS 'Postnummer',
COUNT(*) 'Antal lån', COUNT(DISTINCT statistics.borrowernumber) AS 'Antal låntagare'

FROM
statistics
LEFT JOIN borrowers ON (borrowers.borrowernumber=statistics.borrowernumber)
LEFT JOIN branches utlbibl ON (utlbibl.branchcode=statistics.branch)
LEFT JOIN branches hembibl On (hembibl.branchcode=borrowers.branchcode)
WHERE categorycode!='BIBLIOTEK' 
AND statistics.itemtype IS NOT NULL 
AND statistics.type='issue' 
AND statistics.branch LIKE <<Utlånanade bibliotek|branches:all>>
AND borrowers.branchcode LIKE <<Låntagarens hembibliotek|branches:all>>
AND borrowers.categorycode LIKE <<Låntagarkategori|categorycode:all>>
AND statistics.datetime BETWEEN <<Datum från|date>>-INTERVAL 1 DAY AND <<Datum till |date>>+INTERVAL 1 DAY 
AND CHAR_LENGTH(REPLACE(zipcode, ' ', '' ))=5
GROUP BY statistics.branch,borrowers.branchcode,REPLACE(zipcode, ' ', '' )
ORDER BY REPLACE(zipcode, ' ', '' ),utlbibl.branchname,hembibl.branchname