Alerts for Number sequence numbers running out

we recently faced a problem where a number sequence reached its limit, although it was a bug in configuration and number sequence should be set to range like 9 digits 999,999,999, but this query below can be used to identify the number sequence which are reaching to its limit. 
One can setup some alerts or some batch job that may be run on monthly basis and produce number sequences that are reaching consumption to let say 70%

 SELECT [NUMBERSEQUENCE]  
    ,[TXT]  
    ,[LOWEST]  
    ,[HIGHEST]  
    ,[NEXTREC]  
       ,100 * (NEXTREC - LOWEST)/(HIGHEST - LOWEST) as 'Percent Consumed'   
  FROM [dbo].[NUMBERSEQUENCETABLE]  
  where (NEXTREC - LOWEST) >= (HIGHEST - LOWEST) * 0.2  

 

SQL Query fun: Dynamics AX 2012 Main Account – Financial dimension read

Hi,

This is new blog on knowledge share, today just got chance to work with SQL queries; i was just exploring the dimension extract for one of our customer, i produce this sql query which show main account and financial dimensions associated with main account; i though it is worth to share with AX community also.

-- This script is designed to show the values in columns for better read
-- Deleting if tmp table already exists
Begin
drop
table #MainAccountDimensionTmp
End -- creating new Tmp table.
Begin CREATE TABLE #MainAccountDimensionTmp
(
MainAccount nvarchar(100),
DisplayValue nvarchar(100),
Name nvarchar(100)
) End 

-- selecting main accounts and all financial dimensions and storing into tmp table
Begin Insert into #MainAccountDimensionTmp (Name, DisplayValue, MainAccount)
select
ddv.NAME,
ddv.DISPLAYVALUE,
MAINACCOUNT.MAINACCOUNTID
from
MainAccountLegalEntity as MALE
join MAINACCOUNT on MAINACCOUNT.RECID = MALE.MAINACCOUNT
join DEFAULTDIMENSIONVIEW as ddv on MALE.DEFAULTDIMENSION = ddv.DEFAULTDIMENSION --where MAINACCOUNTid = 'test01'
end 

-- reading again from tmp table in way that values are showin in column.
-- please add as many dimesions as you want, like i have added 4 dimensins below.
Select
MainAccount,
MAX(
Case Name When 'Department' then DISPLAYVALUE ELSE '' END
) AS 'Department',
MAX(
Case Name when 'ICP' then DisplayValue ELSE '' END
) AS 'ICP',
MAX(
Case Name when 'MISC' then DisplayValue ELSE '' END
) AS 'MISC',
MAX(
Case Name when 'Expense_Class' then DisplayValue ELSE '' END
) AS 'Expense Class'
from
#MainAccountDimensionTmp Group by MainAccount


--select * from DEFAULTDIMENSIONVIEW

Query results: