SQL
SQL for Item Detail
select IST.Description as status, ORG.Abbreviation as owner, br.BrowseTitle as Title, br.BrowseAuthor as Author, col.Abbreviation as collection, ltrim(ird.CallNumber) as callnum, CIR.Barcode, br.PublicationYear, CIR.ItemRecordID, CIR.LastCircTransactionDate, CIR.YTDCircCount, CIR.LifetimeCircCount, CIR.RecordStatusDate, CIR.FirstAvailableDate,Cast(datediff(day, CIR.FirstAvailableDate, getdate())/365.00 as decimal(5,2)) as lifespan, CIR.LifetimeCircCount/(Cast(datediff(day, CIR.FirstAvailableDate, getdate())/365.00 as decimal(5,2))) as avg_circ from Polaris.Polaris.CircItemRecords CIR (nolock) join Polaris.Polaris.ItemStatuses IST with (nolock) on CIR.ItemStatusID=IST.ItemStatusID join Polaris.Polaris.Organizations ORG with (nolock) on CIR.AssignedBranchID=ORG.OrganizationID join Polaris.Polaris.BibliographicRecords br with (nolock) on (CIR.AssociatedBibRecordID = br.BibliographicRecordID) left join Polaris.Polaris.Collections col with (nolock) on CIR.AssignedCollectionID=col.CollectionID INNER JOIN polaris.polaris.ItemRecordDetails ird with (nolock) on CIR.ItemRecordID = ird.ItemRecordID where AssignedBranchID in ( 14 ) and AssignedCollectionID in (12, 29, 124, 136, 172, 182, 195 ) and cast(CIR.FirstAvailableDate as date) < cast(getdate() -1 as date) /* toss brand new items to prevent divide-by-zero errors */ and CIR.RecordStatusID <>4 /* deleted item */ and CIR.ItemStatusID not in (7,8,9,10,11,16) /* lost, missing, claimed, withdraw, unavail*/
SQL for Collection Summary
/* Calculations to look at what collections need weeding and what are doing well */ /* with stored media-wide variables */ /* In this example, looking all DVD collections */ /* could use this clause: where AssignedCollectionID in (select CollectionID from Polaris.Collections where name like '%DVD%') */ declare @totcolsize bigint, @totcirc bigint, @totlife bigint, @totsumavgcirc decimal select @totcolsize= count(ItemRecordID), @totcirc=sum(LifetimeCircCount) , @totlife=sum(datediff(day, FirstAvailableDate, getdate())/365.00), @totsumavgcirc=SUM(cast(LifetimeCircCount as decimal(5,2))/cast(datediff(day, FirstAvailableDate, getdate())/365.00 as decimal(5,2))) from Polaris.CircItemRecords (nolock) where assignedbranchID=14 and AssignedCollectionID in (12, 29, 124, 136, 172, 182, 195 ) and cast(FirstAvailableDate as date) < cast(getdate()-1 as date) and RecordStatusID <>4 /* deleted */ and ItemStatusID not in (7,8,9,10,11,15,16) /* lost, missing, claimed, withdrawn, unavail, in process*/ select col.Abbreviation as collection, sum(CIR.LifetimeCircCount) as circ, CAst(sum(CIR.LifetimeCircCount)/sum(datediff(day, CIR.FirstAvailableDate, getdate())/365.00) as decimal(5,2)) as "avg annual circ", /*datediff(day, CIR.FirstAvailableDate, getdate())/365.00 as yearsofcirc, */ SUM(CAST(CIR.LifetimeCircCount AS DECIMAL(5,2))/CAST(datediff(day, CIR.FirstAvailableDate, getdate())/365.00 AS DECIMAL(5,2))) as "sum avg circ", @totsumavgcirc as "sum of total avg circ", count(CIR.ItemRecordID) as colsize, SUM(CAST(CIR.LifetimeCircCount AS DECIMAL(5,2))/CAST(datediff(day, CIR.FirstAvailableDate, getdate())/365.00 AS DECIMAL(5,2)))/count(CIR.ItemRecordID) as turnover, @totcolsize as total_collection_size, @totcirc as "total circ", @totlife as "total lifetime", 100.0*count(CIR.ItemRecordID)/ @totcolsize as "expected usage", 100*SUM(CAST(CIR.LifetimeCircCount AS DECIMAL(5,2))/CAST(datediff(day, CIR.FirstAvailableDate, getdate())/365.00 AS DECIMAL(5,2))) /@totsumavgcirc as "actual usage", (100*SUM(CAST(CIR.LifetimeCircCount AS DECIMAL(5,2))/CAST(datediff(day, CIR.FirstAvailableDate, getdate())/365.00 AS DECIMAL(5,2))) /@totsumavgcirc)-(100.0*count(CIR.ItemRecordID)/ @totcolsize)as "usage difference", avg(publicationyear) as "avg pub yr", CAST(AVG(CAST(CIR.FirstAvailableDate AS INT)) AS DATETIME) as "avg 1st avail", CAST(AVG(CAST(CIR.LastCircTransactionDate AS INT)) AS DATETIME) as "avg last cko" from Polaris.Polaris.CircItemRecords CIR (nolock) left join Polaris.Polaris.Collections col with (nolock) on CIR.AssignedCollectionID=col.CollectionID join Polaris.Polaris.BibliographicRecords br with (nolock) on (CIR.AssociatedBibRecordID = br.BibliographicRecordID) INNER JOIN polaris.polaris.ItemRecordDetails ird with (nolock) on CIR.ItemRecordID = ird.ItemRecordID where AssignedBranchID in (14) and AssignedCollectionID in (12, 29, 124, 136, 172, 182, 195 ) and cast(CIR.FirstAvailableDate as date) < cast(getdate()-1 as date) and CIR.RecordStatusID <>4 /* deleted */ and CIR.ItemStatusID not in (7,8,9,10,11,16) /* lost, missing, claimed, withdraw, unavail*/ group by Col.Abbreviation order by col.abbreviation