USP_MKTSEGMENTGROUPREFRESHPROCESS_GETSEGMENTS

create procedure dbo.[USP_MKTSEGMENTGROUPREFRESHPROCESS_GETSEGMENTS]
(
  @SEGMENTGROUPID uniqueidentifier
)
as
  set nocount on;

  select 
    [MKTGROUPSEGMENTS].[SEGMENTID],
    [MKTSEGMENT].[NAME] as [SEGMENTNAME],
    [MKTSEGMENT].[SEGMENTTYPECODE],
    coalesce([MKTSEGMENTREFRESHPROCESS].[ID], [MKTSEGMENTWHITEMAILREFRESHPROCESS].[ID], '00000000-0000-0000-0000-000000000000') as [PARAMETERSETID]
  from dbo.[MKTSEGMENT]
  inner join dbo.[MKTGROUPSEGMENTS] on [MKTSEGMENT].[ID] = [MKTGROUPSEGMENTS].[SEGMENTID]
  left outer join dbo.[MKTSEGMENTREFRESHPROCESS] on [MKTSEGMENTREFRESHPROCESS].[SEGMENTID] = [MKTSEGMENT].[ID]
  left outer join dbo.[MKTSEGMENTWHITEMAILREFRESHPROCESS] on [MKTSEGMENTWHITEMAILREFRESHPROCESS].[SEGMENTID] = [MKTSEGMENT].[ID]
  left outer join dbo.[MKTSEGMENTLIST] on [MKTSEGMENTLIST].[ID] = [MKTSEGMENT].[CURRENTSEGMENTLISTID]
  where [MKTGROUPSEGMENTS].[SEGMENTGROUPID] = @SEGMENTGROUPID
  and ([MKTSEGMENT].[SEGMENTTYPECODE] <> 2 or [MKTSEGMENTLIST].[PARENTSEGMENTID] is not null)
  and [MKTSEGMENT].[SEGMENTTYPECODE] not in (6, 7, 8);

  return 0;