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