USP_MKTSEGMENTWHITEMAILREFRESHALLPROCESS_GETSEGMENTS

CREATE procedure dbo.[USP_MKTSEGMENTWHITEMAILREFRESHALLPROCESS_GETSEGMENTS]
as
  set nocount on;

  declare @NOW datetime = getdate();

  select 
    [MKTSEGMENTWHITEMAIL].[ID],
    [MKTSEGMENT].[NAME] as [SEGMENTNAME],
    [MKTSEGMENT].[SEGMENTTYPECODE],
    [MKTSEGMENTWHITEMAILREFRESHPROCESS].[ID] as [PARAMETERSETID]
  from dbo.[MKTSEGMENTWHITEMAIL]
  inner join dbo.[MKTSEGMENT] on [MKTSEGMENT].[ID] = [MKTSEGMENTWHITEMAIL].[ID]
  inner join dbo.[MKTSEGMENTWHITEMAILREFRESHPROCESS] on [MKTSEGMENTWHITEMAILREFRESHPROCESS].[SEGMENTID] = [MKTSEGMENT].[ID]
  where
    [MKTSEGMENTWHITEMAIL].[STATUSCODE] = 0
    or
    ([MKTSEGMENTWHITEMAIL].[STATUSCODE] = 1 and @NOW between isnull([MKTSEGMENTWHITEMAIL].[ACTIVEFROM], dateadd(d, -1, @NOW)) and isnull(dateadd(d, 1, [MKTSEGMENTWHITEMAIL].[ACTIVETO]), dateadd(d, 1, @NOW)));

  return 0;