Skillnad mellan versioner av "Lånestatistik Bibliotek Mellansjö"

Från Svenska kohanätverkets wiki
Hoppa till navigering Hoppa till sök
 
(42 mellanliggande sidversioner av samma användare visas inte)
Rad 1: Rad 1:
 
Statistikrapporter som Bibliotek Mellansjö skapat.
 
Statistikrapporter som Bibliotek Mellansjö skapat.
−
Se dem som komplement/alternativ till Hyltes rapporter![[Statistik]]
+
 
 +
En del av rapporterna är lätta bearbetningar av Hyltes rapporter som finns på den här sidan:
 +
[[Statistik]]
 +
 
 +
Andra rapporter är skrivna från scratch
 
Och - precis som för Hylterapporterna krävs det att man byter ut exemplartyper och liknande till det man använder på sitt eget bibliotek
 
Och - precis som för Hylterapporterna krävs det att man byter ut exemplartyper och liknande till det man använder på sitt eget bibliotek
 +
 +
Eftersom Bibliotek Mellansjö är 42 enheter som ska redovisas kommunvis i åtta olika enkäter har vi gjort ett nytt auktorierat värde för att klumpa ihop siffrorna kommunvis
 +
Hur man skapar det värdet beskrivs i punkt 13, och sen finns Mellansjös rapporter i punkt 13.1-13.10.
 +
De rapporterna går inte att köra i någon annan katalog, försåvitt man inte först skapar samma auktoriserade värde
 +
 +
Punkt 1-12 är därför lättare bearbetningar (eller ibland äldre version) som inte använder det auktoriserade värdet, utan ska fungera i alla kataloger
  
 
==Fråga 10 Fysiskt bestånd==
 
==Fråga 10 Fysiskt bestånd==
Rad 24: Rad 34:
 
WHERE items.homebranch = <<Bibliotek|branches>>  
 
WHERE items.homebranch = <<Bibliotek|branches>>  
 
</pre>
 
</pre>
 +
 +
==Fråga 10 Fysiskt bestånd, en krångligare version==
  
 
Vill man vara lite petig så stämmer inte ovanstående rapport, eftersom den räknar hur många exemplar som finns just när man köra rapporten - och det som efterfrågas av KB är bestånd på Nyårsafton. Egentligen borde man alltså räkna aktuellt antal exemplar, plus det som gallrats sedan nyår, minus det som förvärvats sedan nyår. Ett försök på en sådan rapport:
 
Vill man vara lite petig så stämmer inte ovanstående rapport, eftersom den räknar hur många exemplar som finns just när man köra rapporten - och det som efterfrågas av KB är bestånd på Nyårsafton. Egentligen borde man alltså räkna aktuellt antal exemplar, plus det som gallrats sedan nyår, minus det som förvärvats sedan nyår. Ett försök på en sådan rapport:
 +
Om man tar alternativet Alla bibliotek i en stor katalog kommer rapporten antagligen att krascha
  
 
<pre>
 
<pre>
Rad 426: Rad 439:
 
GROUP BY homebranch
 
GROUP BY homebranch
  
 +
</pre>
 +
 +
==Fråga 10, tidskrifter==
 +
Fråga 10 är knepig - har man bara ett fåtal titlar är det helt klart enklare att räkna för hand än att köra en rapport!
 +
 +
Det som efterfrågas är inte antalet prenumerationer utan unika titlar per kommun, dessutom ska man inte räkna veckotidningar som exempelvis Land och ICA-kuriren.
 +
Och nyförvärv ska man bara rapportera om titeln är ny för kommunen - om en filial startar en nyprenumeration av en titel som redan finns på HB räknas det inte som nyförvärv.
 +
 +
Det här är ett första försök att lösa frågan med en rapport. Villkoret är att man ska ha ankomstregistrerat (serial.status=2) mer än ett nummer under året - jag har helt enkelt antagit att enstaka nummer är eftersläntare från föregående år.
 +
För oss är subscription_frequencies.id=4 för veckotidningar. Enklaste sättet att få fram den siffran för sitt eget system är att gå under Periodika > Hantera frekvenser > klicka på Redigera - så ser man i URLen vilket nummer man ska välja.
 +
 +
I den här versionen så visas texten Ny i högraste kolumnen så fort det finns en nystartad prenumeration - oavsett om man har haft prenumerationer på titeln tidigare eller inte. Fritt fram att fixa för den som kommer på hur det ska göras
 +
 +
<pre>
 +
SELECT biblio.title AS Titel ,GROUP_CONCAT(DISTINCT branches.branchname SEPARATOR '<br>') AS 'Bibliotek',COUNT(serialid) AS 'Antal mottagna nummer',GROUP_CONCAT(DISTINCT
 +
subscriptionhistory.histstartdate SEPARATOR '<br>') AS Startdatum,
 +
CASE
 +
WHEN subscriptionhistory.histstartdate > <<Datum från|date>>
 +
THEN
 +
'Ny'
 +
ELSE
 +
''
 +
END
 +
AS 'Ny?'
 +
 +
FROM subscription
 +
LEFT JOIN biblio ON (biblio.biblionumber=subscription.biblionumber)
 +
LEFT JOIN serial on (serial.subscriptionid=subscription.subscriptionid)
 +
LEFT JOIN branches ON (branches.branchcode=subscription.branchcode)
 +
LEFT JOIN subscription_frequencies ON (subscription_frequencies.id=subscription.periodicity)
 +
LEFT JOIN subscriptionhistory ON (subscriptionhistory.subscriptionid=subscription.subscriptionid)
 +
 +
 +
 +
WHERE serial.status='2'
 +
AND subscription_frequencies.id NOT IN ('4')
 +
AND serial.publisheddate BETWEEN <<Datum från|date>>-INTERVAL 1 DAY AND <<Datum till |date>>+INTERVAL 1 DAY
 +
AND subscription.branchcode IN ('Sigel1','Sigel2','Sigel3')
 +
 +
GROUP BY subscription.biblionumber
 +
HAVING COUNT(serialid)>1
 +
 +
ORDER BY biblio.title
 
</pre>
 
</pre>
  
Rad 448: Rad 504:
 
==Fråga 11 olika bestånd - om man måste utgå från hyllsignum==
 
==Fråga 11 olika bestånd - om man måste utgå från hyllsignum==
  
−
Mellansjös rapport för Specialfrågor om bestånd ser ut som följer.
+
Om man tar alternativet Alla bibliotek i en stor katalog kommer rapporten antagligen att krascha
−
Den går inte att använda rakt av i andra kataloger, eftersom vi har skapat ett auktoriserat värde för att avgränsa på kommun.
 
−
Dessutom räknar den med gallringar och nyförvärv som gjorts sedan årsskiftet - något som de flesta nog tycker är onödigt.
 
−
En förenklad variant kommer att komma längre ned
 
  
 +
<pre>
 +
SELECT
 +
CASE WHEN
 +
branches.branchname IS NULL
 +
THEN '=Summa'
 +
ELSE
 +
branches.branchname
 +
END
 +
AS Bibliotek,
 +
 +
SUM(barn) AS 'Bestånd för barn och unga',
 +
SUM(skon) AS 'Bestånd av skönlitteratur',
 +
SUM(referensexemplar) AS'Antal referensexemplar',
 +
SUM(lasnedsattning) AS 'Bestånd läsnedsättning'
 +
 +
 +
FROM
 +
(
 +
(SELECT items.homebranch, 1 as barn, 0 as skon, 0 as referensexemplar, 0 as lasnedsattning
 +
FROM items
 +
 +
WHERE items.homebranch LIKE <<Bibliotek|branches:all>>
 +
AND items.itype IN ('BARN TIDSK','BARNBOK','BOKCDBARN','BOKDAISYBA','BOKMP3BARN','BARN LJUD','BARNMP3','BARNTAL'))
 +
 +
UNION ALL
 +
 +
(SELECT items.homebranch, 0 as barn, 1 as skon, 0 as referensexemplar, 0 as lasnedsattning
 +
FROM items
 +
 +
WHERE items.homebranch LIKE <<Bibliotek|branches:all>>
 +
AND itemcallnumber LIKE BINARY '%H%'
 +
AND items.itype IN ('BARN TIDSK','BARNBOK','BOK','LANGLAN','POCKET','TIDSKRIFT'))
 +
 +
UNION ALL
 +
 +
(SELECT items.homebranch, 0 as barn, 0 as skon, 1 as referensexemplar, 0 as lasnedsattning
 +
FROM items
 +
 +
WHERE items.homebranch LIKE <<Bibliotek|branches:all>>
 +
AND items.location IN ('REFERENS'))
 +
 +
UNION ALL
 +
 +
(SELECT items.homebranch, 0 as barn, 0 as skon, 0 as referensexemplar, 1 as lasnedsattning
 +
FROM items
 +
 +
WHERE items.homebranch LIKE <<Bibliotek|branches:all>>
 +
AND items.itype IN ('BARNTAL','BOKCD','BOKCDBARN','BOKDAISYBA','BOKMP3','BOKMP3BARN','DAISY','STORSTIL','TALBOKKASS'))
 +
 +
) ds
 +
 +
LEFT JOIN branches ON (branches.branchcode=ds.homebranch)
 +
 +
GROUP BY branches.branchname WITH ROLLUP
 +
 +
</pre>
 +
 +
==Fråga 11 Olika nyförvärv==
 +
 +
<pre>
 +
SELECT
 +
  branchname,
 +
  COUNT(CASE WHEN itype IN ('BARN LJUD','BARN TIDSK','BARNBOK','BARNKORT','BOKCDBARN','BOKDAISYBA','BOKMP3BARN') THEN 1 END) 'Nyförvärv tryckt för barn'
 +
 
 +
FROM items
 +
 +
LEFT JOIN branches ON (items.homebranch=branches.branchcode)
 +
 +
WHERE items.homebranch IS NOT NULL
 +
AND items.dateaccessioned BETWEEN <<Datum från|date>>-INTERVAL 1 DAY AND <<Datum till |date>>+INTERVAL 1 DAY
 +
AND items.homebranch = <<Bibliotek|branches>>
 +
 +
 +
GROUP BY homebranch
 +
 +
</pre>
 +
 +
==Fråga 11 Olika utlån==
 +
 +
Förutsätter att man har en separat uppsättning exemplartyper för olika sorters barnmedia
 +
 +
<pre>
 +
SELECT
 +
  branchname AS Bibliotek,
 +
  COUNT(CASE WHEN type IN ('issue','renew') AND itemtype IN ('BARNBOK','BARNKORT','BOKCDBARN','BOKDAISYBA','BOKMP3BARN','BARN LJUD','BARNMP3','BARNTAL') THEN 1 END) 'Utlån för barn',
 +
  COUNT(CASE WHEN type IN ('issue','renew') AND itemtype IN ('BARNTAL','BOKCD','BOKCDBARN','BOKDAISYBA','BOKMP3','BOKMP3BARN','DAISY','STORSTIL') THEN 1 WHEN type IN ('issue','renew') AND location IN ('Lattlast','Appelhyllan') THEN 1 END) 'Utlån läsnedsättning' 
 +
FROM statistics
 +
 +
LEFT JOIN branches ON (statistics.branch=branches.branchcode)
 +
 +
WHERE statistics.branch = <<Bibliotek|branches>>
 +
AND statistics.itemtype IS NOT NULL
 +
AND statistics.datetime BETWEEN <<Datum från|date>>-INTERVAL 1 DAY AND <<Datum till |date>>+INTERVAL 1 DAY
 +
 +
 +
 +
GROUP BY branch
 +
 +
</pre>
 +
 +
==Fråga 12 Frågor om språk==
 +
 +
Bibliotek Mellansjö har ett stort antal äldre katalogposter från ett av de tidigare systemen som saknar språkkod. De flesta av dem är på svenska, så vi låter helt enkelt tom språkkod räknas som svenska
 +
<pre>
 +
SELECT
 +
CASE
 +
WHEN substring( ExtractValue(metadata,'//controlfield[@tag="008"]'), 36,3 )='swe' THEN '1 Svenska'
 +
WHEN substring( ExtractValue(metadata,'//controlfield[@tag="008"]'), 36,3 )='' THEN '1 Svenska'
 +
WHEN substring( ExtractValue(metadata,'//controlfield[@tag="008"]'), 36,3 )='fin' THEN '2 Minoritets'
 +
WHEN substring( ExtractValue(metadata,'//controlfield[@tag="008"]'), 36,3 )='9mk' THEN '2 Minoritets'
 +
WHEN substring( ExtractValue(metadata,'//controlfield[@tag="008"]'), 36,3 )='smi' THEN '2 Minoritets'
 +
WHEN substring( ExtractValue(metadata,'//controlfield[@tag="008"]'), 36,3 )='smj' THEN '2 Minoritets'
 +
WHEN substring( ExtractValue(metadata,'//controlfield[@tag="008"]'), 36,3 )='sme' THEN '2 Minoritets'
 +
WHEN substring( ExtractValue(metadata,'//controlfield[@tag="008"]'), 36,3 )='sms' THEN '2 Minoritets'
 +
WHEN substring( ExtractValue(metadata,'//controlfield[@tag="008"]'), 36,3 )='sma' THEN '2 Minoritets'
 +
WHEN substring( ExtractValue(metadata,'//controlfield[@tag="008"]'), 36,3 )='rom' THEN '2 Minoritets'
 +
WHEN substring( ExtractValue(metadata,'//controlfield[@tag="008"]'), 36,3 )='yid' THEN '2 Minoritets'
 +
ELSE '3 Utländska'
 +
END
 +
AS 'Språk',
 +
COUNT(*) AS 'Antal titlar'
 +
   
 +
FROM biblio_metadata
 +
 +
GROUP BY 1
 +
</pre>
 +
 +
Vill man räkna bara en del av katalogen lägger man till en WHERE-sats där man säger att biblionumber ska förekomma i [...] och så gör man sitt urval i en subquery.
 +
Se hur ett sådant urval kan se ut:
 +
 +
[https://koha.se/wiki/L%C3%A5nestatistik_Bibliotek_Mellansj%C3%B6#Fr.C3.A5ga_12_Fr.C3.A5gor_om_spr.C3.A5k_-_kommunvis Mellansjös språkrapport]
 +
 +
==Fråga 14 Lånestatistik==
 +
 +
<pre>
 +
SELECT
 +
  branchname AS Bibliotek,
 +
  COUNT(CASE WHEN type = 'issue' AND itemtype IN ('BARNBOK','BARNKORT','BOK','BOKCD','BOKCDBARN','BOKDAISY','BOKDAISYBA','BOKMP3','BOKMP3BARN','KORTLON','LANGLAN','PAKET','REFERENS','SPRAKKURS','STORSTIL') THEN 1 END) 'Utlån tryckt bok',
 +
  COUNT(CASE WHEN type = 'renew' AND itemtype IN ('BARNBOK','BARNKORT','BOK','BOKCD','BOKCDBARN','BOKDAICY','BOKDAISYBA','BOKMP3','BOKMP3BARN','KORTLON','LANGLAN','PAKET','REFERENS','SPRAKKURS','STORSTIL') THEN 1 END) 'Omlån tryckt bok',
 +
  COUNT(CASE WHEN type = 'issue' AND itemtype IN ('LAROMDL','LAROMTERM') THEN 1 END) 'Utlån läromedel',
 +
  COUNT(CASE WHEN type = 'renew' AND itemtype IN ('LAROMDL','LAROMTERM') THEN 1 END) 'Omlån läromedel',
 +
  COUNT(CASE WHEN type = 'issue' AND itemtype IN ('BARN LJUD','BARNMP3','LJUDBOK','MP3') THEN 1 END) 'Utlån ljudböcker',
 +
  COUNT(CASE WHEN type = 'renew' AND itemtype IN ('BARN LJUD','BARNMP3','LJUDBOK','MP3') THEN 1 END) 'Omlån ljudböcker',
 +
  COUNT(CASE WHEN type = 'issue' AND itemtype IN ('BARNTAL','DAISY') THEN 1 END) 'Utlån talböcker daisy',
 +
  COUNT(CASE WHEN type = 'renew' AND itemtype IN ('BARNTAL','DAISY') THEN 1 END) 'Omlån talböcker daisy',
 +
  COUNT(CASE WHEN type = 'issue' AND itemtype IN ('BARN TIDSK','TIDSKRIFT') THEN 1 END) 'Utlån tidskrifter',
 +
  COUNT(CASE WHEN type = 'renew' AND itemtype IN ('BARN TIDSK','TIDSKRIFT') THEN 1 END) 'Omlån tidskrifter',
 +
  COUNT(CASE WHEN type = 'issue' AND itemtype IN ('MUSIKCD') THEN 1 END) 'Utlån musik',
 +
  COUNT(CASE WHEN type = 'renew' AND itemtype IN ('MUSIKCD') THEN 1 END) 'Omlån musik',
 +
  COUNT(CASE WHEN type = 'issue' AND itemtype IN ('BLURAY','FILM','VHS') THEN 1 END) 'Utlån film',
 +
  COUNT(CASE WHEN type = 'renew' AND itemtype IN ('BLURAY','FILM','VHS') THEN 1 END) 'Omlån film',
 +
  COUNT(CASE WHEN type = 'issue' AND itemtype IN ('KARTOR') THEN 1 END) 'Utlån kartor',
 +
  COUNT(CASE WHEN type = 'renew' AND itemtype IN ('KARTOR') THEN 1 END) 'Omlån kartor',
 +
  COUNT(CASE WHEN type = 'issue' AND itemtype IN ('MUSIK','MUSIKBARN') THEN 1 END) 'Utlån noter',
 +
  COUNT(CASE WHEN type = 'renew' AND itemtype IN ('MUSIK','MUSIKBARN') THEN 1 END) 'Omlån noter',
 +
  COUNT(CASE WHEN type = 'issue' AND itemtype IN ('TV-SPEL') THEN 1 END) 'Utlån TV-spel',
 +
  COUNT(CASE WHEN type = 'renew' AND itemtype IN ('TV-SPEL') THEN 1 END) 'Omlån TV-spel',
 +
  COUNT(CASE WHEN type = 'issue' AND itemtype IN ('ARTIKEL','BLANDAT','FOREMAL','SUFRPLATTA','VECKOLAN','X') THEN 1 END) 'Utlån övrigt',
 +
  COUNT(CASE WHEN type = 'renew' AND itemtype IN ('ARTIKEL','BLANDAT','FOREMAL','SUFRPLATTA','VECKOLAN','X') THEN 1 END) 'Omlån övrigt'
 +
 +
FROM statistics
 +
 +
LEFT JOIN branches ON (statistics.branch=branches.branchcode)
 +
 +
WHERE statistics.branch=<<Bibliotek|branches>>
 +
AND statistics.itemtype IS NOT NULL
 +
AND statistics.datetime BETWEEN <<Datum från|date>>-INTERVAL 1 DAY AND <<Datum till |date>>+INTERVAL 1 DAY
 +
 +
 +
GROUP BY branch
 +
 +
ORDER BY branchname
 +
 +
</pre>
 +
 +
==Fråga 16 Fjärrlån==
 +
Fråga 16 blandar äpplen och päron.
 +
Fjärrlån-in räknar antal lån som gjorts av exemplartyp Fjärrlån.
 +
Fjärrlån-ut räknar antal lån som gjorts av låntagare i kategorin bibliotek.
 +
Listan med borrowers.cardnumber är bibliotek inom vår egen organisation, eftersom de biblioteken har låntagarkategori bibliotek men inte ska räknas som fjärrlån
 +
 +
<pre>
 +
SELECT
 +
CASE
 +
WHEN branchname IS NULL
 +
THEN '=Summa'
 +
ELSE
 +
branchname
 +
END AS Bibliotek,
 +
SUM(utlan) AS 'Utlån',
 +
SUM(inlan) AS 'Inlån'
 +
 +
FROM
 +
(
 +
(SELECT branch,1 as utlan, 0 as inlan
 +
FROM statistics
 +
 +
LEFT JOIN borrowers ON (statistics.borrowernumber=borrowers.borrowernumber)
 +
LEFT JOIN branches ON (statistics.branch=branches.branchcode)
 +
 +
WHERE type = 'issue'
 +
AND borrowers.categorycode IN ('BIBLIOTEK')
 +
AND borrowers.cardnumber NOT IN ('8bxq','8bxz','8bya','8byb','8byc','8byi','8byl','8bym','8byo','8byq','8byr','8bys','Gull','Hjo','Hova','Kabo','Kbro','Mari','Skgy','Sksb','Tida','Tikf','Tore','Vagy')
 +
AND statistics.branch LIKE <<Bibliotek|branches:all>>
 +
AND statistics.itemtype IS NOT NULL
 +
AND statistics.datetime BETWEEN <<Datum från|date>>-INTERVAL 1 DAY AND <<Datum till |date>>+INTERVAL 1 DAY
 +
)
 +
 +
UNION ALL
 +
 +
(SELECT branch,0 as utlan, 1 as inlan
 +
FROM statistics
 +
 +
LEFT JOIN borrowers ON (statistics.borrowernumber=borrowers.borrowernumber)
 +
LEFT JOIN branches ON (statistics.branch=branches.branchcode)
 +
 +
WHERE type = 'issue'
 +
AND itemtype IN ('FJARRLAN')
 +
AND statistics.branch LIKE<<Bibliotek|branches:all>>
 +
AND statistics.itemtype IS NOT NULL
 +
AND statistics.datetime BETWEEN <<Datum från|date>>-INTERVAL 1 DAY AND <<Datum till |date>>+INTERVAL 1 DAY  )
 +
) ds
 +
LEFT JOIn branches On (branches.branchcode=ds.branch)
 +
 +
GROUP BY branchname WITH ROLLUP
 +
</pre>
 +
 +
==Fråga 19 Aktiva låntagare ==
 +
 +
<pre>
 +
SELECT
 +
CASE WHEN
 +
branchname IS NULL
 +
THEN
 +
'=Summa'
 +
ELSE
 +
branchname
 +
END
 +
AS 'Låntagarens hembibliotek',
 +
COUNT(CASE WHEN code = 'PERSNUMMER' AND LEFT( RIGHT(attribute, 2), 1) % 2 = 0 THEN 1 END) 'Aktiva kvinnor',
 +
COUNT(CASE WHEN code = 'PERSNUMMER' AND LEFT( RIGHT(attribute, 2), 1) % 2 = 1 THEN 1 END) 'Aktiva män',
 +
COUNT (CASE WHEN borrowers.categorycode IN ('BIBLIOTEK','ELEVTIDA','FORSKOLA','INSTT','KVALON','PERSONAL','PTID','SKOLA','SKOLBIBTID') THEN 1 END) 'Institutionslåntagare',
 +
COUNT(CASE WHEN dateofbirth > DATE_SUB(CURDATE(), INTERVAL 18 YEAR) OR borrowers.categorycode IN ('ELEVTIDA','SKOLBIBTID','BARN') THEN 1 END) 'Under 18 år'
 +
 +
FROM borrowers
 +
 +
LEFT JOIN borrower_attributes ON (borrowers.borrowernumber=borrower_attributes.borrowernumber)
 +
LEFT JOIN branches ON (borrowers.branchcode=branches.branchcode)
 +
 +
WHERE
 +
borrowers.borrowernumber IN
 +
 +
(SELECT borrowernumber
 +
 +
FROM
 +
statistics
 +
 +
WHERE
 +
statistics.datetime BETWEEN <<Datum från|date>>-INTERVAL 1 DAY AND <<Datum till |date>>+INTERVAL 1 DAY AND statistics.branch LIKE <<Utlånande bibliotek|branches:all>> AND statistics.type='issue')
 +
 +
GROUP BY branchname WITH ROLLUP
 +
</pre>
 +
 +
==Hur man skapar ett auktoriserat värde för en kommun (eller annan grupp av bibliotek)==
 +
Bibliotek Mellansjö har 42 bibliotek som ska redovisa kommunvis av de åtta kommunerna. För att slippa köra alla rapporter 42 gånger och sedan summera resultaten har vi skapat auktoriserade värden för kommuner.
 +
 +
Först behöver man skapa grupper av bibliotek.
 +
Det gör man under Koha-administration > Biblioteksgrupper
 +
 +
När det finns färdiga grupper kan man köra denna rapport:
 +
<pre>
 +
SELECT
 +
parent_id,
 +
title,
 +
description,
 +
branchcode
 +
FROM library_groups
 +
ORDER BY parent_id
 +
</pre>
 +
 +
Då får man ett resultat som ser ut enligt nedan:
 +
 +
[https://www.skovde.se/globalassets/_2021/kultur-och-fritid/stadsbiblioteket/kohanatverket/parent_id-for-ett-bibliotek.png Bild på resultatet av rapporten]
 +
 +
Notera hur siffrorna i första kolumnen hör ihop med biblioteksgrupperna:
 +
24 för biblioteken i Gullspångs kommun
 +
28 för biblioteket i Hjo kommun
 +
45 för biblioteken i Karlsborgs kommun och så vidare
 +
 +
Gå sedan in på Administration > Auktoriserade värden > Ny kategori och döper den nya kategorin till ”librarygroupsparentid”
 +
 +
Klicka sedan på ”Nytt auktoriserat värde för librarygroupsparentid”
 +
Lägger in siffrorna du fick ovan för de olika kommunerna som auktoriserat värde och kommunnamnet som beskrivning
 +
 +
[https://www.skovde.se/globalassets/_2021/kultur-och-fritid/stadsbiblioteket/kohanatverket/auktorierade-varden-for-librarygroup.png Bild på auktoriserade värden]
 +
 +
Nedan följer Bibliotek Mellansjös kommunvisa rapporter - som förutsätter att man har gjort ett auktoriserat värde för kommuner
 +
 +
===Fråga 10 Fysiskt bestånd - kommunvis===
 +
<pre>
 +
SELECT
 +
CASE
 +
WHEN branches.branchname IS NULL
 +
THEN '=Summa'
 +
ELSE
 +
branches.branchname
 +
END
 +
AS Bibliotek,
 +
SUM(tryckt) AS 'Bestånd tryckt bok',
 +
SUM(laromedel) AS 'Bestånd läromedel',
 +
SUM(ljudbok) AS 'Bestånd ljudböcker',
 +
SUM(talbok) AS 'Bestånd talböcker daisy',
 +
SUM(musik) AS 'Bestånd musik',
 +
SUM(film) AS 'Bestånd film',
 +
SUM(kartor) AS 'Bestånd kartor',
 +
SUM(noter) 'Bestånd noter',
 +
SUM(tvspel) 'Bestånd Tv-spel',
 +
SUM(ovrigt) AS 'Bestånd övrigt'
 +
FROM
 +
(
 +
(SELECT items.homebranch,1 as tryckt,0 as laromedel,0 as ljudbok,0 as talbok,0 as musik,0 as film,0 as kartor,0 as noter,0 as tvspel,0 as ovrigt
 +
FROM items
 +
 +
LEFT JOIN branches ON (items.homebranch=branches.branchcode)
 +
LEFT JOIN library_groups ON (items.homebranch=library_groups.branchcode)
 +
LEFT JOIN biblio ON (biblio.biblionumber=items.biblionumber)
 +
 +
WHERE
 +
(biblio.frameworkcode !='BOKP')
 +
AND items.itype IN ('BARNBOK','BARNKORT','BOK','BOKCD','BOKCDBARN','BOKDAISY','BOKDAISYBA','BOKMP3','BOKMP3BARN','KORTLON','LANGLAN','PAKET','POCKET','REFERENS','SPRAKKURS','STORSTIL')
 +
AND items.homebranch IS NOT NULL AND library_groups.parent_id=<<Kommun|librarygroupsparentid>>)
 +
 +
UNION ALL
 +
 +
(SELECT items.homebranch,materials as tryckt,0 as laromedel,0 as ljudbok,0 as talbok,0 as musik,0 as film,0 as kartor,0 as noter,0 as tvspel,0 as ovrigt
 +
FROM items
 +
 +
LEFT JOIN branches ON (items.homebranch=branches.branchcode)
 +
LEFT JOIN library_groups ON (items.homebranch=library_groups.branchcode)
 +
LEFT JOIN biblio ON (biblio.biblionumber=items.biblionumber)
 +
 +
WHERE
 +
(biblio.frameworkcode ='BOKP')
 +
AND items.itype IN ('BARNBOK','BARNKORT','BOK','BOKCD','BOKCDBARN','BOKDAISY','BOKDAISYBA','BOKMP3','BOKMP3BARN','KORTLON','LANGLAN','PAKET','POCKET','REFERENS','SPRAKKURS','STORSTIL')
 +
AND items.homebranch IS NOT NULL AND library_groups.parent_id=<<Kommun|librarygroupsparentid>>)
 +
 +
UNION ALL
 +
 +
(SELECT items.homebranch,0 as tryckt,1 as laromedel,0 as ljudbok,0 as talbok,0 as musik,0 as film,0 as kartor,0 as noter,0 as tvspel,0 as ovrigt
 +
FROM items
 +
 +
LEFT JOIN branches ON (items.homebranch=branches.branchcode)
 +
LEFT JOIN library_groups ON (items.homebranch=library_groups.branchcode)
 +
 +
WHERE
 +
items.itype IN ('LAROMDL','LAROMTERM')
 +
AND items.homebranch IS NOT NULL AND library_groups.parent_id=<<Kommun|librarygroupsparentid>>)
 +
 +
UNION ALL
 +
 +
(SELECT items.homebranch,0 as tryckt,0 as laromedel,1 as ljudbok,0 as talbok,0 as musik,0 as film,0 as kartor,0 as noter,0 as tvspel,0 as ovrigt
 +
FROM items
 +
 +
LEFT JOIN branches ON (items.homebranch=branches.branchcode)
 +
LEFT JOIN library_groups ON (items.homebranch=library_groups.branchcode)
 +
 +
WHERE
 +
items.itype IN ('BARN LJUD','BARNMP3','KASSETT','LJUDBOK','MP3')
 +
AND items.homebranch IS NOT NULL AND library_groups.parent_id=<<Kommun|librarygroupsparentid>>)
 +
 +
UNION ALL
 +
 +
(SELECT items.homebranch,0 as tryckt,0 as laromedel,0 as ljudbok,1 as talbok,0 as musik,0 as film,0 as kartor,0 as noter,0 as tvspel,0 as ovrigt
 +
FROM items
 +
 +
LEFT JOIN branches ON (items.homebranch=branches.branchcode)
 +
LEFT JOIN library_groups ON (items.homebranch=library_groups.branchcode)
 +
 +
WHERE
 +
items.itype IN ('BARNTAL','DAISY','TALBOKKASS')
 +
AND items.homebranch IS NOT NULL AND library_groups.parent_id=<<Kommun|librarygroupsparentid>>)
 +
 +
UNION ALL
 +
 +
(SELECT items.homebranch,0 as tryckt,0 as laromedel,0 as ljudbok,0 as talbok,1 as musik,0 as film,0 as kartor,0 as noter,0 as tvspel,0 as ovrigt
 +
FROM items
 +
 +
LEFT JOIN branches ON (items.homebranch=branches.branchcode)
 +
LEFT JOIN library_groups ON (items.homebranch=library_groups.branchcode)
 +
 +
WHERE
 +
items.itype IN ('MUSIKCD','MUSCDBARN')
 +
AND items.homebranch IS NOT NULL AND library_groups.parent_id=<<Kommun|librarygroupsparentid>>)
 +
 +
UNION ALL
 +
 +
(SELECT items.homebranch,0 as tryckt,0 as laromedel,0 as ljudbok,0 as talbok,0 as musik,1 as film,0 as kartor,0 as noter,0 as tvspel,0 as ovrigt
 +
FROM items
 +
 +
LEFT JOIN branches ON (items.homebranch=branches.branchcode)
 +
LEFT JOIN library_groups ON (items.homebranch=library_groups.branchcode)
 +
 +
WHERE
 +
items.itype IN ('BLURAY','FILM','VHS')
 +
AND items.homebranch IS NOT NULL AND library_groups.parent_id=<<Kommun|librarygroupsparentid>>)
 +
 +
UNION ALL
 +
 +
(SELECT items.homebranch,0 as tryckt,0 as laromedel,0 as ljudbok,0 as talbok,0 as musik,0 as film,1 as kartor,0 as noter,0 as tvspel,0 as ovrigt
 +
FROM items
 +
 +
LEFT JOIN branches ON (items.homebranch=branches.branchcode)
 +
LEFT JOIN library_groups ON (items.homebranch=library_groups.branchcode)
 +
 +
WHERE
 +
items.itype IN ('KARTOR')
 +
AND items.homebranch IS NOT NULL AND library_groups.parent_id=<<Kommun|librarygroupsparentid>>)
 +
 +
UNION ALL
 +
 +
(SELECT items.homebranch,0 as tryckt,0 as laromedel,0 as ljudbok,0 as talbok,0 as musik,0 as film,0 as kartor,1 as noter,0 as tvspel,0 as ovrigt
 +
FROM items
 +
 +
LEFT JOIN branches ON (items.homebranch=branches.branchcode)
 +
LEFT JOIN library_groups ON (items.homebranch=library_groups.branchcode)
 +
 +
WHERE
 +
items.itype IN ('MUSIK','MUSIKBARN')
 +
AND items.homebranch IS NOT NULL AND library_groups.parent_id=<<Kommun|librarygroupsparentid>>)
 +
 +
UNION ALL
 +
 +
(SELECT items.homebranch,0 as tryckt,0 as laromedel,0 as ljudbok,0 as talbok,0 as musik,0 as film,0 as kartor,0 as noter,1 as tvspel,0 as ovrigt
 +
FROM items
 +
 +
LEFT JOIN branches ON (items.homebranch=branches.branchcode)
 +
LEFT JOIN library_groups ON (items.homebranch=library_groups.branchcode)
 +
 +
WHERE
 +
items.itype IN ('TV-SPEL')
 +
AND items.homebranch IS NOT NULL AND library_groups.parent_id=<<Kommun|librarygroupsparentid>>)
 +
 +
UNION ALL
 +
 +
(SELECT items.homebranch,0 as tryckt,0 as laromedel,0 as ljudbok,0 as talbok,0 as musik,0 as film,0 as kartor,0 as noter,0 as tvspel,1 as ovrigt
 +
FROM items
 +
 +
LEFT JOIN branches ON (items.homebranch=branches.branchcode)
 +
LEFT JOIN library_groups ON (items.homebranch=library_groups.branchcode)
 +
 +
WHERE
 +
items.itype IN ('BLANDAT','BOKPASE','CDROM','FOREMAL','FOREMAL3MD','KONTROLL','SALLSKAPSS','SUFRPLATTA','VECKOLAN','X')
 +
AND items.homebranch IS NOT NULL AND library_groups.parent_id=<<Kommun|librarygroupsparentid>>)
 +
 +
UNION ALL
 +
 +
(SELECT items.homebranch,-1 as tryckt,0 as laromedel,0 as ljudbok,0 as talbok,0 as musik,0 as film,0 as kartor,0 as noter,0 as tvspel,0 as ovrigt
 +
FROM items
 +
 +
LEFT JOIN branches ON (items.homebranch=branches.branchcode)
 +
LEFT JOIN library_groups ON (items.homebranch=library_groups.branchcode)
 +
 +
WHERE
 +
(items.dateaccessioned BETWEEN <<Datum (Nyårsafton i KB-statistiken)|date>> AND CURDATE())
 +
AND items.itype IN ('BARNBOK','BARNKORT','BOK','BOKCD','BOKCDBARN','BOKDAISY','BOKDAISYBA','BOKMP3','BOKMP3BARN','KORTLON','LANGLAN','PAKET','POCKET','REFERENS','SPRAKKURS','STORSTIL')
 +
AND items.homebranch IS NOT NULL AND library_groups.parent_id=<<Kommun|librarygroupsparentid>>)
 +
 +
UNION ALL
 +
 +
(SELECT items.homebranch,0 as tryckt,-1 as laromedel,0 as ljudbok,0 as talbok,0 as musik,0 as film,0 as kartor,0 as noter,0 as tvspel,0 as ovrigt
 +
FROM items
 +
 +
LEFT JOIN branches ON (items.homebranch=branches.branchcode)
 +
LEFT JOIN library_groups ON (items.homebranch=library_groups.branchcode)
 +
 +
WHERE
 +
(items.dateaccessioned BETWEEN <<Datum (Nyårsafton i KB-statistiken)|date>> AND CURDATE())
 +
AND items.itype IN ('LAROMDL','LAROMTERM')
 +
AND items.homebranch IS NOT NULL AND library_groups.parent_id=<<Kommun|librarygroupsparentid>>)
 +
 +
UNION ALL
 +
 +
(SELECT items.homebranch,0 as tryckt,0 as laromedel,-1 as ljudbok,0 as talbok,0 as musik,0 as film,0 as kartor,0 as noter,0 as tvspel,0 as ovrigt
 +
FROM items
 +
 +
LEFT JOIN branches ON (items.homebranch=branches.branchcode)
 +
LEFT JOIN library_groups ON (items.homebranch=library_groups.branchcode)
 +
 +
WHERE
 +
(items.dateaccessioned BETWEEN <<Datum (Nyårsafton i KB-statistiken)|date>> AND CURDATE())
 +
AND items.itype IN ('BARN LJUD','BARNMP3','KASSETT','LJUDBOK','MP3')
 +
AND items.homebranch IS NOT NULL AND library_groups.parent_id=<<Kommun|librarygroupsparentid>>)
 +
 +
UNION ALL
 +
 +
(SELECT items.homebranch,0 as tryckt,0 as laromedel,0 as ljudbok,-1 as talbok,0 as musik,0 as film,0 as kartor,0 as noter,0 as tvspel,0 as ovrigt
 +
FROM items
 +
 +
LEFT JOIN branches ON (items.homebranch=branches.branchcode)
 +
LEFT JOIN library_groups ON (items.homebranch=library_groups.branchcode)
 +
 +
WHERE
 +
(items.dateaccessioned BETWEEN <<Datum (Nyårsafton i KB-statistiken)|date>> AND CURDATE())
 +
AND items.itype IN ('BARNTAL','DAISY','TALBOKKASS')
 +
AND items.homebranch IS NOT NULL AND library_groups.parent_id=<<Kommun|librarygroupsparentid>>)
 +
 +
UNION ALL
 +
 +
(SELECT items.homebranch,0 as tryckt,0 as laromedel,0 as ljudbok,0 as talbok,-1 as musik,0 as film,0 as kartor,0 as noter,0 as tvspel,0 as ovrigt
 +
FROM items
 +
 +
LEFT JOIN branches ON (items.homebranch=branches.branchcode)
 +
LEFT JOIN library_groups ON (items.homebranch=library_groups.branchcode)
 +
 +
WHERE
 +
(items.dateaccessioned BETWEEN <<Datum (Nyårsafton i KB-statistiken)|date>> AND CURDATE())
 +
AND items.itype IN ('MUSIKCD','MUSCDBARN')
 +
AND items.homebranch IS NOT NULL AND library_groups.parent_id=<<Kommun|librarygroupsparentid>>)
 +
 +
UNION ALL
 +
 +
(SELECT items.homebranch,0 as tryckt,0 as laromedel,0 as ljudbok,0 as talbok,0 as musik,-1 as film,0 as kartor,0 as noter,0 as tvspel,0 as ovrigt
 +
FROM items
 +
 +
LEFT JOIN branches ON (items.homebranch=branches.branchcode)
 +
LEFT JOIN library_groups ON (items.homebranch=library_groups.branchcode)
 +
 +
WHERE
 +
(items.dateaccessioned BETWEEN <<Datum (Nyårsafton i KB-statistiken)|date>> AND CURDATE())
 +
AND items.itype IN ('BLURAY','FILM','VHS')
 +
AND items.homebranch IS NOT NULL AND library_groups.parent_id=<<Kommun|librarygroupsparentid>>)
 +
 +
UNION ALL
 +
 +
(SELECT items.homebranch,0 as tryckt,0 as laromedel,0 as ljudbok,0 as talbok,0 as musik,0 as film,-1 as kartor,0 as noter,0 as tvspel,0 as ovrigt
 +
FROM items
 +
 +
LEFT JOIN branches ON (items.homebranch=branches.branchcode)
 +
LEFT JOIN library_groups ON (items.homebranch=library_groups.branchcode)
 +
 +
WHERE
 +
(items.dateaccessioned BETWEEN <<Datum (Nyårsafton i KB-statistiken)|date>> AND CURDATE())
 +
AND items.itype IN ('KARTOR')
 +
AND items.homebranch IS NOT NULL AND library_groups.parent_id=<<Kommun|librarygroupsparentid>>)
 +
 +
UNION ALL
 +
 +
(SELECT items.homebranch,0 as tryckt,0 as laromedel,0 as ljudbok,0 as talbok,0 as musik,0 as film,0 as kartor,-1 as noter,0 as tvspel,0 as ovrigt
 +
FROM items
 +
 +
LEFT JOIN branches ON (items.homebranch=branches.branchcode)
 +
LEFT JOIN library_groups ON (items.homebranch=library_groups.branchcode)
 +
 +
WHERE
 +
(items.dateaccessioned BETWEEN <<Datum (Nyårsafton i KB-statistiken)|date>> AND CURDATE())
 +
AND items.itype IN ('MUSIK','MUSIKBARN')
 +
AND items.homebranch IS NOT NULL AND library_groups.parent_id=<<Kommun|librarygroupsparentid>>)
 +
 +
UNION ALL
 +
 +
(SELECT items.homebranch,0 as tryckt,0 as laromedel,0 as ljudbok,0 as talbok,0 as musik,0 as film,0 as kartor,0 as noter,-1 as tvspel,0 as ovrigt
 +
FROM items
 +
 +
LEFT JOIN branches ON (items.homebranch=branches.branchcode)
 +
LEFT JOIN library_groups ON (items.homebranch=library_groups.branchcode)
 +
 +
WHERE
 +
(items.dateaccessioned BETWEEN <<Datum (Nyårsafton i KB-statistiken)|date>> AND CURDATE())
 +
AND items.itype IN ('TV-SPEL')
 +
AND items.homebranch IS NOT NULL AND library_groups.parent_id=<<Kommun|librarygroupsparentid>>)
 +
 +
UNION ALL
 +
 +
(SELECT items.homebranch,0 as tryckt,0 as laromedel,0 as ljudbok,0 as talbok,0 as musik,0 as film,0 as kartor,0 as noter,0 as tvspel,-1 as ovrigt
 +
FROM items
 +
 +
LEFT JOIN branches ON (items.homebranch=branches.branchcode)
 +
LEFT JOIN library_groups ON (items.homebranch=library_groups.branchcode)
 +
 +
WHERE
 +
(items.dateaccessioned BETWEEN <<Datum (Nyårsafton i KB-statistiken)|date>> AND CURDATE())
 +
AND items.itype IN ('ARTIKEL','BLANDAT','BOKPASE','CDROM','FOREMAL','FOREMAL3MD','KONTROLL','SALLSKAPSS','SUFRPLATTA','VECKOLAN','X')
 +
AND items.homebranch IS NOT NULL AND library_groups.parent_id=<<Kommun|librarygroupsparentid>>)
 +
 +
UNION ALL
 +
 +
(SELECT deleteditems.homebranch,1 as tryckt,0 as laromedel,0 as ljudbok,0 as talbok,0 as musik,0 as film,0 as kartor,0 as noter,0 as tvspel,0 as ovrigt
 +
FROM deleteditems
 +
 +
LEFT JOIN branches ON (deleteditems.homebranch=branches.branchcode)
 +
LEFT JOIN library_groups ON (deleteditems.homebranch=library_groups.branchcode)
 +
 +
WHERE
 +
(deleteditems.timestamp BETWEEN <<Datum (Nyårsafton i KB-statistiken)|date>> AND CURDATE())
 +
AND deleteditems.itype IN ('BARNBOK','BARNKORT','BOK','BOKCD','BOKCDBARN','BOKDAISY','BOKDAISYBA','BOKMP3','BOKMP3BARN','KORTLON','LANGLAN','PAKET','POCKET','REFERENS','SPRAKKURS','STORSTIL')
 +
AND deleteditems.homebranch IS NOT NULL AND library_groups.parent_id=<<Kommun|librarygroupsparentid>>)
 +
 +
UNION ALL
 +
 +
(SELECT deleteditems.homebranch,0 as tryckt,1 as laromedel,0 as ljudbok,0 as talbok,0 as musik,0 as film,0 as kartor,0 as noter,0 as tvspel,0 as ovrigt
 +
FROM deleteditems
 +
 +
LEFT JOIN branches ON (deleteditems.homebranch=branches.branchcode)
 +
LEFT JOIN library_groups ON (deleteditems.homebranch=library_groups.branchcode)
 +
 +
WHERE
 +
(deleteditems.timestamp BETWEEN <<Datum (Nyårsafton i KB-statistiken)|date>> AND CURDATE())
 +
AND deleteditems.itype IN ('LAROMDL','LAROMTERM')
 +
AND deleteditems.homebranch IS NOT NULL AND library_groups.parent_id=<<Kommun|librarygroupsparentid>>)
 +
 +
UNION ALL
 +
 +
(SELECT deleteditems.homebranch,0 as tryckt,0 as laromedel,1 as ljudbok,0 as talbok,0 as musik,0 as film,0 as kartor,0 as noter,0 as tvspel,0 as ovrigt
 +
FROM deleteditems
 +
 +
LEFT JOIN branches ON (deleteditems.homebranch=branches.branchcode)
 +
LEFT JOIN library_groups ON (deleteditems.homebranch=library_groups.branchcode)
 +
 +
WHERE (deleteditems.timestamp BETWEEN <<Datum (Nyårsafton i KB-statistiken)|date>> AND CURDATE())
 +
AND deleteditems.itype IN ('BARN LJUD','BARNMP3','KASSETT','LJUDBOK','MP3')
 +
AND deleteditems.homebranch IS NOT NULL AND library_groups.parent_id=<<Kommun|librarygroupsparentid>>)
 +
 +
UNION ALL
 +
 +
(SELECT deleteditems.homebranch,0 as tryckt,0 as laromedel,0 as ljudbok,1 as talbok,0 as musik,0 as film,0 as kartor,0 as noter,0 as tvspel,0 as ovrigt
 +
FROM deleteditems
 +
 +
LEFT JOIN branches ON (deleteditems.homebranch=branches.branchcode)
 +
LEFT JOIN library_groups ON (deleteditems.homebranch=library_groups.branchcode)
 +
 +
WHERE
 +
(deleteditems.timestamp BETWEEN <<Datum (Nyårsafton i KB-statistiken)|date>> AND CURDATE())
 +
AND deleteditems.itype IN ('BARNTAL','DAISY','TALBOKKASS')
 +
AND deleteditems.homebranch IS NOT NULL AND library_groups.parent_id=<<Kommun|librarygroupsparentid>>)
 +
 +
UNION ALL
 +
 +
(SELECT deleteditems.homebranch,0 as tryckt,0 as laromedel,0 as ljudbok,0 as talbok,1 as musik,0 as film,0 as kartor,0 as noter,0 as tvspel,0 as ovrigt
 +
FROM deleteditems
 +
 +
LEFT JOIN branches ON (deleteditems.homebranch=branches.branchcode)
 +
LEFT JOIN library_groups ON (deleteditems.homebranch=library_groups.branchcode)
 +
 +
WHERE
 +
(deleteditems.timestamp BETWEEN <<Datum (Nyårsafton i KB-statistiken)|date>> AND CURDATE())
 +
AND deleteditems.itype IN ('MUSIKCD','MUSCDBARN')
 +
AND deleteditems.homebranch IS NOT NULL AND library_groups.parent_id=<<Kommun|librarygroupsparentid>>)
 +
 +
UNION ALL
 +
 +
(SELECT deleteditems.homebranch,0 as tryckt,0 as laromedel,0 as ljudbok,0 as talbok,0 as musik,1 as film,0 as kartor,0 as noter,0 as tvspel,0 as ovrigt
 +
FROM deleteditems
 +
 +
LEFT JOIN branches ON (deleteditems.homebranch=branches.branchcode)
 +
LEFT JOIN library_groups ON (deleteditems.homebranch=library_groups.branchcode)
 +
 +
WHERE
 +
(deleteditems.timestamp BETWEEN <<Datum (Nyårsafton i KB-statistiken)|date>> AND CURDATE())
 +
AND deleteditems.itype IN ('BLURAY','FILM','VHS')
 +
AND deleteditems.homebranch IS NOT NULL AND library_groups.parent_id=<<Kommun|librarygroupsparentid>>)
 +
 +
UNION ALL
 +
 +
(SELECT deleteditems.homebranch,0 as tryckt,0 as laromedel,0 as ljudbok,0 as talbok,0 as musik,0 as film,1 as kartor,0 as noter,0 as tvspel,0 as ovrigt
 +
FROM deleteditems
 +
 +
LEFT JOIN branches ON (deleteditems.homebranch=branches.branchcode)
 +
LEFT JOIN library_groups ON (deleteditems.homebranch=library_groups.branchcode)
 +
 +
WHERE
 +
(deleteditems.timestamp BETWEEN <<Datum (Nyårsafton i KB-statistiken)|date>> AND CURDATE())
 +
AND deleteditems.itype IN ('KARTOR')
 +
AND deleteditems.homebranch IS NOT NULL AND library_groups.parent_id=<<Kommun|librarygroupsparentid>>)
 +
 +
UNION ALL
 +
 +
(SELECT deleteditems.homebranch,0 as tryckt,0 as laromedel,0 as ljudbok,0 as talbok,0 as musik,0 as film,0 as kartor,1 as noter,0 as tvspel,0 as ovrigt
 +
FROM deleteditems
 +
 +
LEFT JOIN branches ON (deleteditems.homebranch=branches.branchcode)
 +
LEFT JOIN library_groups ON (deleteditems.homebranch=library_groups.branchcode)
 +
 +
WHERE
 +
(deleteditems.timestamp BETWEEN <<Datum (Nyårsafton i KB-statistiken)|date>> AND CURDATE())
 +
AND deleteditems.itype IN ('MUSIK','MUSIKBARN')
 +
AND deleteditems.homebranch IS NOT NULL AND library_groups.parent_id=<<Kommun|librarygroupsparentid>>)
 +
 +
UNION ALL
 +
 +
(SELECT deleteditems.homebranch,0 as tryckt,0 as laromedel,0 as ljudbok,0 as talbok,0 as musik,0 as film,0 as kartor,0 as noter,1 as tvspel,0 as ovrigt
 +
FROM deleteditems
 +
 +
LEFT JOIN branches ON (deleteditems.homebranch=branches.branchcode)
 +
LEFT JOIN library_groups ON (deleteditems.homebranch=library_groups.branchcode)
 +
 +
WHERE
 +
(deleteditems.timestamp BETWEEN <<Datum (Nyårsafton i KB-statistiken)|date>> AND CURDATE())
 +
AND deleteditems.itype IN ('TV-SPEL')
 +
AND deleteditems.homebranch IS NOT NULL AND library_groups.parent_id=<<Kommun|librarygroupsparentid>>)
 +
 +
UNION ALL
 +
 +
(SELECT deleteditems.homebranch,0 as tryckt,0 as laromedel,0 as ljudbok,0 as talbok,0 as musik,0 as film,0 as kartor,0 as noter,0 as tvspel,1 as ovrigt
 +
FROM deleteditems
 +
 +
LEFT JOIN branches ON (deleteditems.homebranch=branches.branchcode)
 +
LEFT JOIN library_groups ON (deleteditems.homebranch=library_groups.branchcode)
 +
 +
WHERE
 +
(deleteditems.timestamp BETWEEN <<Datum (Nyårsafton i KB-statistiken)|date>> AND CURDATE())
 +
AND deleteditems.itype IN ('ARTIKEL','BLANDAT','BOKPASE','CDROM','FOREMAL','FOREMAL3MD','KONTROLL','SALLSKAPSS','SUFRPLATTA','VECKOLAN','X')
 +
AND deleteditems.homebranch IS NOT NULL AND library_groups.parent_id=<<Kommun|librarygroupsparentid>>)
 +
) ds
 +
LEFT JOIN branches ON (branches.branchcode=ds.homebranch)
 +
GROUP BY branches.branchname WITH ROLLUP
 +
</pre>
 +
 +
===Fråga 10 Fysiskt nyförvärv - kommunvis===
 +
<pre>SELECT
 +
CASE
 +
WHEN branches.branchname IS NULL
 +
THEN '=Summa'
 +
ELSE
 +
branches.branchname
 +
END
 +
AS Bibliotek,
 +
SUM(tryckt) AS 'Nyförvärv tryckt bok',
 +
SUM(laromedel) AS 'Nyförvärv läromedel',
 +
SUM(ljudbok) AS 'Nyförvärv ljudböcker',
 +
SUM(talbok) AS 'Nyförvärv talböcker daisy',
 +
SUM(musik) AS 'Nyförvärv musik',
 +
SUM(film) AS 'Nyförvärv film',
 +
SUM(kartor) AS 'Nyförvärv kartor',
 +
SUM(noter) 'Nyförvärv noter',
 +
SUM(tvspel) 'Nyförvärv Tv-spel',
 +
SUM(ovrigt) AS 'Nyförvärv övrigt'
 +
FROM
 +
(
 +
(SELECT items.homebranch,1 as tryckt,0 as laromedel,0 as ljudbok,0 as talbok,0 as musik,0 as film,0 as kartor,0 as noter,0 as tvspel,0 as ovrigt
 +
FROM items
 +
 +
LEFT JOIN branches ON (items.homebranch=branches.branchcode)
 +
LEFT JOIN library_groups ON (items.homebranch=library_groups.branchcode)
 +
LEFT JOIN biblio ON (biblio.biblionumber=items.biblionumber)
 +
 +
WHERE
 +
(biblio.frameworkcode !='BOKP')
 +
AND items.dateaccessioned BETWEEN <<Datum från|date>>-INTERVAL 1 DAY AND <<Datum till |date>>+INTERVAL 1 DAY
 +
AND items.itype IN ('BARNBOK','BARNKORT','BOK','BOKCD','BOKCDBARN','BOKDAISY','BOKDAISYBA','BOKMP3','BOKMP3BARN','KORTLON','LANGLAN','PAKET','POCKET','REFERENS','SPRAKKURS','STORSTIL')
 +
AND items.homebranch IS NOT NULL AND library_groups.parent_id=<<Kommun|librarygroupsparentid>>)
 +
 +
UNION ALL
 +
 +
(SELECT items.homebranch,materials as tryckt,0 as laromedel,0 as ljudbok,0 as talbok,0 as musik,0 as film,0 as kartor,0 as noter,0 as tvspel,0 as ovrigt
 +
FROM items
 +
 +
LEFT JOIN branches ON (items.homebranch=branches.branchcode)
 +
LEFT JOIN library_groups ON (items.homebranch=library_groups.branchcode)
 +
LEFT JOIN biblio ON (biblio.biblionumber=items.biblionumber)
 +
 +
WHERE
 +
(biblio.frameworkcode ='BOKP')
 +
AND items.dateaccessioned BETWEEN <<Datum från|date>>-INTERVAL 1 DAY AND <<Datum till |date>>+INTERVAL 1 DAY
 +
AND items.itype IN ('BARNBOK','BARNKORT','BOK','BOKCD','BOKCDBARN','BOKDAISY','BOKDAISYBA','BOKMP3','BOKMP3BARN','KORTLON','LANGLAN','PAKET','POCKET','REFERENS','SPRAKKURS','STORSTIL')
 +
AND items.homebranch IS NOT NULL AND library_groups.parent_id=<<Kommun|librarygroupsparentid>>)
 +
 +
UNION ALL
 +
 +
(SELECT items.homebranch,0 as tryckt,1 as laromedel,0 as ljudbok,0 as talbok,0 as musik,0 as film,0 as kartor,0 as noter,0 as tvspel,0 as ovrigt
 +
FROM items
 +
 +
LEFT JOIN branches ON (items.homebranch=branches.branchcode)
 +
LEFT JOIN library_groups ON (items.homebranch=library_groups.branchcode)
 +
 +
WHERE
 +
items.dateaccessioned BETWEEN <<Datum från|date>>-INTERVAL 1 DAY AND <<Datum till |date>>+INTERVAL 1 DAY
 +
AND items.itype IN ('LAROMDL','LAROMTERM')
 +
AND items.homebranch IS NOT NULL AND library_groups.parent_id=<<Kommun|librarygroupsparentid>>)
 +
 +
UNION ALL
 +
 +
(SELECT items.homebranch,0 as tryckt,0 as laromedel,1 as ljudbok,0 as talbok,0 as musik,0 as film,0 as kartor,0 as noter,0 as tvspel,0 as ovrigt
 +
FROM items
 +
 +
LEFT JOIN branches ON (items.homebranch=branches.branchcode)
 +
LEFT JOIN library_groups ON (items.homebranch=library_groups.branchcode)
 +
 +
WHERE
 +
items.dateaccessioned BETWEEN <<Datum från|date>>-INTERVAL 1 DAY AND <<Datum till |date>>+INTERVAL 1 DAY
 +
AND items.itype IN ('BARN LJUD','BARNMP3','KASSETT','LJUDBOK','MP3')
 +
AND items.homebranch IS NOT NULL AND library_groups.parent_id=<<Kommun|librarygroupsparentid>>)
 +
 +
UNION ALL
 +
 +
(SELECT items.homebranch,0 as tryckt,0 as laromedel,0 as ljudbok,1 as talbok,0 as musik,0 as film,0 as kartor,0 as noter,0 as tvspel,0 as ovrigt
 +
FROM items
 +
 +
LEFT JOIN branches ON (items.homebranch=branches.branchcode)
 +
LEFT JOIN library_groups ON (items.homebranch=library_groups.branchcode)
 +
 +
WHERE
 +
items.dateaccessioned BETWEEN <<Datum från|date>>-INTERVAL 1 DAY AND <<Datum till |date>>+INTERVAL 1 DAY
 +
AND items.itype IN ('BARNTAL','DAISY','TALBOKKASS')
 +
AND items.homebranch IS NOT NULL AND library_groups.parent_id=<<Kommun|librarygroupsparentid>>)
 +
 +
UNION ALL
 +
 +
(SELECT items.homebranch,0 as tryckt,0 as laromedel,0 as ljudbok,0 as talbok,1 as musik,0 as film,0 as kartor,0 as noter,0 as tvspel,0 as ovrigt
 +
FROM items
 +
 +
LEFT JOIN branches ON (items.homebranch=branches.branchcode)
 +
LEFT JOIN library_groups ON (items.homebranch=library_groups.branchcode)
 +
 +
WHERE
 +
items.dateaccessioned BETWEEN <<Datum från|date>>-INTERVAL 1 DAY AND <<Datum till |date>>+INTERVAL 1 DAY
 +
AND items.itype IN ('MUSIKCD','MUSCDBARN')
 +
AND items.homebranch IS NOT NULL AND library_groups.parent_id=<<Kommun|librarygroupsparentid>>)
 +
 +
UNION ALL
 +
 +
(SELECT items.homebranch,0 as tryckt,0 as laromedel,0 as ljudbok,0 as talbok,0 as musik,1 as film,0 as kartor,0 as noter,0 as tvspel,0 as ovrigt
 +
FROM items
 +
 +
LEFT JOIN branches ON (items.homebranch=branches.branchcode)
 +
LEFT JOIN library_groups ON (items.homebranch=library_groups.branchcode)
 +
 +
WHERE
 +
items.dateaccessioned BETWEEN <<Datum från|date>>-INTERVAL 1 DAY AND <<Datum till |date>>+INTERVAL 1 DAY
 +
AND items.itype IN ('BLURAY','FILM','VHS')
 +
AND items.homebranch IS NOT NULL AND library_groups.parent_id=<<Kommun|librarygroupsparentid>>)
 +
 +
UNION ALL
 +
 +
(SELECT items.homebranch,0 as tryckt,0 as laromedel,0 as ljudbok,0 as talbok,0 as musik,0 as film,1 as kartor,0 as noter,0 as tvspel,0 as ovrigt
 +
FROM items
 +
 +
LEFT JOIN branches ON (items.homebranch=branches.branchcode)
 +
LEFT JOIN library_groups ON (items.homebranch=library_groups.branchcode)
 +
 +
WHERE
 +
items.dateaccessioned BETWEEN <<Datum från|date>>-INTERVAL 1 DAY AND <<Datum till |date>>+INTERVAL 1 DAY
 +
AND items.itype IN ('KARTOR')
 +
AND items.homebranch IS NOT NULL AND library_groups.parent_id=<<Kommun|librarygroupsparentid>>)
 +
 +
UNION ALL
 +
 +
(SELECT items.homebranch,0 as tryckt,0 as laromedel,0 as ljudbok,0 as talbok,0 as musik,0 as film,0 as kartor,1 as noter,0 as tvspel,0 as ovrigt
 +
FROM items
 +
 +
LEFT JOIN branches ON (items.homebranch=branches.branchcode)
 +
LEFT JOIN library_groups ON (items.homebranch=library_groups.branchcode)
 +
 +
WHERE
 +
items.dateaccessioned BETWEEN <<Datum från|date>>-INTERVAL 1 DAY AND <<Datum till |date>>+INTERVAL 1 DAY
 +
AND items.itype IN ('MUSIK')
 +
AND items.homebranch IS NOT NULL AND library_groups.parent_id=<<Kommun|librarygroupsparentid>>)
 +
 +
UNION ALL
 +
 +
(SELECT items.homebranch,0 as tryckt,0 as laromedel,0 as ljudbok,0 as talbok,0 as musik,0 as film,0 as kartor,0 as noter,1 as tvspel,0 as ovrigt
 +
FROM items
 +
 +
LEFT JOIN branches ON (items.homebranch=branches.branchcode)
 +
LEFT JOIN library_groups ON (items.homebranch=library_groups.branchcode)
 +
 +
WHERE
 +
items.dateaccessioned BETWEEN <<Datum från|date>>-INTERVAL 1 DAY AND <<Datum till |date>>+INTERVAL 1 DAY
 +
AND items.itype IN ('TV-SPEL')
 +
AND items.homebranch IS NOT NULL AND library_groups.parent_id=<<Kommun|librarygroupsparentid>>)
 +
 +
UNION ALL
 +
 +
(SELECT items.homebranch,0 as tryckt,0 as laromedel,0 as ljudbok,0 as talbok,0 as musik,0 as film,0 as kartor,0 as noter,0 as tvspel,1 as ovrigt
 +
FROM items
 +
 +
LEFT JOIN branches ON (items.homebranch=branches.branchcode)
 +
LEFT JOIN library_groups ON (items.homebranch=library_groups.branchcode)
 +
 +
WHERE
 +
items.dateaccessioned BETWEEN <<Datum från|date>>-INTERVAL 1 DAY AND <<Datum till |date>>+INTERVAL 1 DAY
 +
AND items.itype IN ('ARTIKEL','BLANDAT','BOKPASE','CDROM','FOREMAL','FOREMAL3MD','KONTROLL','SUFRPLATTA','VECKOLAN','X')
 +
AND items.homebranch IS NOT NULL AND library_groups.parent_id=<<Kommun|librarygroupsparentid>>)
 +
) ds
 +
LEFT JOIN branches ON (branches.branchcode=ds.homebranch)
 +
GROUP BY branches.branchname WITH ROLLUP
 +
</pre>
 +
 +
===Fråga 10, tidskrifter - kommunvis===
 +
<pre>
 +
SELECT biblio.title AS Titel ,GROUP_CONCAT(DISTINCT branches.branchname SEPARATOR '<br>') AS 'Bibliotek',COUNT(serialid) AS 'Antal mottagna nummer',GROUP_CONCAT(DISTINCT
 +
subscriptionhistory.histstartdate SEPARATOR '<br>') AS Startdatum,
 +
CASE
 +
WHEN subscriptionhistory.histstartdate > <<Datum från|date>>
 +
THEN
 +
'Ny'
 +
ELSE
 +
''
 +
END
 +
AS 'Ny?'
 +
 +
FROM subscription
 +
LEFT JOIN biblio ON (biblio.biblionumber=subscription.biblionumber)
 +
LEFT JOIN serial on (serial.subscriptionid=subscription.subscriptionid)
 +
LEFT JOIN library_groups ON (subscription.branchcode=library_groups.branchcode)
 +
LEFT JOIN branches ON (branches.branchcode=subscription.branchcode)
 +
LEFT JOIN subscription_frequencies ON (subscription_frequencies.id=subscription.periodicity)
 +
LEFT JOIN subscriptionhistory ON (subscriptionhistory.subscriptionid=subscription.subscriptionid)
 +
 +
 +
 +
WHERE serial.status='2'
 +
AND subscription_frequencies.id NOT IN ('4')
 +
AND serial.publisheddate BETWEEN <<Datum från|date>>-INTERVAL 1 DAY AND <<Datum till |date>>+INTERVAL 1 DAY
 +
AND library_groups.parent_id=<<Kommun|librarygroupsparentid>>
 +
 +
GROUP BY subscription.biblionumber
 +
HAVING COUNT(serialid)>1
 +
 +
ORDER BY biblio.title
 +
</pre>
 +
 +
===Fråga 11 olika bestånd - kommunvis===
 +
KB-statistiken definierar skönlitteratur som det som har hyllsignum H med underavdelningar.
 +
Eftersom ungdomslitteratur har hyllsignum uHc, uHce et cetera så har jag lagt både höger- och vänstertrunkering för H. Tyvärr gör det att man får med även signum som ''Lz Hansson, Qz Hennes & Mauritz'' - så den som vill vara noggrann får gärna fixa det
 
<pre>
 
<pre>
 
SELECT  
 
SELECT  
Rad 604: Rad 1 581:
 
</pre>
 
</pre>
  
−
==Fråga 11 Olika nyförvärv==
+
===Fråga 11 Olika nyförvärv - kommunvis===
−
 
 
 
<pre>
 
<pre>
 
SELECT  
 
SELECT  
−
  branchname,
+
CASE
−
   COUNT(CASE WHEN itype IN ('BARN LJUD','BARN TIDSK','BARNBOK','BARNKORT','BOKCDBARN','BOKDAISYBA','BOKMP3BARN') THEN 1 END) 'Nyförvärv tryckt för barn'
+
WHEN
 +
branchname IS NULL
 +
THEN '=Summa'
 +
ELSE
 +
branchname
 +
END
 +
AS Bibliotek,
 +
   COUNT(CASE WHEN itype IN ('BARN TIDSK','BARNBOK','BOKCDBARN','BOKDAISYBA','BOKMP3BARN') THEN 1 END) 'Nyförvärv tryckt för barn'
 
    
 
    
 
FROM items
 
FROM items
  
 
LEFT JOIN branches ON (items.homebranch=branches.branchcode)
 
LEFT JOIN branches ON (items.homebranch=branches.branchcode)
 +
LEFT JOIN library_groups ON (items.homebranch=library_groups.branchcode)
 +
LEFT JOIN library_groups kommun ON (library_groups.parent_id=kommun.id)
  
−
WHERE items.homebranch IS NOT NULL  
+
WHERE items.homebranch IS NOT NULL AND items.dateaccessioned BETWEEN <<Datum från|date>>-INTERVAL 1 DAY AND <<Datum till |date>>+INTERVAL 1 DAY AND library_groups.parent_id=<<Kommun|librarygroupsparentid>>
−
AND items.dateaccessioned BETWEEN <<Datum från|date>>-INTERVAL 1 DAY AND <<Datum till |date>>+INTERVAL 1 DAY  
 
−
AND items.homebranch = <<Bibliotek|branches>>
 
−
 
 
  
−
GROUP BY homebranch
 
  
 +
GROUP BY branchname WITH ROLLUP
 
</pre>
 
</pre>
  
−
==Fråga 11 Olika utlån==
+
===Fråga 11 Olika utlån - kommunvis===
−
 
 
−
Förutsätter att man har en separat uppsättning exemplartyper för olika sorters barnmedia
 
−
 
 
 
<pre>
 
<pre>
 
SELECT  
 
SELECT  
−
  branchname AS Bibliotek,
+
CASE
−
  COUNT(CASE WHEN type IN ('issue','renew') AND itemtype IN ('BARNBOK','BARNKORT','BOKCDBARN','BOKDAISYBA','BOKMP3BARN','BARN LJUD','BARNMP3','BARNTAL') THEN 1 END) 'Utlån för barn',
+
WHEN branchname IS NULL
−
  COUNT(CASE WHEN type IN ('issue','renew') AND itemtype IN ('BARNTAL','BOKCD','BOKCDBARN','BOKDAISYBA','BOKMP3','BOKMP3BARN','DAISY','STORSTIL') THEN 1 WHEN type IN ('issue','renew') AND location IN ('Lattlast','Appelhyllan') THEN 1 END) 'Utlån läsnedsättning' 
+
THEN '=Summa'
 +
ELSE
 +
branchname
 +
END
 +
AS Bibliotek, SUM(barn) AS 'Utlån barn', SUM (lasned) AS 'Utlån läsnedsättning'
 +
FROM
 +
((SELECT branch, 1 as barn, 0 as lasned
 +
 
 
FROM statistics
 
FROM statistics
 +
LEFT JOIN library_groups ON (statistics.branch=library_groups.branchcode)
  
−
LEFT JOIN branches ON (statistics.branch=branches.branchcode)
+
WHERE type IN('issue','renew')  
−
 
+
AND itemtype IN ('BARNBOK','BARNKORT','BARN TIDSK','BOKCDBARN','BOKDAISYBA','BOKMP3BARN','BARN LJUD','BARNMP3','BARNTAL','MUSCDBARN')
−
WHERE statistics.branch = <<Bibliotek|branches>>
+
AND statistics.branch IS NOT NULL
 
AND statistics.itemtype IS NOT NULL  
 
AND statistics.itemtype IS NOT NULL  
 
AND statistics.datetime BETWEEN <<Datum från|date>>-INTERVAL 1 DAY AND <<Datum till |date>>+INTERVAL 1 DAY  
 
AND statistics.datetime BETWEEN <<Datum från|date>>-INTERVAL 1 DAY AND <<Datum till |date>>+INTERVAL 1 DAY  
−
+
AND library_groups.parent_id=<<Kommun|librarygroupsparentid>>)
  
 +
UNION ALL
  
−
GROUP BY branch
+
(SELECT branch, 0 as barn, 1 as lasned
  
−
</pre>
+
FROM statistics
 +
LEFT JOIN library_groups ON (statistics.branch=library_groups.branchcode)
  
−
==Fråga 12 Svenska==
+
WHERE type IN('issue','renew')
−
Mellansjö har 45.000 titlar som saknar språkkod i fält 008, vi har valt att räkna dem som svenska
+
AND itemtype IN ('BARNTAL','BOKCD','BOKCDBARN','BOKDAISYBA','BOKMP3','BOKMP3BARN','DAISY','STORSTIL','TALBOKKASS')
 +
AND statistics.branch IS NOT NULL
 +
AND statistics.itemtype IS NOT NULL
 +
AND statistics.datetime BETWEEN <<Datum från|date>>-INTERVAL 1 DAY AND <<Datum till |date>>+INTERVAL 1 DAY
 +
AND library_groups.parent_id=<<Kommun|librarygroupsparentid>>)
  
−
<pre>
+
UNION ALL
−
SELECT
 
−
branchname AS Bibliotek,
 
−
COUNT(*) AS 'Antal svenska tilar'
 
  
−
FROM biblio_metadata
+
(SELECT branch, 1 as barn, 0 as lasned
  
−
LEFT JOIN biblioitems ON (biblio_metadata.biblionumber=biblioitems.biblionumber)
+
FROM statistics
−
LEFT JOIN items On (biblioitems.biblionumber=items.biblionumber)
+
LEFT JOIN library_groups ON (statistics.branch=library_groups.branchcode)
−
LEFT JOIN branches ON (items.homebranch=branches.branchcode)
+
 
 +
WHERE type IN('issue','renew')  
 +
AND location IN ('Lattlast','Appelhyllan')
 +
AND statistics.branch IS NOT NULL
 +
AND statistics.itemtype IS NOT NULL
 +
AND statistics.datetime BETWEEN <<Datum från|date>>-INTERVAL 1 DAY AND <<Datum till |date>>+INTERVAL 1 DAY
 +
AND library_groups.parent_id=<<Kommun|librarygroupsparentid>>)  
  
−
WHERE (SUBSTRING(ExtractValue(metadata, '//controlfield[@tag="008"]'), 36, 3) = 'swe' OR
+
)ds
−
SUBSTRING(ExtractValue(metadata, '//controlfield[@tag="008"]'), 36, 3) = '')  AND
 
−
items.homebranch=<<Bibliotek|branches>>
 
  
 +
LEFT JOIN branches ON (branches.branchcode=ds.branch)
 +
GROUP BY branchname WITH ROLLUP
 
</pre>
 
</pre>
  
−
==Fråga 12 Minoritetsspråk==
+
===Fråga 12 Frågor om språk - kommunvis===
−
 
 
 
<pre>
 
<pre>
−
SELECT
+
SELECT  
−
branchname AS Bibliotek,
+
CASE
−
COUNT(*) AS 'Antal titlar på minoritetsspråk'
+
WHEN substring( ExtractValue(metadata,'//controlfield[@tag="008"]'), 36,3 )='swe' THEN '1 Svenska'
 +
WHEN substring( ExtractValue(metadata,'//controlfield[@tag="008"]'), 36,3 )='' THEN '1 Svenska'
 +
WHEN substring( ExtractValue(metadata,'//controlfield[@tag="008"]'), 36,3 )='fin' THEN '2 Minoritets'
 +
WHEN substring( ExtractValue(metadata,'//controlfield[@tag="008"]'), 36,3 )='9mk' THEN '2 Minoritets'
 +
WHEN substring( ExtractValue(metadata,'//controlfield[@tag="008"]'), 36,3 )='smi' THEN '2 Minoritets'
 +
WHEN substring( ExtractValue(metadata,'//controlfield[@tag="008"]'), 36,3 )='smj' THEN '2 Minoritets'
 +
WHEN substring( ExtractValue(metadata,'//controlfield[@tag="008"]'), 36,3 )='sme' THEN '2 Minoritets'
 +
WHEN substring( ExtractValue(metadata,'//controlfield[@tag="008"]'), 36,3 )='sms' THEN '2 Minoritets'
 +
WHEN substring( ExtractValue(metadata,'//controlfield[@tag="008"]'), 36,3 )='sma' THEN '2 Minoritets'
 +
WHEN substring( ExtractValue(metadata,'//controlfield[@tag="008"]'), 36,3 )='rom' THEN '2 Minoritets'
 +
WHEN substring( ExtractValue(metadata,'//controlfield[@tag="008"]'), 36,3 )='yid' THEN '2 Minoritets'
 +
ELSE '3 Utländska'
 +
END
 +
AS 'Språk',
 +
COUNT(*) AS 'Antal titlar'
 +
   
 +
FROM biblio_metadata
  
−
FROM biblio_metadata
+
WHERE
 +
biblio_metadata.biblionumber
 +
IN
 +
(SELECT items.biblionumber
  
−
LEFT JOIN biblioitems ON (biblio_metadata.biblionumber=biblioitems.biblionumber)
+
FROM items
−
LEFT JOIN items On (biblioitems.biblionumber=items.biblionumber)
+
LEFT JOIN library_groups ON (items.homebranch=library_groups.branchcode)
−
LEFT JOIN branches ON (items.homebranch=branches.branchcode)
+
 
 +
WHERE items.homebranch IS NOT NULL
 +
AND library_groups.parent_id=<<Kommun|librarygroupsparentid>>)  
  
−
WHERE (SUBSTRING(ExtractValue(metadata, '//controlfield[@tag="008"]'), 36, 3) = 'fin' OR
+
GROUP BY 1
−
SUBSTRING(ExtractValue(metadata, '//controlfield[@tag="008"]'), 36, 3) = 'smi' OR
 
−
SUBSTRING(ExtractValue(metadata, '//controlfield[@tag="008"]'), 36, 3) = '9mk' OR
 
−
SUBSTRING(ExtractValue(metadata, '//controlfield[@tag="008"]'), 36, 3) = 'rom' OR
 
−
SUBSTRING(ExtractValue(metadata, '//controlfield[@tag="008"]'), 36, 3) = 'yid' )  AND
 
−
items.homebranch=<<Bibliotek|branches>>
 
 
</pre>
 
</pre>
  
−
==Fråga 12 Utländska==
+
===Fråga 14 Lånestatistik - kommunvis===
 +
<pre>
 +
SELECT
 +
CASE
 +
WHEN branchname IS NULL
 +
THEN
 +
'=Summa'
 +
ELSE
 +
branchname
 +
END
 +
AS Bibliotek,
 +
SUM(utltryckt) AS 'Utlån tryckt bok',SUM(omltryckt) AS 'Omlån tryckt bok',
 +
SUM(utllarom) AS 'Utlån läromedel',SUM(omllarom) AS 'Omlån läromedel',
 +
SUM(utlljudbok) AS 'Utlån ljudböcker',SUM(omlljudbok) AS 'Omlån ljudböcker',
 +
SUM(utltalbok) AS 'Utlån talböcker daisy',SUM(omltalbok) AS 'Omlån talböcker daisy',
 +
SUM(utltskr) AS 'Utlån tidskrifter',SUM(omltskr) AS 'Omlån tidskrifter',
 +
SUM(utlmusik) AS 'Utlån musik',SUM(omlmusik) AS 'Omlån musik',
 +
SUM(utlfilm) AS 'Utlån film',SUM(omlfilm) AS 'Omlån film',
 +
SUM(utlkartor) AS 'Utlån kartor',SUM(omlkartor) AS 'Omlån kartor',
 +
SUM(utlnoter) AS 'Utlån noter',SUM(omlnoter) AS 'Omlån noter',
 +
SUM(utlinterakt) AS 'Utlån Interaktiva medier',SUM(omlinterakt) AS 'Omlån Interaktiva medier',
 +
SUM(utlovr) AS 'Utlån övrigt',SUM(omlovr) AS 'Omlån övrigt'
  
−
<pre>
+
FROM
−
SELECT
+
(
−
branchname AS Bibliotek,
+
(SELECT branch,
−
COUNT(*) AS 'Antal utländska titlar'
+
1 as utltryckt, 0 as omltryckt,
 +
0 as utllarom, 0 as omllarom,
 +
0 as utlljudbok, 0 as omlljudbok,
 +
0 as utltalbok, 0 as omltalbok,
 +
0 as utltskr, 0 as omltskr,
 +
0 as utlmusik, 0 as omlmusik,
 +
0 as utlfilm, 0 as omlfilm,
 +
0 as utlkartor, 0 as omlkartor,
 +
0 as utlnoter, 0 as omlnoter,
 +
0 as utlinterakt, 0 as omlinterakt,
 +
0 as utlovr, 0 as omlovr
  
−
FROM biblio_metadata
+
  FROM statistics
 +
  LEFT JOIN items ON (statistics.itemnumber=items.itemnumber)
 +
  LEFT JOIN biblio ON (items.biblionumber=biblio.biblionumber)
 +
  LEFT JOIN library_groups ON (statistics.branch=library_groups.branchcode)
 +
 
 +
  WHERE type = 'issue'
 +
  AND itemtype IN ('BARNBOK','BOK','BOKCD','BOKCDBARN','BOKDAISY','BOKDAISYBA','BOKMP3','BOKMP3BARN','BOKPASE','LANGLAN','PAKET','POCKET','REFERENS','SPRAKKURS','STORSTIL') 
 +
  AND frameworkcode !='BOKP'
 +
  AND statistics.branch IS NOT NULL
 +
  AND statistics.itemtype IS NOT NULL
 +
  AND statistics.datetime BETWEEN <<Datum från|date>>-INTERVAL 1 DAY AND <<Datum till |date>>+INTERVAL 1 DAY
 +
  AND library_groups.parent_id=<<Kommun|librarygroupsparentid>>)
 +
 
 +
  UNION ALL
 +
 
 +
  (SELECT branch,
 +
materials as utltryckt, 0 as omltryckt,
 +
0 as utllarom, 0 as omllarom,
 +
0 as utlljudbok, 0 as omlljudbok,
 +
0 as utltalbok, 0 as omltalbok,
 +
0 as utltskr, 0 as omltskr,
 +
0 as utlmusik, 0 as omlmusik,
 +
0 as utlfilm, 0 as omlfilm,
 +
0 as utlkartor, 0 as omlkartor,
 +
0 as utlnoter, 0 as omlnoter,
 +
0 as utlinterakt, 0 as omlinterakt,
 +
0 as utlovr, 0 as omlovr
  
−
LEFT JOIN biblioitems ON (biblio_metadata.biblionumber=biblioitems.biblionumber)
+
  FROM statistics
−
LEFT JOIN items On (biblioitems.biblionumber=items.biblionumber)
+
  LEFT JOIN items ON (statistics.itemnumber=items.itemnumber)
−
LEFT JOIN branches ON (items.homebranch=branches.branchcode)
+
  LEFT JOIN biblio ON (items.biblionumber=biblio.biblionumber)
 +
  LEFT JOIN library_groups ON (statistics.branch=library_groups.branchcode)
 +
 
 +
  WHERE type = 'issue'
 +
  AND itemtype IN ('BARNBOK','BOK','BOKCD','BOKCDBARN','BOKDAISY','BOKDAISYBA','BOKMP3','BOKMP3BARN','BOKPASE','LANGLAN','PAKET','POCKET','REFERENS','SPRAKKURS','STORSTIL') 
 +
  AND frameworkcode ='BOKP'
 +
  AND statistics.branch IS NOT NULL
 +
  AND statistics.itemtype IS NOT NULL
 +
  AND statistics.datetime BETWEEN <<Datum från|date>>-INTERVAL 1 DAY AND <<Datum till |date>>+INTERVAL 1 DAY
 +
  AND library_groups.parent_id=<<Kommun|librarygroupsparentid>>)
 +
 
 +
  UNION ALL
 +
 
 +
  (SELECT branch,
 +
0 as utltryckt, 0 as omltryckt,
 +
1 as utllarom, 0 as omllarom,
 +
0 as utlljudbok, 0 as omlljudbok,
 +
0 as utltalbok, 0 as omltalbok,
 +
0 as utltskr, 0 as omltskr,
 +
0 as utlmusik, 0 as omlmusik,
 +
0 as utlfilm, 0 as omlfilm,
 +
0 as utlkartor, 0 as omlkartor,
 +
0 as utlnoter, 0 as omlnoter,
 +
0 as utlinterakt, 0 as omlinterakt,
 +
0 as utlovr, 0 as omlovr
  
−
WHERE SUBSTRING(ExtractValue(metadata, '//controlfield[@tag="008"]'), 36, 3) != 'swe' AND  
+
  FROM statistics
−
SUBSTRING(ExtractValue(metadata, '//controlfield[@tag="008"]'), 36, 3) != 'fin' AND  
+
  LEFT JOIN items ON (statistics.itemnumber=items.itemnumber)
−
SUBSTRING(ExtractValue(metadata, '//controlfield[@tag="008"]'), 36, 3) != 'smi' AND
+
  LEFT JOIN biblio ON (items.biblionumber=biblio.biblionumber)
−
SUBSTRING(ExtractValue(metadata, '//controlfield[@tag="008"]'), 36, 3) != '9mk' AND
+
  LEFT JOIN library_groups ON (statistics.branch=library_groups.branchcode)
−
SUBSTRING(ExtractValue(metadata, '//controlfield[@tag="008"]'), 36, 3) != 'rom' AND
+
 
−
SUBSTRING(ExtractValue(metadata, '//controlfield[@tag="008"]'), 36, 3) != 'yid' AND
+
  WHERE type = 'issue'  
−
SUBSTRING(ExtractValue(metadata, '//controlfield[@tag="008"]'), 36, 3) != '' AND
+
  AND itemtype IN ('LAROMDL','LAROMTERM') 
−
items.homebranch=<<Bibliotek|branches>>
+
  AND statistics.branch IS NOT NULL
−
</pre>
+
  AND statistics.itemtype IS NOT NULL
 +
  AND statistics.datetime BETWEEN <<Datum från|date>>-INTERVAL 1 DAY AND <<Datum till |date>>+INTERVAL 1 DAY
 +
  AND library_groups.parent_id=<<Kommun|librarygroupsparentid>>)
 +
 
 +
  UNION ALL
 +
 
 +
  (SELECT branch,  
 +
0 as utltryckt, 0 as omltryckt,  
 +
0 as utllarom, 0 as omllarom,  
 +
1 as utlljudbok, 0 as omlljudbok,  
 +
0 as utltalbok, 0 as omltalbok,  
 +
0 as utltskr, 0 as omltskr,  
 +
0 as utlmusik, 0 as omlmusik,
 +
0 as utlfilm, 0 as omlfilm,
 +
0 as utlkartor, 0 as omlkartor,
 +
0 as utlnoter, 0 as omlnoter,
 +
0 as utlinterakt, 0 as omlinterakt,
 +
0 as utlovr, 0 as omlovr
  
−
==Fråga 14 Lånestatistik==
+
  FROM statistics
 +
  LEFT JOIN items ON (statistics.itemnumber=items.itemnumber)
 +
  LEFT JOIN biblio ON (items.biblionumber=biblio.biblionumber)
 +
  LEFT JOIN library_groups ON (statistics.branch=library_groups.branchcode)
 +
 
 +
  WHERE type = 'issue'
 +
  AND itemtype IN ('BARN LJUD','BARNMP3','KASSETT','LJUDBOK','MP3')
 +
  AND statistics.branch IS NOT NULL
 +
  AND statistics.itemtype IS NOT NULL
 +
  AND statistics.datetime BETWEEN <<Datum från|date>>-INTERVAL 1 DAY AND <<Datum till |date>>+INTERVAL 1 DAY
 +
  AND library_groups.parent_id=<<Kommun|librarygroupsparentid>>)
 +
 
 +
  UNION ALL
 +
 
 +
  (SELECT branch,
 +
0 as utltryckt, 0 as omltryckt,
 +
0 as utllarom, 0 as omllarom,
 +
0 as utlljudbok, 0 as omlljudbok,
 +
1 as utltalbok, 0 as omltalbok,
 +
0 as utltskr, 0 as omltskr,
 +
0 as utlmusik, 0 as omlmusik,
 +
0 as utlfilm, 0 as omlfilm,
 +
0 as utlkartor, 0 as omlkartor,
 +
0 as utlnoter, 0 as omlnoter,
 +
0 as utlinterakt, 0 as omlinterakt,
 +
0 as utlovr, 0 as omlovr
  
−
<pre>
+
   FROM statistics
−
SELECT
+
   LEFT JOIN items ON (statistics.itemnumber=items.itemnumber)
−
   branchname AS Bibliotek,
+
   LEFT JOIN biblio ON (items.biblionumber=biblio.biblionumber)
−
   COUNT(CASE WHEN type = 'issue' AND itemtype IN ('BARNBOK','BARNKORT','BOK','BOKCD','BOKCDBARN','BOKDAISY','BOKDAISYBA','BOKMP3','BOKMP3BARN','KORTLON','LANGLAN','PAKET','REFERENS','SPRAKKURS','STORSTIL') THEN 1 END) 'Utlån tryckt bok',
+
   LEFT JOIN library_groups ON (statistics.branch=library_groups.branchcode)
−
   COUNT(CASE WHEN type = 'renew' AND itemtype IN ('BARNBOK','BARNKORT','BOK','BOKCD','BOKCDBARN','BOKDAICY','BOKDAISYBA','BOKMP3','BOKMP3BARN','KORTLON','LANGLAN','PAKET','REFERENS','SPRAKKURS','STORSTIL') THEN 1 END) 'Omlån tryckt bok',
+
    
−
   COUNT(CASE WHEN type = 'issue' AND itemtype IN ('LAROMDL','LAROMTERM') THEN 1 END) 'Utlån läromedel',
+
   WHERE type = 'issue'  
−
   COUNT(CASE WHEN type = 'renew' AND itemtype IN ('LAROMDL','LAROMTERM') THEN 1 END) 'Omlån läromedel',
+
  AND itemtype IN ('BARNTAL','DAISY','TALBOKKASS')
−
   COUNT(CASE WHEN type = 'issue' AND itemtype IN ('BARN LJUD','BARNMP3','LJUDBOK','MP3') THEN 1 END) 'Utlån ljudböcker',
+
   AND statistics.branch IS NOT NULL
−
   COUNT(CASE WHEN type = 'renew' AND itemtype IN ('BARN LJUD','BARNMP3','LJUDBOK','MP3') THEN 1 END) 'Omlån ljudböcker',
+
   AND statistics.itemtype IS NOT NULL
−
   COUNT(CASE WHEN type = 'issue' AND itemtype IN ('BARNTAL','DAISY') THEN 1 END) 'Utlån talböcker daisy',
+
   AND statistics.datetime BETWEEN <<Datum från|date>>-INTERVAL 1 DAY AND <<Datum till |date>>+INTERVAL 1 DAY
−
   COUNT(CASE WHEN type = 'renew' AND itemtype IN ('BARNTAL','DAISY') THEN 1 END) 'Omlån talböcker daisy',
+
   AND library_groups.parent_id=<<Kommun|librarygroupsparentid>>)
−
  COUNT(CASE WHEN type = 'issue' AND itemtype IN ('BARN TIDSK','TIDSKRIFT') THEN 1 END) 'Utlån tidskrifter',
+
    
−
   COUNT(CASE WHEN type = 'renew' AND itemtype IN ('BARN TIDSK','TIDSKRIFT') THEN 1 END) 'Omlån tidskrifter',
+
   UNION ALL
−
   COUNT(CASE WHEN type = 'issue' AND itemtype IN ('MUSIKCD') THEN 1 END) 'Utlån musik',
+
    
−
   COUNT(CASE WHEN type = 'renew' AND itemtype IN ('MUSIKCD') THEN 1 END) 'Omlån musik',
+
   (SELECT branch,  
−
   COUNT(CASE WHEN type = 'issue' AND itemtype IN ('BLURAY','FILM','VHS') THEN 1 END) 'Utlån film',
+
0 as utltryckt, 0 as omltryckt,  
−
   COUNT(CASE WHEN type = 'renew' AND itemtype IN ('BLURAY','FILM','VHS') THEN 1 END) 'Omlån film',
+
0 as utllarom, 0 as omllarom,  
−
  COUNT(CASE WHEN type = 'issue' AND itemtype IN ('KARTOR') THEN 1 END) 'Utlån kartor',
+
0 as utlljudbok, 0 as omlljudbok,  
−
  COUNT(CASE WHEN type = 'renew' AND itemtype IN ('KARTOR') THEN 1 END) 'Omlån kartor',
+
0 as utltalbok, 0 as omltalbok,  
−
  COUNT(CASE WHEN type = 'issue' AND itemtype IN ('MUSIK','MUSIKBARN') THEN 1 END) 'Utlån noter',
+
1 as utltskr, 0 as omltskr,  
−
  COUNT(CASE WHEN type = 'renew' AND itemtype IN ('MUSIK','MUSIKBARN') THEN 1 END) 'Omlån noter',
+
0 as utlmusik, 0 as omlmusik,
−
  COUNT(CASE WHEN type = 'issue' AND itemtype IN ('TV-SPEL') THEN 1 END) 'Utlån TV-spel',
+
0 as utlfilm, 0 as omlfilm,
−
  COUNT(CASE WHEN type = 'renew' AND itemtype IN ('TV-SPEL') THEN 1 END) 'Omlån TV-spel',
+
0 as utlkartor, 0 as omlkartor,
−
  COUNT(CASE WHEN type = 'issue' AND itemtype IN ('ARTIKEL','BLANDAT','FOREMAL','SUFRPLATTA','VECKOLAN','X') THEN 1 END) 'Utlån övrigt',
+
0 as utlnoter, 0 as omlnoter,
−
  COUNT(CASE WHEN type = 'renew' AND itemtype IN ('ARTIKEL','BLANDAT','FOREMAL','SUFRPLATTA','VECKOLAN','X') THEN 1 END) 'Omlån övrigt'
+
0 as utlinterakt, 0 as omlinterakt,
 +
0 as utlovr, 0 as omlovr
  
−
FROM statistics
+
  FROM statistics  
 +
  LEFT JOIN items ON (statistics.itemnumber=items.itemnumber)
 +
  LEFT JOIN biblio ON (items.biblionumber=biblio.biblionumber)
 +
  LEFT JOIN library_groups ON (statistics.branch=library_groups.branchcode)
 +
 
 +
  WHERE type = 'issue'
 +
  AND itemtype IN ('BARN TIDSK','TIDSKRIFT')
 +
  AND statistics.branch IS NOT NULL
 +
  AND statistics.itemtype IS NOT NULL
 +
  AND statistics.datetime BETWEEN <<Datum från|date>>-INTERVAL 1 DAY AND <<Datum till |date>>+INTERVAL 1 DAY
 +
  AND library_groups.parent_id=<<Kommun|librarygroupsparentid>>)
 +
 
 +
  UNION ALL
 +
 
 +
  (SELECT branch,
 +
0 as utltryckt, 0 as omltryckt,
 +
0 as utllarom, 0 as omllarom,
 +
0 as utlljudbok, 0 as omlljudbok,
 +
0 as utltalbok, 0 as omltalbok,
 +
0 as utltskr, 0 as omltskr,
 +
1 as utlmusik, 0 as omlmusik,
 +
0 as utlfilm, 0 as omlfilm,
 +
0 as utlkartor, 0 as omlkartor,
 +
0 as utlnoter, 0 as omlnoter,
 +
0 as utlinterakt, 0 as omlinterakt,
 +
0 as utlovr, 0 as omlovr
  
−
LEFT JOIN branches ON (statistics.branch=branches.branchcode)
+
  FROM statistics
 +
  LEFT JOIN items ON (statistics.itemnumber=items.itemnumber)
 +
  LEFT JOIN biblio ON (items.biblionumber=biblio.biblionumber)
 +
  LEFT JOIN library_groups ON (statistics.branch=library_groups.branchcode)
 +
 
 +
  WHERE type = 'issue'
 +
  AND itemtype IN ('MUSIKCD','MUSCDBARN')
 +
  AND statistics.branch IS NOT NULL
 +
  AND statistics.itemtype IS NOT NULL
 +
  AND statistics.datetime BETWEEN <<Datum från|date>>-INTERVAL 1 DAY AND <<Datum till |date>>+INTERVAL 1 DAY
 +
  AND library_groups.parent_id=<<Kommun|librarygroupsparentid>>)
 +
 
 +
  UNION ALL
 +
 
 +
  (SELECT branch,
 +
0 as utltryckt, 0 as omltryckt,
 +
0 as utllarom, 0 as omllarom,
 +
0 as utlljudbok, 0 as omlljudbok,
 +
0 as utltalbok, 0 as omltalbok,
 +
0 as utltskr, 0 as omltskr,
 +
0 as utlmusik, 0 as omlmusik,
 +
1 as utlfilm, 0 as omlfilm,
 +
0 as utlkartor, 0 as omlkartor,
 +
0 as utlnoter, 0 as omlnoter,
 +
0 as utlinterakt, 0 as omlinterakt,
 +
0 as utlovr, 0 as omlovr
  
−
WHERE statistics.branch=<<Bibliotek|branches>>
+
  FROM statistics
−
AND statistics.itemtype IS NOT NULL  
+
  LEFT JOIN items ON (statistics.itemnumber=items.itemnumber)
−
AND statistics.datetime BETWEEN <<Datum från|date>>-INTERVAL 1 DAY AND <<Datum till |date>>+INTERVAL 1 DAY  
+
  LEFT JOIN biblio ON (items.biblionumber=biblio.biblionumber)
 +
  LEFT JOIN library_groups ON (statistics.branch=library_groups.branchcode)
 +
 
 +
  WHERE type = 'issue'
 +
  AND itemtype IN ('BLURAY','FILM','VHS')
 +
  AND statistics.branch IS NOT NULL
 +
  AND statistics.itemtype IS NOT NULL  
 +
  AND statistics.datetime BETWEEN <<Datum från|date>>-INTERVAL 1 DAY AND <<Datum till |date>>+INTERVAL 1 DAY  
 +
  AND library_groups.parent_id=<<Kommun|librarygroupsparentid>>)
 +
 
 +
  UNION ALL
 +
 
 +
  (SELECT branch,
 +
0 as utltryckt, 0 as omltryckt,
 +
0 as utllarom, 0 as omllarom,
 +
0 as utlljudbok, 0 as omlljudbok,
 +
0 as utltalbok, 0 as omltalbok,
 +
0 as utltskr, 0 as omltskr,
 +
0 as utlmusik, 0 as omlmusik,
 +
0 as utlfilm, 0 as omlfilm,
 +
1 as utlkartor, 0 as omlkartor,
 +
0 as utlnoter, 0 as omlnoter,
 +
0 as utlinterakt, 0 as omlinterakt,
 +
0 as utlovr, 0 as omlovr
  
 +
  FROM statistics
 +
  LEFT JOIN items ON (statistics.itemnumber=items.itemnumber)
 +
  LEFT JOIN biblio ON (items.biblionumber=biblio.biblionumber)
 +
  LEFT JOIN library_groups ON (statistics.branch=library_groups.branchcode)
 +
 
 +
  WHERE type = 'issue'
 +
  AND itemtype IN ('KARTOR')
 +
  AND statistics.branch IS NOT NULL
 +
  AND statistics.itemtype IS NOT NULL
 +
  AND statistics.datetime BETWEEN <<Datum från|date>>-INTERVAL 1 DAY AND <<Datum till |date>>+INTERVAL 1 DAY
 +
  AND library_groups.parent_id=<<Kommun|librarygroupsparentid>>)
 +
 
 +
  UNION ALL
 +
 
 +
  (SELECT branch,
 +
0 as utltryckt, 0 as omltryckt,
 +
0 as utllarom, 0 as omllarom,
 +
0 as utlljudbok, 0 as omlljudbok,
 +
0 as utltalbok, 0 as omltalbok,
 +
0 as utltskr, 0 as omltskr,
 +
0 as utlmusik, 0 as omlmusik,
 +
0 as utlfilm, 0 as omlfilm,
 +
0 as utlkartor, 0 as omlkartor,
 +
1 as utlnoter, 0 as omlnoter,
 +
0 as utlinterakt, 0 as omlinterakt,
 +
0 as utlovr, 0 as omlovr
  
−
GROUP BY branch
+
  FROM statistics
 +
  LEFT JOIN items ON (statistics.itemnumber=items.itemnumber)
 +
  LEFT JOIN biblio ON (items.biblionumber=biblio.biblionumber)
 +
  LEFT JOIN library_groups ON (statistics.branch=library_groups.branchcode)
 +
 
 +
  WHERE type = 'issue'
 +
  AND itemtype IN ('MUSIK','MUSIKBARN')
 +
  AND statistics.branch IS NOT NULL
 +
  AND statistics.itemtype IS NOT NULL
 +
  AND statistics.datetime BETWEEN <<Datum från|date>>-INTERVAL 1 DAY AND <<Datum till |date>>+INTERVAL 1 DAY
 +
  AND library_groups.parent_id=<<Kommun|librarygroupsparentid>>)
 +
 
 +
  UNION ALL
 +
 
 +
  (SELECT branch,
 +
0 as utltryckt, 0 as omltryckt,
 +
0 as utllarom, 0 as omllarom,
 +
0 as utlljudbok, 0 as omlljudbok,
 +
0 as utltalbok, 0 as omltalbok,
 +
0 as utltskr, 0 as omltskr,
 +
0 as utlmusik, 0 as omlmusik,
 +
0 as utlfilm, 0 as omlfilm,
 +
0 as utlkartor, 0 as omlkartor,
 +
0 as utlnoter, 0 as omlnoter,
 +
1 as utlinterakt, 0 as omlinterakt,
 +
0 as utlovr, 0 as omlovr
  
−
ORDER BY branchname
+
  FROM statistics
 +
  LEFT JOIN items ON (statistics.itemnumber=items.itemnumber)
 +
  LEFT JOIN biblio ON (items.biblionumber=biblio.biblionumber)
 +
  LEFT JOIN library_groups ON (statistics.branch=library_groups.branchcode)
 +
 
 +
  WHERE type = 'issue'
 +
  AND itemtype IN ('CDROM','TV-SPEL')
 +
  AND statistics.branch IS NOT NULL
 +
  AND statistics.itemtype IS NOT NULL
 +
  AND statistics.datetime BETWEEN <<Datum från|date>>-INTERVAL 1 DAY AND <<Datum till |date>>+INTERVAL 1 DAY
 +
  AND library_groups.parent_id=<<Kommun|librarygroupsparentid>>)
 +
 
 +
  UNION ALL
 +
 
 +
  (SELECT branch,
 +
0 as utltryckt, 0 as omltryckt,
 +
0 as utllarom, 0 as omllarom,
 +
0 as utlljudbok, 0 as omlljudbok,
 +
0 as utltalbok, 0 as omltalbok,
 +
0 as utltskr, 0 as omltskr,
 +
0 as utlmusik, 0 as omlmusik,
 +
0 as utlfilm, 0 as omlfilm,
 +
0 as utlkartor, 0 as omlkartor,
 +
0 as utlnoter, 0 as omlnoter,
 +
0 as utlinterakt, 0 as omlinterakt,
 +
1 as utlovr, 0 as omlovr
  
−
</pre>
+
  FROM statistics
 +
  LEFT JOIN items ON (statistics.itemnumber=items.itemnumber)
 +
  LEFT JOIN biblio ON (items.biblionumber=biblio.biblionumber)
 +
  LEFT JOIN library_groups ON (statistics.branch=library_groups.branchcode)
 +
 
 +
  WHERE type = 'issue'
 +
  AND itemtype IN ('BLANDAT','FOREMAL','FOREMAL3MD','KONTROLL','SALLSKAPSS','SUFRPLATTA','VECKOLAN','X')
 +
  AND statistics.branch IS NOT NULL
 +
  AND statistics.itemtype IS NOT NULL
 +
  AND statistics.datetime BETWEEN <<Datum från|date>>-INTERVAL 1 DAY AND <<Datum till |date>>+INTERVAL 1 DAY
 +
  AND library_groups.parent_id=<<Kommun|librarygroupsparentid>>)
 +
 
 +
  UNION ALL
 +
 
 +
  (SELECT branch,
 +
0 as utltryckt, 1 as omltryckt,
 +
0 as utllarom, 0 as omllarom,
 +
0 as utlljudbok, 0 as omlljudbok,
 +
0 as utltalbok, 0 as omltalbok,
 +
0 as utltskr, 0 as omltskr,
 +
0 as utlmusik, 0 as omlmusik,
 +
0 as utlfilm, 0 as omlfilm,
 +
0 as utlkartor, 0 as omlkartor,
 +
0 as utlnoter, 0 as omlnoter,
 +
0 as utlinterakt, 0 as omlinterakt,
 +
0 as utlovr, 0 as omlovr
  
−
==Fråga 16 Fjärrlån==
+
  FROM statistics
 +
  LEFT JOIN items ON (statistics.itemnumber=items.itemnumber)
 +
  LEFT JOIN biblio ON (items.biblionumber=biblio.biblionumber)
 +
  LEFT JOIN library_groups ON (statistics.branch=library_groups.branchcode)
 +
 
 +
  WHERE type = 'renew'
 +
  AND itemtype IN ('BARNBOK','BOK','BOKCD','BOKCDBARN','BOKDAISY','BOKDAISYBA','BOKMP3','BOKMP3BARN','BOKPASE','LANGLAN','PAKET','POCKET','REFERENS','SPRAKKURS','STORSTIL') 
 +
  AND frameworkcode !='BOKP'
 +
  AND statistics.branch IS NOT NULL
 +
  AND statistics.itemtype IS NOT NULL
 +
  AND statistics.datetime BETWEEN <<Datum från|date>>-INTERVAL 1 DAY AND <<Datum till |date>>+INTERVAL 1 DAY
 +
  AND library_groups.parent_id=<<Kommun|librarygroupsparentid>>)
 +
 
 +
  UNION ALL
 +
 
 +
  (SELECT branch,
 +
0 as utltryckt, materials as omltryckt,
 +
0 as utllarom, 0 as omllarom,
 +
0 as utlljudbok, 0 as omlljudbok,
 +
0 as utltalbok, 0 as omltalbok,
 +
0 as utltskr, 0 as omltskr,
 +
0 as utlmusik, 0 as omlmusik,
 +
0 as utlfilm, 0 as omlfilm,
 +
0 as utlkartor, 0 as omlkartor,
 +
0 as utlnoter, 0 as omlnoter,
 +
0 as utlinterakt, 0 as omlinterakt,
 +
0 as utlovr, 0 as omlovr
  
−
<pre>
+
  FROM statistics
−
SELECT
+
  LEFT JOIN items ON (statistics.itemnumber=items.itemnumber)
−
   branchname,
+
   LEFT JOIN biblio ON (items.biblionumber=biblio.biblionumber)
−
   COUNT(CASE WHEN type = 'issue' AND borrowers.categorycode IN ('BIBLIOTEK') THEN 1 END) 'Fjärrutlån',
+
   LEFT JOIN library_groups ON (statistics.branch=library_groups.branchcode)
−
   COUNT(CASE WHEN type = 'issue' AND itemtype IN ('FJARRLAN') THEN 1 END) 'Fjärrinlån'
+
 
 +
  WHERE type = 'renew'  
 +
  AND itemtype IN ('BARNBOK','BOK','BOKCD','BOKCDBARN','BOKDAISY','BOKDAISYBA','BOKMP3','BOKMP3BARN','BOKPASE','LANGLAN','PAKET','POCKET','REFERENS','SPRAKKURS','STORSTIL') 
 +
   AND frameworkcode ='BOKP'
 +
  AND statistics.branch IS NOT NULL
 +
  AND statistics.itemtype IS NOT NULL
 +
  AND statistics.datetime BETWEEN <<Datum från|date>>-INTERVAL 1 DAY AND <<Datum till |date>>+INTERVAL 1 DAY
 +
  AND library_groups.parent_id=<<Kommun|librarygroupsparentid>>)
 +
 
 +
  UNION ALL
 +
 
 +
  (SELECT branch,
 +
0 as utltryckt, 0 as omltryckt,
 +
0 as utllarom, 1 as omllarom,
 +
0 as utlljudbok, 0 as omlljudbok,
 +
0 as utltalbok, 0 as omltalbok,
 +
0 as utltskr, 0 as omltskr,
 +
0 as utlmusik, 0 as omlmusik,
 +
0 as utlfilm, 0 as omlfilm,
 +
0 as utlkartor, 0 as omlkartor,
 +
0 as utlnoter, 0 as omlnoter,
 +
0 as utlinterakt, 0 as omlinterakt,
 +
0 as utlovr, 0 as omlovr
  
−
FROM statistics
+
  FROM statistics  
 +
  LEFT JOIN items ON (statistics.itemnumber=items.itemnumber)
 +
  LEFT JOIN biblio ON (items.biblionumber=biblio.biblionumber)
 +
  LEFT JOIN library_groups ON (statistics.branch=library_groups.branchcode)
 +
 
 +
  WHERE type = 'renew'
 +
  AND itemtype IN ('LAROMDL','LAROMTERM') 
 +
  AND statistics.branch IS NOT NULL
 +
  AND statistics.itemtype IS NOT NULL
 +
  AND statistics.datetime BETWEEN <<Datum från|date>>-INTERVAL 1 DAY AND <<Datum till |date>>+INTERVAL 1 DAY
 +
  AND library_groups.parent_id=<<Kommun|librarygroupsparentid>>)
 +
 
 +
  UNION ALL
 +
 
 +
  (SELECT branch,
 +
0 as utltryckt, 0 as omltryckt,
 +
0 as utllarom, 0 as omllarom,
 +
0 as utlljudbok, 1 as omlljudbok,
 +
0 as utltalbok, 0 as omltalbok,
 +
0 as utltskr, 0 as omltskr,
 +
0 as utlmusik, 0 as omlmusik,
 +
0 as utlfilm, 0 as omlfilm,
 +
0 as utlkartor, 0 as omlkartor,
 +
0 as utlnoter, 0 as omlnoter,
 +
0 as utlinterakt, 0 as omlinterakt,
 +
0 as utlovr, 0 as omlovr
  
−
LEFT JOIN borrowers ON (statistics.borrowernumber=borrowers.borrowernumber)
+
  FROM statistics
−
LEFT JOIN branches ON (statistics.branch=branches.branchcode)
+
  LEFT JOIN items ON (statistics.itemnumber=items.itemnumber)
 +
  LEFT JOIN biblio ON (items.biblionumber=biblio.biblionumber)
 +
  LEFT JOIN library_groups ON (statistics.branch=library_groups.branchcode)
 +
 
 +
  WHERE type = 'renew'
 +
  AND itemtype IN ('BARN LJUD','BARNMP3','KASSETT','LJUDBOK','MP3')
 +
  AND statistics.branch IS NOT NULL
 +
  AND statistics.itemtype IS NOT NULL
 +
  AND statistics.datetime BETWEEN <<Datum från|date>>-INTERVAL 1 DAY AND <<Datum till |date>>+INTERVAL 1 DAY
 +
  AND library_groups.parent_id=<<Kommun|librarygroupsparentid>>)
 +
 
 +
  UNION ALL
 +
 
 +
  (SELECT branch,
 +
0 as utltryckt, 0 as omltryckt,
 +
0 as utllarom, 0 as omllarom,
 +
0 as utlljudbok, 0 as omlljudbok,
 +
0 as utltalbok, 1 as omltalbok,
 +
0 as utltskr, 0 as omltskr,
 +
0 as utlmusik, 0 as omlmusik,
 +
0 as utlfilm, 0 as omlfilm,
 +
0 as utlkartor, 0 as omlkartor,
 +
0 as utlnoter, 0 as omlnoter,
 +
0 as utlinterakt, 0 as omlinterakt,
 +
0 as utlovr, 0 as omlovr
  
−
WHERE statistics.branch=<<Bibliotek|branches>>
+
  FROM statistics
−
AND statistics.itemtype IS NOT NULL  
+
  LEFT JOIN items ON (statistics.itemnumber=items.itemnumber)
−
AND statistics.datetime BETWEEN <<Datum från|date>>-INTERVAL 1 DAY AND <<Datum till |date>>+INTERVAL 1 DAY  
+
  LEFT JOIN biblio ON (items.biblionumber=biblio.biblionumber)
 +
  LEFT JOIN library_groups ON (statistics.branch=library_groups.branchcode)
 +
 
 +
  WHERE type = 'renew'
 +
  AND itemtype IN ('BARNTAL','DAISY','TALBOKKASS')
 +
  AND statistics.branch IS NOT NULL
 +
  AND statistics.itemtype IS NOT NULL  
 +
  AND statistics.datetime BETWEEN <<Datum från|date>>-INTERVAL 1 DAY AND <<Datum till |date>>+INTERVAL 1 DAY  
 +
  AND library_groups.parent_id=<<Kommun|librarygroupsparentid>>)
 +
 
 +
  UNION ALL
 +
 
 +
  (SELECT branch,
 +
0 as utltryckt, 0 as omltryckt,
 +
0 as utllarom, 0 as omllarom,
 +
0 as utlljudbok, 0 as omlljudbok,
 +
0 as utltalbok, 0 as omltalbok,
 +
0 as utltskr, 1 as omltskr,
 +
0 as utlmusik, 0 as omlmusik,
 +
0 as utlfilm, 0 as omlfilm,
 +
0 as utlkartor, 0 as omlkartor,
 +
0 as utlnoter, 0 as omlnoter,
 +
0 as utlinterakt, 0 as omlinterakt,
 +
0 as utlovr, 0 as omlovr
  
 +
  FROM statistics
 +
  LEFT JOIN items ON (statistics.itemnumber=items.itemnumber)
 +
  LEFT JOIN biblio ON (items.biblionumber=biblio.biblionumber)
 +
  LEFT JOIN library_groups ON (statistics.branch=library_groups.branchcode)
 +
 
 +
  WHERE type = 'renew'
 +
  AND itemtype IN ('BARN TIDSK','TIDSKRIFT')
 +
  AND statistics.branch IS NOT NULL
 +
  AND statistics.itemtype IS NOT NULL
 +
  AND statistics.datetime BETWEEN <<Datum från|date>>-INTERVAL 1 DAY AND <<Datum till |date>>+INTERVAL 1 DAY
 +
  AND library_groups.parent_id=<<Kommun|librarygroupsparentid>>)
 +
 
 +
  UNION ALL
 +
 
 +
  (SELECT branch,
 +
0 as utltryckt, 0 as omltryckt,
 +
0 as utllarom, 0 as omllarom,
 +
0 as utlljudbok, 0 as omlljudbok,
 +
0 as utltalbok, 0 as omltalbok,
 +
0 as utltskr, 0 as omltskr,
 +
0 as utlmusik, 1 as omlmusik,
 +
0 as utlfilm, 0 as omlfilm,
 +
0 as utlkartor, 0 as omlkartor,
 +
0 as utlnoter, 0 as omlnoter,
 +
0 as utlinterakt, 0 as omlinterakt,
 +
0 as utlovr, 0 as omlovr
  
−
GROUP BY branch
+
  FROM statistics
−
</pre>
+
  LEFT JOIN items ON (statistics.itemnumber=items.itemnumber)
 +
  LEFT JOIN biblio ON (items.biblionumber=biblio.biblionumber)
 +
  LEFT JOIN library_groups ON (statistics.branch=library_groups.branchcode)
 +
 
 +
  WHERE type = 'renew'
 +
  AND itemtype IN ('MUSIKCD','MUSCDBARN')
 +
  AND statistics.branch IS NOT NULL
 +
  AND statistics.itemtype IS NOT NULL
 +
  AND statistics.datetime BETWEEN <<Datum från|date>>-INTERVAL 1 DAY AND <<Datum till |date>>+INTERVAL 1 DAY
 +
  AND library_groups.parent_id=<<Kommun|librarygroupsparentid>>)
 +
 
 +
  UNION ALL
 +
 
 +
  (SELECT branch,
 +
0 as utltryckt, 0 as omltryckt,
 +
0 as utllarom, 0 as omllarom,
 +
0 as utlljudbok, 0 as omlljudbok,
 +
0 as utltalbok, 0 as omltalbok,
 +
0 as utltskr, 0 as omltskr,
 +
0 as utlmusik, 0 as omlmusik,
 +
0 as utlfilm, 1 as omlfilm,
 +
0 as utlkartor, 0 as omlkartor,
 +
0 as utlnoter, 0 as omlnoter,
 +
0 as utlinterakt, 0 as omlinterakt,
 +
0 as utlovr, 0 as omlovr
  
−
==Fråga 19 Kvinnor ==
+
  FROM statistics
 +
  LEFT JOIN items ON (statistics.itemnumber=items.itemnumber)
 +
  LEFT JOIN biblio ON (items.biblionumber=biblio.biblionumber)
 +
  LEFT JOIN library_groups ON (statistics.branch=library_groups.branchcode)
 +
 
 +
  WHERE type = 'renew'
 +
  AND itemtype IN ('BLURAY','FILM','VHS')
 +
  AND statistics.branch IS NOT NULL
 +
  AND statistics.itemtype IS NOT NULL
 +
  AND statistics.datetime BETWEEN <<Datum från|date>>-INTERVAL 1 DAY AND <<Datum till |date>>+INTERVAL 1 DAY
 +
  AND library_groups.parent_id=<<Kommun|librarygroupsparentid>>)
 +
 
 +
  UNION ALL
 +
 
 +
  (SELECT branch,
 +
0 as utltryckt, 0 as omltryckt,
 +
0 as utllarom, 0 as omllarom,
 +
0 as utlljudbok, 0 as omlljudbok,
 +
0 as utltalbok, 0 as omltalbok,
 +
0 as utltskr, 0 as omltskr,
 +
0 as utlmusik, 0 as omlmusik,
 +
0 as utlfilm, 0 as omlfilm,
 +
0 as utlkartor, 1 as omlkartor,
 +
0 as utlnoter, 0 as omlnoter,
 +
0 as utlinterakt, 0 as omlinterakt,
 +
0 as utlovr, 0 as omlovr
  
−
<pre>
+
  FROM statistics
−
SELECT
+
  LEFT JOIN items ON (statistics.itemnumber=items.itemnumber)
−
branches.branchname AS Bibliotek,
+
  LEFT JOIN biblio ON (items.biblionumber=biblio.biblionumber)
−
COUNT(DISTINCT statistics.borrowernumber) AS 'Aktiva låntagare kvinnor'  
+
  LEFT JOIN library_groups ON (statistics.branch=library_groups.branchcode)
 +
 
 +
  WHERE type = 'renew'  
 +
  AND itemtype IN ('KARTOR')
 +
  AND statistics.branch IS NOT NULL
 +
  AND statistics.itemtype IS NOT NULL
 +
  AND statistics.datetime BETWEEN <<Datum från|date>>-INTERVAL 1 DAY AND <<Datum till |date>>+INTERVAL 1 DAY
 +
  AND library_groups.parent_id=<<Kommun|librarygroupsparentid>>)
 +
 
 +
  UNION ALL
 +
 
 +
  (SELECT branch,
 +
0 as utltryckt, 0 as omltryckt,
 +
0 as utllarom, 0 as omllarom,
 +
0 as utlljudbok, 0 as omlljudbok,
 +
0 as utltalbok, 0 as omltalbok,
 +
0 as utltskr, 0 as omltskr,
 +
0 as utlmusik, 0 as omlmusik,
 +
0 as utlfilm, 0 as omlfilm,
 +
0 as utlkartor, 0 as omlkartor,
 +
0 as utlnoter, 1 as omlnoter,
 +
0 as utlinterakt, 0 as omlinterakt,
 +
0 as utlovr, 0 as omlovr
  
−
FROM statistics  
+
  FROM statistics  
−
LEFT JOIN borrowers ON (borrowers.borrowernumber=statistics.borrowernumber)  
+
  LEFT JOIN items ON (statistics.itemnumber=items.itemnumber)
−
LEFT JOIN borrower_attributes ON (borrowers.borrowernumber=borrower_attributes.borrowernumber)  
+
  LEFT JOIN biblio ON (items.biblionumber=biblio.biblionumber)
−
LEFT JOIN branches ON (statistics.branch=branches.branchcode)
+
  LEFT JOIN library_groups ON (statistics.branch=library_groups.branchcode)
 +
 
 +
  WHERE type = 'renew'
 +
  AND itemtype IN ('MUSIK','MUSIKBARN')
 +
  AND statistics.branch IS NOT NULL
 +
  AND statistics.itemtype IS NOT NULL
 +
  AND statistics.datetime BETWEEN <<Datum från|date>>-INTERVAL 1 DAY AND <<Datum till |date>>+INTERVAL 1 DAY
 +
  AND library_groups.parent_id=<<Kommun|librarygroupsparentid>>)
 +
 
 +
  UNION ALL
 +
 
 +
  (SELECT branch,
 +
0 as utltryckt, 0 as omltryckt,
 +
0 as utllarom, 0 as omllarom,
 +
0 as utlljudbok, 0 as omlljudbok,
 +
0 as utltalbok, 0 as omltalbok,
 +
0 as utltskr, 0 as omltskr,
 +
0 as utlmusik, 0 as omlmusik,
 +
0 as utlfilm, 0 as omlfilm,
 +
0 as utlkartor, 0 as omlkartor,
 +
0 as utlnoter, 0 as omlnoter,
 +
0 as utlinterakt, 1 as omlinterakt,
 +
0 as utlovr, 0 as omlovr
  
−
WHERE statistics.datetime LIKE '2018%'
+
  FROM statistics
−
AND code = 'PERSNUMMER'  
+
  LEFT JOIN items ON (statistics.itemnumber=items.itemnumber)
−
AND LEFT( RIGHT(attribute, 2), 1) % 2 = 0
+
  LEFT JOIN biblio ON (items.biblionumber=biblio.biblionumber)
−
AND statistics.branch=<<Bibliotek|branches>>
+
  LEFT JOIN library_groups ON (statistics.branch=library_groups.branchcode)
 +
 
 +
  WHERE type = 'renew'  
 +
  AND itemtype IN ('CDROM','TV-SPEL')
 +
  AND statistics.branch IS NOT NULL
 +
  AND statistics.itemtype IS NOT NULL
 +
  AND statistics.datetime BETWEEN <<Datum från|date>>-INTERVAL 1 DAY AND <<Datum till |date>>+INTERVAL 1 DAY
 +
  AND library_groups.parent_id=<<Kommun|librarygroupsparentid>>)
 +
 
 +
  UNION ALL
 +
 
 +
  (SELECT branch,
 +
0 as utltryckt, 0 as omltryckt,
 +
0 as utllarom, 0 as omllarom,
 +
0 as utlljudbok, 0 as omlljudbok,
 +
0 as utltalbok, 0 as omltalbok,
 +
0 as utltskr, 0 as omltskr,
 +
0 as utlmusik, 0 as omlmusik,
 +
0 as utlfilm, 0 as omlfilm,
 +
0 as utlkartor, 0 as omlkartor,
 +
0 as utlnoter, 0 as omlnoter,
 +
0 as utlinterakt, 0 as omlinterakt,
 +
0 as utlovr, 1 as omlovr
  
−
GROUP BY statistics.branch
+
  FROM statistics
 +
  LEFT JOIN items ON (statistics.itemnumber=items.itemnumber)
 +
  LEFT JOIN biblio ON (items.biblionumber=biblio.biblionumber)
 +
  LEFT JOIN library_groups ON (statistics.branch=library_groups.branchcode)
 +
 
 +
  WHERE type = 'renew'
 +
  AND itemtype IN ('BLANDAT','FOREMAL','FOREMAL3MD','KONTROLL','SALLSKAPSS','SUFRPLATTA','VECKOLAN','X')
 +
  AND statistics.branch IS NOT NULL
 +
  AND statistics.itemtype IS NOT NULL
 +
  AND statistics.datetime BETWEEN <<Datum från|date>>-INTERVAL 1 DAY AND <<Datum till |date>>+INTERVAL 1 DAY
 +
  AND library_groups.parent_id=<<Kommun|librarygroupsparentid>>)
 +
 
 +
 
 +
  ) ds
 +
 
 +
  LEFT JOIN branches ON (ds.branch=branches.branchcode)
 +
  GROUP BY branchname WITH ROLLUP
 
</pre>
 
</pre>
  
−
==Fråga 19 Män==
+
===Fråga 16 Fjärrlån - kommunvis===
−
 
 
 
<pre>
 
<pre>
 
SELECT  
 
SELECT  
−
branches.branchname AS Bibliotek,
+
CASE
−
COUNT(DISTINCT statistics.borrowernumber) AS 'Aktiva låntagare kvinnor'  
+
WHEN branchname IS NULL
 +
THEN '=Summa'
 +
ELSE
 +
branchname
 +
END AS Bibliotek,
 +
SUM(utlan) AS 'Utlån',
 +
SUM(inlan) AS 'Inlån'
 +
 +
FROM
 +
(
 +
(SELECT branch,1 as utlan, 0 as inlan
 +
FROM statistics
  
−
FROM statistics
+
LEFT JOIN borrowers ON (statistics.borrowernumber=borrowers.borrowernumber)
−
LEFT JOIN borrowers ON (borrowers.borrowernumber=statistics.borrowernumber)
 
−
LEFT JOIN borrower_attributes ON (borrowers.borrowernumber=borrower_attributes.borrowernumber)  
 
 
LEFT JOIN branches ON (statistics.branch=branches.branchcode)
 
LEFT JOIN branches ON (statistics.branch=branches.branchcode)
 +
LEFT JOIN library_groups ON (statistics.branch=library_groups.branchcode)
  
−
WHERE statistics.datetime LIKE '2018%'  
+
WHERE type = 'issue'  
−
AND code = 'PERSNUMMER'  
+
AND borrowers.categorycode IN ('BIBLIOTEK')
−
AND LEFT( RIGHT(attribute, 2), 1) % 2 = 1
+
AND borrowers.cardnumber NOT IN ('8bxq','8bxz','8bya','8byb','8byc','8byi','8byl','8bym','8byo','8byq','8byr','8bys','Gull','Hjo','Hova','Kabo','Kbro','Mari','Skgy','Sksb','Tida','Tikf','Tore','Vagy')
−
AND statistics.branch=<<Bibliotek|branches>>
+
AND statistics.branch IS NOT NULL
−
 
+
AND statistics.itemtype IS NOT NULL
−
GROUP BY statistics.branch
+
AND statistics.datetime BETWEEN <<Datum från|date>>-INTERVAL 1 DAY AND <<Datum till |date>>+INTERVAL 1 DAY
−
</pre>
+
AND library_groups.parent_id=<<Kommun|librarygroupsparentid>>
−
 
+
)
−
==Fråga 19 Institution==
+
−
 
+
UNION ALL
−
<pre>
+
−
SELECT branches.branchname AS Bibliotek, COUNT (DISTINCT statistics.borrowernumber) AS 'Antal institutionslåntagare'
+
(SELECT branch,0 as utlan, 1 as inlan
 +
FROM statistics
  
−
FROM statistics
 
 
LEFT JOIN borrowers ON (statistics.borrowernumber=borrowers.borrowernumber)
 
LEFT JOIN borrowers ON (statistics.borrowernumber=borrowers.borrowernumber)
 
LEFT JOIN branches ON (statistics.branch=branches.branchcode)
 
LEFT JOIN branches ON (statistics.branch=branches.branchcode)
 +
LEFT JOIN library_groups ON (statistics.branch=library_groups.branchcode)
  
−
WHERE statistics.datetime LIKE '2018%'  
+
WHERE type = 'issue'  
−
AND borrowers.categorycode IN ('BIBLIOTEK','ELEVTIDA','FORSKOLA','INSTT','KVALON','PTID','SKOLA','SKOLBIBTID')  
+
AND itemtype IN ('FJARRLAN')
−
AND statistics.branch=<<Bibliotek|branches>>
+
AND statistics.branch IS NOT NULL
 +
AND statistics.itemtype IS NOT NULL
 +
AND statistics.datetime BETWEEN <<Datum från|date>>-INTERVAL 1 DAY AND <<Datum till |date>>+INTERVAL 1 DAY
 +
AND library_groups.parent_id=<<Kommun|librarygroupsparentid>> )
 +
) ds
 +
LEFT JOIn branches On (branches.branchcode=ds.branch)
  
−
GROUP BY statistics.branch
+
GROUP BY branchname WITH ROLLUP
 
</pre>
 
</pre>
  
−
==Fråga 19 Aktiva låntagare som är under 18 år==
+
===Fråga 19 Aktiva låntagare - kommunvis===
−
 
 
 
<pre>
 
<pre>
−
SELECT branchname AS Bibliotek, COUNT (DISTINCT statistics.borrowernumber) AS 'Antal aktiva under 18 år'
+
SELECT  
 +
CASE WHEN
 +
branchname IS NULL
 +
THEN
 +
'=Summa'
 +
ELSE
 +
branchname
 +
END
 +
AS 'Låntagarens hembibliotek',
 +
COUNT(CASE WHEN code = 'PERSNUMMER' AND LEFT( RIGHT(attribute, 2), 1) % 2 = 0 THEN 1 END) 'Aktiva kvinnor',
 +
COUNT(CASE WHEN code = 'PERSNUMMER' AND LEFT( RIGHT(attribute, 2), 1) % 2 = 1 THEN 1 END) 'Aktiva män',
 +
COUNT (CASE WHEN borrowers.categorycode IN ('BIBLIOTEK','ELEVTIDA','FORSKOLA','INSTT','KVALON','PERSONAL','PTID','SKOLA','SKOLBIBTID') THEN 1 END) 'Institutionslåntagare',
 +
COUNT(CASE WHEN dateofbirth > DATE_SUB(CURDATE(), INTERVAL 18 YEAR) OR borrowers.categorycode IN ('ELEVTIDA','SKOLBIBTID','BARN') THEN 1 END) 'Under 18 år'
  
−
FROM statistics
+
FROM borrowers  
−
LEFT JOIN borrowers ON (statistics.borrowernumber=borrowers.borrowernumber)
 
−
LEFT JOIN branches ON (statistics.branch=branches.branchcode)
 
  
−
WHERE statistics.datetime LIKE '2018%'
+
LEFT JOIN borrower_attributes ON (borrowers.borrowernumber=borrower_attributes.borrowernumber)  
−
AND (borrowers.dateofbirth > DATE_SUB(CURDATE(), INTERVAL 18 YEAR) OR borrowers.categorycode IN ('ELEVTIDA','SKOLBIBTIDA','BARN'))  
+
LEFT JOIN branches ON (borrowers.branchcode=branches.branchcode)
−
AND statistics.branch=<<Bibliotek|branches>>
 
  
−
GROUP BY statistics.branch
+
WHERE
−
</pre>
+
borrowers.borrowernumber IN
  
−
==Fråga 19 Aktiva låntagare (experiment för 2019 och framåt)==
+
(SELECT borrowernumber
  
−
Ett försök att använda den nya systempreferensen TrackLastPatronActivity
+
FROM
 +
statistics
  
−
<pre>
+
LEFT JOIN library_groups ON (statistics.branch=library_groups.branchcode)
  
−
SELECT
+
WHERE
−
  branches.branchname AS Bibliotek,
+
statistics.datetime BETWEEN <<Datum från|date>>-INTERVAL 1 DAY AND <<Datum till |date>>+INTERVAL 1 DAY AND library_groups.parent_id=<<Kommun|librarygroupsparentid>> AND statistics.type='issue')
−
  COUNT(CASE WHEN borrower_attributes.code='PERSNUMMER'  AND LEFT( RIGHT(attribute, 2), 1) % 2 = 0 THEN 1 END) 'Kvinnor',
 
−
  COUNT(CASE WHEN borrower_attributes.code='PERSNUMMER'  AND LEFT( RIGHT(attribute, 2), 1) % 2 = 1 THEN 1 END) 'Män',
 
−
  COUNT (CASE WHEN borrowers.categorycode IN ('BIBLIOTEK','ELEVTIDA','FORSKOLA','INSTT','KVALON','PERSONAL','PTID','SKOLA','SKOLBIBTID') THEN 1 END) 'Institutionslåntagare',
 
−
  COUNT(CASE WHEN dateofbirth > DATE_SUB(CURDATE(), INTERVAL 18 YEAR) OR borrowers.categorycode IN ('ELEVTIDA','SKOLBIBTID','BARN') THEN 1 END) 'Under 18 år'
 
−
 
 
−
FROM borrowers
 
−
LEFT JOIN borrower_attributes ON (borrowers.borrowernumber=borrower_attributes.borrowernumber)
 
−
LEFT JOIN branches ON (borrowers.branchcode=branches.branchcode)
 
−
 
 
−
WHERE borrowers.lastseen BETWEEN <<Datum från|date>>-INTERVAL 1 DAY  
 
−
AND <<Datum till |date>>+INTERVAL 1 DAY  
 
−
AND borrowers.branchcode=<<Bibliotek|branches>>
 
  
−
GROUP BY borrowers.branchcode
+
GROUP BY branchname WITH ROLLUP
 
</pre>
 
</pre>

Nuvarande version från 19 januari 2021 kl. 16.54

Statistikrapporter som Bibliotek Mellansjö skapat.

En del av rapporterna är lätta bearbetningar av Hyltes rapporter som finns på den här sidan: Statistik

Andra rapporter är skrivna från scratch Och - precis som för Hylterapporterna krävs det att man byter ut exemplartyper och liknande till det man använder på sitt eget bibliotek

Eftersom Bibliotek Mellansjö är 42 enheter som ska redovisas kommunvis i åtta olika enkäter har vi gjort ett nytt auktorierat värde för att klumpa ihop siffrorna kommunvis Hur man skapar det värdet beskrivs i punkt 13, och sen finns Mellansjös rapporter i punkt 13.1-13.10. De rapporterna går inte att köra i någon annan katalog, försåvitt man inte först skapar samma auktoriserade värde

Punkt 1-12 är därför lättare bearbetningar (eller ibland äldre version) som inte använder det auktoriserade värdet, utan ska fungera i alla kataloger

Fråga 10 Fysiskt bestånd

SELECT 
  branchname,
  COUNT(CASE WHEN itype IN ('BARNBOK','BARNKORT','BOK','BOKCD','BOKCDBARN','BOKDAISY','BOKDAISYBA','BOKMP3','BOKMP3BARN','KORTLON','LANGLAN','PAKET','REFERENS','SPRAKKURS','STORSTIL') THEN 1 END) 'Bestånd tryckt bok',
  COUNT(CASE WHEN itype IN ('LAROMDL','LAROMTERM') THEN 1 END) 'Bestånd läromedel',
  COUNT(CASE WHEN itype IN ('BARN LJUD','BARNMP3','LJUDBOK','MP3') THEN 1 END) 'Bestånd ljudböcker',
  COUNT(CASE WHEN itype IN ('BARNTAL','DAISY') THEN 1 END) 'Bestånd talböcker daisy',
  COUNT(CASE WHEN itype IN ('MUSIKCD') THEN 1 END) 'Bestånd musik',
  COUNT(CASE WHEN itype IN ('BLURAY','FILM','VHS') THEN 1 END) 'Bestånd film',
  COUNT(CASE WHEN itype IN ('KARTOR') THEN 1 END) 'Bestånd kartor',
  COUNT(CASE WHEN itype IN ('MUSIK') THEN 1 END) 'Bestånd noter',
  COUNT(CASE WHEN itype IN ('TV-SPEL') THEN 1 END) 'Bestånd TV-spel',
  COUNT(CASE WHEN itype IN ('ARTIKEL','BLANDAT','FOREMAL','SUFRPLATTA','VECKOLAN','X') THEN 1 END) 'Bestånd övrigt'
  
FROM items

LEFT JOIN branches ON (items.homebranch=branches.branchcode)

WHERE items.homebranch = <<Bibliotek|branches>>

Fråga 10 Fysiskt bestånd, en krångligare version

Vill man vara lite petig så stämmer inte ovanstående rapport, eftersom den räknar hur många exemplar som finns just när man köra rapporten - och det som efterfrågas av KB är bestånd på Nyårsafton. Egentligen borde man alltså räkna aktuellt antal exemplar, plus det som gallrats sedan nyår, minus det som förvärvats sedan nyår. Ett försök på en sådan rapport: Om man tar alternativet Alla bibliotek i en stor katalog kommer rapporten antagligen att krascha 

SELECT 
CASE
WHEN branches.branchname IS NULL
THEN '=Summa'
ELSE
branches.branchname
END
AS Bibliotek,
SUM(tryckt) AS 'Bestånd tryckt bok',
SUM(laromedel) AS 'Bestånd läromedel',
SUM(ljudbok) AS 'Bestånd ljudböcker',
SUM(talbok) AS 'Bestånd talböcker daisy',
SUM(musik) AS 'Bestånd musik',
SUM(film) AS 'Bestånd film',
SUM(kartor) AS 'Bestånd kartor',
SUM(noter) 'Bestånd noter',
SUM(tvspel) 'Bestånd Tv-spel',
SUM(ovrigt) AS 'Bestånd övrigt'
FROM
(
(SELECT items.homebranch,1 as tryckt,0 as laromedel,0 as ljudbok,0 as talbok,0 as musik,0 as film,0 as kartor,0 as noter,0 as tvspel,0 as ovrigt
FROM items 

LEFT JOIN branches ON (items.homebranch=branches.branchcode)

WHERE 
items.itype IN ('BARNBOK','BARNKORT','BOK','BOKCD','BOKCDBARN','BOKDAISY','BOKDAISYBA','BOKMP3','BOKMP3BARN','KORTLON','LANGLAN','PAKET','POCKET','REFERENS','SPRAKKURS','STORSTIL')
AND items.homebranch LIKE <<Bibliotek|branches:all>>)

UNION ALL

(SELECT items.homebranch,0 as tryckt,1 as laromedel,0 as ljudbok,0 as talbok,0 as musik,0 as film,0 as kartor,0 as noter,0 as tvspel,0 as ovrigt
FROM items 

LEFT JOIN branches ON (items.homebranch=branches.branchcode)

WHERE 
items.itype IN ('LAROMDL','LAROMTERM')
AND items.homebranch  LIKE <<Bibliotek|branches:all>>) 

UNION ALL

(SELECT items.homebranch,0 as tryckt,0 as laromedel,1 as ljudbok,0 as talbok,0 as musik,0 as film,0 as kartor,0 as noter,0 as tvspel,0 as ovrigt
FROM items 

LEFT JOIN branches ON (items.homebranch=branches.branchcode)

WHERE 
items.itype IN ('BARN LJUD','BARNMP3','KASSETT','LJUDBOK','MP3')
AND items.homebranch  LIKE <<Bibliotek|branches:all>>) 

UNION ALL

(SELECT items.homebranch,0 as tryckt,0 as laromedel,0 as ljudbok,1 as talbok,0 as musik,0 as film,0 as kartor,0 as noter,0 as tvspel,0 as ovrigt
FROM items 

LEFT JOIN branches ON (items.homebranch=branches.branchcode)

WHERE 
items.itype IN ('BARNTAL','DAISY','TALBOKKASS')
AND items.homebranch  LIKE <<Bibliotek|branches:all>>) 

UNION ALL

(SELECT items.homebranch,0 as tryckt,0 as laromedel,0 as ljudbok,0 as talbok,1 as musik,0 as film,0 as kartor,0 as noter,0 as tvspel,0 as ovrigt
FROM items 

LEFT JOIN branches ON (items.homebranch=branches.branchcode)

WHERE 
items.itype IN ('MUSIKCD','MUSCDBARN')
AND items.homebranch  LIKE <<Bibliotek|branches:all>>) 

UNION ALL

(SELECT items.homebranch,0 as tryckt,0 as laromedel,0 as ljudbok,0 as talbok,0 as musik,1 as film,0 as kartor,0 as noter,0 as tvspel,0 as ovrigt
FROM items 

LEFT JOIN branches ON (items.homebranch=branches.branchcode)

WHERE 
items.itype IN ('BLURAY','FILM','VHS')
AND items.homebranch  LIKE <<Bibliotek|branches:all>>) 

UNION ALL

(SELECT items.homebranch,0 as tryckt,0 as laromedel,0 as ljudbok,0 as talbok,0 as musik,0 as film,1 as kartor,0 as noter,0 as tvspel,0 as ovrigt
FROM items 

LEFT JOIN branches ON (items.homebranch=branches.branchcode)

WHERE 
items.itype IN ('KARTOR')
AND items.homebranch  LIKE <<Bibliotek|branches:all>>) 

UNION ALL

(SELECT items.homebranch,0 as tryckt,0 as laromedel,0 as ljudbok,0 as talbok,0 as musik,0 as film,0 as kartor,1 as noter,0 as tvspel,0 as ovrigt
FROM items 

LEFT JOIN branches ON (items.homebranch=branches.branchcode)

WHERE 
items.itype IN ('MUSIK','MUSIKBARN') 
AND items.homebranch  LIKE <<Bibliotek|branches:all>>) 

UNION ALL

(SELECT items.homebranch,0 as tryckt,0 as laromedel,0 as ljudbok,0 as talbok,0 as musik,0 as film,0 as kartor,0 as noter,1 as tvspel,0 as ovrigt
FROM items 

LEFT JOIN branches ON (items.homebranch=branches.branchcode)

WHERE 
items.itype IN ('TV-SPEL')
AND items.homebranch  LIKE <<Bibliotek|branches:all>>) 

UNION ALL

(SELECT items.homebranch,0 as tryckt,0 as laromedel,0 as ljudbok,0 as talbok,0 as musik,0 as film,0 as kartor,0 as noter,0 as tvspel,1 as ovrigt
FROM items 

LEFT JOIN branches ON (items.homebranch=branches.branchcode)

WHERE 
items.itype IN ('BLANDAT','BOKPASE','CDROM','FOREMAL','FOREMAL3MD','KONTROLL','SALLSKAPSS','SUFRPLATTA','VECKOLAN','X')
AND items.homebranch  LIKE <<Bibliotek|branches:all>>) 

UNION ALL

(SELECT items.homebranch,-1 as tryckt,0 as laromedel,0 as ljudbok,0 as talbok,0 as musik,0 as film,0 as kartor,0 as noter,0 as tvspel,0 as ovrigt
FROM items 

LEFT JOIN branches ON (items.homebranch=branches.branchcode)

WHERE 
(items.dateaccessioned BETWEEN <<Datum (Nyårsafton i KB-statistiken)|date>> AND CURDATE()) 
AND items.itype IN ('BARNBOK','BARNKORT','BOK','BOKCD','BOKCDBARN','BOKDAISY','BOKDAISYBA','BOKMP3','BOKMP3BARN','KORTLON','LANGLAN','PAKET','POCKET','REFERENS','SPRAKKURS','STORSTIL')
AND items.homebranch  LIKE <<Bibliotek|branches:all>>) 

UNION ALL

(SELECT items.homebranch,0 as tryckt,-1 as laromedel,0 as ljudbok,0 as talbok,0 as musik,0 as film,0 as kartor,0 as noter,0 as tvspel,0 as ovrigt
FROM items 

LEFT JOIN branches ON (items.homebranch=branches.branchcode)

WHERE 
(items.dateaccessioned BETWEEN <<Datum (Nyårsafton i KB-statistiken)|date>> AND CURDATE()) 
AND items.itype IN ('LAROMDL','LAROMTERM')
AND items.homebranch  LIKE <<Bibliotek|branches:all>>) 

UNION ALL

(SELECT items.homebranch,0 as tryckt,0 as laromedel,-1 as ljudbok,0 as talbok,0 as musik,0 as film,0 as kartor,0 as noter,0 as tvspel,0 as ovrigt
FROM items 

LEFT JOIN branches ON (items.homebranch=branches.branchcode)

WHERE 
(items.dateaccessioned BETWEEN <<Datum (Nyårsafton i KB-statistiken)|date>> AND CURDATE()) 
AND items.itype IN ('BARN LJUD','BARNMP3','KASSETT','LJUDBOK','MP3')
AND items.homebranch  LIKE <<Bibliotek|branches:all>>) 

UNION ALL

(SELECT items.homebranch,0 as tryckt,0 as laromedel,0 as ljudbok,-1 as talbok,0 as musik,0 as film,0 as kartor,0 as noter,0 as tvspel,0 as ovrigt
FROM items 

LEFT JOIN branches ON (items.homebranch=branches.branchcode)

WHERE 
(items.dateaccessioned BETWEEN <<Datum (Nyårsafton i KB-statistiken)|date>> AND CURDATE()) 
AND items.itype IN ('BARNTAL','DAISY','TALBOKKASS')
AND items.homebranch  LIKE <<Bibliotek|branches:all>>) 

UNION ALL

(SELECT items.homebranch,0 as tryckt,0 as laromedel,0 as ljudbok,0 as talbok,-1 as musik,0 as film,0 as kartor,0 as noter,0 as tvspel,0 as ovrigt
FROM items 

LEFT JOIN branches ON (items.homebranch=branches.branchcode)

WHERE 
(items.dateaccessioned BETWEEN <<Datum (Nyårsafton i KB-statistiken)|date>> AND CURDATE()) 
AND items.itype IN ('MUSIKCD','MUSCDBARN')
AND items.homebranch  LIKE <<Bibliotek|branches:all>>) 

UNION ALL

(SELECT items.homebranch,0 as tryckt,0 as laromedel,0 as ljudbok,0 as talbok,0 as musik,-1 as film,0 as kartor,0 as noter,0 as tvspel,0 as ovrigt
FROM items 

LEFT JOIN branches ON (items.homebranch=branches.branchcode)

WHERE 
(items.dateaccessioned BETWEEN <<Datum (Nyårsafton i KB-statistiken)|date>> AND CURDATE()) 
AND items.itype IN ('BLURAY','FILM','VHS')
AND items.homebranch  LIKE <<Bibliotek|branches:all>>) 

UNION ALL

(SELECT items.homebranch,0 as tryckt,0 as laromedel,0 as ljudbok,0 as talbok,0 as musik,0 as film,-1 as kartor,0 as noter,0 as tvspel,0 as ovrigt
FROM items 

LEFT JOIN branches ON (items.homebranch=branches.branchcode)

WHERE 
(items.dateaccessioned BETWEEN <<Datum (Nyårsafton i KB-statistiken)|date>> AND CURDATE()) 
AND items.itype IN ('KARTOR')
AND items.homebranch  LIKE <<Bibliotek|branches:all>>) 

UNION ALL

(SELECT items.homebranch,0 as tryckt,0 as laromedel,0 as ljudbok,0 as talbok,0 as musik,0 as film,0 as kartor,-1 as noter,0 as tvspel,0 as ovrigt
FROM items 

LEFT JOIN branches ON (items.homebranch=branches.branchcode)

WHERE 
(items.dateaccessioned BETWEEN <<Datum (Nyårsafton i KB-statistiken)|date>> AND CURDATE()) 
AND items.itype IN ('MUSIK','MUSIKBARN') 
AND items.homebranch  LIKE <<Bibliotek|branches:all>>) 

UNION ALL

(SELECT items.homebranch,0 as tryckt,0 as laromedel,0 as ljudbok,0 as talbok,0 as musik,0 as film,0 as kartor,0 as noter,-1 as tvspel,0 as ovrigt
FROM items 

LEFT JOIN branches ON (items.homebranch=branches.branchcode)

WHERE 
(items.dateaccessioned BETWEEN <<Datum (Nyårsafton i KB-statistiken)|date>> AND CURDATE()) 
AND items.itype IN ('TV-SPEL')
AND items.homebranch  LIKE <<Bibliotek|branches:all>>) 

UNION ALL

(SELECT items.homebranch,0 as tryckt,0 as laromedel,0 as ljudbok,0 as talbok,0 as musik,0 as film,0 as kartor,0 as noter,0 as tvspel,-1 as ovrigt
FROM items 

LEFT JOIN branches ON (items.homebranch=branches.branchcode)

WHERE 
(items.dateaccessioned BETWEEN <<Datum (Nyårsafton i KB-statistiken)|date>> AND CURDATE()) 
AND items.itype IN ('ARTIKEL','BLANDAT','BOKPASE','CDROM','FOREMAL','FOREMAL3MD','KONTROLL','SALLSKAPSS','SUFRPLATTA','VECKOLAN','X')
AND items.homebranch  LIKE <<Bibliotek|branches:all>>) 

UNION ALL

(SELECT deleteditems.homebranch,1 as tryckt,0 as laromedel,0 as ljudbok,0 as talbok,0 as musik,0 as film,0 as kartor,0 as noter,0 as tvspel,0 as ovrigt
FROM deleteditems 

LEFT JOIN branches ON (deleteditems.homebranch=branches.branchcode)

WHERE 
(deleteditems.timestamp BETWEEN <<Datum (Nyårsafton i KB-statistiken)|date>> AND CURDATE())
AND deleteditems.itype IN ('BARNBOK','BARNKORT','BOK','BOKCD','BOKCDBARN','BOKDAISY','BOKDAISYBA','BOKMP3','BOKMP3BARN','KORTLON','LANGLAN','PAKET','POCKET','REFERENS','SPRAKKURS','STORSTIL')
AND deleteditems.homebranch  LIKE <<Bibliotek|branches:all>>) 

UNION ALL

(SELECT deleteditems.homebranch,0 as tryckt,1 as laromedel,0 as ljudbok,0 as talbok,0 as musik,0 as film,0 as kartor,0 as noter,0 as tvspel,0 as ovrigt
FROM deleteditems 

LEFT JOIN branches ON (deleteditems.homebranch=branches.branchcode)

WHERE 
(deleteditems.timestamp BETWEEN <<Datum (Nyårsafton i KB-statistiken)|date>> AND CURDATE())
AND deleteditems.itype IN ('LAROMDL','LAROMTERM')
AND deleteditems.homebranch  LIKE <<Bibliotek|branches:all>>) 

UNION ALL

(SELECT deleteditems.homebranch,0 as tryckt,0 as laromedel,1 as ljudbok,0 as talbok,0 as musik,0 as film,0 as kartor,0 as noter,0 as tvspel,0 as ovrigt
FROM deleteditems 

LEFT JOIN branches ON (deleteditems.homebranch=branches.branchcode)

WHERE (deleteditems.timestamp BETWEEN <<Datum (Nyårsafton i KB-statistiken)|date>> AND CURDATE())
AND deleteditems.itype IN ('BARN LJUD','BARNMP3','KASSETT','LJUDBOK','MP3')
AND deleteditems.homebranch  LIKE <<Bibliotek|branches:all>>) 

UNION ALL

(SELECT deleteditems.homebranch,0 as tryckt,0 as laromedel,0 as ljudbok,1 as talbok,0 as musik,0 as film,0 as kartor,0 as noter,0 as tvspel,0 as ovrigt
FROM deleteditems 

LEFT JOIN branches ON (deleteditems.homebranch=branches.branchcode)

WHERE 
(deleteditems.timestamp BETWEEN <<Datum (Nyårsafton i KB-statistiken)|date>> AND CURDATE())
AND deleteditems.itype IN ('BARNTAL','DAISY','TALBOKKASS')
AND deleteditems.homebranch  LIKE <<Bibliotek|branches:all>>) 

UNION ALL

(SELECT deleteditems.homebranch,0 as tryckt,0 as laromedel,0 as ljudbok,0 as talbok,1 as musik,0 as film,0 as kartor,0 as noter,0 as tvspel,0 as ovrigt
FROM deleteditems 

LEFT JOIN branches ON (deleteditems.homebranch=branches.branchcode)

WHERE 
(deleteditems.timestamp BETWEEN <<Datum (Nyårsafton i KB-statistiken)|date>> AND CURDATE())
AND deleteditems.itype IN ('MUSIKCD','MUSCDBARN')
AND deleteditems.homebranch  LIKE <<Bibliotek|branches:all>>) 

UNION ALL

(SELECT deleteditems.homebranch,0 as tryckt,0 as laromedel,0 as ljudbok,0 as talbok,0 as musik,1 as film,0 as kartor,0 as noter,0 as tvspel,0 as ovrigt
FROM deleteditems 

LEFT JOIN branches ON (deleteditems.homebranch=branches.branchcode)

WHERE 
(deleteditems.timestamp BETWEEN <<Datum (Nyårsafton i KB-statistiken)|date>> AND CURDATE())
AND deleteditems.itype IN ('BLURAY','FILM','VHS')
AND deleteditems.homebranch  LIKE <<Bibliotek|branches:all>>) 

UNION ALL

(SELECT deleteditems.homebranch,0 as tryckt,0 as laromedel,0 as ljudbok,0 as talbok,0 as musik,0 as film,1 as kartor,0 as noter,0 as tvspel,0 as ovrigt
FROM deleteditems 

LEFT JOIN branches ON (deleteditems.homebranch=branches.branchcode)

WHERE 
(deleteditems.timestamp BETWEEN <<Datum (Nyårsafton i KB-statistiken)|date>> AND CURDATE())
AND deleteditems.itype IN ('KARTOR')
AND deleteditems.homebranch  LIKE <<Bibliotek|branches:all>>) 

UNION ALL

(SELECT deleteditems.homebranch,0 as tryckt,0 as laromedel,0 as ljudbok,0 as talbok,0 as musik,0 as film,0 as kartor,1 as noter,0 as tvspel,0 as ovrigt
FROM deleteditems 

LEFT JOIN branches ON (deleteditems.homebranch=branches.branchcode)

WHERE 
(deleteditems.timestamp BETWEEN <<Datum (Nyårsafton i KB-statistiken)|date>> AND CURDATE())
AND deleteditems.itype IN ('MUSIK','MUSIKBARN') 
AND deleteditems.homebranch  LIKE <<Bibliotek|branches:all>>) 

UNION ALL

(SELECT deleteditems.homebranch,0 as tryckt,0 as laromedel,0 as ljudbok,0 as talbok,0 as musik,0 as film,0 as kartor,0 as noter,1 as tvspel,0 as ovrigt
FROM deleteditems 

LEFT JOIN branches ON (deleteditems.homebranch=branches.branchcode)

WHERE 
(deleteditems.timestamp BETWEEN <<Datum (Nyårsafton i KB-statistiken)|date>> AND CURDATE())
AND deleteditems.itype IN ('TV-SPEL')
AND deleteditems.homebranch  LIKE <<Bibliotek|branches:all>>) 

UNION ALL

(SELECT deleteditems.homebranch,0 as tryckt,0 as laromedel,0 as ljudbok,0 as talbok,0 as musik,0 as film,0 as kartor,0 as noter,0 as tvspel,1 as ovrigt
FROM deleteditems 

LEFT JOIN branches ON (deleteditems.homebranch=branches.branchcode)

WHERE 
(deleteditems.timestamp BETWEEN <<Datum (Nyårsafton i KB-statistiken)|date>> AND CURDATE())
AND deleteditems.itype IN ('ARTIKEL','BLANDAT','BOKPASE','CDROM','FOREMAL','FOREMAL3MD','KONTROLL','SALLSKAPSS','SUFRPLATTA','VECKOLAN','X')
AND deleteditems.homebranch  LIKE <<Bibliotek|branches:all>>) 
) ds
LEFT JOIN branches ON (branches.branchcode=ds.homebranch)
GROUP BY branches.branchname WITH ROLLUP

Fråga 10 Fysiskt nyförvärv

SELECT 
  branchname,
  COUNT(CASE WHEN itype IN ('BARNBOK','BARNKORT','BOK','BOKCD','BOKCDBARN','BOKDAISY','BOKDAISYBA','BOKMP3','BOKMP3BARN','KORTLON','LANGLAN','PAKET','REFERENS','SPRAKKURS','STORSTIL') THEN 1 END) 'Nyförvärv tryckt bok',
  COUNT(CASE WHEN itype IN ('LAROMDL','LAROMTERM') THEN 1 END) 'Nyförvärv läromedel',
  COUNT(CASE WHEN itype IN ('BARN LJUD','BARNMP3','LJUDBOK','MP3') THEN 1 END) 'Nyförvärv ljudböcker',
  COUNT(CASE WHEN itype IN ('BARNTAL','DAISY') THEN 1 END) 'Nyförvärv talböcker daisy',
  COUNT(CASE WHEN itype IN ('MUSIKCD') THEN 1 END) 'Nyförvärv musik',
  COUNT(CASE WHEN itype IN ('BLURAY','FILM','VHS') THEN 1 END) 'Nyförvärv film',
  COUNT(CASE WHEN itype IN ('KARTOR') THEN 1 END) 'Nyförvärv kartor',
  COUNT(CASE WHEN itype IN ('MUSIK') THEN 1 END) 'Nyförvärv noter',
  COUNT(CASE WHEN itype IN ('TV-SPEL') THEN 1 END) 'Nyförvärv TV-spel',
  COUNT(CASE WHEN itype IN ('ARTIKEL','BLANDAT','FOREMAL','SUFRPLATTA','VECKOLAN','X') THEN 1 END) 'Nyförvärv övrigt'
  
FROM items

LEFT JOIN branches ON (items.homebranch=branches.branchcode)

WHERE items.homebranch = <<Bibliotek|branches>>  
AND items.dateaccessioned BETWEEN <<Datum från|date>>-INTERVAL 1 DAY AND <<Datum till |date>>+INTERVAL 1 DAY 


GROUP BY homebranch

Fråga 10, tidskrifter

Fråga 10 är knepig - har man bara ett fåtal titlar är det helt klart enklare att räkna för hand än att köra en rapport!

Det som efterfrågas är inte antalet prenumerationer utan unika titlar per kommun, dessutom ska man inte räkna veckotidningar som exempelvis Land och ICA-kuriren. Och nyförvärv ska man bara rapportera om titeln är ny för kommunen - om en filial startar en nyprenumeration av en titel som redan finns på HB räknas det inte som nyförvärv.

Det här är ett första försök att lösa frågan med en rapport. Villkoret är att man ska ha ankomstregistrerat (serial.status=2) mer än ett nummer under året - jag har helt enkelt antagit att enstaka nummer är eftersläntare från föregående år. För oss är subscription_frequencies.id=4 för veckotidningar. Enklaste sättet att få fram den siffran för sitt eget system är att gå under Periodika > Hantera frekvenser > klicka på Redigera - så ser man i URLen vilket nummer man ska välja.

I den här versionen så visas texten Ny i högraste kolumnen så fort det finns en nystartad prenumeration - oavsett om man har haft prenumerationer på titeln tidigare eller inte. Fritt fram att fixa för den som kommer på hur det ska göras 

SELECT biblio.title AS Titel ,GROUP_CONCAT(DISTINCT branches.branchname SEPARATOR '<br>') AS 'Bibliotek',COUNT(serialid) AS 'Antal mottagna nummer',GROUP_CONCAT(DISTINCT 
subscriptionhistory.histstartdate SEPARATOR '<br>') AS Startdatum,
CASE 
WHEN subscriptionhistory.histstartdate > <<Datum från|date>> 
THEN
'Ny'
ELSE
''
END
AS 'Ny?'

FROM subscription
LEFT JOIN biblio ON (biblio.biblionumber=subscription.biblionumber)
LEFT JOIN serial on (serial.subscriptionid=subscription.subscriptionid)
LEFT JOIN branches ON (branches.branchcode=subscription.branchcode)
LEFT JOIN subscription_frequencies ON (subscription_frequencies.id=subscription.periodicity)
LEFT JOIN subscriptionhistory ON (subscriptionhistory.subscriptionid=subscription.subscriptionid)

 

WHERE serial.status='2' 
AND subscription_frequencies.id NOT IN ('4')
AND serial.publisheddate BETWEEN <<Datum från|date>>-INTERVAL 1 DAY AND <<Datum till |date>>+INTERVAL 1 DAY
AND subscription.branchcode IN ('Sigel1','Sigel2','Sigel3') 

GROUP BY subscription.biblionumber
HAVING COUNT(serialid)>1 

ORDER BY biblio.title

Fråga 11 olika bestånd - om man har en placeringskod för skönlitteratur

Det enklaste är naturligtvis om man har placeringskoder på alla exemplar - då kan man göra en så här enkel rapport 

SELECT 
  COUNT(CASE WHEN itype IN ('BARNBOK','BARNKORT','BOKCDBARN','BOKDAISYBA','BOKMP3BARN','BARN LJUD','BARNMP3','BARNTAL','MUSIKBARN','MUSIKBARN') THEN 1 END) 'Bestånd för barn och unga',
  COUNT(CASE WHEN location IN ('Skonlitteratur','Deckare','Deckare & Thriller','Fantasy','Fantasy/SF','Noveller') THEN 1 END) 'Bestånd av skönlitteratur',
  COUNT(CASE WHEN location IN ('REFERENS') THEN 1 END) 'Antal referensexemplar',
  COUNT(CASE WHEN itype IN ('BARNTAL','BOKCD','BOKCDBARN','BOKDAISYBA','BOKMP3','BOKMP3BARN','DAISY','STORSTIL') THEN 1 WHEN location IN ('Lattlast','Appelhyllan') THEN 1 END) 'Bestånd läsnedsättning'
  
FROM items

LEFT JOIN branches ON (items.homebranch=branches.branchcode)

 WHERE items.homebranch = <<Bibliotek|branches>>

Fråga 11 olika bestånd - om man måste utgå från hyllsignum

Om man tar alternativet Alla bibliotek i en stor katalog kommer rapporten antagligen att krascha 

SELECT 
CASE WHEN 
branches.branchname IS NULL
THEN '=Summa'
ELSE
branches.branchname 
END 
AS Bibliotek,

SUM(barn) AS 'Bestånd för barn och unga',
SUM(skon) AS 'Bestånd av skönlitteratur',
SUM(referensexemplar) AS'Antal referensexemplar',
SUM(lasnedsattning) AS 'Bestånd läsnedsättning'


FROM
(
(SELECT items.homebranch, 1 as barn, 0 as skon, 0 as referensexemplar, 0 as lasnedsattning
FROM items 

WHERE items.homebranch LIKE <<Bibliotek|branches:all>>
AND items.itype IN ('BARN TIDSK','BARNBOK','BOKCDBARN','BOKDAISYBA','BOKMP3BARN','BARN LJUD','BARNMP3','BARNTAL'))

UNION ALL 

(SELECT items.homebranch, 0 as barn, 1 as skon, 0 as referensexemplar, 0 as lasnedsattning
FROM items 

WHERE items.homebranch LIKE <<Bibliotek|branches:all>>
AND itemcallnumber LIKE BINARY '%H%' 
AND items.itype IN ('BARN TIDSK','BARNBOK','BOK','LANGLAN','POCKET','TIDSKRIFT'))

UNION ALL 

(SELECT items.homebranch, 0 as barn, 0 as skon, 1 as referensexemplar, 0 as lasnedsattning
FROM items 

WHERE items.homebranch LIKE <<Bibliotek|branches:all>>
AND items.location IN ('REFERENS'))

UNION ALL

(SELECT items.homebranch, 0 as barn, 0 as skon, 0 as referensexemplar, 1 as lasnedsattning
FROM items 

WHERE items.homebranch LIKE <<Bibliotek|branches:all>> 
AND items.itype IN ('BARNTAL','BOKCD','BOKCDBARN','BOKDAISYBA','BOKMP3','BOKMP3BARN','DAISY','STORSTIL','TALBOKKASS'))

) ds

LEFT JOIN branches ON (branches.branchcode=ds.homebranch)

GROUP BY branches.branchname WITH ROLLUP

Fråga 11 Olika nyförvärv

SELECT 
  branchname,
  COUNT(CASE WHEN itype IN ('BARN LJUD','BARN TIDSK','BARNBOK','BARNKORT','BOKCDBARN','BOKDAISYBA','BOKMP3BARN') THEN 1 END) 'Nyförvärv tryckt för barn'
  
FROM items

LEFT JOIN branches ON (items.homebranch=branches.branchcode)

WHERE items.homebranch IS NOT NULL 
AND items.dateaccessioned BETWEEN <<Datum från|date>>-INTERVAL 1 DAY AND <<Datum till |date>>+INTERVAL 1 DAY 
AND items.homebranch = <<Bibliotek|branches>>


GROUP BY homebranch

Fråga 11 Olika utlån

Förutsätter att man har en separat uppsättning exemplartyper för olika sorters barnmedia 

SELECT 
  branchname AS Bibliotek,
  COUNT(CASE WHEN type IN ('issue','renew') AND itemtype IN ('BARNBOK','BARNKORT','BOKCDBARN','BOKDAISYBA','BOKMP3BARN','BARN LJUD','BARNMP3','BARNTAL') THEN 1 END) 'Utlån för barn',
  COUNT(CASE WHEN type IN ('issue','renew') AND itemtype IN ('BARNTAL','BOKCD','BOKCDBARN','BOKDAISYBA','BOKMP3','BOKMP3BARN','DAISY','STORSTIL') THEN 1 WHEN type IN ('issue','renew') AND location IN ('Lattlast','Appelhyllan') THEN 1 END) 'Utlån läsnedsättning'  
FROM statistics

LEFT JOIN branches ON (statistics.branch=branches.branchcode)

WHERE statistics.branch = <<Bibliotek|branches>> 
AND statistics.itemtype IS NOT NULL 
AND statistics.datetime BETWEEN <<Datum från|date>>-INTERVAL 1 DAY AND <<Datum till |date>>+INTERVAL 1 DAY 
 


GROUP BY branch

Fråga 12 Frågor om språk

Bibliotek Mellansjö har ett stort antal äldre katalogposter från ett av de tidigare systemen som saknar språkkod. De flesta av dem är på svenska, så vi låter helt enkelt tom språkkod räknas som svenska 

SELECT 
CASE 
WHEN substring( ExtractValue(metadata,'//controlfield[@tag="008"]'), 36,3 )='swe' THEN '1 Svenska'
WHEN substring( ExtractValue(metadata,'//controlfield[@tag="008"]'), 36,3 )='' THEN '1 Svenska'
WHEN substring( ExtractValue(metadata,'//controlfield[@tag="008"]'), 36,3 )='fin' THEN '2 Minoritets'
WHEN substring( ExtractValue(metadata,'//controlfield[@tag="008"]'), 36,3 )='9mk' THEN '2 Minoritets'
WHEN substring( ExtractValue(metadata,'//controlfield[@tag="008"]'), 36,3 )='smi' THEN '2 Minoritets'
WHEN substring( ExtractValue(metadata,'//controlfield[@tag="008"]'), 36,3 )='smj' THEN '2 Minoritets'
WHEN substring( ExtractValue(metadata,'//controlfield[@tag="008"]'), 36,3 )='sme' THEN '2 Minoritets'
WHEN substring( ExtractValue(metadata,'//controlfield[@tag="008"]'), 36,3 )='sms' THEN '2 Minoritets'
WHEN substring( ExtractValue(metadata,'//controlfield[@tag="008"]'), 36,3 )='sma' THEN '2 Minoritets'
WHEN substring( ExtractValue(metadata,'//controlfield[@tag="008"]'), 36,3 )='rom' THEN '2 Minoritets'
WHEN substring( ExtractValue(metadata,'//controlfield[@tag="008"]'), 36,3 )='yid' THEN '2 Minoritets'
ELSE '3 Utländska'
END
AS 'Språk',
COUNT(*) AS 'Antal titlar'
    
FROM biblio_metadata 

GROUP BY 1

Vill man räkna bara en del av katalogen lägger man till en WHERE-sats där man säger att biblionumber ska förekomma i [...] och så gör man sitt urval i en subquery. Se hur ett sådant urval kan se ut:

Mellansjös språkrapport

Fråga 14 Lånestatistik

SELECT 
  branchname AS Bibliotek,
  COUNT(CASE WHEN type = 'issue' AND itemtype IN ('BARNBOK','BARNKORT','BOK','BOKCD','BOKCDBARN','BOKDAISY','BOKDAISYBA','BOKMP3','BOKMP3BARN','KORTLON','LANGLAN','PAKET','REFERENS','SPRAKKURS','STORSTIL') THEN 1 END) 'Utlån tryckt bok',
  COUNT(CASE WHEN type = 'renew' AND itemtype IN ('BARNBOK','BARNKORT','BOK','BOKCD','BOKCDBARN','BOKDAICY','BOKDAISYBA','BOKMP3','BOKMP3BARN','KORTLON','LANGLAN','PAKET','REFERENS','SPRAKKURS','STORSTIL') THEN 1 END) 'Omlån tryckt bok',
  COUNT(CASE WHEN type = 'issue' AND itemtype IN ('LAROMDL','LAROMTERM') THEN 1 END) 'Utlån läromedel',
  COUNT(CASE WHEN type = 'renew' AND itemtype IN ('LAROMDL','LAROMTERM') THEN 1 END) 'Omlån läromedel',
  COUNT(CASE WHEN type = 'issue' AND itemtype IN ('BARN LJUD','BARNMP3','LJUDBOK','MP3') THEN 1 END) 'Utlån ljudböcker',
  COUNT(CASE WHEN type = 'renew' AND itemtype IN ('BARN LJUD','BARNMP3','LJUDBOK','MP3') THEN 1 END) 'Omlån ljudböcker',
  COUNT(CASE WHEN type = 'issue' AND itemtype IN ('BARNTAL','DAISY') THEN 1 END) 'Utlån talböcker daisy',
  COUNT(CASE WHEN type = 'renew' AND itemtype IN ('BARNTAL','DAISY') THEN 1 END) 'Omlån talböcker daisy',
  COUNT(CASE WHEN type = 'issue' AND itemtype IN ('BARN TIDSK','TIDSKRIFT') THEN 1 END) 'Utlån tidskrifter',
  COUNT(CASE WHEN type = 'renew' AND itemtype IN ('BARN TIDSK','TIDSKRIFT') THEN 1 END) 'Omlån tidskrifter',
  COUNT(CASE WHEN type = 'issue' AND itemtype IN ('MUSIKCD') THEN 1 END) 'Utlån musik',
  COUNT(CASE WHEN type = 'renew' AND itemtype IN ('MUSIKCD') THEN 1 END) 'Omlån musik',
  COUNT(CASE WHEN type = 'issue' AND itemtype IN ('BLURAY','FILM','VHS') THEN 1 END) 'Utlån film',
  COUNT(CASE WHEN type = 'renew' AND itemtype IN ('BLURAY','FILM','VHS') THEN 1 END) 'Omlån film',
  COUNT(CASE WHEN type = 'issue' AND itemtype IN ('KARTOR') THEN 1 END) 'Utlån kartor',
  COUNT(CASE WHEN type = 'renew' AND itemtype IN ('KARTOR') THEN 1 END) 'Omlån kartor',
  COUNT(CASE WHEN type = 'issue' AND itemtype IN ('MUSIK','MUSIKBARN') THEN 1 END) 'Utlån noter',
  COUNT(CASE WHEN type = 'renew' AND itemtype IN ('MUSIK','MUSIKBARN') THEN 1 END) 'Omlån noter',
  COUNT(CASE WHEN type = 'issue' AND itemtype IN ('TV-SPEL') THEN 1 END) 'Utlån TV-spel',
  COUNT(CASE WHEN type = 'renew' AND itemtype IN ('TV-SPEL') THEN 1 END) 'Omlån TV-spel',
  COUNT(CASE WHEN type = 'issue' AND itemtype IN ('ARTIKEL','BLANDAT','FOREMAL','SUFRPLATTA','VECKOLAN','X') THEN 1 END) 'Utlån övrigt',
  COUNT(CASE WHEN type = 'renew' AND itemtype IN ('ARTIKEL','BLANDAT','FOREMAL','SUFRPLATTA','VECKOLAN','X') THEN 1 END) 'Omlån övrigt'

FROM statistics

LEFT JOIN branches ON (statistics.branch=branches.branchcode)

WHERE statistics.branch=<<Bibliotek|branches>> 
AND statistics.itemtype IS NOT NULL 
AND statistics.datetime BETWEEN <<Datum från|date>>-INTERVAL 1 DAY AND <<Datum till |date>>+INTERVAL 1 DAY 


GROUP BY branch

ORDER BY branchname

Fråga 16 Fjärrlån

Fråga 16 blandar äpplen och päron. Fjärrlån-in räknar antal lån som gjorts av exemplartyp Fjärrlån. Fjärrlån-ut räknar antal lån som gjorts av låntagare i kategorin bibliotek. Listan med borrowers.cardnumber är bibliotek inom vår egen organisation, eftersom de biblioteken har låntagarkategori bibliotek men inte ska räknas som fjärrlån 

SELECT 
CASE
WHEN branchname IS NULL
THEN '=Summa'
ELSE
 branchname
 END AS Bibliotek,
 SUM(utlan) AS 'Utlån',
 SUM(inlan) AS 'Inlån'
 
 FROM
 (
 (SELECT branch,1 as utlan, 0 as inlan
 FROM statistics

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

WHERE type = 'issue' 
AND borrowers.categorycode IN ('BIBLIOTEK') 
AND borrowers.cardnumber NOT IN ('8bxq','8bxz','8bya','8byb','8byc','8byi','8byl','8bym','8byo','8byq','8byr','8bys','Gull','Hjo','Hova','Kabo','Kbro','Mari','Skgy','Sksb','Tida','Tikf','Tore','Vagy')
AND statistics.branch LIKE <<Bibliotek|branches:all>>
AND statistics.itemtype IS NOT NULL 
AND statistics.datetime BETWEEN <<Datum från|date>>-INTERVAL 1 DAY AND <<Datum till |date>>+INTERVAL 1 DAY 
 )
 
 UNION ALL
 
 (SELECT branch,0 as utlan, 1 as inlan
 FROM statistics

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

WHERE type = 'issue' 
AND itemtype IN ('FJARRLAN')
AND statistics.branch LIKE<<Bibliotek|branches:all>>
AND statistics.itemtype IS NOT NULL 
AND statistics.datetime BETWEEN <<Datum från|date>>-INTERVAL 1 DAY AND <<Datum till |date>>+INTERVAL 1 DAY  )
) ds
LEFT JOIn branches On (branches.branchcode=ds.branch)

GROUP BY branchname WITH ROLLUP

Fråga 19 Aktiva låntagare

SELECT 
CASE WHEN
branchname IS NULL
THEN
'=Summa'
ELSE
branchname 
END 
AS 'Låntagarens hembibliotek',
COUNT(CASE WHEN code = 'PERSNUMMER' AND LEFT( RIGHT(attribute, 2), 1) % 2 = 0 THEN 1 END) 'Aktiva kvinnor',
COUNT(CASE WHEN code = 'PERSNUMMER' AND LEFT( RIGHT(attribute, 2), 1) % 2 = 1 THEN 1 END) 'Aktiva män',
COUNT (CASE WHEN borrowers.categorycode IN ('BIBLIOTEK','ELEVTIDA','FORSKOLA','INSTT','KVALON','PERSONAL','PTID','SKOLA','SKOLBIBTID') THEN 1 END) 'Institutionslåntagare',
COUNT(CASE WHEN dateofbirth > DATE_SUB(CURDATE(), INTERVAL 18 YEAR) OR borrowers.categorycode IN ('ELEVTIDA','SKOLBIBTID','BARN') THEN 1 END) 'Under 18 år'

FROM borrowers 

LEFT JOIN borrower_attributes ON (borrowers.borrowernumber=borrower_attributes.borrowernumber) 
LEFT JOIN branches ON (borrowers.branchcode=branches.branchcode)

WHERE 
borrowers.borrowernumber IN

(SELECT borrowernumber 

FROM
statistics 

WHERE 
statistics.datetime BETWEEN <<Datum från|date>>-INTERVAL 1 DAY AND <<Datum till |date>>+INTERVAL 1 DAY AND statistics.branch LIKE <<Utlånande bibliotek|branches:all>> AND statistics.type='issue')

GROUP BY branchname WITH ROLLUP

Hur man skapar ett auktoriserat värde för en kommun (eller annan grupp av bibliotek)

Bibliotek Mellansjö har 42 bibliotek som ska redovisa kommunvis av de åtta kommunerna. För att slippa köra alla rapporter 42 gånger och sedan summera resultaten har vi skapat auktoriserade värden för kommuner.

Först behöver man skapa grupper av bibliotek. Det gör man under Koha-administration > Biblioteksgrupper

När det finns färdiga grupper kan man köra denna rapport: 

SELECT 
parent_id,
title,
description,
branchcode 
FROM library_groups
ORDER BY parent_id

Då får man ett resultat som ser ut enligt nedan:

Bild på resultatet av rapporten

Notera hur siffrorna i första kolumnen hör ihop med biblioteksgrupperna: 24 för biblioteken i Gullspångs kommun 28 för biblioteket i Hjo kommun 45 för biblioteken i Karlsborgs kommun och så vidare

Gå sedan in på Administration > Auktoriserade värden > Ny kategori och döper den nya kategorin till ”librarygroupsparentid”

Klicka sedan på ”Nytt auktoriserat värde för librarygroupsparentid” Lägger in siffrorna du fick ovan för de olika kommunerna som auktoriserat värde och kommunnamnet som beskrivning

Bild på auktoriserade värden

Nedan följer Bibliotek Mellansjös kommunvisa rapporter - som förutsätter att man har gjort ett auktoriserat värde för kommuner

Fråga 10 Fysiskt bestånd - kommunvis

SELECT 
CASE
WHEN branches.branchname IS NULL
THEN '=Summa'
ELSE
branches.branchname
END
AS Bibliotek,
SUM(tryckt) AS 'Bestånd tryckt bok',
SUM(laromedel) AS 'Bestånd läromedel',
SUM(ljudbok) AS 'Bestånd ljudböcker',
SUM(talbok) AS 'Bestånd talböcker daisy',
SUM(musik) AS 'Bestånd musik',
SUM(film) AS 'Bestånd film',
SUM(kartor) AS 'Bestånd kartor',
SUM(noter) 'Bestånd noter',
SUM(tvspel) 'Bestånd Tv-spel',
SUM(ovrigt) AS 'Bestånd övrigt'
FROM
(
(SELECT items.homebranch,1 as tryckt,0 as laromedel,0 as ljudbok,0 as talbok,0 as musik,0 as film,0 as kartor,0 as noter,0 as tvspel,0 as ovrigt
FROM items 

LEFT JOIN branches ON (items.homebranch=branches.branchcode)
LEFT JOIN library_groups ON (items.homebranch=library_groups.branchcode)
LEFT JOIN biblio ON (biblio.biblionumber=items.biblionumber)

WHERE 
(biblio.frameworkcode !='BOKP')
AND items.itype IN ('BARNBOK','BARNKORT','BOK','BOKCD','BOKCDBARN','BOKDAISY','BOKDAISYBA','BOKMP3','BOKMP3BARN','KORTLON','LANGLAN','PAKET','POCKET','REFERENS','SPRAKKURS','STORSTIL')
AND items.homebranch IS NOT NULL AND library_groups.parent_id=<<Kommun|librarygroupsparentid>>) 

UNION ALL

(SELECT items.homebranch,materials as tryckt,0 as laromedel,0 as ljudbok,0 as talbok,0 as musik,0 as film,0 as kartor,0 as noter,0 as tvspel,0 as ovrigt
FROM items 

LEFT JOIN branches ON (items.homebranch=branches.branchcode)
LEFT JOIN library_groups ON (items.homebranch=library_groups.branchcode)
LEFT JOIN biblio ON (biblio.biblionumber=items.biblionumber)

WHERE 
(biblio.frameworkcode ='BOKP') 
AND items.itype IN ('BARNBOK','BARNKORT','BOK','BOKCD','BOKCDBARN','BOKDAISY','BOKDAISYBA','BOKMP3','BOKMP3BARN','KORTLON','LANGLAN','PAKET','POCKET','REFERENS','SPRAKKURS','STORSTIL')
AND items.homebranch IS NOT NULL AND library_groups.parent_id=<<Kommun|librarygroupsparentid>>) 

UNION ALL

(SELECT items.homebranch,0 as tryckt,1 as laromedel,0 as ljudbok,0 as talbok,0 as musik,0 as film,0 as kartor,0 as noter,0 as tvspel,0 as ovrigt
FROM items 

LEFT JOIN branches ON (items.homebranch=branches.branchcode)
LEFT JOIN library_groups ON (items.homebranch=library_groups.branchcode)

WHERE 
items.itype IN ('LAROMDL','LAROMTERM')
AND items.homebranch IS NOT NULL AND library_groups.parent_id=<<Kommun|librarygroupsparentid>>) 

UNION ALL

(SELECT items.homebranch,0 as tryckt,0 as laromedel,1 as ljudbok,0 as talbok,0 as musik,0 as film,0 as kartor,0 as noter,0 as tvspel,0 as ovrigt
FROM items 

LEFT JOIN branches ON (items.homebranch=branches.branchcode)
LEFT JOIN library_groups ON (items.homebranch=library_groups.branchcode)

WHERE 
items.itype IN ('BARN LJUD','BARNMP3','KASSETT','LJUDBOK','MP3')
AND items.homebranch IS NOT NULL AND library_groups.parent_id=<<Kommun|librarygroupsparentid>>) 

UNION ALL

(SELECT items.homebranch,0 as tryckt,0 as laromedel,0 as ljudbok,1 as talbok,0 as musik,0 as film,0 as kartor,0 as noter,0 as tvspel,0 as ovrigt
FROM items 

LEFT JOIN branches ON (items.homebranch=branches.branchcode)
LEFT JOIN library_groups ON (items.homebranch=library_groups.branchcode)

WHERE 
items.itype IN ('BARNTAL','DAISY','TALBOKKASS')
AND items.homebranch IS NOT NULL AND library_groups.parent_id=<<Kommun|librarygroupsparentid>>) 

UNION ALL

(SELECT items.homebranch,0 as tryckt,0 as laromedel,0 as ljudbok,0 as talbok,1 as musik,0 as film,0 as kartor,0 as noter,0 as tvspel,0 as ovrigt
FROM items 

LEFT JOIN branches ON (items.homebranch=branches.branchcode)
LEFT JOIN library_groups ON (items.homebranch=library_groups.branchcode)

WHERE 
items.itype IN ('MUSIKCD','MUSCDBARN')
AND items.homebranch IS NOT NULL AND library_groups.parent_id=<<Kommun|librarygroupsparentid>>) 

UNION ALL

(SELECT items.homebranch,0 as tryckt,0 as laromedel,0 as ljudbok,0 as talbok,0 as musik,1 as film,0 as kartor,0 as noter,0 as tvspel,0 as ovrigt
FROM items 

LEFT JOIN branches ON (items.homebranch=branches.branchcode)
LEFT JOIN library_groups ON (items.homebranch=library_groups.branchcode)

WHERE 
items.itype IN ('BLURAY','FILM','VHS')
AND items.homebranch IS NOT NULL AND library_groups.parent_id=<<Kommun|librarygroupsparentid>>) 

UNION ALL

(SELECT items.homebranch,0 as tryckt,0 as laromedel,0 as ljudbok,0 as talbok,0 as musik,0 as film,1 as kartor,0 as noter,0 as tvspel,0 as ovrigt
FROM items 

LEFT JOIN branches ON (items.homebranch=branches.branchcode)
LEFT JOIN library_groups ON (items.homebranch=library_groups.branchcode)

WHERE 
items.itype IN ('KARTOR')
AND items.homebranch IS NOT NULL AND library_groups.parent_id=<<Kommun|librarygroupsparentid>>) 

UNION ALL

(SELECT items.homebranch,0 as tryckt,0 as laromedel,0 as ljudbok,0 as talbok,0 as musik,0 as film,0 as kartor,1 as noter,0 as tvspel,0 as ovrigt
FROM items 

LEFT JOIN branches ON (items.homebranch=branches.branchcode)
LEFT JOIN library_groups ON (items.homebranch=library_groups.branchcode)

WHERE 
items.itype IN ('MUSIK','MUSIKBARN') 
AND items.homebranch IS NOT NULL AND library_groups.parent_id=<<Kommun|librarygroupsparentid>>) 

UNION ALL

(SELECT items.homebranch,0 as tryckt,0 as laromedel,0 as ljudbok,0 as talbok,0 as musik,0 as film,0 as kartor,0 as noter,1 as tvspel,0 as ovrigt
FROM items 

LEFT JOIN branches ON (items.homebranch=branches.branchcode)
LEFT JOIN library_groups ON (items.homebranch=library_groups.branchcode)

WHERE 
items.itype IN ('TV-SPEL')
AND items.homebranch IS NOT NULL AND library_groups.parent_id=<<Kommun|librarygroupsparentid>>) 

UNION ALL

(SELECT items.homebranch,0 as tryckt,0 as laromedel,0 as ljudbok,0 as talbok,0 as musik,0 as film,0 as kartor,0 as noter,0 as tvspel,1 as ovrigt
FROM items 

LEFT JOIN branches ON (items.homebranch=branches.branchcode)
LEFT JOIN library_groups ON (items.homebranch=library_groups.branchcode)

WHERE 
items.itype IN ('BLANDAT','BOKPASE','CDROM','FOREMAL','FOREMAL3MD','KONTROLL','SALLSKAPSS','SUFRPLATTA','VECKOLAN','X')
AND items.homebranch IS NOT NULL AND library_groups.parent_id=<<Kommun|librarygroupsparentid>>) 

UNION ALL

(SELECT items.homebranch,-1 as tryckt,0 as laromedel,0 as ljudbok,0 as talbok,0 as musik,0 as film,0 as kartor,0 as noter,0 as tvspel,0 as ovrigt
FROM items 

LEFT JOIN branches ON (items.homebranch=branches.branchcode)
LEFT JOIN library_groups ON (items.homebranch=library_groups.branchcode)

WHERE 
(items.dateaccessioned BETWEEN <<Datum (Nyårsafton i KB-statistiken)|date>> AND CURDATE()) 
AND items.itype IN ('BARNBOK','BARNKORT','BOK','BOKCD','BOKCDBARN','BOKDAISY','BOKDAISYBA','BOKMP3','BOKMP3BARN','KORTLON','LANGLAN','PAKET','POCKET','REFERENS','SPRAKKURS','STORSTIL')
AND items.homebranch IS NOT NULL AND library_groups.parent_id=<<Kommun|librarygroupsparentid>>) 

UNION ALL

(SELECT items.homebranch,0 as tryckt,-1 as laromedel,0 as ljudbok,0 as talbok,0 as musik,0 as film,0 as kartor,0 as noter,0 as tvspel,0 as ovrigt
FROM items 

LEFT JOIN branches ON (items.homebranch=branches.branchcode)
LEFT JOIN library_groups ON (items.homebranch=library_groups.branchcode)

WHERE 
(items.dateaccessioned BETWEEN <<Datum (Nyårsafton i KB-statistiken)|date>> AND CURDATE()) 
AND items.itype IN ('LAROMDL','LAROMTERM')
AND items.homebranch IS NOT NULL AND library_groups.parent_id=<<Kommun|librarygroupsparentid>>) 

UNION ALL

(SELECT items.homebranch,0 as tryckt,0 as laromedel,-1 as ljudbok,0 as talbok,0 as musik,0 as film,0 as kartor,0 as noter,0 as tvspel,0 as ovrigt
FROM items 

LEFT JOIN branches ON (items.homebranch=branches.branchcode)
LEFT JOIN library_groups ON (items.homebranch=library_groups.branchcode)

WHERE 
(items.dateaccessioned BETWEEN <<Datum (Nyårsafton i KB-statistiken)|date>> AND CURDATE()) 
AND items.itype IN ('BARN LJUD','BARNMP3','KASSETT','LJUDBOK','MP3')
AND items.homebranch IS NOT NULL AND library_groups.parent_id=<<Kommun|librarygroupsparentid>>) 

UNION ALL

(SELECT items.homebranch,0 as tryckt,0 as laromedel,0 as ljudbok,-1 as talbok,0 as musik,0 as film,0 as kartor,0 as noter,0 as tvspel,0 as ovrigt
FROM items 

LEFT JOIN branches ON (items.homebranch=branches.branchcode)
LEFT JOIN library_groups ON (items.homebranch=library_groups.branchcode)

WHERE 
(items.dateaccessioned BETWEEN <<Datum (Nyårsafton i KB-statistiken)|date>> AND CURDATE()) 
AND items.itype IN ('BARNTAL','DAISY','TALBOKKASS')
AND items.homebranch IS NOT NULL AND library_groups.parent_id=<<Kommun|librarygroupsparentid>>) 

UNION ALL

(SELECT items.homebranch,0 as tryckt,0 as laromedel,0 as ljudbok,0 as talbok,-1 as musik,0 as film,0 as kartor,0 as noter,0 as tvspel,0 as ovrigt
FROM items 

LEFT JOIN branches ON (items.homebranch=branches.branchcode)
LEFT JOIN library_groups ON (items.homebranch=library_groups.branchcode)

WHERE 
(items.dateaccessioned BETWEEN <<Datum (Nyårsafton i KB-statistiken)|date>> AND CURDATE()) 
AND items.itype IN ('MUSIKCD','MUSCDBARN')
AND items.homebranch IS NOT NULL AND library_groups.parent_id=<<Kommun|librarygroupsparentid>>) 

UNION ALL

(SELECT items.homebranch,0 as tryckt,0 as laromedel,0 as ljudbok,0 as talbok,0 as musik,-1 as film,0 as kartor,0 as noter,0 as tvspel,0 as ovrigt
FROM items 

LEFT JOIN branches ON (items.homebranch=branches.branchcode)
LEFT JOIN library_groups ON (items.homebranch=library_groups.branchcode)

WHERE 
(items.dateaccessioned BETWEEN <<Datum (Nyårsafton i KB-statistiken)|date>> AND CURDATE()) 
AND items.itype IN ('BLURAY','FILM','VHS')
AND items.homebranch IS NOT NULL AND library_groups.parent_id=<<Kommun|librarygroupsparentid>>) 

UNION ALL

(SELECT items.homebranch,0 as tryckt,0 as laromedel,0 as ljudbok,0 as talbok,0 as musik,0 as film,-1 as kartor,0 as noter,0 as tvspel,0 as ovrigt
FROM items 

LEFT JOIN branches ON (items.homebranch=branches.branchcode)
LEFT JOIN library_groups ON (items.homebranch=library_groups.branchcode)

WHERE 
(items.dateaccessioned BETWEEN <<Datum (Nyårsafton i KB-statistiken)|date>> AND CURDATE()) 
AND items.itype IN ('KARTOR')
AND items.homebranch IS NOT NULL AND library_groups.parent_id=<<Kommun|librarygroupsparentid>>) 

UNION ALL

(SELECT items.homebranch,0 as tryckt,0 as laromedel,0 as ljudbok,0 as talbok,0 as musik,0 as film,0 as kartor,-1 as noter,0 as tvspel,0 as ovrigt
FROM items 

LEFT JOIN branches ON (items.homebranch=branches.branchcode)
LEFT JOIN library_groups ON (items.homebranch=library_groups.branchcode)

WHERE 
(items.dateaccessioned BETWEEN <<Datum (Nyårsafton i KB-statistiken)|date>> AND CURDATE()) 
AND items.itype IN ('MUSIK','MUSIKBARN') 
AND items.homebranch IS NOT NULL AND library_groups.parent_id=<<Kommun|librarygroupsparentid>>) 

UNION ALL

(SELECT items.homebranch,0 as tryckt,0 as laromedel,0 as ljudbok,0 as talbok,0 as musik,0 as film,0 as kartor,0 as noter,-1 as tvspel,0 as ovrigt
FROM items 

LEFT JOIN branches ON (items.homebranch=branches.branchcode)
LEFT JOIN library_groups ON (items.homebranch=library_groups.branchcode)

WHERE 
(items.dateaccessioned BETWEEN <<Datum (Nyårsafton i KB-statistiken)|date>> AND CURDATE()) 
AND items.itype IN ('TV-SPEL')
AND items.homebranch IS NOT NULL AND library_groups.parent_id=<<Kommun|librarygroupsparentid>>) 

UNION ALL

(SELECT items.homebranch,0 as tryckt,0 as laromedel,0 as ljudbok,0 as talbok,0 as musik,0 as film,0 as kartor,0 as noter,0 as tvspel,-1 as ovrigt
FROM items 

LEFT JOIN branches ON (items.homebranch=branches.branchcode)
LEFT JOIN library_groups ON (items.homebranch=library_groups.branchcode)

WHERE 
(items.dateaccessioned BETWEEN <<Datum (Nyårsafton i KB-statistiken)|date>> AND CURDATE()) 
AND items.itype IN ('ARTIKEL','BLANDAT','BOKPASE','CDROM','FOREMAL','FOREMAL3MD','KONTROLL','SALLSKAPSS','SUFRPLATTA','VECKOLAN','X')
AND items.homebranch IS NOT NULL AND library_groups.parent_id=<<Kommun|librarygroupsparentid>>) 

UNION ALL

(SELECT deleteditems.homebranch,1 as tryckt,0 as laromedel,0 as ljudbok,0 as talbok,0 as musik,0 as film,0 as kartor,0 as noter,0 as tvspel,0 as ovrigt
FROM deleteditems 

LEFT JOIN branches ON (deleteditems.homebranch=branches.branchcode)
LEFT JOIN library_groups ON (deleteditems.homebranch=library_groups.branchcode)

WHERE 
(deleteditems.timestamp BETWEEN <<Datum (Nyårsafton i KB-statistiken)|date>> AND CURDATE())
AND deleteditems.itype IN ('BARNBOK','BARNKORT','BOK','BOKCD','BOKCDBARN','BOKDAISY','BOKDAISYBA','BOKMP3','BOKMP3BARN','KORTLON','LANGLAN','PAKET','POCKET','REFERENS','SPRAKKURS','STORSTIL')
AND deleteditems.homebranch IS NOT NULL AND library_groups.parent_id=<<Kommun|librarygroupsparentid>>) 

UNION ALL

(SELECT deleteditems.homebranch,0 as tryckt,1 as laromedel,0 as ljudbok,0 as talbok,0 as musik,0 as film,0 as kartor,0 as noter,0 as tvspel,0 as ovrigt
FROM deleteditems 

LEFT JOIN branches ON (deleteditems.homebranch=branches.branchcode)
LEFT JOIN library_groups ON (deleteditems.homebranch=library_groups.branchcode)

WHERE 
(deleteditems.timestamp BETWEEN <<Datum (Nyårsafton i KB-statistiken)|date>> AND CURDATE())
AND deleteditems.itype IN ('LAROMDL','LAROMTERM')
AND deleteditems.homebranch IS NOT NULL AND library_groups.parent_id=<<Kommun|librarygroupsparentid>>) 

UNION ALL

(SELECT deleteditems.homebranch,0 as tryckt,0 as laromedel,1 as ljudbok,0 as talbok,0 as musik,0 as film,0 as kartor,0 as noter,0 as tvspel,0 as ovrigt
FROM deleteditems 

LEFT JOIN branches ON (deleteditems.homebranch=branches.branchcode)
LEFT JOIN library_groups ON (deleteditems.homebranch=library_groups.branchcode)

WHERE (deleteditems.timestamp BETWEEN <<Datum (Nyårsafton i KB-statistiken)|date>> AND CURDATE())
AND deleteditems.itype IN ('BARN LJUD','BARNMP3','KASSETT','LJUDBOK','MP3')
AND deleteditems.homebranch IS NOT NULL AND library_groups.parent_id=<<Kommun|librarygroupsparentid>>) 

UNION ALL

(SELECT deleteditems.homebranch,0 as tryckt,0 as laromedel,0 as ljudbok,1 as talbok,0 as musik,0 as film,0 as kartor,0 as noter,0 as tvspel,0 as ovrigt
FROM deleteditems 

LEFT JOIN branches ON (deleteditems.homebranch=branches.branchcode)
LEFT JOIN library_groups ON (deleteditems.homebranch=library_groups.branchcode)

WHERE 
(deleteditems.timestamp BETWEEN <<Datum (Nyårsafton i KB-statistiken)|date>> AND CURDATE())
AND deleteditems.itype IN ('BARNTAL','DAISY','TALBOKKASS')
AND deleteditems.homebranch IS NOT NULL AND library_groups.parent_id=<<Kommun|librarygroupsparentid>>) 

UNION ALL

(SELECT deleteditems.homebranch,0 as tryckt,0 as laromedel,0 as ljudbok,0 as talbok,1 as musik,0 as film,0 as kartor,0 as noter,0 as tvspel,0 as ovrigt
FROM deleteditems 

LEFT JOIN branches ON (deleteditems.homebranch=branches.branchcode)
LEFT JOIN library_groups ON (deleteditems.homebranch=library_groups.branchcode)

WHERE 
(deleteditems.timestamp BETWEEN <<Datum (Nyårsafton i KB-statistiken)|date>> AND CURDATE())
AND deleteditems.itype IN ('MUSIKCD','MUSCDBARN')
AND deleteditems.homebranch IS NOT NULL AND library_groups.parent_id=<<Kommun|librarygroupsparentid>>) 

UNION ALL

(SELECT deleteditems.homebranch,0 as tryckt,0 as laromedel,0 as ljudbok,0 as talbok,0 as musik,1 as film,0 as kartor,0 as noter,0 as tvspel,0 as ovrigt
FROM deleteditems 

LEFT JOIN branches ON (deleteditems.homebranch=branches.branchcode)
LEFT JOIN library_groups ON (deleteditems.homebranch=library_groups.branchcode)

WHERE 
(deleteditems.timestamp BETWEEN <<Datum (Nyårsafton i KB-statistiken)|date>> AND CURDATE())
AND deleteditems.itype IN ('BLURAY','FILM','VHS')
AND deleteditems.homebranch IS NOT NULL AND library_groups.parent_id=<<Kommun|librarygroupsparentid>>) 

UNION ALL

(SELECT deleteditems.homebranch,0 as tryckt,0 as laromedel,0 as ljudbok,0 as talbok,0 as musik,0 as film,1 as kartor,0 as noter,0 as tvspel,0 as ovrigt
FROM deleteditems 

LEFT JOIN branches ON (deleteditems.homebranch=branches.branchcode)
LEFT JOIN library_groups ON (deleteditems.homebranch=library_groups.branchcode)

WHERE 
(deleteditems.timestamp BETWEEN <<Datum (Nyårsafton i KB-statistiken)|date>> AND CURDATE())
AND deleteditems.itype IN ('KARTOR')
AND deleteditems.homebranch IS NOT NULL AND library_groups.parent_id=<<Kommun|librarygroupsparentid>>) 

UNION ALL

(SELECT deleteditems.homebranch,0 as tryckt,0 as laromedel,0 as ljudbok,0 as talbok,0 as musik,0 as film,0 as kartor,1 as noter,0 as tvspel,0 as ovrigt
FROM deleteditems 

LEFT JOIN branches ON (deleteditems.homebranch=branches.branchcode)
LEFT JOIN library_groups ON (deleteditems.homebranch=library_groups.branchcode)

WHERE 
(deleteditems.timestamp BETWEEN <<Datum (Nyårsafton i KB-statistiken)|date>> AND CURDATE())
AND deleteditems.itype IN ('MUSIK','MUSIKBARN') 
AND deleteditems.homebranch IS NOT NULL AND library_groups.parent_id=<<Kommun|librarygroupsparentid>>) 

UNION ALL

(SELECT deleteditems.homebranch,0 as tryckt,0 as laromedel,0 as ljudbok,0 as talbok,0 as musik,0 as film,0 as kartor,0 as noter,1 as tvspel,0 as ovrigt
FROM deleteditems 

LEFT JOIN branches ON (deleteditems.homebranch=branches.branchcode)
LEFT JOIN library_groups ON (deleteditems.homebranch=library_groups.branchcode)

WHERE 
(deleteditems.timestamp BETWEEN <<Datum (Nyårsafton i KB-statistiken)|date>> AND CURDATE())
AND deleteditems.itype IN ('TV-SPEL')
AND deleteditems.homebranch IS NOT NULL AND library_groups.parent_id=<<Kommun|librarygroupsparentid>>) 

UNION ALL

(SELECT deleteditems.homebranch,0 as tryckt,0 as laromedel,0 as ljudbok,0 as talbok,0 as musik,0 as film,0 as kartor,0 as noter,0 as tvspel,1 as ovrigt
FROM deleteditems 

LEFT JOIN branches ON (deleteditems.homebranch=branches.branchcode)
LEFT JOIN library_groups ON (deleteditems.homebranch=library_groups.branchcode)

WHERE 
(deleteditems.timestamp BETWEEN <<Datum (Nyårsafton i KB-statistiken)|date>> AND CURDATE())
AND deleteditems.itype IN ('ARTIKEL','BLANDAT','BOKPASE','CDROM','FOREMAL','FOREMAL3MD','KONTROLL','SALLSKAPSS','SUFRPLATTA','VECKOLAN','X')
AND deleteditems.homebranch IS NOT NULL AND library_groups.parent_id=<<Kommun|librarygroupsparentid>>) 
) ds
LEFT JOIN branches ON (branches.branchcode=ds.homebranch)
GROUP BY branches.branchname WITH ROLLUP

Fråga 10 Fysiskt nyförvärv - kommunvis

SELECT 
CASE
WHEN branches.branchname IS NULL
THEN '=Summa'
ELSE
branches.branchname
END
AS Bibliotek,
SUM(tryckt) AS 'Nyförvärv tryckt bok',
SUM(laromedel) AS 'Nyförvärv läromedel',
SUM(ljudbok) AS 'Nyförvärv ljudböcker',
SUM(talbok) AS 'Nyförvärv talböcker daisy',
SUM(musik) AS 'Nyförvärv musik',
SUM(film) AS 'Nyförvärv film',
SUM(kartor) AS 'Nyförvärv kartor',
SUM(noter) 'Nyförvärv noter',
SUM(tvspel) 'Nyförvärv Tv-spel',
SUM(ovrigt) AS 'Nyförvärv övrigt'
FROM
(
(SELECT items.homebranch,1 as tryckt,0 as laromedel,0 as ljudbok,0 as talbok,0 as musik,0 as film,0 as kartor,0 as noter,0 as tvspel,0 as ovrigt
FROM items 

LEFT JOIN branches ON (items.homebranch=branches.branchcode)
LEFT JOIN library_groups ON (items.homebranch=library_groups.branchcode)
LEFT JOIN biblio ON (biblio.biblionumber=items.biblionumber)

WHERE 
(biblio.frameworkcode !='BOKP') 
AND items.dateaccessioned BETWEEN <<Datum från|date>>-INTERVAL 1 DAY AND <<Datum till |date>>+INTERVAL 1 DAY 
AND items.itype IN ('BARNBOK','BARNKORT','BOK','BOKCD','BOKCDBARN','BOKDAISY','BOKDAISYBA','BOKMP3','BOKMP3BARN','KORTLON','LANGLAN','PAKET','POCKET','REFERENS','SPRAKKURS','STORSTIL')
AND items.homebranch IS NOT NULL AND library_groups.parent_id=<<Kommun|librarygroupsparentid>>) 

UNION ALL

(SELECT items.homebranch,materials as tryckt,0 as laromedel,0 as ljudbok,0 as talbok,0 as musik,0 as film,0 as kartor,0 as noter,0 as tvspel,0 as ovrigt
FROM items 

LEFT JOIN branches ON (items.homebranch=branches.branchcode)
LEFT JOIN library_groups ON (items.homebranch=library_groups.branchcode)
LEFT JOIN biblio ON (biblio.biblionumber=items.biblionumber)

WHERE 
(biblio.frameworkcode ='BOKP')
AND items.dateaccessioned BETWEEN <<Datum från|date>>-INTERVAL 1 DAY AND <<Datum till |date>>+INTERVAL 1 DAY 
AND items.itype IN ('BARNBOK','BARNKORT','BOK','BOKCD','BOKCDBARN','BOKDAISY','BOKDAISYBA','BOKMP3','BOKMP3BARN','KORTLON','LANGLAN','PAKET','POCKET','REFERENS','SPRAKKURS','STORSTIL')
AND items.homebranch IS NOT NULL AND library_groups.parent_id=<<Kommun|librarygroupsparentid>>) 

UNION ALL

(SELECT items.homebranch,0 as tryckt,1 as laromedel,0 as ljudbok,0 as talbok,0 as musik,0 as film,0 as kartor,0 as noter,0 as tvspel,0 as ovrigt
FROM items 

LEFT JOIN branches ON (items.homebranch=branches.branchcode)
LEFT JOIN library_groups ON (items.homebranch=library_groups.branchcode)

WHERE 
items.dateaccessioned BETWEEN <<Datum från|date>>-INTERVAL 1 DAY AND <<Datum till |date>>+INTERVAL 1 DAY 
AND items.itype IN ('LAROMDL','LAROMTERM')
AND items.homebranch IS NOT NULL AND library_groups.parent_id=<<Kommun|librarygroupsparentid>>) 

UNION ALL

(SELECT items.homebranch,0 as tryckt,0 as laromedel,1 as ljudbok,0 as talbok,0 as musik,0 as film,0 as kartor,0 as noter,0 as tvspel,0 as ovrigt
FROM items 

LEFT JOIN branches ON (items.homebranch=branches.branchcode)
LEFT JOIN library_groups ON (items.homebranch=library_groups.branchcode)

WHERE 
items.dateaccessioned BETWEEN <<Datum från|date>>-INTERVAL 1 DAY AND <<Datum till |date>>+INTERVAL 1 DAY 
AND items.itype IN ('BARN LJUD','BARNMP3','KASSETT','LJUDBOK','MP3')
AND items.homebranch IS NOT NULL AND library_groups.parent_id=<<Kommun|librarygroupsparentid>>) 

UNION ALL

(SELECT items.homebranch,0 as tryckt,0 as laromedel,0 as ljudbok,1 as talbok,0 as musik,0 as film,0 as kartor,0 as noter,0 as tvspel,0 as ovrigt
FROM items 

LEFT JOIN branches ON (items.homebranch=branches.branchcode)
LEFT JOIN library_groups ON (items.homebranch=library_groups.branchcode)

WHERE 
items.dateaccessioned BETWEEN <<Datum från|date>>-INTERVAL 1 DAY AND <<Datum till |date>>+INTERVAL 1 DAY 
AND items.itype IN ('BARNTAL','DAISY','TALBOKKASS')
AND items.homebranch IS NOT NULL AND library_groups.parent_id=<<Kommun|librarygroupsparentid>>) 

UNION ALL

(SELECT items.homebranch,0 as tryckt,0 as laromedel,0 as ljudbok,0 as talbok,1 as musik,0 as film,0 as kartor,0 as noter,0 as tvspel,0 as ovrigt
FROM items 

LEFT JOIN branches ON (items.homebranch=branches.branchcode)
LEFT JOIN library_groups ON (items.homebranch=library_groups.branchcode)

WHERE 
items.dateaccessioned BETWEEN <<Datum från|date>>-INTERVAL 1 DAY AND <<Datum till |date>>+INTERVAL 1 DAY 
AND items.itype IN ('MUSIKCD','MUSCDBARN')
AND items.homebranch IS NOT NULL AND library_groups.parent_id=<<Kommun|librarygroupsparentid>>) 

UNION ALL

(SELECT items.homebranch,0 as tryckt,0 as laromedel,0 as ljudbok,0 as talbok,0 as musik,1 as film,0 as kartor,0 as noter,0 as tvspel,0 as ovrigt
FROM items 

LEFT JOIN branches ON (items.homebranch=branches.branchcode)
LEFT JOIN library_groups ON (items.homebranch=library_groups.branchcode)

WHERE 
items.dateaccessioned BETWEEN <<Datum från|date>>-INTERVAL 1 DAY AND <<Datum till |date>>+INTERVAL 1 DAY 
AND items.itype IN ('BLURAY','FILM','VHS')
AND items.homebranch IS NOT NULL AND library_groups.parent_id=<<Kommun|librarygroupsparentid>>) 

UNION ALL

(SELECT items.homebranch,0 as tryckt,0 as laromedel,0 as ljudbok,0 as talbok,0 as musik,0 as film,1 as kartor,0 as noter,0 as tvspel,0 as ovrigt
FROM items 

LEFT JOIN branches ON (items.homebranch=branches.branchcode)
LEFT JOIN library_groups ON (items.homebranch=library_groups.branchcode)

WHERE 
items.dateaccessioned BETWEEN <<Datum från|date>>-INTERVAL 1 DAY AND <<Datum till |date>>+INTERVAL 1 DAY 
AND items.itype IN ('KARTOR')
AND items.homebranch IS NOT NULL AND library_groups.parent_id=<<Kommun|librarygroupsparentid>>) 

UNION ALL

(SELECT items.homebranch,0 as tryckt,0 as laromedel,0 as ljudbok,0 as talbok,0 as musik,0 as film,0 as kartor,1 as noter,0 as tvspel,0 as ovrigt
FROM items 

LEFT JOIN branches ON (items.homebranch=branches.branchcode)
LEFT JOIN library_groups ON (items.homebranch=library_groups.branchcode)

WHERE 
items.dateaccessioned BETWEEN <<Datum från|date>>-INTERVAL 1 DAY AND <<Datum till |date>>+INTERVAL 1 DAY 
AND items.itype IN ('MUSIK') 
AND items.homebranch IS NOT NULL AND library_groups.parent_id=<<Kommun|librarygroupsparentid>>) 

UNION ALL

(SELECT items.homebranch,0 as tryckt,0 as laromedel,0 as ljudbok,0 as talbok,0 as musik,0 as film,0 as kartor,0 as noter,1 as tvspel,0 as ovrigt
FROM items 

LEFT JOIN branches ON (items.homebranch=branches.branchcode)
LEFT JOIN library_groups ON (items.homebranch=library_groups.branchcode)

WHERE 
items.dateaccessioned BETWEEN <<Datum från|date>>-INTERVAL 1 DAY AND <<Datum till |date>>+INTERVAL 1 DAY 
AND items.itype IN ('TV-SPEL')
AND items.homebranch IS NOT NULL AND library_groups.parent_id=<<Kommun|librarygroupsparentid>>) 

UNION ALL

(SELECT items.homebranch,0 as tryckt,0 as laromedel,0 as ljudbok,0 as talbok,0 as musik,0 as film,0 as kartor,0 as noter,0 as tvspel,1 as ovrigt
FROM items 

LEFT JOIN branches ON (items.homebranch=branches.branchcode)
LEFT JOIN library_groups ON (items.homebranch=library_groups.branchcode)

WHERE 
items.dateaccessioned BETWEEN <<Datum från|date>>-INTERVAL 1 DAY AND <<Datum till |date>>+INTERVAL 1 DAY 
AND items.itype IN ('ARTIKEL','BLANDAT','BOKPASE','CDROM','FOREMAL','FOREMAL3MD','KONTROLL','SUFRPLATTA','VECKOLAN','X')
AND items.homebranch IS NOT NULL AND library_groups.parent_id=<<Kommun|librarygroupsparentid>>) 
) ds
LEFT JOIN branches ON (branches.branchcode=ds.homebranch)
GROUP BY branches.branchname WITH ROLLUP

Fråga 10, tidskrifter - kommunvis

SELECT biblio.title AS Titel ,GROUP_CONCAT(DISTINCT branches.branchname SEPARATOR '<br>') AS 'Bibliotek',COUNT(serialid) AS 'Antal mottagna nummer',GROUP_CONCAT(DISTINCT 
subscriptionhistory.histstartdate SEPARATOR '<br>') AS Startdatum,
CASE 
WHEN subscriptionhistory.histstartdate > <<Datum från|date>> 
THEN
'Ny'
ELSE
''
END
AS 'Ny?'

FROM subscription
LEFT JOIN biblio ON (biblio.biblionumber=subscription.biblionumber)
LEFT JOIN serial on (serial.subscriptionid=subscription.subscriptionid)
LEFT JOIN library_groups ON (subscription.branchcode=library_groups.branchcode)
LEFT JOIN branches ON (branches.branchcode=subscription.branchcode)
LEFT JOIN subscription_frequencies ON (subscription_frequencies.id=subscription.periodicity)
LEFT JOIN subscriptionhistory ON (subscriptionhistory.subscriptionid=subscription.subscriptionid)

 

WHERE serial.status='2' 
AND subscription_frequencies.id NOT IN ('4')
AND serial.publisheddate BETWEEN <<Datum från|date>>-INTERVAL 1 DAY AND <<Datum till |date>>+INTERVAL 1 DAY
AND library_groups.parent_id=<<Kommun|librarygroupsparentid>> 

GROUP BY subscription.biblionumber
HAVING COUNT(serialid)>1 

ORDER BY biblio.title

Fråga 11 olika bestånd - kommunvis

KB-statistiken definierar skönlitteratur som det som har hyllsignum H med underavdelningar. Eftersom ungdomslitteratur har hyllsignum uHc, uHce et cetera så har jag lagt både höger- och vänstertrunkering för H. Tyvärr gör det att man får med även signum som Lz Hansson, Qz Hennes & Mauritz - så den som vill vara noggrann får gärna fixa det 

SELECT 
CASE WHEN 
branches.branchname IS NULL
THEN '=Summa'
ELSE
branches.branchname 
END 
AS Bibliotek,

SUM(barn) AS 'Bestånd för barn och unga',
SUM(skon) AS 'Bestånd av skönlitteratur',
SUM(referensexemplar) AS'Antal referensexemplar',
SUM(lasnedsattning) AS 'Bestånd läsnedsättning'


FROM
(
(SELECT items.homebranch, 1 as barn, 0 as skon, 0 as referensexemplar, 0 as lasnedsattning
FROM items 

LEFT JOIN library_groups ON (items.homebranch=library_groups.branchcode)

WHERE items.homebranch IS NOT NULL AND library_groups.parent_id=<<Kommun|librarygroupsparentid>> 
AND items.itype IN ('BARN TIDSK','BARNBOK','BOKCDBARN','BOKDAISYBA','BOKMP3BARN','BARN LJUD','BARNMP3','BARNTAL'))

UNION ALL 

(SELECT items.homebranch, 0 as barn, 1 as skon, 0 as referensexemplar, 0 as lasnedsattning
FROM items 

LEFT JOIN library_groups ON (items.homebranch=library_groups.branchcode)

WHERE items.homebranch IS NOT NULL AND library_groups.parent_id=<<Kommun|librarygroupsparentid>> 
AND itemcallnumber LIKE BINARY '%H%' 
AND items.itype IN ('BARN TIDSK','BARNBOK','BOK','LANGLAN','POCKET','TIDSKRIFT'))

UNION ALL 

(SELECT items.homebranch, 0 as barn, 0 as skon, 1 as referensexemplar, 0 as lasnedsattning
FROM items 

LEFT JOIN library_groups ON (items.homebranch=library_groups.branchcode)

WHERE items.homebranch IS NOT NULL AND library_groups.parent_id=<<Kommun|librarygroupsparentid>> 
AND items.location IN ('REFERENS'))

UNION ALL

(SELECT items.homebranch, 0 as barn, 0 as skon, 0 as referensexemplar, 1 as lasnedsattning
FROM items 

LEFT JOIN library_groups ON (items.homebranch=library_groups.branchcode)

WHERE items.homebranch IS NOT NULL AND library_groups.parent_id=<<Kommun|librarygroupsparentid>> AND items.itype IN ('BARNTAL','BOKCD','BOKCDBARN','BOKDAISYBA','BOKMP3','BOKMP3BARN','DAISY','STORSTIL','TALBOKKASS'))

UNION ALL 

(SELECT items.homebranch, -1 as barn, 0 as skon, 0 as referensexemplar, 0 as lasnedsattning
FROM items 

LEFT JOIN library_groups ON (items.homebranch=library_groups.branchcode)

WHERE (items.dateaccessioned BETWEEN <<Datum (Nyårsafton i KB-statistiken)|date>> AND CURDATE()) 
AND items.homebranch IS NOT NULL AND library_groups.parent_id=<<Kommun|librarygroupsparentid>> 
AND items.itype IN ('BARN TIDSK','BARNBOK','BARNKORT','BOKCDBARN','BOKDAISYBA','BOKMP3BARN','BARN LJUD','BARNMP3','BARNTAL'))

UNION ALL 

(SELECT items.homebranch, 0 as barn, -1 as skon, 0 as referensexemplar, 0 as lasnedsattning
FROM items 

LEFT JOIN library_groups ON (items.homebranch=library_groups.branchcode)

WHERE (items.dateaccessioned BETWEEN <<Datum (Nyårsafton i KB-statistiken)|date>> AND CURDATE()) 
AND items.homebranch IS NOT NULL AND library_groups.parent_id=<<Kommun|librarygroupsparentid>> 
AND itemcallnumber LIKE BINARY '%H%' 
AND items.itype IN ('BARN TIDSK','BARNBOK','BARNKORT','BOK','KORTLON','LANGLAN','POCKET','TIDSKRIFT'))

UNION ALL 

(SELECT items.homebranch, 0 as barn, 0 as skon, -1 as referensexemplar, 0 as lasnedsattning
FROM items 

LEFT JOIN library_groups ON (items.homebranch=library_groups.branchcode)

WHERE (items.dateaccessioned BETWEEN <<Datum (Nyårsafton i KB-statistiken)|date>> AND CURDATE()) 
AND items.homebranch IS NOT NULL AND library_groups.parent_id=<<Kommun|librarygroupsparentid>> 
AND items.location IN ('REFERENS'))

UNION ALL

(SELECT items.homebranch, 0 as barn, 0 as skon, 0 as referensexemplar, -1 as lasnedsattning
FROM items 

LEFT JOIN library_groups ON (items.homebranch=library_groups.branchcode)

WHERE (items.dateaccessioned BETWEEN <<Datum (Nyårsafton i KB-statistiken)|date>> AND CURDATE()) 
AND items.homebranch IS NOT NULL AND library_groups.parent_id=<<Kommun|librarygroupsparentid>> AND items.itype IN ('BARNTAL','BOKCD','BOKCDBARN','BOKDAISYBA','BOKMP3','BOKMP3BARN','DAISY','STORSTIL','TALBOKKASS'))

UNION ALL 

(SELECT deleteditems.homebranch, 1 as barn, 0 as skon, 0 as referensexemplar, 0 as lasnedsattning
FROM deleteditems 

LEFT JOIN library_groups ON (deleteditems.homebranch=library_groups.branchcode)

WHERE (deleteditems.timestamp BETWEEN <<Datum (Nyårsafton i KB-statistiken)|date>> AND CURDATE())
AND deleteditems.homebranch IS NOT NULL AND library_groups.parent_id=<<Kommun|librarygroupsparentid>> 
AND deleteditems.itype IN ('BARN TIDSK','BARNBOK','BARNKORT','BOKCDBARN','BOKDAISYBA','BOKMP3BARN','BARN LJUD','BARNMP3','BARNTAL'))

UNION ALL 

(SELECT deleteditems.homebranch, 0 as barn, 1 as skon, 0 as referensexemplar, 0 as lasnedsattning
FROM deleteditems 

LEFT JOIN library_groups ON (deleteditems.homebranch=library_groups.branchcode)

WHERE (deleteditems.timestamp BETWEEN <<Datum (Nyårsafton i KB-statistiken)|date>> AND CURDATE())
AND deleteditems.homebranch IS NOT NULL AND library_groups.parent_id=<<Kommun|librarygroupsparentid>> 
AND deleteditems.itemcallnumber LIKE BINARY '%H%' 
AND deleteditems.itype IN ('BARN TIDSK','BARNBOK','BARNKORT','BOK','KORTLON','LANGLAN','POCKET','TIDSKRIFT'))

UNION ALL 

(SELECT deleteditems.homebranch, 0 as barn, 0 as skon, 1 as referensexemplar, 0 as lasnedsattning
FROM deleteditems 

LEFT JOIN library_groups ON (deleteditems.homebranch=library_groups.branchcode)

WHERE (deleteditems.timestamp BETWEEN <<Datum (Nyårsafton i KB-statistiken)|date>> AND CURDATE())
AND deleteditems.homebranch IS NOT NULL AND library_groups.parent_id=<<Kommun|librarygroupsparentid>> 
AND deleteditems.location IN ('REFERENS'))

UNION ALL

(SELECT deleteditems.homebranch, 0 as barn, 0 as skon, 0 as referensexemplar, 1 as lasnedsattning
FROM deleteditems 

LEFT JOIN library_groups ON (deleteditems.homebranch=library_groups.branchcode)

WHERE (deleteditems.timestamp BETWEEN <<Datum (Nyårsafton i KB-statistiken)|date>> AND CURDATE())
AND deleteditems.homebranch IS NOT NULL AND library_groups.parent_id=<<Kommun|librarygroupsparentid>> AND deleteditems.itype IN ('BARNTAL','BOKCD','BOKCDBARN','BOKDAISYBA','BOKMP3','BOKMP3BARN','DAISY','STORSTIL','TALBOKKASS'))

) ds

LEFT JOIN branches ON (branches.branchcode=ds.homebranch)

GROUP BY branches.branchname WITH ROLLUP

Fråga 11 Olika nyförvärv - kommunvis

SELECT 
CASE 
WHEN 
branchname IS NULL
THEN '=Summa'
ELSE
branchname
END
AS Bibliotek,
  COUNT(CASE WHEN itype IN ('BARN TIDSK','BARNBOK','BOKCDBARN','BOKDAISYBA','BOKMP3BARN') THEN 1 END) 'Nyförvärv tryckt för barn'
  
FROM items

LEFT JOIN branches ON (items.homebranch=branches.branchcode)
LEFT JOIN library_groups ON (items.homebranch=library_groups.branchcode)
LEFT JOIN library_groups kommun ON (library_groups.parent_id=kommun.id)

WHERE items.homebranch IS NOT NULL AND items.dateaccessioned BETWEEN <<Datum från|date>>-INTERVAL 1 DAY AND <<Datum till |date>>+INTERVAL 1 DAY AND library_groups.parent_id=<<Kommun|librarygroupsparentid>>


GROUP BY branchname WITH ROLLUP

Fråga 11 Olika utlån - kommunvis

SELECT 
CASE 
WHEN branchname IS NULL
THEN '=Summa'
ELSE
branchname
END
AS Bibliotek, SUM(barn) AS 'Utlån barn', SUM (lasned) AS 'Utlån läsnedsättning'
FROM
((SELECT branch, 1 as barn, 0 as lasned

FROM statistics
LEFT JOIN library_groups ON (statistics.branch=library_groups.branchcode)

WHERE type IN('issue','renew') 
AND itemtype IN ('BARNBOK','BARNKORT','BARN TIDSK','BOKCDBARN','BOKDAISYBA','BOKMP3BARN','BARN LJUD','BARNMP3','BARNTAL','MUSCDBARN')
AND statistics.branch IS NOT NULL 
AND statistics.itemtype IS NOT NULL 
AND statistics.datetime BETWEEN <<Datum från|date>>-INTERVAL 1 DAY AND <<Datum till |date>>+INTERVAL 1 DAY 
AND library_groups.parent_id=<<Kommun|librarygroupsparentid>>) 

UNION ALL 

(SELECT branch, 0 as barn, 1 as lasned

FROM statistics
LEFT JOIN library_groups ON (statistics.branch=library_groups.branchcode)

WHERE type IN('issue','renew') 
AND itemtype IN ('BARNTAL','BOKCD','BOKCDBARN','BOKDAISYBA','BOKMP3','BOKMP3BARN','DAISY','STORSTIL','TALBOKKASS')
AND statistics.branch IS NOT NULL 
AND statistics.itemtype IS NOT NULL 
AND statistics.datetime BETWEEN <<Datum från|date>>-INTERVAL 1 DAY AND <<Datum till |date>>+INTERVAL 1 DAY 
AND library_groups.parent_id=<<Kommun|librarygroupsparentid>>) 

UNION ALL 

(SELECT branch, 1 as barn, 0 as lasned

FROM statistics
LEFT JOIN library_groups ON (statistics.branch=library_groups.branchcode)

WHERE type IN('issue','renew') 
AND location IN ('Lattlast','Appelhyllan')
AND statistics.branch IS NOT NULL 
AND statistics.itemtype IS NOT NULL 
AND statistics.datetime BETWEEN <<Datum från|date>>-INTERVAL 1 DAY AND <<Datum till |date>>+INTERVAL 1 DAY 
AND library_groups.parent_id=<<Kommun|librarygroupsparentid>>) 

)ds

LEFT JOIN branches ON (branches.branchcode=ds.branch)
GROUP BY branchname WITH ROLLUP

Fråga 12 Frågor om språk - kommunvis

SELECT 
CASE 
WHEN substring( ExtractValue(metadata,'//controlfield[@tag="008"]'), 36,3 )='swe' THEN '1 Svenska'
WHEN substring( ExtractValue(metadata,'//controlfield[@tag="008"]'), 36,3 )='' THEN '1 Svenska'
WHEN substring( ExtractValue(metadata,'//controlfield[@tag="008"]'), 36,3 )='fin' THEN '2 Minoritets'
WHEN substring( ExtractValue(metadata,'//controlfield[@tag="008"]'), 36,3 )='9mk' THEN '2 Minoritets'
WHEN substring( ExtractValue(metadata,'//controlfield[@tag="008"]'), 36,3 )='smi' THEN '2 Minoritets'
WHEN substring( ExtractValue(metadata,'//controlfield[@tag="008"]'), 36,3 )='smj' THEN '2 Minoritets'
WHEN substring( ExtractValue(metadata,'//controlfield[@tag="008"]'), 36,3 )='sme' THEN '2 Minoritets'
WHEN substring( ExtractValue(metadata,'//controlfield[@tag="008"]'), 36,3 )='sms' THEN '2 Minoritets'
WHEN substring( ExtractValue(metadata,'//controlfield[@tag="008"]'), 36,3 )='sma' THEN '2 Minoritets'
WHEN substring( ExtractValue(metadata,'//controlfield[@tag="008"]'), 36,3 )='rom' THEN '2 Minoritets'
WHEN substring( ExtractValue(metadata,'//controlfield[@tag="008"]'), 36,3 )='yid' THEN '2 Minoritets'
ELSE '3 Utländska'
END
AS 'Språk',
COUNT(*) AS 'Antal titlar'
    
FROM biblio_metadata 

WHERE
biblio_metadata.biblionumber
IN
(SELECT items.biblionumber

FROM items
LEFT JOIN library_groups ON (items.homebranch=library_groups.branchcode)

WHERE items.homebranch IS NOT NULL 
AND library_groups.parent_id=<<Kommun|librarygroupsparentid>>) 

GROUP BY 1

Fråga 14 Lånestatistik - kommunvis

SELECT 
CASE 
WHEN branchname IS NULL
THEN 
'=Summa'
ELSE 
branchname
END
AS Bibliotek, 
SUM(utltryckt) AS 'Utlån tryckt bok',SUM(omltryckt) AS 'Omlån tryckt bok',
SUM(utllarom) AS 'Utlån läromedel',SUM(omllarom) AS 'Omlån läromedel',
SUM(utlljudbok) AS 'Utlån ljudböcker',SUM(omlljudbok) AS 'Omlån ljudböcker', 
SUM(utltalbok) AS 'Utlån talböcker daisy',SUM(omltalbok) AS 'Omlån talböcker daisy', 
SUM(utltskr) AS 'Utlån tidskrifter',SUM(omltskr) AS 'Omlån tidskrifter', 
SUM(utlmusik) AS 'Utlån musik',SUM(omlmusik) AS 'Omlån musik',
SUM(utlfilm) AS 'Utlån film',SUM(omlfilm) AS 'Omlån film',
SUM(utlkartor) AS 'Utlån kartor',SUM(omlkartor) AS 'Omlån kartor',
SUM(utlnoter) AS 'Utlån noter',SUM(omlnoter) AS 'Omlån noter',
SUM(utlinterakt) AS 'Utlån Interaktiva medier',SUM(omlinterakt) AS 'Omlån Interaktiva medier',
SUM(utlovr) AS 'Utlån övrigt',SUM(omlovr) AS 'Omlån övrigt'

FROM
( 
(SELECT branch, 
1 as utltryckt, 0 as omltryckt, 
0 as utllarom, 0 as omllarom, 
0 as utlljudbok, 0 as omlljudbok, 
0 as utltalbok, 0 as omltalbok, 
0 as utltskr, 0 as omltskr, 
0 as utlmusik, 0 as omlmusik,
0 as utlfilm, 0 as omlfilm,
0 as utlkartor, 0 as omlkartor,
0 as utlnoter, 0 as omlnoter,
0 as utlinterakt, 0 as omlinterakt,
0 as utlovr, 0 as omlovr

  FROM statistics 
  LEFT JOIN items ON (statistics.itemnumber=items.itemnumber)
  LEFT JOIN biblio ON (items.biblionumber=biblio.biblionumber)
  LEFT JOIN library_groups ON (statistics.branch=library_groups.branchcode)
  
  WHERE type = 'issue' 
  AND itemtype IN ('BARNBOK','BOK','BOKCD','BOKCDBARN','BOKDAISY','BOKDAISYBA','BOKMP3','BOKMP3BARN','BOKPASE','LANGLAN','PAKET','POCKET','REFERENS','SPRAKKURS','STORSTIL')  
  AND frameworkcode !='BOKP'
  AND statistics.branch IS NOT NULL 
  AND statistics.itemtype IS NOT NULL 
  AND statistics.datetime BETWEEN <<Datum från|date>>-INTERVAL 1 DAY AND <<Datum till |date>>+INTERVAL 1 DAY 
  AND library_groups.parent_id=<<Kommun|librarygroupsparentid>>)
  
  UNION ALL 
  
  (SELECT branch, 
materials as utltryckt, 0 as omltryckt, 
0 as utllarom, 0 as omllarom, 
0 as utlljudbok, 0 as omlljudbok, 
0 as utltalbok, 0 as omltalbok, 
0 as utltskr, 0 as omltskr, 
0 as utlmusik, 0 as omlmusik,
0 as utlfilm, 0 as omlfilm,
0 as utlkartor, 0 as omlkartor,
0 as utlnoter, 0 as omlnoter,
0 as utlinterakt, 0 as omlinterakt,
0 as utlovr, 0 as omlovr

  FROM statistics 
  LEFT JOIN items ON (statistics.itemnumber=items.itemnumber)
  LEFT JOIN biblio ON (items.biblionumber=biblio.biblionumber)
  LEFT JOIN library_groups ON (statistics.branch=library_groups.branchcode)
  
  WHERE type = 'issue' 
  AND itemtype IN ('BARNBOK','BOK','BOKCD','BOKCDBARN','BOKDAISY','BOKDAISYBA','BOKMP3','BOKMP3BARN','BOKPASE','LANGLAN','PAKET','POCKET','REFERENS','SPRAKKURS','STORSTIL')  
  AND frameworkcode ='BOKP'
  AND statistics.branch IS NOT NULL 
  AND statistics.itemtype IS NOT NULL 
  AND statistics.datetime BETWEEN <<Datum från|date>>-INTERVAL 1 DAY AND <<Datum till |date>>+INTERVAL 1 DAY 
  AND library_groups.parent_id=<<Kommun|librarygroupsparentid>>) 
  
  UNION ALL 
  
  (SELECT branch, 
0 as utltryckt, 0 as omltryckt, 
1 as utllarom, 0 as omllarom, 
0 as utlljudbok, 0 as omlljudbok, 
0 as utltalbok, 0 as omltalbok, 
0 as utltskr, 0 as omltskr, 
0 as utlmusik, 0 as omlmusik,
0 as utlfilm, 0 as omlfilm,
0 as utlkartor, 0 as omlkartor,
0 as utlnoter, 0 as omlnoter,
0 as utlinterakt, 0 as omlinterakt,
0 as utlovr, 0 as omlovr

  FROM statistics 
  LEFT JOIN items ON (statistics.itemnumber=items.itemnumber)
  LEFT JOIN biblio ON (items.biblionumber=biblio.biblionumber)
  LEFT JOIN library_groups ON (statistics.branch=library_groups.branchcode)
  
  WHERE type = 'issue' 
  AND itemtype IN ('LAROMDL','LAROMTERM')  
  AND statistics.branch IS NOT NULL 
  AND statistics.itemtype IS NOT NULL 
  AND statistics.datetime BETWEEN <<Datum från|date>>-INTERVAL 1 DAY AND <<Datum till |date>>+INTERVAL 1 DAY 
  AND library_groups.parent_id=<<Kommun|librarygroupsparentid>>)
  
  UNION ALL 
  
  (SELECT branch, 
0 as utltryckt, 0 as omltryckt, 
0 as utllarom, 0 as omllarom, 
1 as utlljudbok, 0 as omlljudbok, 
0 as utltalbok, 0 as omltalbok, 
0 as utltskr, 0 as omltskr, 
0 as utlmusik, 0 as omlmusik,
0 as utlfilm, 0 as omlfilm,
0 as utlkartor, 0 as omlkartor,
0 as utlnoter, 0 as omlnoter,
0 as utlinterakt, 0 as omlinterakt,
0 as utlovr, 0 as omlovr

  FROM statistics 
  LEFT JOIN items ON (statistics.itemnumber=items.itemnumber)
  LEFT JOIN biblio ON (items.biblionumber=biblio.biblionumber)
  LEFT JOIN library_groups ON (statistics.branch=library_groups.branchcode)
  
  WHERE type = 'issue' 
  AND itemtype IN ('BARN LJUD','BARNMP3','KASSETT','LJUDBOK','MP3')
  AND statistics.branch IS NOT NULL 
  AND statistics.itemtype IS NOT NULL 
  AND statistics.datetime BETWEEN <<Datum från|date>>-INTERVAL 1 DAY AND <<Datum till |date>>+INTERVAL 1 DAY 
  AND library_groups.parent_id=<<Kommun|librarygroupsparentid>>)
  
  UNION ALL 
  
  (SELECT branch, 
0 as utltryckt, 0 as omltryckt, 
0 as utllarom, 0 as omllarom, 
0 as utlljudbok, 0 as omlljudbok, 
1 as utltalbok, 0 as omltalbok, 
0 as utltskr, 0 as omltskr, 
0 as utlmusik, 0 as omlmusik,
0 as utlfilm, 0 as omlfilm,
0 as utlkartor, 0 as omlkartor,
0 as utlnoter, 0 as omlnoter,
0 as utlinterakt, 0 as omlinterakt,
0 as utlovr, 0 as omlovr

  FROM statistics 
  LEFT JOIN items ON (statistics.itemnumber=items.itemnumber)
  LEFT JOIN biblio ON (items.biblionumber=biblio.biblionumber)
  LEFT JOIN library_groups ON (statistics.branch=library_groups.branchcode)
  
  WHERE type = 'issue' 
  AND itemtype IN ('BARNTAL','DAISY','TALBOKKASS')
  AND statistics.branch IS NOT NULL 
  AND statistics.itemtype IS NOT NULL 
  AND statistics.datetime BETWEEN <<Datum från|date>>-INTERVAL 1 DAY AND <<Datum till |date>>+INTERVAL 1 DAY 
  AND library_groups.parent_id=<<Kommun|librarygroupsparentid>>)
  
  UNION ALL 
  
  (SELECT branch, 
0 as utltryckt, 0 as omltryckt, 
0 as utllarom, 0 as omllarom, 
0 as utlljudbok, 0 as omlljudbok, 
0 as utltalbok, 0 as omltalbok, 
1 as utltskr, 0 as omltskr, 
0 as utlmusik, 0 as omlmusik,
0 as utlfilm, 0 as omlfilm,
0 as utlkartor, 0 as omlkartor,
0 as utlnoter, 0 as omlnoter,
0 as utlinterakt, 0 as omlinterakt,
0 as utlovr, 0 as omlovr

  FROM statistics 
  LEFT JOIN items ON (statistics.itemnumber=items.itemnumber)
  LEFT JOIN biblio ON (items.biblionumber=biblio.biblionumber)
  LEFT JOIN library_groups ON (statistics.branch=library_groups.branchcode)
  
  WHERE type = 'issue' 
  AND itemtype IN ('BARN TIDSK','TIDSKRIFT')
  AND statistics.branch IS NOT NULL 
  AND statistics.itemtype IS NOT NULL 
  AND statistics.datetime BETWEEN <<Datum från|date>>-INTERVAL 1 DAY AND <<Datum till |date>>+INTERVAL 1 DAY 
  AND library_groups.parent_id=<<Kommun|librarygroupsparentid>>)
  
  UNION ALL 
  
  (SELECT branch, 
0 as utltryckt, 0 as omltryckt, 
0 as utllarom, 0 as omllarom, 
0 as utlljudbok, 0 as omlljudbok, 
0 as utltalbok, 0 as omltalbok, 
0 as utltskr, 0 as omltskr, 
1 as utlmusik, 0 as omlmusik,
0 as utlfilm, 0 as omlfilm,
0 as utlkartor, 0 as omlkartor,
0 as utlnoter, 0 as omlnoter,
0 as utlinterakt, 0 as omlinterakt,
0 as utlovr, 0 as omlovr

  FROM statistics 
  LEFT JOIN items ON (statistics.itemnumber=items.itemnumber)
  LEFT JOIN biblio ON (items.biblionumber=biblio.biblionumber)
  LEFT JOIN library_groups ON (statistics.branch=library_groups.branchcode)
  
  WHERE type = 'issue' 
  AND itemtype IN ('MUSIKCD','MUSCDBARN')
  AND statistics.branch IS NOT NULL 
  AND statistics.itemtype IS NOT NULL 
  AND statistics.datetime BETWEEN <<Datum från|date>>-INTERVAL 1 DAY AND <<Datum till |date>>+INTERVAL 1 DAY 
  AND library_groups.parent_id=<<Kommun|librarygroupsparentid>>)
  
  UNION ALL 
  
  (SELECT branch, 
0 as utltryckt, 0 as omltryckt, 
0 as utllarom, 0 as omllarom, 
0 as utlljudbok, 0 as omlljudbok, 
0 as utltalbok, 0 as omltalbok, 
0 as utltskr, 0 as omltskr, 
0 as utlmusik, 0 as omlmusik,
1 as utlfilm, 0 as omlfilm,
0 as utlkartor, 0 as omlkartor,
0 as utlnoter, 0 as omlnoter,
0 as utlinterakt, 0 as omlinterakt,
0 as utlovr, 0 as omlovr

  FROM statistics 
  LEFT JOIN items ON (statistics.itemnumber=items.itemnumber)
  LEFT JOIN biblio ON (items.biblionumber=biblio.biblionumber)
  LEFT JOIN library_groups ON (statistics.branch=library_groups.branchcode)
  
  WHERE type = 'issue' 
  AND itemtype IN ('BLURAY','FILM','VHS')
  AND statistics.branch IS NOT NULL 
  AND statistics.itemtype IS NOT NULL 
  AND statistics.datetime BETWEEN <<Datum från|date>>-INTERVAL 1 DAY AND <<Datum till |date>>+INTERVAL 1 DAY 
  AND library_groups.parent_id=<<Kommun|librarygroupsparentid>>)
  
  UNION ALL 
  
  (SELECT branch, 
0 as utltryckt, 0 as omltryckt, 
0 as utllarom, 0 as omllarom, 
0 as utlljudbok, 0 as omlljudbok, 
0 as utltalbok, 0 as omltalbok, 
0 as utltskr, 0 as omltskr, 
0 as utlmusik, 0 as omlmusik,
0 as utlfilm, 0 as omlfilm,
1 as utlkartor, 0 as omlkartor,
0 as utlnoter, 0 as omlnoter,
0 as utlinterakt, 0 as omlinterakt,
0 as utlovr, 0 as omlovr

  FROM statistics 
  LEFT JOIN items ON (statistics.itemnumber=items.itemnumber)
  LEFT JOIN biblio ON (items.biblionumber=biblio.biblionumber)
  LEFT JOIN library_groups ON (statistics.branch=library_groups.branchcode)
  
  WHERE type = 'issue' 
  AND itemtype IN ('KARTOR')
  AND statistics.branch IS NOT NULL 
  AND statistics.itemtype IS NOT NULL 
  AND statistics.datetime BETWEEN <<Datum från|date>>-INTERVAL 1 DAY AND <<Datum till |date>>+INTERVAL 1 DAY 
  AND library_groups.parent_id=<<Kommun|librarygroupsparentid>>)
  
  UNION ALL 
  
  (SELECT branch, 
0 as utltryckt, 0 as omltryckt, 
0 as utllarom, 0 as omllarom, 
0 as utlljudbok, 0 as omlljudbok, 
0 as utltalbok, 0 as omltalbok, 
0 as utltskr, 0 as omltskr, 
0 as utlmusik, 0 as omlmusik,
0 as utlfilm, 0 as omlfilm,
0 as utlkartor, 0 as omlkartor,
1 as utlnoter, 0 as omlnoter,
0 as utlinterakt, 0 as omlinterakt,
0 as utlovr, 0 as omlovr

  FROM statistics 
  LEFT JOIN items ON (statistics.itemnumber=items.itemnumber)
  LEFT JOIN biblio ON (items.biblionumber=biblio.biblionumber)
  LEFT JOIN library_groups ON (statistics.branch=library_groups.branchcode)
  
  WHERE type = 'issue' 
  AND itemtype IN ('MUSIK','MUSIKBARN')
  AND statistics.branch IS NOT NULL 
  AND statistics.itemtype IS NOT NULL 
  AND statistics.datetime BETWEEN <<Datum från|date>>-INTERVAL 1 DAY AND <<Datum till |date>>+INTERVAL 1 DAY 
  AND library_groups.parent_id=<<Kommun|librarygroupsparentid>>)
  
  UNION ALL 
  
  (SELECT branch, 
0 as utltryckt, 0 as omltryckt, 
0 as utllarom, 0 as omllarom, 
0 as utlljudbok, 0 as omlljudbok, 
0 as utltalbok, 0 as omltalbok, 
0 as utltskr, 0 as omltskr, 
0 as utlmusik, 0 as omlmusik,
0 as utlfilm, 0 as omlfilm,
0 as utlkartor, 0 as omlkartor,
0 as utlnoter, 0 as omlnoter,
1 as utlinterakt, 0 as omlinterakt,
0 as utlovr, 0 as omlovr

  FROM statistics 
  LEFT JOIN items ON (statistics.itemnumber=items.itemnumber)
  LEFT JOIN biblio ON (items.biblionumber=biblio.biblionumber)
  LEFT JOIN library_groups ON (statistics.branch=library_groups.branchcode)
  
  WHERE type = 'issue' 
  AND itemtype IN ('CDROM','TV-SPEL')
  AND statistics.branch IS NOT NULL 
  AND statistics.itemtype IS NOT NULL 
  AND statistics.datetime BETWEEN <<Datum från|date>>-INTERVAL 1 DAY AND <<Datum till |date>>+INTERVAL 1 DAY 
  AND library_groups.parent_id=<<Kommun|librarygroupsparentid>>)
  
  UNION ALL 
  
  (SELECT branch, 
0 as utltryckt, 0 as omltryckt, 
0 as utllarom, 0 as omllarom, 
0 as utlljudbok, 0 as omlljudbok, 
0 as utltalbok, 0 as omltalbok, 
0 as utltskr, 0 as omltskr, 
0 as utlmusik, 0 as omlmusik,
0 as utlfilm, 0 as omlfilm,
0 as utlkartor, 0 as omlkartor,
0 as utlnoter, 0 as omlnoter,
0 as utlinterakt, 0 as omlinterakt,
1 as utlovr, 0 as omlovr

  FROM statistics 
  LEFT JOIN items ON (statistics.itemnumber=items.itemnumber)
  LEFT JOIN biblio ON (items.biblionumber=biblio.biblionumber)
  LEFT JOIN library_groups ON (statistics.branch=library_groups.branchcode)
  
  WHERE type = 'issue' 
  AND itemtype IN ('BLANDAT','FOREMAL','FOREMAL3MD','KONTROLL','SALLSKAPSS','SUFRPLATTA','VECKOLAN','X')
  AND statistics.branch IS NOT NULL 
  AND statistics.itemtype IS NOT NULL 
  AND statistics.datetime BETWEEN <<Datum från|date>>-INTERVAL 1 DAY AND <<Datum till |date>>+INTERVAL 1 DAY 
  AND library_groups.parent_id=<<Kommun|librarygroupsparentid>>)
  
  UNION ALL
  
  (SELECT branch, 
0 as utltryckt, 1 as omltryckt, 
0 as utllarom, 0 as omllarom, 
0 as utlljudbok, 0 as omlljudbok, 
0 as utltalbok, 0 as omltalbok, 
0 as utltskr, 0 as omltskr, 
0 as utlmusik, 0 as omlmusik,
0 as utlfilm, 0 as omlfilm,
0 as utlkartor, 0 as omlkartor,
0 as utlnoter, 0 as omlnoter,
0 as utlinterakt, 0 as omlinterakt,
0 as utlovr, 0 as omlovr

  FROM statistics 
  LEFT JOIN items ON (statistics.itemnumber=items.itemnumber)
  LEFT JOIN biblio ON (items.biblionumber=biblio.biblionumber)
  LEFT JOIN library_groups ON (statistics.branch=library_groups.branchcode)
  
  WHERE type = 'renew' 
  AND itemtype IN ('BARNBOK','BOK','BOKCD','BOKCDBARN','BOKDAISY','BOKDAISYBA','BOKMP3','BOKMP3BARN','BOKPASE','LANGLAN','PAKET','POCKET','REFERENS','SPRAKKURS','STORSTIL')  
  AND frameworkcode !='BOKP'
  AND statistics.branch IS NOT NULL 
  AND statistics.itemtype IS NOT NULL 
  AND statistics.datetime BETWEEN <<Datum från|date>>-INTERVAL 1 DAY AND <<Datum till |date>>+INTERVAL 1 DAY 
  AND library_groups.parent_id=<<Kommun|librarygroupsparentid>>)
  
  UNION ALL 
  
  (SELECT branch, 
0 as utltryckt, materials as omltryckt, 
0 as utllarom, 0 as omllarom, 
0 as utlljudbok, 0 as omlljudbok, 
0 as utltalbok, 0 as omltalbok, 
0 as utltskr, 0 as omltskr, 
0 as utlmusik, 0 as omlmusik,
0 as utlfilm, 0 as omlfilm,
0 as utlkartor, 0 as omlkartor,
0 as utlnoter, 0 as omlnoter,
0 as utlinterakt, 0 as omlinterakt,
0 as utlovr, 0 as omlovr

  FROM statistics 
  LEFT JOIN items ON (statistics.itemnumber=items.itemnumber)
  LEFT JOIN biblio ON (items.biblionumber=biblio.biblionumber)
  LEFT JOIN library_groups ON (statistics.branch=library_groups.branchcode)
  
  WHERE type = 'renew' 
  AND itemtype IN ('BARNBOK','BOK','BOKCD','BOKCDBARN','BOKDAISY','BOKDAISYBA','BOKMP3','BOKMP3BARN','BOKPASE','LANGLAN','PAKET','POCKET','REFERENS','SPRAKKURS','STORSTIL')  
  AND frameworkcode ='BOKP'
  AND statistics.branch IS NOT NULL 
  AND statistics.itemtype IS NOT NULL 
  AND statistics.datetime BETWEEN <<Datum från|date>>-INTERVAL 1 DAY AND <<Datum till |date>>+INTERVAL 1 DAY 
  AND library_groups.parent_id=<<Kommun|librarygroupsparentid>>) 
  
  UNION ALL 
  
  (SELECT branch, 
0 as utltryckt, 0 as omltryckt, 
0 as utllarom, 1 as omllarom, 
0 as utlljudbok, 0 as omlljudbok, 
0 as utltalbok, 0 as omltalbok, 
0 as utltskr, 0 as omltskr, 
0 as utlmusik, 0 as omlmusik,
0 as utlfilm, 0 as omlfilm,
0 as utlkartor, 0 as omlkartor,
0 as utlnoter, 0 as omlnoter,
0 as utlinterakt, 0 as omlinterakt,
0 as utlovr, 0 as omlovr

  FROM statistics 
  LEFT JOIN items ON (statistics.itemnumber=items.itemnumber)
  LEFT JOIN biblio ON (items.biblionumber=biblio.biblionumber)
  LEFT JOIN library_groups ON (statistics.branch=library_groups.branchcode)
  
  WHERE type = 'renew' 
  AND itemtype IN ('LAROMDL','LAROMTERM')  
  AND statistics.branch IS NOT NULL 
  AND statistics.itemtype IS NOT NULL 
  AND statistics.datetime BETWEEN <<Datum från|date>>-INTERVAL 1 DAY AND <<Datum till |date>>+INTERVAL 1 DAY 
  AND library_groups.parent_id=<<Kommun|librarygroupsparentid>>)
  
  UNION ALL 
  
  (SELECT branch, 
0 as utltryckt, 0 as omltryckt, 
0 as utllarom, 0 as omllarom, 
0 as utlljudbok, 1 as omlljudbok, 
0 as utltalbok, 0 as omltalbok, 
0 as utltskr, 0 as omltskr, 
0 as utlmusik, 0 as omlmusik,
0 as utlfilm, 0 as omlfilm,
0 as utlkartor, 0 as omlkartor,
0 as utlnoter, 0 as omlnoter,
0 as utlinterakt, 0 as omlinterakt,
0 as utlovr, 0 as omlovr

  FROM statistics 
  LEFT JOIN items ON (statistics.itemnumber=items.itemnumber)
  LEFT JOIN biblio ON (items.biblionumber=biblio.biblionumber)
  LEFT JOIN library_groups ON (statistics.branch=library_groups.branchcode)
  
  WHERE type = 'renew' 
  AND itemtype IN ('BARN LJUD','BARNMP3','KASSETT','LJUDBOK','MP3')
  AND statistics.branch IS NOT NULL 
  AND statistics.itemtype IS NOT NULL 
  AND statistics.datetime BETWEEN <<Datum från|date>>-INTERVAL 1 DAY AND <<Datum till |date>>+INTERVAL 1 DAY 
  AND library_groups.parent_id=<<Kommun|librarygroupsparentid>>)
  
  UNION ALL 
  
  (SELECT branch, 
0 as utltryckt, 0 as omltryckt, 
0 as utllarom, 0 as omllarom, 
0 as utlljudbok, 0 as omlljudbok, 
0 as utltalbok, 1 as omltalbok, 
0 as utltskr, 0 as omltskr, 
0 as utlmusik, 0 as omlmusik,
0 as utlfilm, 0 as omlfilm,
0 as utlkartor, 0 as omlkartor,
0 as utlnoter, 0 as omlnoter,
0 as utlinterakt, 0 as omlinterakt,
0 as utlovr, 0 as omlovr

  FROM statistics 
  LEFT JOIN items ON (statistics.itemnumber=items.itemnumber)
  LEFT JOIN biblio ON (items.biblionumber=biblio.biblionumber)
  LEFT JOIN library_groups ON (statistics.branch=library_groups.branchcode)
  
  WHERE type = 'renew' 
  AND itemtype IN ('BARNTAL','DAISY','TALBOKKASS')
  AND statistics.branch IS NOT NULL 
  AND statistics.itemtype IS NOT NULL 
  AND statistics.datetime BETWEEN <<Datum från|date>>-INTERVAL 1 DAY AND <<Datum till |date>>+INTERVAL 1 DAY 
  AND library_groups.parent_id=<<Kommun|librarygroupsparentid>>)
  
  UNION ALL 
  
  (SELECT branch, 
0 as utltryckt, 0 as omltryckt, 
0 as utllarom, 0 as omllarom, 
0 as utlljudbok, 0 as omlljudbok, 
0 as utltalbok, 0 as omltalbok, 
0 as utltskr, 1 as omltskr, 
0 as utlmusik, 0 as omlmusik,
0 as utlfilm, 0 as omlfilm,
0 as utlkartor, 0 as omlkartor,
0 as utlnoter, 0 as omlnoter,
0 as utlinterakt, 0 as omlinterakt,
0 as utlovr, 0 as omlovr

  FROM statistics 
  LEFT JOIN items ON (statistics.itemnumber=items.itemnumber)
  LEFT JOIN biblio ON (items.biblionumber=biblio.biblionumber)
  LEFT JOIN library_groups ON (statistics.branch=library_groups.branchcode)
  
  WHERE type = 'renew' 
  AND itemtype IN ('BARN TIDSK','TIDSKRIFT')
  AND statistics.branch IS NOT NULL 
  AND statistics.itemtype IS NOT NULL 
  AND statistics.datetime BETWEEN <<Datum från|date>>-INTERVAL 1 DAY AND <<Datum till |date>>+INTERVAL 1 DAY 
  AND library_groups.parent_id=<<Kommun|librarygroupsparentid>>)
  
  UNION ALL 
  
  (SELECT branch, 
0 as utltryckt, 0 as omltryckt, 
0 as utllarom, 0 as omllarom, 
0 as utlljudbok, 0 as omlljudbok, 
0 as utltalbok, 0 as omltalbok, 
0 as utltskr, 0 as omltskr, 
0 as utlmusik, 1 as omlmusik,
0 as utlfilm, 0 as omlfilm,
0 as utlkartor, 0 as omlkartor,
0 as utlnoter, 0 as omlnoter,
0 as utlinterakt, 0 as omlinterakt,
0 as utlovr, 0 as omlovr

  FROM statistics 
  LEFT JOIN items ON (statistics.itemnumber=items.itemnumber)
  LEFT JOIN biblio ON (items.biblionumber=biblio.biblionumber)
  LEFT JOIN library_groups ON (statistics.branch=library_groups.branchcode)
  
  WHERE type = 'renew' 
  AND itemtype IN ('MUSIKCD','MUSCDBARN')
  AND statistics.branch IS NOT NULL 
  AND statistics.itemtype IS NOT NULL 
  AND statistics.datetime BETWEEN <<Datum från|date>>-INTERVAL 1 DAY AND <<Datum till |date>>+INTERVAL 1 DAY 
  AND library_groups.parent_id=<<Kommun|librarygroupsparentid>>)
  
  UNION ALL 
  
  (SELECT branch, 
0 as utltryckt, 0 as omltryckt, 
0 as utllarom, 0 as omllarom, 
0 as utlljudbok, 0 as omlljudbok, 
0 as utltalbok, 0 as omltalbok, 
0 as utltskr, 0 as omltskr, 
0 as utlmusik, 0 as omlmusik,
0 as utlfilm, 1 as omlfilm,
0 as utlkartor, 0 as omlkartor,
0 as utlnoter, 0 as omlnoter,
0 as utlinterakt, 0 as omlinterakt,
0 as utlovr, 0 as omlovr

  FROM statistics 
  LEFT JOIN items ON (statistics.itemnumber=items.itemnumber)
  LEFT JOIN biblio ON (items.biblionumber=biblio.biblionumber)
  LEFT JOIN library_groups ON (statistics.branch=library_groups.branchcode)
  
  WHERE type = 'renew' 
  AND itemtype IN ('BLURAY','FILM','VHS')
  AND statistics.branch IS NOT NULL 
  AND statistics.itemtype IS NOT NULL 
  AND statistics.datetime BETWEEN <<Datum från|date>>-INTERVAL 1 DAY AND <<Datum till |date>>+INTERVAL 1 DAY 
  AND library_groups.parent_id=<<Kommun|librarygroupsparentid>>)
  
  UNION ALL 
  
  (SELECT branch, 
0 as utltryckt, 0 as omltryckt, 
0 as utllarom, 0 as omllarom, 
0 as utlljudbok, 0 as omlljudbok, 
0 as utltalbok, 0 as omltalbok, 
0 as utltskr, 0 as omltskr, 
0 as utlmusik, 0 as omlmusik,
0 as utlfilm, 0 as omlfilm,
0 as utlkartor, 1 as omlkartor,
0 as utlnoter, 0 as omlnoter,
0 as utlinterakt, 0 as omlinterakt,
0 as utlovr, 0 as omlovr

  FROM statistics 
  LEFT JOIN items ON (statistics.itemnumber=items.itemnumber)
  LEFT JOIN biblio ON (items.biblionumber=biblio.biblionumber)
  LEFT JOIN library_groups ON (statistics.branch=library_groups.branchcode)
  
  WHERE type = 'renew' 
  AND itemtype IN ('KARTOR')
  AND statistics.branch IS NOT NULL 
  AND statistics.itemtype IS NOT NULL 
  AND statistics.datetime BETWEEN <<Datum från|date>>-INTERVAL 1 DAY AND <<Datum till |date>>+INTERVAL 1 DAY 
  AND library_groups.parent_id=<<Kommun|librarygroupsparentid>>)
  
  UNION ALL 
  
  (SELECT branch, 
0 as utltryckt, 0 as omltryckt, 
0 as utllarom, 0 as omllarom, 
0 as utlljudbok, 0 as omlljudbok, 
0 as utltalbok, 0 as omltalbok, 
0 as utltskr, 0 as omltskr, 
0 as utlmusik, 0 as omlmusik,
0 as utlfilm, 0 as omlfilm,
0 as utlkartor, 0 as omlkartor,
0 as utlnoter, 1 as omlnoter,
0 as utlinterakt, 0 as omlinterakt,
0 as utlovr, 0 as omlovr

  FROM statistics 
  LEFT JOIN items ON (statistics.itemnumber=items.itemnumber)
  LEFT JOIN biblio ON (items.biblionumber=biblio.biblionumber)
  LEFT JOIN library_groups ON (statistics.branch=library_groups.branchcode)
  
  WHERE type = 'renew' 
  AND itemtype IN ('MUSIK','MUSIKBARN')
  AND statistics.branch IS NOT NULL 
  AND statistics.itemtype IS NOT NULL 
  AND statistics.datetime BETWEEN <<Datum från|date>>-INTERVAL 1 DAY AND <<Datum till |date>>+INTERVAL 1 DAY 
  AND library_groups.parent_id=<<Kommun|librarygroupsparentid>>)
  
  UNION ALL 
  
  (SELECT branch, 
0 as utltryckt, 0 as omltryckt, 
0 as utllarom, 0 as omllarom, 
0 as utlljudbok, 0 as omlljudbok, 
0 as utltalbok, 0 as omltalbok, 
0 as utltskr, 0 as omltskr, 
0 as utlmusik, 0 as omlmusik,
0 as utlfilm, 0 as omlfilm,
0 as utlkartor, 0 as omlkartor,
0 as utlnoter, 0 as omlnoter,
0 as utlinterakt, 1 as omlinterakt,
0 as utlovr, 0 as omlovr

  FROM statistics 
  LEFT JOIN items ON (statistics.itemnumber=items.itemnumber)
  LEFT JOIN biblio ON (items.biblionumber=biblio.biblionumber)
  LEFT JOIN library_groups ON (statistics.branch=library_groups.branchcode)
  
  WHERE type = 'renew' 
  AND itemtype IN ('CDROM','TV-SPEL')
  AND statistics.branch IS NOT NULL 
  AND statistics.itemtype IS NOT NULL 
  AND statistics.datetime BETWEEN <<Datum från|date>>-INTERVAL 1 DAY AND <<Datum till |date>>+INTERVAL 1 DAY 
  AND library_groups.parent_id=<<Kommun|librarygroupsparentid>>)
  
  UNION ALL 
  
  (SELECT branch, 
0 as utltryckt, 0 as omltryckt, 
0 as utllarom, 0 as omllarom, 
0 as utlljudbok, 0 as omlljudbok, 
0 as utltalbok, 0 as omltalbok, 
0 as utltskr, 0 as omltskr, 
0 as utlmusik, 0 as omlmusik,
0 as utlfilm, 0 as omlfilm,
0 as utlkartor, 0 as omlkartor,
0 as utlnoter, 0 as omlnoter,
0 as utlinterakt, 0 as omlinterakt,
0 as utlovr, 1 as omlovr

  FROM statistics 
  LEFT JOIN items ON (statistics.itemnumber=items.itemnumber)
  LEFT JOIN biblio ON (items.biblionumber=biblio.biblionumber)
  LEFT JOIN library_groups ON (statistics.branch=library_groups.branchcode)
  
  WHERE type = 'renew' 
  AND itemtype IN ('BLANDAT','FOREMAL','FOREMAL3MD','KONTROLL','SALLSKAPSS','SUFRPLATTA','VECKOLAN','X')
  AND statistics.branch IS NOT NULL 
  AND statistics.itemtype IS NOT NULL 
  AND statistics.datetime BETWEEN <<Datum från|date>>-INTERVAL 1 DAY AND <<Datum till |date>>+INTERVAL 1 DAY 
  AND library_groups.parent_id=<<Kommun|librarygroupsparentid>>)
  
  
  ) ds
  
  LEFT JOIN branches ON (ds.branch=branches.branchcode)
  GROUP BY branchname WITH ROLLUP

Fråga 16 Fjärrlån - kommunvis

SELECT 
CASE
WHEN branchname IS NULL
THEN '=Summa'
ELSE
 branchname
 END AS Bibliotek,
 SUM(utlan) AS 'Utlån',
 SUM(inlan) AS 'Inlån'
 
 FROM
 (
 (SELECT branch,1 as utlan, 0 as inlan
 FROM statistics

LEFT JOIN borrowers ON (statistics.borrowernumber=borrowers.borrowernumber)
LEFT JOIN branches ON (statistics.branch=branches.branchcode)
LEFT JOIN library_groups ON (statistics.branch=library_groups.branchcode)

WHERE type = 'issue' 
AND borrowers.categorycode IN ('BIBLIOTEK') 
AND borrowers.cardnumber NOT IN ('8bxq','8bxz','8bya','8byb','8byc','8byi','8byl','8bym','8byo','8byq','8byr','8bys','Gull','Hjo','Hova','Kabo','Kbro','Mari','Skgy','Sksb','Tida','Tikf','Tore','Vagy')
AND statistics.branch IS NOT NULL 
AND statistics.itemtype IS NOT NULL 
AND statistics.datetime BETWEEN <<Datum från|date>>-INTERVAL 1 DAY AND <<Datum till |date>>+INTERVAL 1 DAY 
AND library_groups.parent_id=<<Kommun|librarygroupsparentid>>
 )
 
 UNION ALL
 
 (SELECT branch,0 as utlan, 1 as inlan
 FROM statistics

LEFT JOIN borrowers ON (statistics.borrowernumber=borrowers.borrowernumber)
LEFT JOIN branches ON (statistics.branch=branches.branchcode)
LEFT JOIN library_groups ON (statistics.branch=library_groups.branchcode)

WHERE type = 'issue' 
AND itemtype IN ('FJARRLAN')
AND statistics.branch IS NOT NULL 
AND statistics.itemtype IS NOT NULL 
AND statistics.datetime BETWEEN <<Datum från|date>>-INTERVAL 1 DAY AND <<Datum till |date>>+INTERVAL 1 DAY 
AND library_groups.parent_id=<<Kommun|librarygroupsparentid>> )
) ds
LEFT JOIn branches On (branches.branchcode=ds.branch)

GROUP BY branchname WITH ROLLUP

Fråga 19 Aktiva låntagare - kommunvis

SELECT 
CASE WHEN
branchname IS NULL
THEN
'=Summa'
ELSE
branchname 
END 
AS 'Låntagarens hembibliotek',
COUNT(CASE WHEN code = 'PERSNUMMER' AND LEFT( RIGHT(attribute, 2), 1) % 2 = 0 THEN 1 END) 'Aktiva kvinnor',
COUNT(CASE WHEN code = 'PERSNUMMER' AND LEFT( RIGHT(attribute, 2), 1) % 2 = 1 THEN 1 END) 'Aktiva män',
COUNT (CASE WHEN borrowers.categorycode IN ('BIBLIOTEK','ELEVTIDA','FORSKOLA','INSTT','KVALON','PERSONAL','PTID','SKOLA','SKOLBIBTID') THEN 1 END) 'Institutionslåntagare',
COUNT(CASE WHEN dateofbirth > DATE_SUB(CURDATE(), INTERVAL 18 YEAR) OR borrowers.categorycode IN ('ELEVTIDA','SKOLBIBTID','BARN') THEN 1 END) 'Under 18 år'

FROM borrowers 

LEFT JOIN borrower_attributes ON (borrowers.borrowernumber=borrower_attributes.borrowernumber) 
LEFT JOIN branches ON (borrowers.branchcode=branches.branchcode)

WHERE 
borrowers.borrowernumber IN

(SELECT borrowernumber 

FROM
statistics 

LEFT JOIN library_groups ON (statistics.branch=library_groups.branchcode)

WHERE 
statistics.datetime BETWEEN <<Datum från|date>>-INTERVAL 1 DAY AND <<Datum till |date>>+INTERVAL 1 DAY AND library_groups.parent_id=<<Kommun|librarygroupsparentid>> AND statistics.type='issue')

GROUP BY branchname WITH ROLLUP
Hämtad från "https://koha.se/wiki/index.php?title=Lånestatistik_Bibliotek_Mellansjö&oldid=814"