cv304
9/8/2017 - 4:16 PM

Check Date using Transaction Table

https://www.linkedin.com/groups/1276507/1276507-6004367711256268800

Hello,

Curious if anyone knows how to fix this formula to pull the check date. I am using the Transaction table but need the check date as a reference.

I am pulling Vendor, Project ID, Cost and Vendor Type as my columns with the formula attempting to pull check date.

Ultimately I need to get data that shows project info, vendor, payment amount, vendor type or activity, and payment date so if there are any suggestions to get this data but from a different table, I'm all ears.

SELECT TOP 1 CONVERT(VARCHAR, beDate, 110) FROM AxTransaction JOIN AxGLDetail ON gldTransaction = tKey JOIN AxBankEntry ON gldBankEntry = beKey WHERE tKey = [Transaction Key] ORDER BY beDate

Thank you in advance!

Ajera-transaction-checkdate.sql
content_copyfile_download
/* Courtesy MJ Brennan posted to https://www.linkedin.com/groups/1276507/1276507-6004367711256268800 */
/* Transaction Base */
SELECT TOP (100) PERCENT MAX(CONVERT(VARCHAR, dbo.AxBankEntry.beDate, 110)) AS BE_Date, dbo.AxTransaction.tKey
FROM dbo.AxTransaction INNER JOIN
dbo.AxGLDetail ON dbo.AxGLDetail.gldTransaction = dbo.AxTransaction.tKey INNER JOIN
dbo.AxBankEntry ON dbo.AxGLDetail.gldBankEntry = dbo.AxBankEntry.beKey
GROUP BY dbo.AxTransaction.tKey
HAVING (dbo.AxTransaction.tKey = [Transaction Key] )