|

Uitleencijfers

Er is een rapport waarmee de uitleencijfers per maand kunnen worden uitgelezen. Per maand worden de cijfers verzameld van uitleningen, verlengingen en het aantal ingeleverde boeken (issue, renew, return).

SELECT DISTINCT 
  CONCAT('<strong>',s.type,'</strong>')       AS ' ', 
  CONCAT('<em>',b.branchname,'</em>')       AS 'Bibliotheek', 
  SUM(IF(MONTHNAME(s.datetime) = 'January',  1, 0)) AS 'Januari',
  SUM(IF(MONTHNAME(s.datetime) = 'February',   1, 0)) AS 'Februari',
  SUM(IF(MONTHNAME(s.datetime) = 'March',    1, 0)) AS 'Maart', 
  SUM(IF(MONTHNAME(s.datetime) = 'April',    1, 0)) AS 'April',
  SUM(IF(MONTHNAME(s.datetime) = 'May',    1, 0)) AS 'Mei',
  SUM(IF(MONTHNAME(s.datetime) = 'June',     1, 0)) AS 'Juni',
  SUM(IF(MONTHNAME(s.datetime) = 'July',     1, 0)) AS 'Juli',
  SUM(IF(MONTHNAME(s.datetime) = 'August',   1, 0)) AS 'Augustus',
  SUM(IF(MONTHNAME(s.datetime) = 'September',  1, 0)) AS 'September',
  SUM(IF(MONTHNAME(s.datetime) = 'October',  1, 0)) AS 'Oktober',
  SUM(IF(MONTHNAME(s.datetime) = 'November',   1, 0)) AS 'November',
  SUM(IF(MONTHNAME(s.datetime) = 'December',   1, 0)) AS 'December',
  COUNT(*)            AS 'Totaal',
  CONCAT('<strong>',s.type,'</strong>')       AS ' '
FROM  statistics s, branches b
WHERE s.branch = b.branchcode
AND s.type IN ('renew', 'return', 'issue') 
AND YEAR(s.datetime)=@CurrentYear:=<<Jaar>> 
GROUP BY  b.branchname, s.type 
UNION 
SELECT 
  NULL AS ' ', 
  NULL AS 'BRANCH', 
  NULL AS 'January',
  NULL AS 'February',
  NULL AS 'March', 
  NULL AS 'April',
  NULL AS 'May',
  NULL AS 'June',
  NULL AS 'July',
  NULL AS 'August',
  NULL AS 'September',
  NULL AS 'October',
  NULL AS 'November',
  NULL AS 'December',
  NULL AS 'Totaal', 
  NULL AS ' '
UNION 
SELECT DISTINCT 
  CONCAT('<strong>',c.type,'</strong>')       AS ' ',  
  '<em>ALL BRANCHES</em>'         AS 'BRANCH', 
  SUM(IF(MONTHNAME(c.datetime) = 'January',  1, 0)) AS 'Januari',
  SUM(IF(MONTHNAME(c.datetime) = 'February',   1, 0)) AS 'Februari',
  SUM(IF(MONTHNAME(c.datetime) = 'March',    1, 0)) AS 'Maart', 
  SUM(IF(MONTHNAME(c.datetime) = 'April',    1, 0)) AS 'April',
  SUM(IF(MONTHNAME(c.datetime) = 'May',    1, 0)) AS 'Mei',
  SUM(IF(MONTHNAME(c.datetime) = 'June',     1, 0)) AS 'Juni',
  SUM(IF(MONTHNAME(c.datetime) = 'July',     1, 0)) AS 'Juli',
  SUM(IF(MONTHNAME(c.datetime) = 'August',   1, 0)) AS 'Augustus',
  SUM(IF(MONTHNAME(c.datetime) = 'September',  1, 0)) AS 'September',
  SUM(IF(MONTHNAME(c.datetime) = 'October',  1, 0)) AS 'Oktober',
  SUM(IF(MONTHNAME(c.datetime) = 'November',   1, 0)) AS 'November',
  SUM(IF(MONTHNAME(c.datetime) = 'December',   1, 0)) AS 'December',
  CONCAT('<strong>',COUNT(*),'</strong>')     AS 'Totaal', 
  CONCAT('<strong>',c.type,'</strong>')       AS ' '
FROM  statistics c 
WHERE c.type IN ('renew', 'return', 'issue') 
AND YEAR(c.datetime) = @CurrentYear 
GROUP BY c.type
Ook interessant:
Ook interessant: