Käyttäjät

Rekisteröidyt lainaajat & Uudet asiakkaat

SELECT
CASE
WHEN YEAR(bo.dateenrolled) = YEAR(CURDATE()) - 1 THEN 'Uudet asiakkaat'
   WHEN YEAR(bo.dateenrolled) <= YEAR(CURDATE()) - 1 THEN 'Vanhat asiakkaat'
END AS Asiakkaat,
COUNT(bo.borrowernumber) AS Lkm
FROM borrowers bo
WHERE YEAR(bo.dateenrolled) <= YEAR(CURDATE()) - 1
GROUP BY Asiakkaat DESC
;

Vuoden lopun asiakkaiden kokonaismäärä siis uudet + vanhat.

Aktiiviset lainaajat

SELECT COUNT(DISTINCT ps.hashed_borrowernumber) AS 'Aktiiviset lainaajat'
FROM pseudonymized_transactions ps
WHERE YEAR(ps.dateenrolled) <= YEAR(CURDATE()) - 1
AND YEAR(ps.datetime) = YEAR(CURDATE()) - 1
;

Lainaus

Lainat, fyysinen aineisto & Uusinnat, fyysinen aineisto

SELECT
CASE
    WHEN al.action = 'ISSUE' THEN 'Lainat'
    WHEN al.action = 'RENEWAL' THEN 'Uusinnat'
END AS Lainatoimi,
COUNT(*) as Lkm
FROM action_logs al
WHERE al.module = 'CIRCULATION'
AND al.action in ('ISSUE', 'RENEWAL')
AND YEAR(al.timestamp) = YEAR(CURDATE()) -1
GROUP BY Lainatoimi
;

Kaukolainaus

Annetut kaukolainat

SELECT COUNT(*) As 'Annetut kaukolainat'
FROM action_logs al, borrower_attributes ba
WHERE al.module = 'CIRCULATION'
AND al.action = 'ISSUE'
AND al.object = ba.borrowernumber
AND SUBSTR(ba.attribute, 1, 1) in('N', 'O')
AND YEAR(al.timestamp) = YEAR(CURDATE()) -1
;