• 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 Brian. 

    I see that, but there doesn’t appear to be an event code for NOT attended…at least I don’t think there is.

     

    Here’s the query I have for finding attended seats (A fellow Tessitura user was kind enough to give this to me earlier this year):

     

    SELECT     c.season, c.perf_code, c.perf_dt, a.customer_no, SUM(b.paid_amt) AS paid_amt

    FROM         dbo.VS_PERF AS c WITH (NOLOCK) INNER JOIN

                          dbo.T_SUB_LINEITEM AS b WITH (NOLOCK) ON c.perf_no = b.perf_no INNER JOIN

                          dbo.T_ORDER_SEAT_HIST AS a WITH (NOLOCK) ON a.sli_no = b.sli_no AND a.perf_no = b.perf_no AND a.seat_no = b.seat_no

    WHERE     (a.event_code = 22) AND (a.customer_no > 0) AND (a.id IN

                              (SELECT     MAX(id) AS maxID

                                FROM          dbo.T_ORDER_SEAT_HIST

                                GROUP BY order_no, perf_no))

    GROUP BY c.season, c.perf_code, c.perf_dt, a.customer_no, a.event_code

     

     

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

     

    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!

Reply
  • $organization

    Thanks Brian. 

    I see that, but there doesn’t appear to be an event code for NOT attended…at least I don’t think there is.

     

    Here’s the query I have for finding attended seats (A fellow Tessitura user was kind enough to give this to me earlier this year):

     

    SELECT     c.season, c.perf_code, c.perf_dt, a.customer_no, SUM(b.paid_amt) AS paid_amt

    FROM         dbo.VS_PERF AS c WITH (NOLOCK) INNER JOIN

                          dbo.T_SUB_LINEITEM AS b WITH (NOLOCK) ON c.perf_no = b.perf_no INNER JOIN

                          dbo.T_ORDER_SEAT_HIST AS a WITH (NOLOCK) ON a.sli_no = b.sli_no AND a.perf_no = b.perf_no AND a.seat_no = b.seat_no

    WHERE     (a.event_code = 22) AND (a.customer_no > 0) AND (a.id IN

                              (SELECT     MAX(id) AS maxID

                                FROM          dbo.T_ORDER_SEAT_HIST

                                GROUP BY order_no, perf_no))

    GROUP BY c.season, c.perf_code, c.perf_dt, a.customer_no, a.event_code

     

     

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

     

    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!

Children
No Data