-- Search for details about which libraries run bib imports, and how frequently

use polaris

Select 
Year(ij.ImportJobSubmittedDate) ImportYear
,Month(ij.ImportJobSubmittedDate) ImportMonth
,po.Name as Library
-- Comment in below lines for showing totals by wheether a bib already existed, or what the Leader 17 was
-- ,iif(t.BibRecordAlreadyExisted = 0, 'No', iif(t.BibRecordAlreadyExisted = 1, 'Yes', 'Unclear')) As BibAlreadyExisted
--,br.MARCBibEncodingLevel
,Sup.SupplierName
,count(distinct ij.ImportJobID) ImportCount
,Sum(ijs.BibRecordSavedAsFinalCount) BibRecordSavedAsFinalCount,
Sum(ijs.BibRecordSavedAsProvisionalCount) BibRecordSavedAsProvisionalCount,
Sum(ijs.BibRecordSavedAsDeletedCount) BibRecordSavedAsDeletedCount,
Sum(ijs.BibRecordRejectedCount) BibRecordRejectedCount
From polaris.importjobs ij (nolock)
inner join polaris.ImportJobsBibRecordCreated t (nolock)
on ij.ImportJobID = t.ImportJobID
left join Polaris.ImportJobsStatistics ijs (nolock)
on ijs.importjobid = ij.importjobid
left join polaris.Organizations o (nolock)
on ij.OrganizationID = o.OrganizationID
left join polaris.Organizations po (nolock)
on o.ParentOrganizationID = po.OrganizationID
left join polaris.PolarisUsers pu (nolock) -- In case we want to bring in users
on ij.PolarisUserID = pu.PolarisUserID

left join polaris.ImportJobsItemRecordFromBibCreated itemj (nolock)
on itemj.ImportJobID = ij.ImportJobid
left join polaris.LineItemSegmentToItemRecord POtoIt (nolock)
on POToIT.ItemRecordID = itemj.ItemRecordID
left join polaris.POLineItemSegments POLine (nolock)
on POtoIt.POLineItemSegmentID = POLine.POLineItemSegmentID
Left Join Polaris.PurchaseOrders POs (nolock)
on POLine.PurchaseOrderID = POs.PurchaseOrderID
Left Join Polaris.Suppliers Sup (nolock)
on POs.SupplierID = Sup.SupplierID


where ij.ImportJobSubmittedDate > '2017-12-31' -- Enter a date the job ran after, or change as appropriate
-- and ij.ImportFileNameClient like '%hoopla%' -- Enter all or part of the name of the import job
and ij.ImportCompleted = 1
group by Year(ij.ImportJobSubmittedDate) 
,Month(ij.ImportJobSubmittedDate) 
,po.Name
,Sup.SupplierName
--,iif(t.BibRecordAlreadyExisted = 0, 'No', iif(t.BibRecordAlreadyExisted = 1, 'Yes', 'Unclear'))
--,br.MARCBibEncodingLevel
Order by Year(ij.ImportJobSubmittedDate) desc
,Month(ij.ImportJobSubmittedDate) desc
,po.Name
,Sup.SupplierName
--,iif(t.BibRecordAlreadyExisted = 0, 'No', iif(t.BibRecordAlreadyExisted = 1, 'Yes', 'Unclear'))
--,br.MARCBibEncodingLevel