• Replies 1 reply
  • Subscribers 961 subscribers
  • Views 103 views
  • Users 0 members are here
Related

Convert New-to-File to Dynamic List

Anh Le
$organization
Hi everyone,
 
I’m wondering if any of the very smart people on this listserv have figured out a way to turn the following NTF formula into a dynamic list? I’m not trained in SQL, and was hoping there was some way to do this between Analytics and List Manager.
 
COUNT ( [Constituent ID] ,
  IF (
    MIN( DDIFF ( [Days in Date] , [Days in First Performance Date] ) ) = 0
    , MAX ( [Constituent ID] )
    , NULL
    )
)
 
Thanks for your help!
 
  • $organization

    If you have someone with access to SSMS and a little SQL knowledge you can use what I created. It's a very basic local view and then I have some list criteria that are based on this table. So I can pull a list by first perf date, first perf prod season, first perf season, and/or first perf price type.

    The view is this:

    USE [impresario]
    GO

    /****** Object: View [dbo].[LVS_TICKET_HISTORY_FIRST_PERF] Script Date: 3/28/2022 1:19:49 PM ******/
    SET ANSI_NULLS ON
    GO

    SET QUOTED_IDENTIFIER ON
    GO

    CREATE VIEW [dbo].[LVS_TICKET_HISTORY_FIRST_PERF]
    AS
    SELECT *
    FROM (SELECT ROW_NUMBER() OVER (partition BY customer_no
    ORDER BY perf_dt) row_num, *
    FROM VS_TICKET_HISTORY) a
    WHERE a.row_num = 1 and role != 2
    GO