I have a procedure that updates attributes for consecutive years as a subscriber and a separate one for total years as a subscriber based on a view. It works great by pulling from package history in Tess as well as a table I imported from our legacy system. This year, our box office team has allowed folks to "defer" which means that they can keep their seats for FY22 even if they don't subscribe in FY21. The way our subscriptions manager did this was she paid the order to an invoice, then refunded the money on account, and then did a zero adjustment to the same on account payment method (I'm sure my finance department is like WTF!). What this means to me is that their FY21 packages are still showing up as package history. Any suggestions for how to adjust a view so that these folks don't populate for FY21 and still keep their seats? Below is an example of one of the views:
USE [impresario]GO
/****** Object: View [dbo].[LVS_TOTAL_SUB_YEARS] Script Date: 1/21/2021 2:56:34 PM ******/SET ANSI_NULLS ONGO
SET QUOTED_IDENTIFIER ONGO
ALTER VIEW [dbo].[LVS_TOTAL_SUB_YEARS]ASWITH yrs AS (SELECT ph.customer_no, s.fyear FROM dbo.T_PACKAGE_HISTORY AS ph INNER JOIN dbo.TR_SEASON AS s ON ph.season = s.id), legacy AS (SELECT customer_no, fiscal_yr FROM dbo.LT_PKG_HIST_LEGACY), fiscalyrs AS (SELECT customer_no, fyear AS fiscal_yr FROM yrs AS yrs_1 UNION SELECT customer_no, fiscal_yr FROM legacy AS legacy_1) SELECT customer_no, COUNT(*) AS total_sub_yrs, MIN(fiscal_yr) AS first_yr, MAX(fiscal_yr) AS last_yr FROM fiscalyrs AS fiscalyrs_1 GROUP BY customer_noGO
Thanks in advance!
You could exclude all packages with a season FY21 entirely by inserting a where clause like below.
WITH yrs AS (SELECT ph.customer_no, s.fyear FROM dbo.T_PACKAGE_HISTORY AS ph INNER JOIN dbo.TR_SEASON AS s ON ph.season = s.idwhere s.fyear <> 2021)
But it sounds like you will have both people who subscribe to that season and people who defer, is that right?
Yes, we will have both those who subscribe and those who defer. I am debating between having my subscription manager actually remove those orders since these folks are marked as deferred in plans or figuring out how to update my code to exclude those folks via a list but only for FY21. It seems like the cleaner, more accurate thing to do would be to get rid of the orders, right?
It depends on what the reasons are for keeping those orders. If it's to keep the seat on hold for use for FY22 subscription rollover then that might be a good reason to keep them but if the constituents are already tagged in some other way then perhaps it might not be needed?
I'm leaning toward removing the orders. We have these folks listed as deferred elsewhere so we will be able to find them and can execute a rollover set from their FY20 orders with seemingly no issue.