Moderator: Lisa Ennis
I am trying to do some analysis on receipts over a certain time period to determine whereby I am looking to determine what % of receipts are of type Incoming Wire. I would also like to understand what % of those wires are coming from different contact types (i.e. Buyers, Lenders, etc.). I found the payorpayee column in the pt.transaction table, but would prefer to use the payor code field associated with the receipt. Is this column available in the database somewhere?
Code: Select all
SELECT * FROM pt.[Transaction] AS T INNER JOIN pt.TransactionExtended AS TE ON T.[GUID]=TE.ID INNER JOIN [pt].[TransactionDetail] AS TD ON TD.ID = T.DetailID INNER JOIN pt.Ledger AS L ON T.LedgerID=L.[ID] INNER JOIN zref.TransactionType AS TT ON T.Kind=TT.Code INNER JOIN zref.TransactionStatus AS zTS ON T.[Status]=zTS.ID INNER JOIN pt.TransactionAttribute AS TA ON TA.ID = T.AttributeID INNER JOIN pt.ContactDetail AS OC ON TA.OrderContactID=OC.[ID] INNER JOIN zref.ContactType AS zCT ON OC.[Type]=zCT.ID LEFT OUTER JOIN (SELECT [Guid], [ID] FROM [pt].[Transaction]) AS TTX ON TTX.[ID] = T.[RelatedTransactionID] LEFT OUTER JOIN pt.GroupDeposit AS GDEP ON TD.GroupDepositID=GDEP.ID LEFT OUTER JOIN zref.MediumType AS zM ON T.MediumID = zM.[ID] LEFT OUTER JOIN (SELECT TTX.[Guid], TTX.[ID], TXL.Name AS TransferToFrom_LedgerName FROM pt.[Transaction] AS TTX INNER JOIN pt.Ledger AS TXL ON TTX.LedgerID=TXL.ID ) AS TTF ON TTF.ID = T.RelatedTransactionID