# Weekly Dynamic:Accounts Over Budget SQL for Dynamics GP

- URL: https://dynamicaccounting.net/weekly-dynamicaccounts-over-budget-sql-for-dynamics-gp/
- Date: 2010-07-19
- Topics: General Ledger & Financials, Tips & Hidden Features
- Type: original article

I’m wrapping up my metrics series today with some SQL code that shows GL accounts that are over budget. In the past I’ve used this as part of a regular alert to see accounts over budget. It can also be tweaked to show accounts that are approaching budget. This script is already [available for download](https://app.box.com/shared/tgvmqjg0k8).

```
 
```

–Displays accounts overbudget for the month as of today.

–Set your budget ID in place of ‘Budget 2008’. Leave in the single quotes.

declare @BudgetID as varchar(30)

Set @BudgetID=’Budget 2008′

SELECT left(GL00105.ACTNUMST,15) as Account,

left(GL00100.ACTDESCR,15) as Description,

left(GL10110.PERIODID,5) as Period,

right(convert (nchar,cast(GL10110.PERDBLNC as Money),1),12) as GL,

right(convert(nchar, cast(GL00201.BUDGETAMT as money),1),12) as Budget,

right (convert (nchar,cast(GL10110.PERDBLNC – GL00201.BUDGETAMT as money),1),12) AS Overbudget

FROM GL00105 INNER JOIN

GL00100 ON GL00105.ACTINDX = GL00100.ACTINDX INNER JOIN

GL10110 ON GL00105.ACTINDX = GL10110.ACTINDX INNER JOIN

GL00201 ON GL10110.ACTINDX = GL00201.ACTINDX AND GL10110.PERIODID = GL00201.PERIODID AND

GL10110.PERDBLNC > GL00201.BUDGETAMT INNER JOIN

SY40100 ON GL10110.PERIODID = SY40100.PERIODID AND GL10110.YEAR1 = SY40100.YEAR1

WHERE (GL10110.YEAR1 = year(getdate())) AND (GL00201.BUDGETID = @BudgetID)

AND (GL10110.PERDBLNC – GL00201.BUDGETAMT > 100) AND ((GETDATE()

>= SY40100.PERIODDT) AND (GETDATE() <= SY40100.PERDENDT))and sy40100.ODESCTN=’General Entry’

Don’t forget to subscribe to the DynamicAccounting.net newsletter for monthly news and tips on Dynamics GP.