if exists (select * from dbo.sysobjects where id = object_id(N'[Polaris].[SLCO_CIRC_Patron_Circ_Stattran]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure Polaris.SLCO_CIRC_Patron_Circ_Stattran
GO


---Created: 6/25/01, Beth Silliman

--This stored procedure is used for PatronCirculation Report.

--Count of the number of circulation transactions by patron code. 

--The report measures activity within a patron code, not items circulated.(PatronCirculationStatistics Report)

--Modified by C. Medling 3/8/2017 Inserts Patron Circulation data into aggrate table


CREATE PROCEDURE Polaris.SLCO_CIRC_Patron_Circ_Stattran
 
AS
BEGIN
	SET NOCOUNT ON    

        INSERT INTO polaris.SLCO_PATRONCIRC_DAY_MONTH_YEAR
        (
                Organizationid,
                Patroncodeid,
                Description,
                Day,
                Month,
                Year,
                circ_by_patron_code
        )
SELECT distinct
			O.OrganizationID,
			TD.Numvalue,
			PC.Description,
			Datepart(dd,TH.TranClientDate),
			datepart(mm,TH.TranClientDate),
			datepart(yyyy,TH.TranClientDate),
			count(DISTINCT TH.TransactionID)
		FROM
			PolarisTransactions.Polaris.TransactionDetails TD with (NOLOCK)
			INNER JOIN PolarisTransactions.Polaris.TransactionHeaders TH with (NOLOCK)
				ON (td.TransactionID=th.TransactionID and td.TransactionSubTypeID=7 )
			--INNER JOIN #Branches B
			--	ON(th.OrganizationID = B.OrganizationID)
			INNER JOIN Polaris.organizations O with (NOLOCK)
				ON(th.OrganizationID=o.OrganizationID)
			INNER JOIN polaris.patronCodes PC with (NOLOCK)
				ON (pc.PatronCodeID = td.NumValue)
		WHERE
				datepart(dd,TH.TranClientDate)=datepart(dd,getdate()-30) and datepart(mm,TH.TranClientDate)=datepart(mm,getdate()-30) and datepart(yyyy,TH.TranClientDate)=datepart(yyyy,getdate()-30) AND
				th.TransactionTypeID=6001
group by O.OrganizationID,
			TD.Numvalue,
			PC.Description,
			Datepart(dd,TH.TranClientDate),
			datepart(mm,TH.TranClientDate),
			datepart(yyyy,TH.TranClientDate)
RETURN
END