Open Forum

 View Only
  • 1.  SQL Query for 6/30/2022 balance for accounts beginning with 1400 and 2400

    SILVER CONTRIBUTOR
    Posted Jul 13, 2022 11:07 AM
    Edited by Heidi Gibbons Jul 13, 2022 11:31 AM
    I have 80 databases and I am trying to figure out what query to create in order to return the ending balance of all account beginning with 1400 and 2400. I also want it to include the account description and database name. It seems simple enough but I am having issues. I am currently opening every database and running a trial balance with those parameters and it is taking a long time. I do this every quarter so any help would be greatly appreciated. Also, can anyone recommend a good course to take? I have taken SQL classes at the GPUG Conferences but want more knowledge than they have to offer.

    ------------------------------
    Heidi Gibbons
    Senior Accountant
    Walnut Ridge Strategic Management Company, LLC
    Akron OH
    ------------------------------


  • 2.  RE: SQL Query for 6/30/2022 balance for accounts beginning with 1400 and 2400

    TOP CONTRIBUTOR
    Posted Jul 14, 2022 08:12 AM
    Try adding your SQL to this code. It will loop through every GP company database. 

    DB_name() AS [Database] will include a column with the DB  name and change as it loops through the companies.

    Replace my Top 50 SQL code with your own.
    Important: Double up on any single quotes in your code. Any ' becomes '' (two single quotes.)

    ----------------------------------------------
    -- Top 50 Table Row Counts Each GP Company
    -- Recommend Setting Query > Results to File
    ----------------------------------------------
    EXEC sp_MSforeachdb
    ' use ?
    if exists ( select INTERID from DYNAMICS..SY01500 D where INTERID = ''?'' )
    begin
    /* this is a Great Plains company db */
    --print ''Processing database ?''
    /* Insert your script below this Line, Note: Double up single quotes */

    SELECT TOP 50
    @@Servername as [SQL Server]
    ,DB_name() AS [Database]
    ,(SCHEMA_NAME(A.schema_id) + ''.'' + A.Name) AS TableName
    ,SUM(B.rows) AS RecordCount
    FROM sys.objects A
    INNER JOIN sys.partitions B ON A.object_id = B.object_id
    WHERE A.type = ''U''
    GROUP BY A.schema_id, A.Name
    Order by SUM(B.rows) Desc

    /* Insert your script above this Line, Note: Double up single quotes */
    end
    '
    ---****************************************************************************************************


    ------------------------------
    David Morinello
    Senior Developer
    TruckPro LLC
    Cordova TN
    ------------------------------



  • 3.  RE: SQL Query for 6/30/2022 balance for accounts beginning with 1400 and 2400

    SILVER CONTRIBUTOR
    Posted Jul 14, 2022 09:07 AM
    Can that script be leveraged in SmartList builder to become a smartlist/excel report?

    I have built a bunch of SQL Union queries where each DB is called out specifically, it would be great to change the code over to this!

    ------------------------------
    Mike Bresnan
    Arabella Advisors
    Washington MD
    ------------------------------



  • 4.  RE: SQL Query for 6/30/2022 balance for accounts beginning with 1400 and 2400

    TOP CONTRIBUTOR
    Posted Jul 14, 2022 12:38 PM
    It could, but SmartList Builder has an option for a Multicompany SmartList.
    Add a calculated field to identify the company.
    Make sure your "Maximum records" are high enough to not drop off one or more companies.

    If the result set is too large for SmartList, try SSRS.





    ------------------------------
    David Morinello
    Senior Developer
    TruckPro LLC
    Cordova TN
    ------------------------------



  • 5.  RE: SQL Query for 6/30/2022 balance for accounts beginning with 1400 and 2400

    SILVER CONTRIBUTOR
    Posted Jul 22, 2022 09:48 AM
    ah, looks like this actually runs a query for each DB, rather than one body of data for all companies (unless i'm doing it wrong?). this DQ's this solution for me, but if it's more of a report you are looking for @Heidi Gibbons, just change your results over to Text, and this script would be super useful.

    That being said, I did not know about the DB_Name() function until now, that was what was stopping me from using the multicompany smartlists!! so thanks @David Morinello !!​​

    ------------------------------
    Mike Bresnan
    Arabella Advisors
    Washington MD
    ------------------------------



If you've found this thread useful, dive deeper into User Group community content by role