Statistikk i Mikromarc 2 - Egendefinerte

Utlån (inkl. fornyelser) på topp - Titler (> 50 lån)

SELECT Title AS "Tittel", Author AS "Forfatter", Doctype AS "Materialbetegnelse", count(*) AS "Utlån"
FROM marchist
WHERE Service_Code in (8,9)
:AddFilter
GROUP BY  "Tittel", "Forfatter", "Materialbetegnelse"
HAVING "Utlån" > 50
ORDER BY "Utlån" DESC
</code>

Utlån (inkl. fornyelser) på topp - Forfatter (> 50 lån)

SELECT Author AS "Forfatter", count(*) AS "Utlån"
FROM marchist
WHERE Service_Code in (8,9)
:AddFilter
GROUP BY  "Forfatter"
HAVING "Utlån" > 50
ORDER BY "Utlån" DESC
</code>

Hyllevarmere - Titler uten utlån i perioden

SELECT pm_value11 as "Hylle", Pm_Value2 AS "Forfatter", Pm_Value1 AS "Tittel",  Pm_Value4 AS Utgitt
FROM Marc_Record
WHERE Marc_Record.isscrapped = 0 
and Marc_Record.Marc_id NOT IN (SELECT Marc_id FROM Service_History WHERE Service_Time >= ':AddFilterFromDate' AND Service_Time <= ':AddFilterToDate' and service_code in (8,9))
:AddFilterUnit :AddFilter
ORDER BY "Hylle", "Tittel",  "Forfatter" , "Utgitt"
</code>

Hyllevarmere - Eksemplar uten utlån i perioden

SELECT (select pm_value1 from marc_record where marc_id = c.marc_id) AS Tittel 
-- Måtte jukse litt for at :AddFilter skulle slå inn på Marc_record og ikke service_history som er med i en select lenger ned men før første marc_record-select
, Pm_Value2 AS Forfatter, Pm_Value4 AS Utgitt,
getshelfmark(C.ex_id) as Hylle,
C.bar_code AS Strekkode,
C.Reg_date as "Reg dato",
(SELECT Description FROM Copy_status WHERE Code = C.Status_code) AS Status,
(select date(max(service_time)) from service_history where service_code = 8 and service_history.ex_id = c.ex_id) as "Sist utlånt",
(select count(1) from service_history where service_code in (8) and service_history.ex_id = c.ex_id) as "Antall utlån"
FROM Marc_Record KEY JOIN Copy C
WHERE Marc_record.IsScrapped = 0 
AND Marc_record.Reg_Date < GetDate() - 365 
AND C.Permit_Loan = 1 
AND C.Is_scrapped = 0
AND C.status_code <> 2
AND NOT EXISTS (SELECT 1 FROM Service_History WHERE service_code = 8 and Service_Time > ':AddFilterFromDate' AND Service_Time <= ':AddFilterToDate' AND ex_id = c.ex_id) 
:AddFilterUnit :AddFilter
Order by Hylle, Forfatter, Tittel, marc_record.marc_id, "Antall utlån"
</code>

Lånere - Antall nye lånere i perioden

SELECT
lg.grp_code as "Lånergruppe", 
count(*) as Antall
FROM loaner key join loaner_group lg
WHERE reg_date >= ':AddFilterFromDate' and reg_date <= ':AddFilterToDate' 
:AddFilter 
group by "Lånergruppe"
order by "Lånergruppe"
</code>