/*** Report Parameters ***/

DECLARE @BDATE datetime

DECLARE @EDATE datetime

SET @BDATE = '2015-01-01'

SET @EDATE = '2015-01-31'

/*** END Parameters ***/



-- Initial table to hold TransactionHeader information

CREATE TABLE #TEMPTrans (

	TransactionID int,

	OrganizationID int

	)



INSERT INTO #TEMPTrans

SELECT TH.TransactionID, TH.OrganizationID

FROM PolarisTransactions.Polaris.TransactionHeaders TH

WHERE TH.TransactionTypeID = 6001 -- checkouts only

AND TH.TranClientDate BETWEEN @BDATE AND @EDATE



-- Holds informaiton used to calculate statistics later on, including collection and materialtype

CREATE TABLE #TransStats (

	TransactionID int,

	OrganizationID int,

	CollectionName varchar(50),

	MatType varchar(50)

	)



--Gathering all stats together for later processing

INSERT INTO #TransStats

SELECT TMP.TransactionID, TMP.OrganizationID,

'Collection' = ( SELECT PC.Name FROM PolarisTransactions.Polaris.TransactionDetails Col

	JOIN Polaris.Polaris.Collections PC ON

	PC.CollectionID = Col.numValue

	WHERE Col.TransactionSubTypeID = 61 -- Assigned Collection Code

	AND Col.TransactionID = TMP.TransactionID

	),

'MaterialType' = ( SELECT MT.Description FROM PolarisTransactions.Polaris.TransactionDetails Mat

	JOIN Polaris.Polaris.MaterialTypes MT ON

	MT.MaterialTypeID = Mat.numValue

	WHERE Mat.TransactionSubTypeID = 4 -- Material Type

	AND Mat.TransactionID = TMP.TransactionID

	)

FROM #TEMPTrans TMP

WHERE TMP.TransactionID NOT IN ( SELECT TT.TransactionID FROM #TEMPTrans TT

			JOIN PolarisTransactions.Polaris.TransactionDetails TD2

			ON TD2.TransactionID = TT.TransactionID

			WHERE TD2.TransactionSubTypeID = 124 -- Renewal Subtype

			)

ORDER BY OrganizationID, 'MaterialType', 'Collection'



DECLARE @Results TABLE (

	Orgname varchar(50),

	OrgID int,

	OrgCount int,

	MatType varchar(50),

	MatCount int,

	CollName varchar(50),

	CollCount int

)



INSERT INTO @Results

SELECT PO.Name,

TS.OrganizationID,

NULL,

MatType,

NULL,

CollectionName,

'CollCount' = Count(*)

FROM #TransStats TS

JOIN Polaris.Polaris.Organizations PO ON 

PO.OrganizationID = TS.OrganizationID

GROUP BY PO.Name, TS.OrganizationID, MatType, CollectionName

Order By PO.Name, MatType, 'CollCount' DESC



UPDATE Res

SET MatCount = ( SELECT 'MatCount' = COUNT(*)

FROM #TransStats TS

WHERE Res.MatType = TS.MatType

AND Res.OrgID = TS.OrganizationID

)

FROM @Results Res



UPDATE Res

SET OrgCount = ( SELECT 'OrgCount' = COUNT(*)

FROM #TransStats TS

WHERE Res.OrgID = TS.OrganizationID

)

FROM @Results Res



SELECT * FROM @Results

ORDER BY OrgName, MatCount DESC, CollCount DESC



DROP TABLE #TEMPTrans

DROP TABLE #TransStats