KBPlus Useful SQL

Subscriptions, and subscribing org

select s.sub_name, o.org_name
from subscription s, org o, org_role orl, refdata_value rdv
where orl.or_sub_fk = s.sub_id
and orl.or_org_fk = o.org_id
and orl.or_roletype_fk = rdv.rdv_id
and rdv.rdv_value = 'Subscriber'

All issue entitlements as a csv for the entire database

select s.sub_name, o.org_name, ti.ti_title
from subscription s, 
     org o, 
     org_role orl, 
     refdata_value rdv, 
     title_instance ti,
     issue_entitlement ie,
     title_instance_package_platform tipp
where orl.or_sub_fk = s.sub_id
and orl.or_org_fk = o.org_id
and orl.or_roletype_fk = rdv.rdv_id
and rdv.rdv_value = 'Subscriber'
and ie.ie_subscription_fk = s.sub_id
and ie.ie_tipp_fk = tipp.tipp_id
and tipp.tipp_ti_fk = ti.ti_id
limit 100;

As above, with coverage, inst id and title id

select s.sub_name, o.org_id, o.org_name, ti.ti_id, ti.ti_title, ie.ie_start_date, ie.ie_end_date
from subscription s, 
     org o, 
     org_role orl, 
     refdata_value rdv, 
     title_instance ti,
     issue_entitlement ie,
     title_instance_package_platform tipp
where orl.or_sub_fk = s.sub_id
and orl.or_org_fk = o.org_id
and orl.or_roletype_fk = rdv.rdv_id
and rdv.rdv_value = 'Subscriber'
and ie.ie_subscription_fk = s.sub_id
and ie.ie_tipp_fk = tipp.tipp_id
and tipp.tipp_ti_fk = ti.ti_id
limit 100;

All IEs for an institution

select s.sub_name, o.org_id, o.org_name, ti.ti_id, ti.ti_title, ie.ie_start_date, ie.ie_end_date
from subscription s, 
    org o, 
    org_role orl, 
    refdata_value rdv, 
    title_instance ti,
    issue_entitlement ie,
    title_instance_package_platform tipp,
    refdata_value rdv2,

where orl.or_sub_fk = s.sub_id and orl.or_org_fk = o.org_id and orl.or_roletype_fk = rdv.rdv_id and rdv.rdv_value = 'Subscriber' and ie.ie_subscription_fk = s.sub_id and ie.ie_tipp_fk = tipp.tipp_id and tipp.tipp_ti_fk = ti.ti_id and o.org_name = 'University of Birmingham' and rdv2.rdv_id = ie.core_status_id and rdv2.value = '' limit 100;