Removing MOS from multiple performances

You can't delete a MOS from multiple performances in Season Manager.  If the need arises, what we do is set the active date to a past date so it's not available to that MOS anymore.

And the answer to your second question, I believe, is no as well.  It's just a static date, not a relative one.

You can't delete a MOS from multiple performances in Season Manager.  If the need arises, what we do is set the active date to a past date so it's not available to that MOS anymore.

And the answer to your second question, I believe, is no as well.  It's just a static date, not a relative one.

Oh well.... thanks for the speedy response

We use a stored procedure and custom report to make the Web publish dates relative to the performance date when we need to (though we don't do it that often).  Below is the stored procedure the report calls.  This runs on the safe side and will run in a "preview" mode unless the parameter "Preview Changes Only" is set to No explicitly.  That means it will return the list of performances with the proposed changes, but will not have actually changed anything.

Example usage, changes the start web publish date of the performance to be 10 days and 10 minutes before the date of the performance:

EXEC

dbo].[LP_UPDATE_PERF_WEB_DATES_RELATIVE] @perf_nos = '218675', @adjust_startdate_by_days = -10, @adjust_startdate_by_minutes = -10, @preview_changes_only = 'N'

The Procedure:

ALTER PROCEDURE [dbo].[LP_UPDATE_PERF_WEB_DATES_RELATIVE] (

      @perf_nos VARCHAR(2000) = NULL,

      @season_nos VARCHAR(2000) = NULL,

      @adjust_startdate_by_days INT = 0,

      @adjust_startdate_by_minutes INT = 0,

      @adjust_enddate_by_days INT = 0,

      @adjust_enddate_by_minutes INT = 0,

      @preview_changes_only CHAR(1) = 'Y'

)

AS

DECLARE @err VARCHAR(1000)

--Check to see if all the perfs exist

IF @perf_nos IS NOT NULL AND NOT EXISTS (SELECT * FROM T_PERF WHERE CHARINDEX(',' + CAST(perf_no AS VARCHAR) + ',', ',' + @perf_nos + ',') > 0)

BEGIN

      RAISERROR('One or more of the selected performances could not be found.',11,2) WITH SETERROR

      RETURN

END

--Check to see if all the seasons exist and have performances

IF @season_nos IS NOT NULL

BEGIN

      IF NOT EXISTS(SELECT * FROM TR_SEASON WHERE CHARINDEX(',' + CAST(id AS VARCHAR) + ',', ',' + @season_nos + ',') > 0)

      BEGIN

            RAISERROR('One or more of the selected seasons do not exist.', 11, 2) WITH SETERROR

            RETURN

      END

      IF NOT EXISTS(SELECT * FROM T_PERF WHERE CHARINDEX(',' + CAST(season AS VARCHAR) + ',', ',' + @season_nos + ',') > 0)

      BEGIN

            RAISERROR('One or more of the selected seasons has no performances.',11,2) WITH SETERROR

            RETURN

      END

END

/*

Perfs and seasons check out, so check out rest of parameters and process accordingly

*/

IF (@adjust_startdate_by_days <> 0 OR @adjust_startdate_by_minutes <> 0 OR @adjust_enddate_by_days <> 0 OR @adjust_enddate_by_minutes <> 0) AND (ISNULL(@perf_nos, ISNULL(@season_nos, '')) <> '')

BEGIN

      SET NOCOUNT ON

      --Put the list of performances to be altered in a temp table so the list can be returned for confirmation

      SELECT

            P.perf_no,

            P.perf_code,

            P.perf_dt,

            old_publish_webapi_start = P.publish_webapi_start,

            old_publish_webapi_end = P.publish_webapi_end,

            new_publish_webapi_start = CASE WHEN @adjust_startdate_by_minutes <> 0 AND @adjust_startdate_by_days <> 0 THEN DATEADD(mi, @adjust_startdate_by_minutes, DATEADD(dd, @adjust_startdate_by_days, P.perf_dt)) ELSE P.publish_webapi_end END,

            new_publish_webapi_end = CASE WHEN @adjust_enddate_by_minutes <> 0 AND @adjust_enddate_by_days <> 0 THEN DATEADD(mi, @adjust_enddate_by_minutes, DATEADD(dd, @adjust_enddate_by_days, P.perf_dt)) ELSE P.publish_webapi_end END,

            changes_made = 'N'

      INTO #Perfs

      FROM T_PERF P

      WHERE

            CASE

                  WHEN ISNULL(@perf_nos,'') <> '' THEN CHARINDEX(',' + CAST(perf_no AS VARCHAR) + ',', ',' + @perf_nos + ',')

                  WHEN ISNULL(@season_nos,'') <> '' THEN CHARINDEX(',' + CAST(season AS VARCHAR) + ',', ',' + @season_nos + ',')

            END > 0

      IF @preview_changes_only = 'N'

      BEGIN

            BEGIN TRANSACTION

            BEGIN TRY

                  UPDATE P

                  SET P.publish_webapi_start = P2.new_publish_webapi_start,

                        P.publish_webapi_end = P2.new_publish_webapi_end

                  FROM T_PERF P

                  INNER JOIN #Perfs P2 ON P2.perf_no = P.perf_no

                  COMMIT TRANSACTION

                  UPDATE #Perfs

                  SET changes_made = 'Y'

            END TRY

            BEGIN CATCH

                  SET @err = ERROR_MESSAGE()

                  ROLLBACK TRANSACTION

                  RAISERROR(@err, 11, 2) WITH SETERROR

            END CATCH

      END

      SELECT * FROM #Perfs

      DROP TABLE #Perfs

      SET NOCOUNT OFF

END

ELSE

BEGIN

      SET @err = 'All date adjustment parameters are zero, so no changes ' + CASE WHEN @perf_nos IS NOT NULL THEN 'to the selected performances ' WHEN @season_nos IS NOT NULL THEN 'to the selected seasons ' END + 'were done.'

      RAISERROR(@err,11,2) WITH SETERROR

END

GO

Thanks