Confirm SQL Code for List Manager

Technically, doing an inner join on T_TICKET_HISTORY will accomplish the same thing as your LEFT OUTER/NOT NULL, but don't forget to do "SELECT DISTINCT", as you will get multiple rows for each match in T_TICKET_HISTORY, and List Manager won't like that.

You could also use existing list criteria to achieve, for example:

Hi Thomas,

Thanks for your inputs. How would you do, if you are interested to know how many "First Time" buyers whose accounts were created within last 30 days from today? 

Are those criteria staying the same? 

Please advise.

Best,

Tom Nguyen 

With date criteria you can use the 'relative' function which you can specify number of days or number of months before or after the run date (date the list is generated). As it's relative it will be 30 days ago from when you run the list. It should achieve the same as your SQL query and if you add those list criteria and then click show query it will show you the SQL code behind the list criteria you input.

There's some more info on the help system https://www.tessituranetwork.com/Help_System/Content/Criteria%20Sets/Date%20Value%20Considerations.htm

Here's an example and options with relative dates

Hi Tom,

Maybe I've overthought/overengineered this but this was something we were often trying to do - so I wrote a database view. It returns a single row from the secure, canned database view VS_TICKET_HISTORY based on the earliest performance date. We thought that using the create date of the constituent record wouldn't work for us as we're more interested in what their dfirst performance was rather than how long we'd "known" about them. After all, they may have been on our mailing list for a while before actually purchasing a ticket.

It was then easy enough for List Manager/Extraction elements to be created based on this view.

Happy to share if you're interested - email me at martin.keen@nida.edu.au.

I hope everyone is keeping well and in good spirits!

Martin