USP_CONSTITUENTSOLICITCODE_ADJUSTSOLICITCODEDATERANGES

CREATE procedure USP_CONSTITUENTSOLICITCODE_ADJUSTSOLICITCODEDATERANGES
(
  @CONSTITUENTID uniqueidentifier,
  @SOLICITCODES xml,
  @CHANGEAGENTID uniqueidentifier = null
)
as
begin
  if @CHANGEAGENTID is null  
    exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;

  declare @CURRENTDATE date = getdate();

  declare @INPUT table
  (
    SOLICITCODEID uniqueidentifier,
    NEW_STARTDATE date,
    NEW_ENDDATE date
  );

  -- Populate temp table with selected data

  insert into @INPUT
  select
    T.c.value('(SOLICITCODEID)[1]','uniqueidentifier') AS 'SOLICITCODEID',
    case when T.c.value('(STARTDATE)[1]','date') ='0001-01-01' then null else  T.c.value('(STARTDATE)[1]','date') end AS 'STARTDATE',
    case when T.c.value('(ENDDATE)[1]','date') ='0001-01-01' then null else  T.c.value('(ENDDATE)[1]','date') end AS 'ENDDATE'
  from @SOLICITCODES.nodes('/CONSENT/ITEM') T(c);

  begin try
    with UPDATEROWS as
    (
      select
        ROW_NUMBER() over (partition by CONSTITUENTSOLICITCODE.CONSTITUENTID, CONSTITUENTSOLICITCODE.SOLICITCODEID order by CONSTITUENTSOLICITCODE.STARTDATE desc) as ROWNUMBER,
        CONSTITUENTSOLICITCODE.ID as CONSTITUENTSOLICITCODEID,
        INPUT.NEW_STARTDATE,
        INPUT.NEW_ENDDATE
      from @INPUT INPUT
      inner join dbo.CONSTITUENTSOLICITCODE on INPUT.SOLICITCODEID = CONSTITUENTSOLICITCODE.SOLICITCODEID
      inner join dbo.SOLICITCODE on CONSTITUENTSOLICITCODE.SOLICITCODEID = SOLICITCODE.ID
      where
        CONSTITUENTSOLICITCODE.CONSTITUENTID = @CONSTITUENTID
        and [dbo].[UFN_DATE_GETEARLIESTTIME](CONSTITUENTSOLICITCODE.STARTDATE) < [dbo].[UFN_DATE_GETEARLIESTTIME] (INPUT.NEW_STARTDATE)
        and CONSTITUENTSOLICITCODE.ENDDATE is null
    )
    update dbo.CONSTITUENTSOLICITCODE
    set
      ENDDATE = case when UPDATEROWS.NEW_ENDDATE is null then dateadd(day, -1, UPDATEROWS.NEW_STARTDATE)
                else UPDATEROWS.NEW_ENDDATE
                end,
      DATECHANGED = @CURRENTDATE,
      CHANGEDBYID = @CHANGEAGENTID
    from UPDATEROWS
    where
      CONSTITUENTSOLICITCODE.ID = UPDATEROWS.CONSTITUENTSOLICITCODEID
      and UPDATEROWS.ROWNUMBER = 1;
  end try
  begin catch
    exec dbo.USP_RAISE_ERROR;
    return 1;
  end catch

  return 0;
end