USE [gsa2]
GO
/****** Object:  UserDefinedFunction [dbo].[fn_get_assign_id]    Script Date: 08/15/2013 19:29:29 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER   fUNCTION [dbo].[fn_get_assign_id] (@pk_work_order_id uniqueidentifier)
Returns  nvarchar(max)
As
-- select dbo.fn_get_assign_id('10bd5def-d809-4fe6-9175-ef176bde9c07')
Begin
declare @var nvarchar(max)
declare @count int
set @var = ''

declare @RET table (id int identity, name nvarchar(max))


declare @IssueUserCount table (UserCount int,name nvarchar(max),pk_organization_id uniqueidentifier)

declare @OrganizationUserCount table (UserCount int,name nvarchar(max),pk_organization_id uniqueidentifier)

declare @temp table (UserCount int,pk_organization_id uniqueidentifier)
--return


insert into  @IssueUserCount
 Select Count(1) as UserCount, vgo.name,vgo.pk_organization_id

        from  vw_get_organization vgo
              Inner Join vw_get_user vgu on vgu.fk_organization_id=vgo.pk_organization_id
              Inner Join tbl_work_order_owner_linkup tiol on tiol.fk_user_id=vgu.pk_user_id
              where tiol.fk_work_order_id=@pk_work_order_id
              group by vgo.name,vgo.pk_organization_id
              order by vgo.name

         insert into @OrganizationUserCount
 Select count(1) as UserCount ,vgo.name ,vgo.pk_organization_id

                from  vw_get_organization vgo
                        Inner Join vw_get_user vgu on vgu.fk_organization_id=vgo.pk_organization_id
                group by vgo.name,vgo.pk_organization_id
                order by vgo.name

            insert into @temp
 Select uc.USerCount,uc.pk_organization_id
  from @IssueUserCount uc

               inner join @OrganizationUserCount ouc on ouc.pk_organization_id=uc.pk_organization_id
                and uc.UserCount<>ouc.UserCount
 insert into @RET
 Select uc.name as UserOrganizationname

        from @IssueUserCount uc
        inner join @OrganizationUserCount ouc on ouc.pk_organization_id=uc.pk_organization_id
                   and uc.UserCount=ouc.UserCount
 Union
 Select distinct cast(vgu.pk_user_id as varchar(50)) as UserId
                  from tbl_work_order_owner_linkup tiol
                               inner join vw_get_user vgu on vgu.pk_user_id=tiol.fk_user_id
                               inner join vw_get_organization vgo on vgo.pk_organization_id=vgu.fk_organization_id
                               where vgo.pk_organization_id in (select pk_organization_id from @temp)
                              and tiol.fk_work_order_id=@pk_work_order_id

  select @count = count(1)  from @RET

  while (@count > 0 )
  BEGIN

   select  @var =  @var + name+',' from @RET
   where id = @count

  set  @count = @count - 1

  END

   if(len(@var)>0)
   begin
   select  @var =  substring (@var,1,len(@var)-1)
   end

  return @var

 --drop table @temp
 --drop table @IssueUserCount
 --drop table @OrganizationUserCount


End