View: dbo.GL_Account_Segment_Code_Sort_View
View definition
--drop view dbo.GL_Account_Segment_Code_Sort_View
create view dbo.GL_Account_Segment_Code_Sort_View
as
select
m.gl_account_segment_KEY
, c.gl_account_segment_code_KEY
, case
-- A numeric segment code. It's assigned a sort string with padding (of zeroes) to the right and left of the
-- segment code such that the decimal point (if any) will line up with all other numeric account number
-- decimal points.
when patindex(N'%[^0-9.]%',c.code) = 0 and patindex(N'%.%.%',c.code) = 0
then
replicate(N'0', m.max_left__char_count - c.left__char_count)
+
left(c.code, c.left__char_count)
+
N'.'
+
right(c.code, c.right__char_count)
+
replicate(N'.', m.max_right__char_count - c.right__char_count)
-- An alpha-numeric account number. Its sort string is simply itself.
else
c.code
end as code_sortable
from
(
select
gl_account_segment_KEY
, max_left__char_count = max(case when charindex(N'.', code) > 0 then charindex(N'.', code) - 1 else len(code) end)
, max_right__char_count = max(case when charindex(N'.', code) > 0 then len(code) - charindex(N'.', code) else 0 end)
from
dbo.GL_Account_Segment_Code
group by
gl_account_segment_KEY
-- The mask metrics should be able to be derived less expensively from the GL_Account_Segment table but this
-- won't work until we have constraints in place that force the segment codes to match the mask's pattern. For
-- now, this code has to remain commented out and the metrics will be gathered directly from existing codes.
) as m
inner join
(
select
gl_account_segment_KEY
, gl_account_segment_code_KEY
, code
, left__char_count = case when charindex(N'.', code) > 0 then charindex(N'.', code) - 1 else len(code) end
, right__char_count = case when charindex(N'.', code) > 0 then len(code) - charindex(N'.', code) else 0 end
from
dbo.GL_Account_Segment_Code
) as c
on m.gl_account_segment_KEY = c.gl_account_segment_KEY