Lånestatistik Bibliotek Mellansjö

Statistikrapporter som Bibliotek Mellansjö skapat. Se dem som komplement/alternativ till Hyltes rapporter!Statistik 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

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

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:

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 11 olika bestånd - om man har en placeringskod för skönlitteratur

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

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

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 Svenska

Mellansjö har 45.000 titlar som saknar språkkod i fält 008, vi har valt att räkna dem som svenska

SELECT
branchname AS Bibliotek,
COUNT(*) AS 'Antal svenska tilar'

FROM biblio_metadata

LEFT JOIN biblioitems ON (biblio_metadata.biblionumber=biblioitems.biblionumber)
LEFT JOIN items On (biblioitems.biblionumber=items.biblionumber)
LEFT JOIN branches ON (items.homebranch=branches.branchcode)

WHERE (SUBSTRING(ExtractValue(metadata, '//controlfield[@tag="008"]'), 36, 3) = 'swe' OR 
SUBSTRING(ExtractValue(metadata, '//controlfield[@tag="008"]'), 36, 3) = '')  AND
items.homebranch=<<Bibliotek|branches>> 

Fråga 12 Minoritetsspråk

SELECT
branchname AS Bibliotek,
COUNT(*) AS 'Antal titlar på minoritetsspråk'

FROM biblio_metadata

LEFT JOIN biblioitems ON (biblio_metadata.biblionumber=biblioitems.biblionumber)
LEFT JOIN items On (biblioitems.biblionumber=items.biblionumber)
LEFT JOIN branches ON (items.homebranch=branches.branchcode)

WHERE (SUBSTRING(ExtractValue(metadata, '//controlfield[@tag="008"]'), 36, 3) = 'fin' OR 
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>> 

Fråga 12 Utländska

SELECT
branchname AS Bibliotek,
COUNT(*) AS 'Antal utländska titlar'

FROM biblio_metadata

LEFT JOIN biblioitems ON (biblio_metadata.biblionumber=biblioitems.biblionumber)
LEFT JOIN items On (biblioitems.biblionumber=items.biblionumber)
LEFT JOIN branches ON (items.homebranch=branches.branchcode)

WHERE SUBSTRING(ExtractValue(metadata, '//controlfield[@tag="008"]'), 36, 3) != 'swe' AND 
SUBSTRING(ExtractValue(metadata, '//controlfield[@tag="008"]'), 36, 3) != 'fin' AND 
SUBSTRING(ExtractValue(metadata, '//controlfield[@tag="008"]'), 36, 3) != 'smi' AND 
SUBSTRING(ExtractValue(metadata, '//controlfield[@tag="008"]'), 36, 3) != '9mk' AND 
SUBSTRING(ExtractValue(metadata, '//controlfield[@tag="008"]'), 36, 3) != 'rom' AND 
SUBSTRING(ExtractValue(metadata, '//controlfield[@tag="008"]'), 36, 3) != 'yid' AND
SUBSTRING(ExtractValue(metadata, '//controlfield[@tag="008"]'), 36, 3) != '' AND
items.homebranch=<<Bibliotek|branches>> 

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

SELECT 
  branchname,
  COUNT(CASE WHEN type = 'issue' AND borrowers.categorycode IN ('BIBLIOTEK') THEN 1 END) 'Fjärrutlån',
  COUNT(CASE WHEN type = 'issue' AND itemtype IN ('FJARRLAN') THEN 1 END) 'Fjärrinlån'

FROM statistics

LEFT JOIN borrowers ON (statistics.borrowernumber=borrowers.borrowernumber)
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 19 Kvinnor

SELECT 
branches.branchname AS Bibliotek,
COUNT(DISTINCT statistics.borrowernumber) AS 'Aktiva låntagare kvinnor' 

FROM statistics 
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)

WHERE statistics.datetime LIKE '2018%' 
AND code = 'PERSNUMMER' 
AND LEFT( RIGHT(attribute, 2), 1) % 2 = 0 
AND statistics.branch=<<Bibliotek|branches>>

GROUP BY statistics.branch

Fråga 19 Män

SELECT 
branches.branchname AS Bibliotek,
COUNT(DISTINCT statistics.borrowernumber) AS 'Aktiva låntagare kvinnor' 

FROM statistics 
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)

WHERE statistics.datetime LIKE '2018%' 
AND code = 'PERSNUMMER' 
AND LEFT( RIGHT(attribute, 2), 1) % 2 = 1 
AND statistics.branch=<<Bibliotek|branches>>

GROUP BY statistics.branch

Fråga 19 Institution

SELECT branches.branchname AS Bibliotek, COUNT (DISTINCT statistics.borrowernumber) AS 'Antal institutionslåntagare'

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

WHERE statistics.datetime LIKE '2018%' 
AND borrowers.categorycode IN ('BIBLIOTEK','ELEVTIDA','FORSKOLA','INSTT','KVALON','PTID','SKOLA','SKOLBIBTID') 
AND statistics.branch=<<Bibliotek|branches>>

GROUP BY statistics.branch

Fråga 19 Aktiva låntagare som är under 18 år

SELECT branchname AS Bibliotek, COUNT (DISTINCT statistics.borrowernumber) AS 'Antal aktiva under 18 år'

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

WHERE statistics.datetime LIKE '2018%' 
AND (borrowers.dateofbirth > DATE_SUB(CURDATE(), INTERVAL 18 YEAR) OR borrowers.categorycode IN ('ELEVTIDA','SKOLBIBTIDA','BARN')) 
AND statistics.branch=<<Bibliotek|branches>>

GROUP BY statistics.branch

Fråga 19 Aktiva låntagare (experiment för 2019 och framåt)

Ett försök att använda den nya systempreferensen TrackLastPatronActivity

SELECT 
  branches.branchname AS Bibliotek,
  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