• Replies 17 replies
  • Subscribers 338 subscribers
  • Views 196 views
  • Users 0 members are here
Related

pulling list of patron who did NOT attend

Tiffany Elliott (Past Member)
$organization

Hi!

I've had such good luck on the forums...but this one might be a little hard for me to explain, so bear with me. I apologize in advance if this too broad or if I'm not providing enough info to help troubleshoot.

We send a daily email to all patrons who attended the performance the night before (perf_code from a local view which joins T_ORDER_SEAT_HIST and T_SUB_LINEITEM) .  These lists are dynamic and are automatically re-generated every day through wordfly (or email application). The problem is, I'm not sure how to identify patrons whose tickets were NOT scanned (assumedly because they did not attend the show).  We'd like to send them a different message and give them the opportunity to exchange their tickets into something else.

Do any other organizations ever send messages to patrons who did not have their tickets scanned and therefore, are assumed to have missed a performance?

Any help would be great.  Otherwise, I'll have to begin training carrier pigeons.  :-3

Thanks,

Tiffany

Parents
  • $organization

    Thanks Heather!! J

    Yes, this view has been very helpful!

     

    I think I just don’t know the syntax to suppress people from the dynamic list.

    Do you do something special with the criteria to pull people who do not have the “attended” event code but were ticketed for the performance?

     

     

     

    From: Tessitura Technical Forum [mailto:forums-technical@tessituranetwork.com] On Behalf Of Heather Kraft
    Sent: Tuesday, October 01, 2013 2:57 PM
    To: Tiffany Evans
    Subject: RE: [Tessitura Technical Forum] pulling list of patron who did NOT attend

     

    Hi Tiffany -

    We've made a custom list criteria element going off a view (although the view is not necessary, we use it for a couple of other things as well). The view references the event code that Brian highlighted.

    Then we use that to pull (or suppress) people from our dynamic lists that we pull the next day. We actually call our subscribers who missed a show (vs email). But either way, it lets us suppress people in a 'Thank you for coming' when they didn't actually make it to the show. At least we're not rubbing salt in the wound. :D

    Have fun!

    Heather

    From: Brian Wilbur Grundstrom <bounce-brianwilburgrundstrom7163@tessituranetwork.com>
    Sent: 10/1/2013 1:25:33 PM

    An event code in T_ORDER_SEAT_HIST of 22 indicates attended.

     

    From: Tessitura Technical Forum [mailto:forums-technical@tessituranetwork.com] On Behalf Of Tiffany Evans
    Sent: Tuesday, October 01, 2013 2:17 PM
    To: Brian W. Grundstrom
    Subject: [Tessitura Technical Forum] pulling list of patron who did NOT attend

     

    Hi!

    I've had such good luck on the forums...but this one might be a little hard for me to explain, so bear with me. I apologize in advance if this too broad or if I'm not providing enough info to help troubleshoot.

    We send a daily email to all patrons who attended the performance the night before (perf_code from a local view which joins T_ORDER_SEAT_HIST and T_SUB_LINEITEM) .  These lists are dynamic and are automatically re-generated every day through wordfly (or email application). The problem is, I'm not sure how to identify patrons whose tickets were NOT scanned (assumedly because they did not attend the show).  We'd like to send them a different message and give them the opportunity to exchange their tickets into something else.

    Do any other organizations ever send messages to patrons who did not have their tickets scanned and therefore, are assumed to have missed a performance?

    Any help would be great.  Otherwise, I'll have to begin training carrier pigeons.  :-3

    Thanks,

    Tiffany




    This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Technical Forum. You may reply to this message to post to the Technical forum or visit the site to search, read and post to the forums. In the interest of keeping the forum posts from becoming cluttered, we encourage you to delete previous message text from your reply before sending. Thank you!




    This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Technical Forum. You may reply to this message to post to the Technical forum or visit the site to search, read and post to the forums. In the interest of keeping the forum posts from becoming cluttered, we encourage you to delete previous message text from your reply before sending. Thank you!

  • $organization in reply to Tiffany Elliott (Past Member)

    Unknown said:
    Do you do something special with the criteria to pull people who do not have the “attended” event code but were ticketed for the performance?

    FYI, in case it's helpful, the code below will find customer_no's that had TPD status tickets to a perf, but weren't scanned, i.e. didn't attend, as BWG described above.

    select distinct o.customer_no
    from T_SUB_LINEITEM li (nolock)
    join T_ORDER o (nolock)
        on o.order_no = li.order_no
    join T_ORDER_SEAT_HIST sh (nolock)
        on li.order_no = li.order_no
    where li.sli_status in (3,12)
    and o.customer_no > 0
    and li.perf_no = 15266 -- subsititute your perf_no
    and sh.event_code = 2 -- printed
    and not exists (
        select 1
        from T_ORDER_SEAT_HIST x
        where x.order_no = li.order_no
        and x.event_code = 22 -- attended
    )

  • $organization in reply to Chris Jensen

    Thanks Chris.

    I like this except I would still need to go into the dynamic list and manually change the perf_no.

    Otherwise, this is lovely.

  • $organization in reply to Tiffany Elliott (Past Member)

    I'm unfamiliar with your org; Do you have more than one perf per day? Depending on a few things, your manual list code could find the correct perf_no each day, too.

  • $organization in reply to Chris Jensen

    Sorry, yes...sometimes we do. We have multiple venues and the venue is often considered a part of the performance name to help with setup and help with quickly identifying the show.

    For example:  YYY0930E would be the performance name for the show that was in the YYY theatre on 9/30 in the evening.

  • $organization in reply to Tiffany Elliott (Past Member)

    Unknown said:

    Sorry, yes...sometimes we do. We have multiple venues and the venue is often considered a part of the performance name to help with setup and help with quickly identifying the show.

    For example:  YYY0930E would be the performance name for the show that was in the YYY theatre on 9/30 in the evening.

    Assuming you'd run the code the next day you code make it something like:

    select distinct o.customer_no
    from T_SUB_LINEITEM li (nolock)
    join T_ORDER o (nolock)
        on o.order_no = li.order_no
    join T_ORDER_SEAT_HIST sh (nolock)
        on li.order_no = li.order_no
    where li.sli_status in (3,12)
    and o.customer_no > 0
    and li.perf_no = (
        select perf_no
        from T_PERF
        where facility_no = 123 -- subsititute your facility_no
        and right(perf_code,1) = 'E' -- this kind of thing, or maybe time_slot, to confine to evening perf of "YYY0930E"
        and perf_dt = perf_dt - 1 -- i.e. yesterday
    )
    and sh.event_code = 2 -- printed
    and not exists (
        select 1
        from T_ORDER_SEAT_HIST x (nolock)
        where x.order_no = li.order_no
        and x.event_code = 22 -- attended
    )

Reply
  • $organization in reply to Tiffany Elliott (Past Member)

    Unknown said:

    Sorry, yes...sometimes we do. We have multiple venues and the venue is often considered a part of the performance name to help with setup and help with quickly identifying the show.

    For example:  YYY0930E would be the performance name for the show that was in the YYY theatre on 9/30 in the evening.

    Assuming you'd run the code the next day you code make it something like:

    select distinct o.customer_no
    from T_SUB_LINEITEM li (nolock)
    join T_ORDER o (nolock)
        on o.order_no = li.order_no
    join T_ORDER_SEAT_HIST sh (nolock)
        on li.order_no = li.order_no
    where li.sli_status in (3,12)
    and o.customer_no > 0
    and li.perf_no = (
        select perf_no
        from T_PERF
        where facility_no = 123 -- subsititute your facility_no
        and right(perf_code,1) = 'E' -- this kind of thing, or maybe time_slot, to confine to evening perf of "YYY0930E"
        and perf_dt = perf_dt - 1 -- i.e. yesterday
    )
    and sh.event_code = 2 -- printed
    and not exists (
        select 1
        from T_ORDER_SEAT_HIST x (nolock)
        where x.order_no = li.order_no
        and x.event_code = 22 -- attended
    )

Children