Payment History and Totals

Clearinghouse Repository Polaris Repository
,
1

Payment History and Totals

This pulls total payment history, by fee reason, for libraries within a system. Because it’s pulling from the Transactions database, it’s a query that will get you historical information.

SELECT
    torg.Name AS "Branch/Library",
    frc.FeeDescription AS "Fee Reason",
    COUNT(DISTINCT th.TransactionID) AS "Number of Transactions", 
    STR(ROUND(CAST(SUM(mon.numvalue/1000.00) AS MONEY),2),20,2) AS "Amount"

FROM
    PolarisTransactions.Polaris.TransactionHeaders th WITH (NOLOCK) 

INNER JOIN
    PolarisTransactions.Polaris.TransactionDetails td WITH (NOLOCK) ON (th.TransactionID = td.TransactionID) 
INNER JOIN
    PolarisTransactions.Polaris.TransactionDetails mon WITH (NOLOCK) ON (th.TransactionID = mon.TransactionID AND mon.TransactionSubTypeID = 141)  
INNER JOIN
    Polaris.Polaris.Organizations torg WITH (NOLOCK) ON (th.OrganizationID = torg.OrganizationID)  
LEFT JOIN
    Polaris.Polaris.FeeReasonCodes frc WITH (NOLOCK) ON (td.numValue = frc.FeeReasonCodeID)

WHERE
    th.TransactionTypeID = 6016 -- Patron accounting payment
AND
    td.TransactionSubTypeID = 8  -- FeeReasonCodeID
AND
    th.TranClientDate BETWEEN '2021-01-01 00:00:00.000' AND '2021-02-22 23:59:59.999'  -- Adjust dates as you like
AND
    th.OrganizationID IN (1,2,3,4,5,6,7,8,9,10)  

GROUP BY
    torg.name,
    frc.FeeDescription
ORDER BY
    torg.name,
    frc.FeeDescription