Kirjat

Painetut kirjat nimekettä

SELECT 'Painetut kirjat nimekettä' AS query_type,
       COUNT(biblionumber) AS biblio_counts
FROM biblio_metadata
WHERE SUBSTR(EXTRACTVALUE(metadata, '//controlfield[@tag="007"]'), 1, 1) <> 'c'
  AND biblionumber IN (
    SELECT biblionumber
    FROM biblio
    WHERE YEAR(datecreated) <= YEAR(CURDATE()) - 1
  )
  AND SUBSTR(metadata, INSTR(metadata, '<leader>') + 8 + 6, 2) = 'am';

Painetut kirjat niteitä

SELECT 'Painetut kirjat nid/sy' AS query_type,
       COUNT(itemnumber) AS item_counts
FROM items
INNER JOIN biblio_metadata USING (biblionumber)
WHERE YEAR(dateaccessioned) <= YEAR(CURDATE()) - 1
  AND SUBSTR(metadata, INSTR(metadata, '<leader>') + 8 + 6, 2) = 'am';

Elektroniset kirjat nimekettä (siltä osin kun ovat Kohassa)

SELECT 'Elektroniset kirjat nimekettä' AS query_type,
       COUNT(biblionumber) AS biblio_counts
FROM biblio_metadata
WHERE SUBSTR(EXTRACTVALUE(metadata, '//controlfield[@tag="007"]'), 1, 1) = 'c'
  AND biblionumber IN (
    SELECT biblionumber
    FROM biblio
    WHERE YEAR(datecreated) <= YEAR(CURDATE()) - 1
  )
  AND SUBSTR(metadata, INSTR(metadata, '<leader>') + 8 + 6, 2) = 'am';

Kausijulkaisut

Painetut kausijulkaisut

SELECT 'Painetut kausijulkaisut, nimekettä' AS query_type,
       COUNT(biblionumber) AS biblio_counts
FROM biblio_metadata
WHERE SUBSTR(EXTRACTVALUE(metadata, '//controlfield[@tag="007"]'), 1, 1) <> 'c'
  AND SUBSTR(ExtractValue(metadata,'//controlfield[@tag="008"]'), 24, 1) <> 'o'
  AND biblionumber IN (
    SELECT biblionumber
    FROM biblio
    WHERE YEAR(datecreated) <= YEAR(CURDATE()) - 1
  )
  AND SUBSTR(metadata, INSTR(metadata, '<leader>') + 8 + 6, 2) = 'as';

Metatiedon tuotanto

Luettelotietueet & Vuoden aikana lisätyt tietueet

SELECT
CASE
    WHEN YEAR(b.datecreated) = YEAR(CURDATE()) - 1 THEN 'Joista tilastovuoden aikana lisätyt'
    WHEN YEAR(b.datecreated) <= YEAR(CURDATE()) - 1 THEN 'Luettelotietueet'
END AS Luettelotietueet,
COUNT(b.biblionumber) AS Lkm
FROM biblio b
WHERE YEAR(b.datecreated) <= YEAR(CURDATE()) - 1
GROUP BY Luettelotietueet DESC
;