Creating Inventory Lists

These notes are for creating an inventory based on a list of specifications.

Specifications:

What fields to include

Some examples are:

• • Title

  • Call Number

  • Publication Date

  • Count of Items

Data should be retrieved from the Data Warehouse. The following is an example query to retrieve the items above and to sort them:

select a.HOL_DOC_NUM, a.BIB_DOC_NUM, e.ADM_DOC

,a.SUB_LIB, a.COLL, b.CN_DISPLAY, b.CN_SORT, c.DISPLAY_TITLE, d.PUB_DATE

,count(f.BARCODE) AS Items

from SB.AL_HOL_LOCN a

join SB.AL_CALL_NUMBER b on a.HOL_DOC_NUM = b.HOL_DOC_NUM

join SB.AL_TITLES c on a.BIB_DOC_NUM = c.BIB_DOC_NUM

join SB.AL_KEY_LINKS e on a.HOL_DOC_NUM = e.HOL_DOC

left join SB.AL_PUB d on c.BIB_DOC_NUM = d.BIB_DOC_NUM

left join SB.AL_ITEMS f on e.ADM_DOC = f.ADM_DOC_NUM and f.INST = e.INST and a.HOL_DOC_NUM = f.HOL_DOC_NUM

left join SB.AL_STA x on a.HOL_DOC_NUM = x.HOL_DOC_NUM

where 1=1

and a.COLL = 'SPC'

and a.SUB_LIB = 'FABOC'

and x.HOL_DOC_NUM is null

and b.CN_SORT >= 'K'

and b.CN_SORT <= 'MU'

group by a.HOL_DOC_NUM, a.BIB_DOC_NUM, e.ADM_DOC

,a.SUB_LIB, a.COLL, b.CN_DISPLAY, b.CN_SORT, c.DISPLAY_TITLE, d.PUB_DATE

order by b.CN_SORT

Collections are: