UFN_SALESORDERITEMTICKETCOMBINATION_GETCOMBINATIONNUMBER

CREATE function dbo.UFN_SALESORDERITEMTICKETCOMBINATION_GETCOMBINATIONNUMBER
(
  @TICKETCOMBINATIONID uniqueidentifier
)
returns nvarchar(10)
with execute as caller
as begin
  declare @NUMBERTABLE table (
    NUMBER int not null identity (1, 1),
    TICKETCOMBINATIONID uniqueidentifier,
    DATEADDED datetime
  );
  declare @ORDERID uniqueidentifier
  declare @COMBINATIONID uniqueidentifier

  select @ORDERID = SALESORDERITEM.SALESORDERID, @COMBINATIONID = SALESORDERITEMTICKETCOMBINATION.COMBINATIONID
  from dbo.SALESORDERITEM 
  inner join dbo.[SALESORDERITEMTICKET] on [SALESORDERITEM].[ID] = [SALESORDERITEMTICKET].[ID]
  inner join dbo.[SALESORDERITEMTICKETCOMBINATION] on [SALESORDERITEMTICKET].[ID] = [SALESORDERITEMTICKETCOMBINATION].[ID]
  where SALESORDERITEMTICKETCOMBINATION.TICKETCOMBINATIONID = @TICKETCOMBINATIONID

  insert into @NUMBERTABLE (TICKETCOMBINATIONID, DATEADDED)
  select distinct TICKETCOMBINATIONID, min(SALESORDERITEM.DATEADDED)
  from dbo.SALESORDERITEM
    inner join dbo.[SALESORDERITEMTICKET] on [SALESORDERITEM].[ID] = [SALESORDERITEMTICKET].[ID]
    inner join dbo.[SALESORDERITEMTICKETCOMBINATION] on [SALESORDERITEMTICKET].[ID] = [SALESORDERITEMTICKETCOMBINATION].[ID]
    inner join dbo.[COMBINATION] on [SALESORDERITEMTICKETCOMBINATION].[COMBINATIONID] = [COMBINATION].[ID]
    inner join dbo.[COMBINATIONPRICETYPE] on [COMBINATIONPRICETYPE].PRICETYPECODEID  = [SALESORDERITEMTICKET].[PRICETYPECODEID]
                                            and [COMBINATION].[ID] = [COMBINATIONPRICETYPE].[COMBINATIONID] 
  where SALESORDERITEMTICKETCOMBINATION.COMBINATIONID = @COMBINATIONID and SALESORDERITEM.SALESORDERID = @ORDERID and
      SALESORDERITEMTICKET.PRICETYPECODEID in (
      select top 1 COMBINATIONPRICETYPE.PRICETYPECODEID from dbo.COMBINATIONPRICETYPE
                inner join dbo.SALESORDERITEMTICKETCOMBINATION SOITC on COMBINATIONPRICETYPE.COMBINATIONID = SOITC.COMBINATIONID
                inner join dbo.SALESORDERITEMTICKET on COMBINATIONPRICETYPE.PRICETYPECODEID = SALESORDERITEMTICKET.PRICETYPECODEID 
                where SOITC.ID = SALESORDERITEMTICKET.ID and SOITC.TICKETCOMBINATIONID = SALESORDERITEMTICKETCOMBINATION.TICKETCOMBINATIONID 
    )
    group by TICKETCOMBINATIONID 
  order by min(SALESORDERITEM.DATEADDED)

  declare @NUMBER nvarchar(10)
  select @NUMBER = convert(nvarchar(10), NUMBER) from @NUMBERTABLE where TICKETCOMBINATIONID = @TICKETCOMBINATIONID
  return @NUMBER

end