web
You’re offline. This is a read only version of the page.
close
Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics GP (Archived) / SQL statement for Qrtl...
Microsoft Dynamics GP (Archived)

SQL statement for Qrtly transaction total for Financial Series in GP

(0) ShareShare
ReportReport
Posted on by Bob O'Malley 145

Hello - I'm looking for an easier method for computing the total number of financial transaction on a quarterly basis.  The current method is to run a smartlist for account transactions.  This method is slow and takes way too long to run.  Would prefer a SQL statement if available.

Thanks, Bob

*This post is locked for comments

I have the same question (0)
  • Richard Wheeler Profile Picture
    Richard Wheeler 75,848 Moderator on at

    Please define total number of financial transactions? Do you mean anything that hits any GL account or do you mean just ledger entries that did not originate from a sub-ledger(i.e were directly entered as a ledger entry)?

    You can do something like select count(*) from GL20000 where trxdate >="StartDate" and trxdate<="EndDate". This will simply count the number of transactions. You can also sum amount with other fields.

  • Bob O'Malley Profile Picture
    Bob O'Malley 145 on at

    It would be any transaction that hits the GL either a journal entry or from PM and RM sub-ledger.  Thank you for the info!

  • Victoria Yudin Profile Picture
    Victoria Yudin 22,769 on at

    Bob,

    So only GL entries coming from GL, PM and RM?  What about SOP or POP?  If you're simply not using any other modules, it's easier to take all GL transactions, but if you are looking to pick and choose, you have to be very specific.  :-)

    Also, if one GL entry has 5 GL distributions, does that count as 5 transactions or 1?

  • Bob O'Malley Profile Picture
    Bob O'Malley 145 on at

    Hi Victoria - Yes, GL entries coming from GL, PM and RM.  We are not utilziing SOP or POP.  

    The process now is to run the smartlist -  Financial -> Account Transactions for the financial series within a trx date range.  The total number of transactions returned is what is used.  One GL entry with 5 GL distributions will count as 5 transactions.

    Thanks in advance!

    Bob

  • Victoria Yudin Profile Picture
    Victoria Yudin 22,769 on at

    Bob, if what you're using now works, then what Richard suggested should give you the same results, here is the SQL code for it, just change the dates as needed:

    select count(*) from GL20000 where TRXDATE between '1/1/2012' and '3/31/2012'

    Only issue I can see with this is if you need to get this data from a closed year, in that case, you can use GL30000 instead of GL20000.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Neeraj Kumar – Community Spotlight

We are honored to recognize Neeraj Kumar as our Community Spotlight honoree for…

Congratulations to the November Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics GP (Archived)

#1
mtabor Profile Picture

mtabor 1

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans