• Replies 3 replies
  • Subscribers 960 subscribers
  • Views 120 views
  • Users 0 members are here
Related

List of people who have come to musuem on any 2 sequential days

Ronan MacEwan (Past Member)
$organization

Hi All,

We're looking to make a list of people who 

a) bought a ticket to our gallery on any particular day in our (tessitura) history)

b) and bought a ticket for the following day

Any idea of how I might be able to generate this list? I can get people who have > 2 unique perfs, but they're not limited to side by side days.

Is this a job for SQL? And if so, what might that look like. Thanks for any tips.

Ronan

Parents
  • $organization

    I am not sure you can do this as a list.

    In SQL it would look something like this

    select distinct (day1.customer_no)
    from [dbo].[VS_TICKET_HISTORY] day1 with (NOLOCK)
    join [dbo].[VS_TICKET_HISTORY] day2 with (NOLOCK) on day1.customer_no=day2.customer_no
         and convert(Varchar(10),day1.perf_dt,102)=dateadd(day,-1,convert(Varchar(10),day2.perf_dt,102))

    This will list anyone who has an entry in Ticket History for performances where the dates are consecutive (day2 -1 day = day1)

    Mark

Reply
  • $organization

    I am not sure you can do this as a list.

    In SQL it would look something like this

    select distinct (day1.customer_no)
    from [dbo].[VS_TICKET_HISTORY] day1 with (NOLOCK)
    join [dbo].[VS_TICKET_HISTORY] day2 with (NOLOCK) on day1.customer_no=day2.customer_no
         and convert(Varchar(10),day1.perf_dt,102)=dateadd(day,-1,convert(Varchar(10),day2.perf_dt,102))

    This will list anyone who has an entry in Ticket History for performances where the dates are consecutive (day2 -1 day = day1)

    Mark

Children