pulling list of patron who did NOT attend

Hi Tiffany -

This is where you start making your cascading 'lists of lists inside lists'. *grin* So I have a dynamic list that is anyone who DID attend. Then in my 'did not attend' I'd make a list of anyone who had tickets for that night, but were not on the attended list. You can also do this with careful application of has/does not have in one list, but since I already needed a list of attendees I just built off that list as I was being lazy. 

Does that make sense?

- Heather

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
)

Oh Heather!  :)

I think I see what you're saying.  I was having some trouble with my lists--- that referenced other lists--- that referenced other lists...etc.... 

I'm trying it now and it seems to be taking forever to run, but I'll let you know if it works!

As always, you rock.  Keep being awesome and thanks for your help.

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.

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.

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.

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
)

and we have multiple venues with multiple shows, so the performance IDs are not always in natural order. :(

Some variation of the above code could work, perhaps for a different list for each venue, or the like.

Don't give up! :-)

That looks so nice, but it gave me 0 constituents, though the query was successful.

I even tried playing with the perf_dt as I thought maybe it was a dynamic date issue, but I'm not seeing anything. 

I'll keep trying... we're so close!!! :)

Unknown said:

That looks so nice, but it gave me 0 constituents, though the query was successful.

Yeah, the code has a bug I wrote in haste. Where it has

perf_dt = perf_dt - 1

Try this:

perf_dt = REPLACE(CONVERT(VARCHAR(10), GETDATE() - 1, 111), '/', '-')
    + ' '
    + convert(varchar,DATEPART(HH, GETDATE() - 1))
    + ':'
    + convert(varchar,DATEPART(MI, GETDATE() - 1))

We use a dynamic list with the list criteria pieces Performance Date <= RunDate -1 and Performance Date >= RunDate -1 since Performance Date = RunDate-1 tends to be iffy.  This solves the issue of having to change the performance number each day in order to send out the triggered Wordfly email to people who attended the previous day.  Pairing this with Performance Name Like has always done the trick (so we don't send to patrons with other events like rentals).  Though we've not gone the extra step to separate out those who Attended from those who have not.