UFN_MKTSOURCECODE_GETPOSITION

CREATE function dbo.[UFN_MKTSOURCECODE_GETPOSITION]
(
  @SOURCECODEITEMID uniqueidentifier
)
/*
Returns a table of all of the source code items that have been assigned to the given source code layout
*/
returns nvarchar(7)
as
begin
  declare @SOURCECODEID uniqueidentifier;
  declare @SEQUENCE int;
  declare @LENGTH int;
  declare @TOTALLENGTH int;
  declare @PREVIOUSTOTALLENGTH int;

  select
    @SOURCECODEID = [ITEM].[SOURCECODEID],
    @SEQUENCE = [ITEM].[SEQUENCE],
    @LENGTH = [ITEM].[TOTALLENGTH]
  from
    (select top 1
      [MKTSOURCECODEITEM].[SOURCECODEID], 
      [MKTSOURCECODEITEM].[SEQUENCE], 
      case when len([MKTSOURCECODEPARTDEFINITIONVALUES].[DELIM]) > 0 then [MKTSOURCECODEPARTDEFINITIONVALUES].[LENGTH] + len([MKTSOURCECODEPARTDEFINITIONVALUES].[DELIM]) else [MKTSOURCECODEPARTDEFINITIONVALUES].[LENGTH] end as [TOTALLENGTH]
    from dbo.[MKTSOURCECODEITEM]
    inner join dbo.[MKTSOURCECODEVALIDPARTVALUES] on [MKTSOURCECODEVALIDPARTVALUES].MKTSOURCECODEITEMID = [MKTSOURCECODEITEM].[ID]
    inner join dbo.[MKTSOURCECODEPARTDEFINITIONVALUES] on [MKTSOURCECODEPARTDEFINITIONVALUES].[ID] = [MKTSOURCECODEVALIDPARTVALUES].[MKTSOURCECODEPARTDEFINITIONVALUESID]
    where [MKTSOURCECODEITEM].[ID] = @SOURCECODEITEMID
    group by [MKTSOURCECODEITEM].[SOURCECODEID], [MKTSOURCECODEITEM].[SEQUENCE], [MKTSOURCECODEPARTDEFINITIONVALUES].[LENGTH], [MKTSOURCECODEPARTDEFINITIONVALUES].[DELIM]
    order by [TOTALLENGTH] desc) as [ITEM];

    with [PREVIOUSITEMS] ([SOURCECODEID], [SEQUENCE], [TOTALLENGTH]) as
    (
    select
          [MKTSOURCECODEITEM].[SOURCECODEID], 
          [MKTSOURCECODEITEM].[SEQUENCE], 
          max(case when len([MKTSOURCECODEPARTDEFINITIONVALUES].[DELIM]) > 0 then [MKTSOURCECODEPARTDEFINITIONVALUES].[LENGTH] + len([MKTSOURCECODEPARTDEFINITIONVALUES].[DELIM]) else [MKTSOURCECODEPARTDEFINITIONVALUES].[LENGTH] end) as [TOTALLENGTH]
        from dbo.[MKTSOURCECODEITEM]
        inner join dbo.[MKTSOURCECODEVALIDPARTVALUES] on [MKTSOURCECODEVALIDPARTVALUES].MKTSOURCECODEITEMID = [MKTSOURCECODEITEM].[ID]
        inner join dbo.[MKTSOURCECODEPARTDEFINITIONVALUES] on [MKTSOURCECODEPARTDEFINITIONVALUES].[ID] = [MKTSOURCECODEVALIDPARTVALUES].[MKTSOURCECODEPARTDEFINITIONVALUESID]
        where [SOURCECODEID] = @SOURCECODEID and [SEQUENCE] < @SEQUENCE
        group by [MKTSOURCECODEITEM].[SOURCECODEID], [MKTSOURCECODEITEM].[SEQUENCE], [MKTSOURCECODEPARTDEFINITIONVALUES].[LENGTH]
    )
    select @PREVIOUSTOTALLENGTH = sum([TOTALLENGTH])
    from [PREVIOUSITEMS];

    set @PREVIOUSTOTALLENGTH = isnull(@PREVIOUSTOTALLENGTH, 0);

  return cast(@PREVIOUSTOTALLENGTH + 1 as nvarchar(3))+' - '+ cast(@PREVIOUSTOTALLENGTH + @LENGTH as nvarchar(3));
end