• Replies 5 replies
  • Subscribers 338 subscribers
  • Views 170 views
  • Users 0 members are here
Related

Confirm SQL Code for List Manager

Thanh Nguyen
$organization

Hi guys,

We are trying to make a list which shows a list of constituents with at least 1 order in ticket history, and their account creation date is within last 30 days from today. 

Could you please confirm if this SQL code is correct?

Select A.customer_no
From T_CUSTOMER A left join T_TICKET_HISTORY B
ON A.customer_no = B.customer_no
Where A.create_dt >= dateadd(DAY,-30,getdate()) and B.order_no is not null and A.inactive = 1
If it is not correct, could you please provide your inputs?
Thank you very much,
Tom Nguyen
Parents
  • $organization

    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

Reply
  • $organization

    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

Children
No Data