View: dbo.Calculated_Numeric_GL_Account_Number_Metrics_View
View definition
-- drop view dbo.Calculated_Numeric_GL_Account_Number_Metrics_View
create view dbo.Calculated_Numeric_GL_Account_Number_Metrics_View
as
select
c.client_KEY
, isnull(metrics.max_digits_to_left_of_decimal, 0) as max_digits_to_left_of_decimal
, isnull(metrics.max_digits_to_right_of_decimal, 0) as max_digits_to_right_of_decimal
from
dbo.Client as c
left join
(
select
client_KEY
, max(case when charindex(N'.', gl_account_number) > 0
then charindex(N'.', gl_account_number) - 1
else len(gl_account_number) end) as max_digits_to_left_of_decimal
, max(case when charindex(N'.', gl_account_number) > 0
then len(gl_account_number) - charindex(N'.', gl_account_number)
else 0 end) as max_digits_to_right_of_decimal
from
dbo.GL_Account
where
-- Only analyze numeric account numbers (all digits with one optional decimal).
patindex(N'%[^0-9.]%',gl_account_number) = 0 and patindex(N'%.%.%',gl_account_number) = 0
group by
client_KEY
) as metrics
on c.client_KEY = metrics.client_KEY
where
c.client_type_KEY in (1, 2, 3, 5); -- Standard, Firm, Template, Consolidated Client