USP_DATALIST_DIRECTMARKETINGEFFORTBATCHTESTSEGMENT

CREATE procedure dbo.[USP_DATALIST_DIRECTMARKETINGEFFORTBATCHTESTSEGMENT]
(
  @BATCHID uniqueidentifier,
  @TESTSEGMENTID uniqueidentifier
)
as
  set nocount on;

  if exists(select * from dbo.[MKTSEGMENTATIONTESTSEGMENT] where [ID] = @TESTSEGMENTID)
    --Grab the data for the test segment if it already exists...

    select top 1
      cast(1 as bit) as [EXISTINGTESTSEGMENT],
      [MKTSEGMENTATIONTESTSEGMENT].[NAME],
      [MKTSEGMENTATIONTESTSEGMENT].[DESCRIPTION],
      [MKTSOURCECODEPARTDEFINITIONVALUES].[ID] as [CODEVALUEID],
      [MKTSOURCECODEPARTDEFINITIONVALUES].[DESCRIPTION] as [CODEVALUE],
      (select [FORMAT] from dbo.[MKTSOURCECODEPARTDEFINITIONVALUES] where [ID] = [MKTSEGMENTATIONTESTSEGMENT].[TESTPARTDEFINITIONVALUESID]) as [CODEFORMAT],
      (select [REGEX] from dbo.[MKTSOURCECODEPARTDEFINITIONVALUES] where [ID] = [MKTSEGMENTATIONTESTSEGMENT].[TESTPARTDEFINITIONVALUESID]) as [CODEREGEX],
      [MKTSEGMENTATIONTESTSEGMENT].[TESTSEGMENTCODE] as [CODE],
      [MKTSEGMENTATIONTESTSEGMENT].[GIFTAMOUNT] as [EXPECTEDGIFTAMOUNT],
      [MKTSEGMENTATIONTESTSEGMENT].[RESPONSERATE] as [EXPECTEDRESPONSERATE],
      [MKTSEGMENTATIONTESTSEGMENT].[SAMPLESIZE] as [QUANTITY],
      [MKTSEGMENTATIONTESTSEGMENT].[PACKAGEID]
    from dbo.[MKTSEGMENTATIONTESTSEGMENT]
    left join dbo.[MKTSOURCECODEPARTDEFINITIONVALUES] on [MKTSOURCECODEPARTDEFINITIONVALUES].[ID] = [MKTSEGMENTATIONTESTSEGMENT].[TESTPARTDEFINITIONVALUESID]
    where [MKTSEGMENTATIONTESTSEGMENT].[ID] = @TESTSEGMENTID;
  else
    --If the test segment does not exist yet, then grab the data from the batch table...

    select top 1
      cast(0 as bit) as [EXISTINGTESTSEGMENT],
      [BATCHDIRECTMARKETINGEFFORTTESTSEGMENT].[NAME],
      [BATCHDIRECTMARKETINGEFFORTTESTSEGMENT].[DESCRIPTION],
      [MKTSOURCECODEPARTDEFINITIONVALUES].[ID] as [CODEVALUEID],
      [MKTSOURCECODEPARTDEFINITIONVALUES].[DESCRIPTION] as [CODEVALUE],
      (select [FORMAT] from dbo.[MKTSOURCECODEPARTDEFINITIONVALUES] where [ID] = [BATCHDIRECTMARKETINGEFFORTTESTSEGMENT].[CODEPARTDEFINITIONVALUESID]) as [CODEFORMAT],
      (select [REGEX] from dbo.[MKTSOURCECODEPARTDEFINITIONVALUES] where [ID] = [BATCHDIRECTMARKETINGEFFORTTESTSEGMENT].[CODEPARTDEFINITIONVALUESID]) as [CODEREGEX],
      [BATCHDIRECTMARKETINGEFFORTTESTSEGMENT].[CODE],
      [BATCHDIRECTMARKETINGEFFORTTESTSEGMENT].[EXPECTEDGIFTAMOUNT],
      [BATCHDIRECTMARKETINGEFFORTTESTSEGMENT].[EXPECTEDRESPONSERATE],
      [BATCHDIRECTMARKETINGEFFORTTESTSEGMENT].[QUANTITY],
      isnull([BATCHDIRECTMARKETINGEFFORTPACKAGE].[MKTPACKAGEID], [BATCHDIRECTMARKETINGEFFORTPACKAGE].[ID]) as [PACKAGEID]
    from dbo.[BATCHDIRECTMARKETINGEFFORT]
    inner join dbo.[BATCHDIRECTMARKETINGEFFORTTESTSEGMENT] on [BATCHDIRECTMARKETINGEFFORTTESTSEGMENT].[ID] = [BATCHDIRECTMARKETINGEFFORT].[BATCHDIRECTMARKETINGEFFORTTESTSEGMENTID]
    left join dbo.[BATCHDIRECTMARKETINGEFFORTPACKAGE] on [BATCHDIRECTMARKETINGEFFORTPACKAGE].[ID] = [BATCHDIRECTMARKETINGEFFORT].[BATCHDIRECTMARKETINGEFFORTPACKAGEID]
    left join dbo.[MKTSOURCECODEPARTDEFINITIONVALUES] on [MKTSOURCECODEPARTDEFINITIONVALUES].[ID] = [BATCHDIRECTMARKETINGEFFORTTESTSEGMENT].[CODEPARTDEFINITIONVALUESID]
    where [BATCHDIRECTMARKETINGEFFORT].[BATCHID] = @BATCHID
    and [BATCHDIRECTMARKETINGEFFORTTESTSEGMENT].[ID] = @TESTSEGMENTID;

  return 0;